Using dbms_shared_pool.purge to remove a single task from the library cache
因?yàn)樵谙到y(tǒng)繁忙的時(shí)侯 使用 alter system flush shared_pool? 是很危險(xiǎn)的， 在oracle 10.2.0.4 以及 11g 有了新的方法。提供的DBMS_SHARED_POOL.PURGE 程序來(lái)完成。
不過(guò)需要注意一點(diǎn)，在10.2.0.4中，雖然PURGE過(guò)程已經(jīng)存在，但是要使這個(gè)過(guò)程可以真正的生效，還必須設(shè)置一個(gè)EVENT：
SQL> alter system set event = '5614566 trace name context forever' scope = spfile;
System altered.
設(shè)置EVENT后需要重啟，DBMS_SHARED_POOL的PURGE才可以生效。也就是說(shuō)，除非提前進(jìn)行過(guò)設(shè)置，否則這個(gè)PURGE的功能對(duì)于一個(gè)產(chǎn)品環(huán)境而言，必須在10.2.0.5以上版本才可以使用。
Porcedure的說(shuō)明如下 procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);? ?Explanation: Purge the named object or particular heap(s) of the object.??Input arguments:?
? name: The name of the object to purge.
??????? There are two kinds of objects:?
???????? PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
???????? SQL cursor objects which are specified by a twopart number. The value for this identifier
???????? is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view. ? flag: This is an optional parameter. If the parameter is not specified,?
??????? the package assumes that the first parameter is the name of a?
??????? package/procedure/function and will resolve the name. Otherwise,?
??????? the parameter is a character string indicating what kind of object?
??????? to purge the name identifies. The string is case insensitive.?
??????? The possible values and the kinds of objects they indicate are?
??????? given in the following table:? ??????? Value Kind of Object to keep?
??????? ----- ----------------------?
??????????? P package/procedure/function?
??????????? Q sequence?
??????????? R trigger?
??????????? T type?
?????????? JS java source?
?????????? JC java class?
?????????? JR java resource?
?????????? JD java shared data?
??????????? C cursor? ? heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.?
???????? 1<0 | 1<6 => hex 0x41 => decimal 65. so specify heaps=>65.?
???????? Default is 1 i.e heap 0 which means the whole object will be purged.? eg：
SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C'); ? In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.
This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.
Here is an example is using?dbms_shared_pool.purge?to remove RAM for a specific cursor from the shared pool library cache:
SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6?
PL/SQL procedure successfully completed.
This would actually not work against a cursor which is currently executing.(pinned)
Session 1:
=========
Do a massive Merge Join Cartesian
select * from dba_objects a, dba_objects b, dba_objects c;
Session 2:
=========
Identify the sql address and hash value and try to purge the cursor..
exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs
and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1
Session 3
==========
select event,p1,p2 from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1
The p1 value here is the Hash value of the cursor we are trying to flush.
From the short stack of the process which is executing the purge API a function called?kxsPurgeCursor?is called which would try to take a mutex (since?_kks_use_mutex_pin?is TRUE by default) The purge completes only after you cancel the sql in session 1 and exit from the same or kill the session executing the SQL.
????
?? Set the event 5614566 in the init.ora to turn purge on.
??? event=”5614566 trace name context forever”
??? SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;
??? ADDRESS HASH_VALUE
??? ——– ———-
??? 2671F27C 3599690174
??? SQL> exec dbms_shared_pool.purge(’2671F27C,3599690174′,’C');
??? PL/SQL procedure successfully completed.
??? SQL> select address, hash_value from v$sqlarea where sql_text = ’select * from dept’;
??? no rows selected
備注：
如果沒(méi)有dbms_shared_pool , 可以通過(guò) $ORACLE_HOME/rdbms/admin/dbmspool.sql 來(lái)創(chuàng)建。
在10.2.0.2/3 也提供了相應(yīng)的patch?There exists patches for some platforms in 10.2.0.2 and 10.2.0.3 downloadable as Patch 5614566. 參考：
457309.1? How To Flush an Object out the Library Cache [SGA]
751876.1? DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 http://www.dba-oracle.com/t_flush_session_dbms_shared_pool_purge.htm ? 通常我們想單獨(dú)的從library cache中purge一個(gè)執(zhí)行計(jì)劃，其實(shí)是為了讓SQL在重新進(jìn)行一次解析. 要達(dá)到這個(gè)目的，在10G之前，可以用 comment on table/ grant&revoke 來(lái)實(shí)現(xiàn)這樣的功能。基本原理就是一個(gè)表進(jìn)行了DDL操作后，依賴于它的執(zhí)行計(jì)劃就會(huì)自動(dòng)失效. 下面是一個(gè)演示：
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations?
from v$sqlarea?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
?
對(duì)于其他用戶而言，都可以使用將表的查詢權(quán)限授權(quán)給OWNER本身的方法，但是測(cè)試用戶本身為SYS，因此需要其他用戶授權(quán)，方便起見(jiàn)使用了授權(quán)給PUBLIC的方式。
可以看到，這種方式同樣可以生效，但是仍然存在打擊面過(guò)大的問(wèn)題。對(duì)于系統(tǒng)中一個(gè)頻繁訪問(wèn)的表，很可能這個(gè)授權(quán)的操作，導(dǎo)致少則幾十，多則幾百個(gè)SQL都是失效，
這個(gè)風(fēng)險(xiǎn)仍然不可小覷。
轉(zhuǎn)載于:https://www.cnblogs.com/princessd8251/p/3843630.html
總結(jié)
以上是生活随笔為你收集整理的Using dbms_shared_pool.purge to remove a single task from the library cache的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 借入单的后续处理-借入归还
- 下一篇： BIND_MISMATCH导致过多VER