將指定SQL的執行計劃從共享池刪除

如果Oracle的優化器產生了某種錯誤的執行計劃，或者我們希望Oracle對於某個SQL重新進行分析，那麼就需要這個SQL的執行計劃在共享池中過期，而簡單的方法在10.2.0.4以後才出現。

對於以前的版本而言，最顯而易見的方法莫過於直接重新整理共享池，但是如果是資料庫中絕大部分的SQL都存在問題，那麼這種方法無可厚非，也可能是見效最快的方法，而如果資料庫中僅僅是個別的SQL存在問題，那麼這種方法就過於暴力了。

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select count(*) from dual';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 1 1 0

SQL> select count(*) from v$sqlarea;

COUNT(*)
----------
3061

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select count(*) from dual';

no rows selected

SQL> select count(*) from v$sqlarea;

COUNT(*)
----------
37

為了一個SQL而清空整個共享池，這個代價確實太大了，何況對於一個繁忙的OLTP系統而言，這個重新整理共享池的操作所帶來的風險和後果與直接關閉資料庫相比，也沒有太大的差別。

那麼有沒有細粒度一點的辦法呢，其實方法有很多，相關表上任何的DDL都會導致SQL執行計劃的失效，但是DDL本身風險就畢竟高，如果想要對系統影響最小，那麼這個DDL就非GRANT莫屬。只需要當前使用者將這個表的許可權授權給自己，就可以達到想要的效果：

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select count(*) from dual';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 1 1 0

SQL> select 1 from dual;

1
----------
1

SQL> select * from dual;

D
-
X

SQL> select 'a' from dual;

'
-
a

SQL> select count(1) from dual;

COUNT(1)
----------
1

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 1 1 1 0
40p7rprfbt1as 00000000B69BDC38 3703342424 1 1 1 0
520mkxqpf15q8 00000000B6DD9610 2866845384 1 1 1 0
ak90gdq0udv37 00000000B6E3C6B0 2175200359 2 2 2 1
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 1 1 0
a5ks9fhw2v9s1 00000000B698DA88 942515969 1 1 1 0
800hwktjz3zuc 00000000B6999268 1676803916 1 1 1 0

7 rows selected.

SQL> grant select on dual to sys;
grant select on dual to sys
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

SQL> grant select on dual to public;

Grant succeeded.

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 2 1 2 0
ak90gdq0udv37 00000000B6E3C6B0 2175200359 2 2 2 1

對於其他使用者而言，都可以使用將表的查詢許可權授權給OWNER本身的方法，但是測試使用者本身為SYS，因此需要其他使用者授權，方便起見使用了授權給PUBLIC的方式。可以看到，這種方式同樣可以生效，但是仍然存在打擊面過大的問題。對於系統中一個頻繁訪問的表，很可能這個授權的操作，導致少則幾十，多則幾百個SQL都是失效，這個風險仍然不可小覷。

那麼對於就沒有一個可以將粒度控制在SQL本身上的方法嗎？在11g中，Oracle的DBMS_SHARED_POOL包新增了PURGE功能，可以完美的解決這個問題，這個方法在10.2.0.4和10.2.0.5補丁集中也被新增進來，使得10.2的高版本同樣可以實現這個功能，使用方法很簡單：

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select count(*) from dual';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 2 1 1

SQL> select 1 from dual;

1
----------
1

SQL> select * from dual;

D
-
X

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 3 1 3 0
520mkxqpf15q8 00000000B6DD9610 2866845384 1 2 1 1
ak90gdq0udv37 00000000B6E3C6B0 2175200359 3 2 3 1
4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 2 1 1
a5ks9fhw2v9s1 00000000B698DA88 942515969 1 2 1 1

SQL> exec dbms_shared_pool.purge('00000000B6C61FC0,4094900530', 'c')

PL/SQL procedure successfully completed.

SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where lower(sql_text) like '%dual%';

SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
gr7s3j0cg8pr6 00000000B6A5A470 418666214 4 1 4 0
520mkxqpf15q8 00000000B6DD9610 2866845384 1 2 1 1
ak90gdq0udv37 00000000B6E3C6B0 2175200359 3 2 3 1
a5ks9fhw2v9s1 00000000B698DA88 942515969 1 2 1 1

過程PURGE的第一個引數為V$SQLAREA中用逗號分隔的ADDRESS列和HASH_VALUE列的值，第二個引數’c’表示PURGE的物件是CURSOR，不過實際上這裡可以使用除了P（PROCEDURE/FUNCTION/PACKAGE）、T（TYPE）、R（TRIGGER）和Q（SEQUENCE）的任何值

使用這種方法，就可以精確的將一個SQL從共享池中刪除，從而使得Oracle為這個SQL重新生成執行計劃。這種方法只針對單個SQL語句，使得解決問題的同時不會造成任何的誤傷。

不過需要注意一點，在10.2.0.4中，雖然PURGE過程已經存在，但是要使這個過程可以真正的生效，還必須設定一個EVENT：

SQL> alter system set event = '5614566 trace name context forever' scope = spfile;

System altered.

設定EVENT後需要重啟，DBMS_SHARED_POOL的PURGE才可以生效。也就是說，除非提前進行過設定，否則這個PURGE的功能對於一個產品環境而言，必須在10.2.0.5以上版本才可以使用。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23071790/viewspace-721706/，如需轉載，請註明出處，否則將追究法律責任。