SQL的reload以及Invalidations

  關於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