dbms_shared_pool.purge 清理某個SQL執行計劃

lhrbest發表於2016-12-06


dbms_shared_pool.purge 清理某個SQL執行計劃





在日常管理中,經常有讓sql重新解析的需求,比如說使用了bind peeking,第一次繫結特定值的時候執行計劃走的特別糟,因為繫結變數導致之後的語句不作重新解析,重用了最差的執行計劃,這時候我們希望重新解析來得到一個相對好的執行計劃,常見的方法有:


a.alter system flush shared_pool;

b.對語句中的物件做個ddl ; --只會重新生成一個子遊標

c.重新收集統計資訊

但是這些操作的影響都比較大,因此oracle在10.2.0.4後提供了個dbms_shared_pool.purge的方法,能夠將某個sql的shared  cursor從共享池中清除,這樣只會對單個sql產生影響.

 

測試:

 

10.2.0.4:

 

SQL> create table test(id int);

表已建立。

SQL> select * from test;

未選定行

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS

---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          1           1


QL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');

PL/SQL 過程已成功完成。

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          1           1

 

可以看到purge並沒有成功,為了進一步證實,再做一遍查詢

 

SQL> select * from test;

未選定行

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000040229F039E0 1689401402          2           2

 

executions和parse_calls增加,說明前面的parse確實沒生效

 

SQL> alter session set events '5614566 trace name context forever';

會話已更改。

SQL> exec dbms_shared_pool.purge('0000040229F039E0,1689401402','C');

PL/SQL 過程已成功完成。

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from test%';

未選定行

 

參考 metalink Doc ID:  751876.1  
10.2.0.4預設不開啟,要靠event 5614566或者補丁5614566來啟用

 

11g:

 

11g>create table test_purge(id int);

 

11g>select * from test_purge;

 

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';

ASH_VALUE
---------
683003671

11g>exec dbms_shared_pool.purge('215E2F78,3683003671','C');

PL/SQL 過程已成功完成。

11g>select address,hash_value from v$sql where sql_text like 'select * from test%';



清除掉shared pool中某條sql語句方法

原文地址:

在論壇中看到一個帖子,如何清除掉shared pool中某條sql語句,如果是在10g以前的版本,那估計會比較麻煩,為了刪除一條sql語句記錄,需要清空整個shared pool,在10g中提供了新的dbms_shared_pool包可以實現該功能(如果該包沒有安裝,可以透過?/rdbms/admin/dbmspool.sql安裝),下面我透過在11g中試驗證明該問題
1.資料庫版本資訊
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2.模擬一條sql語句
SQL> create table xff as
  2  select * from dba_tables
  3  where rownum<10;
 
表已建立。
 
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
3.打破神話一:許可權操作
對錶進行許可權操作,可以清除該表在shared pool中關於該表記錄
SQL> grant select on xff to chf;
 
授權成功。
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
 
SQL> revoke select on xff from chf;
 
撤銷成功。
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
證明grant和revoke操作沒有清除shared pool中關於該表的sql語句


4.打破神話二:ddl操作
對錶進行ddl操作,可以清除該表在shared pool中關於該表記錄
SQL> alter table xff add  owner1 varchar2(100);
 
表已更改。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
事實證明ddl操作不能達到預期效果,沒有清除掉這條sql語句


5.重新整理shared pool
SQL> alter system flush shared_pool
  2  ;
 
系統已更改。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
未選定行
把整個shared pool都重新整理了,自然其中的一條sql語句也沒有了,在10g前只能透過這種方法實現


6.使用dbms_shared_pool.purge
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C');
 
PL/SQL 過程已成功完成。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
未選定行
試驗證明在shared pool中清除了一條sql記錄


7.關於dbms_shared_pool.purge引數說明
purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
--    name
--      The name of the object to keep.  There are two kinds of objects:
--      PL/SQL objects, triggers, sequences, types and Java objects,
--      which are specified by name, and
--      SQL cursor objects which are specified by a two-part number
--      (indicating a location in the shared pool).  For example:
--        dbms_shared_pool.keep('scott.hispackage')
--      will keep package HISPACKAGE, owned by SCOTT.  The names for
--      PL/SQL objects follows SQL rules for naming objects (i.e.,
--      delimited identifiers, multi-byte names, etc. are allowed).
--      A cursor can be keeped by
--        dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
--      The complete hexadecimal address must be in the first 8 characters.
--      The value for this identifier is the concatenation of the
--      'address' and 'hash_value' columns from the v$sqlarea view.  This
--      is displayed by the 'sizes' call above.
--      Currently 'TABLE' and 'VIEW' objects may not be keeped.
--
--    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 keep 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
--      If and only if the first argument is a cursor address and hash-value,
--        the flag parameter should be set to 'C' (or 'c').
--
--    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.
10.2.0.4中bug(DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1])
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 09:59:03 2012
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select count(*) from v$sqlarea;
 
  COUNT(*)
----------
      2886
 
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
BEGIN dbms_shared_pool.purge('000000007C9BAC90,418614462','C'); END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
 
SQL> @?/rdbms/admin/dbmspool.sql
 
Package created.
 
 
Grant succeeded.
 
 
View created.
 
 
Package body created.
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> alter session set events '5614566 trace name context forever';
 
Session altered.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
000000007C9BAC90  418614462 select count(*) from v$sqlarea
 
SQL> exec dbms_shared_pool.purge('000000007C9BAC90,418614462','C');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2   WHERE SQL_TEXT LIKE 'select count(*) from v$sqlarea';
 
no rows selected




刪除表,也不能清除shared pool中的sql語句
SQL> select count(*) from xff;
 
  COUNT(*)
----------
         9
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
 
SQL> drop table xff;
 
表已刪除。
 
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'select % xff';
 
ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff






About Me

...............................................................................................................................

● 本文作者:小麥苗,只專注於資料庫的技術,更注重技術的運用

● 本文在itpub(http://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 聯絡我請加QQ好友(646634621),註明新增緣由

● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號:xiaomaimiaolhr,掃描右邊的二維碼加入小麥苗的QQ群,學習最實用的資料庫技術。

dbms_shared_pool.purge 清理某個SQL執行計劃
DBA筆試面試講解
歡迎與我聯絡

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

相關文章