关于SQL 的 游标版本不高，但 reload 以及 Invalidations 次数相对比较多。基本上是每一次 Invalidations 便伴随着一次 reload. 关于 load 以及 Invalidations 的说明如下，
Invalidations - An invalidation is a measure of the number of times a cached cursor is deleted from the cache because it is no longer valid. A cursor is invalidated because something has changed such that the copy of the cursor in memory is not valid any more. For example, regathering the statistics on an object or modifying a table definition is enough to invalidate a cursor for a query that is based on that object. When a cursor is invalidated, any sessions wanting to use the cursor need to wait for a valid version to be loaded.
Reloads - Reload is a count of the number of times a cursor that previously existed in the cache, was searched for, found to not be there (because it had aged out etc) and then had to be re-compiled and re-loaded in to the library cache. High reloads are a bad thing because they indicate that you are doing work that you would not have had to do if your cache was setup appropriately so as not to remove the cursor in the first place.
目前看起来是统计信息收集引起，导致当每次统计收集后便发生一次 load 。
以下，关于keep cursor
实验，看起来
keep
后还是会发生
reload
情况。
LOADS 1次， INVALIDATIONS 0次 select sql_id, LOADS,INVALIDATIONS,FIRST_LOAD_TIME,LAST_LOAD_TIME,address,hash_value from v$sql where sql_id='adh4jbfb4q8bt'; SQL_ID LOADS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME ADDRESS HASH_VALUE -------------- ---------- ------------- -------------------------------------- -------------------------------------- ---------------- ---------- adh4jbfb4q8bt 1 0 2020-12-30/00:06:19 2020-12-30/00:06:19 000000019DC2D5B8 2521506169 2. 将 cusor keep 起来： SQL> exec DBMS_SHARED_POOL.KEEP('000000019DC2D5B8,2521506169','C'); PL/SQL procedure successfully completed. 3. 做一次统计信息收集，让游标立即失效 ,no_invalidate=>FALSE 。 SQL> delete from cwdtest.TEST_STA1 where rownum<=5000; 5000 rows deleted. SQL> commit; Commit complete. SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'CWDTEST',tabname=>'TEST_STA1',cascade=>true,force=>true,no_invalidate=>FALSE); PL/SQL procedure successfully completed. 4. 再次执行 sql 之后还是会出现 INVALIDATIONS ，并再 reload 一次。 select sql_id, LOADS,INVALIDATIONS,FIRST_LOAD_TIME,LAST_LOAD_TIME,address,hash_value from v$sql 3 where sql_id='adh4jbfb4q8bt'; SQL_ID LOADS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME ADDRESS HASH_VALUE -------------------------- ---------- ------------- -------------------------------------- -------------------------------------- ---------------- ---------- adh4jbfb4q8bt 2 1 2020-12-30/00:06:19 2020-12-30/00:15:51 000000019DC2D5B8 2521506169 SQL> SQL> / SQL_ID LOADS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME ADDRESS HASH_VALUE -------------------------- ---------- ------------- -------------------------------------- -------------------------------------- ---------------- ---------- adh4jbfb4q8bt 2 1 2020-12-30/00:06:19 2020-12-30/00:15:51 000000019DC2D5B8 2521506169 5. 再次做一次统计信息收集，游标不失效 no_invalidate=>TRUE 。 SQL> delete from cwdtest.TEST_STA1 where rownum<=5000; 5000 rows deleted. SQL> commit; Commit complete. SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'CWDTEST',tabname=>'TEST_STA1',cascade=>true,force=>true,no_invalidate=>TRUE); PL/SQL procedure successfully completed. 6. 再次执行 sql 后不会出现 INVALIDATIONS 且不会 reload. select sql_id, LOADS,INVALIDATIONS,FIRST_LOAD_TIME,LAST_LOAD_TIME,address,hash_value from v$sql 3 where sql_id='adh4jbfb4q8bt'; SQL_ID LOADS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME ADDRESS HASH_VALUE -------------------------- ---------- ------------- -------------------------------------- -------------------------------------- ---------------- ---------- adh4jbfb4q8bt 2 1 2020-12-30/00:06:19 2020-12-30/00:15:51 000000019DC2D5B8 2521506169
