如果Oracle的优化器产生了某种错误的执行计划,或者我们希望Oracle对于某个SQL重新进行分析,那么就需要这个SQL的执行计划在共享池中过期。
在业务时段不可能使用alter system flush shared_pool的方式
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where lower(sql_text) like '%test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
bmgz3h5qtng75 000000007866D210 1838824677 1 4 1
3
0kxrz1ugtjpkm 00000000783DD930 2677593683 1 1 1
0
bb926a5dcb8kr 0000000078941CE0 1522901591 21 2 21
0
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
a5ks9fhw2v9s1 0000000079701150 942515961 1 1 1
0
SQL> exec dbms_shared_pool.purge('0000000079701150,942515961','c');
PL/SQL procedure successfully completed.
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where lower(sql_text) like '%test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
bmgz3h5qtng75 000000007866D210 1838824677 1 4 1
3
0kxrz1ugtjpkm 00000000783DD930 2677593683 2 1 2
0
bb926a5dcb8kr 0000000078941CE0 1522901591 21 2 21
0
在业务时段不可能使用alter system flush shared_pool的方式
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where lower(sql_text) like '%test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
bmgz3h5qtng75 000000007866D210 1838824677 1 4 1
3
0kxrz1ugtjpkm 00000000783DD930 2677593683 1 1 1
0
bb926a5dcb8kr 0000000078941CE0 1522901591 21 2 21
0
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
a5ks9fhw2v9s1 0000000079701150 942515961 1 1 1
0
SQL> exec dbms_shared_pool.purge('0000000079701150,942515961','c');
PL/SQL procedure successfully completed.
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where lower(sql_text) like '%test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS
------------- ---------------- ---------- ---------- ---------- -----------
INVALIDATIONS
-------------
bmgz3h5qtng75 000000007866D210 1838824677 1 4 1
3
0kxrz1ugtjpkm 00000000783DD930 2677593683 2 1 2
0
bb926a5dcb8kr 0000000078941CE0 1522901591 21 2 21
0
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26870952/viewspace-2142218/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26870952/viewspace-2142218/