dbms_shared_pool.purge 清理某個SQL執行計劃
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群,學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2129896/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 清除shared pool中某條sql的執行計劃SQL
- sql 執行計劃SQL
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 如何清除某條SQL在庫快取中的執行計劃SQL快取
- 獲取SQL執行計劃SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer
- 檢視sql執行計劃SQL
- 產生AWR及單個sql的執行計劃SQL
- sql執行計劃是否改變SQL
- sql的執行計劃 詳解SQL
- sql執行計劃基本命令SQL
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- 決定一個SQL執行效率的是執行計劃, 而不是SQL的寫法SQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- 如何檢視SQL的執行計劃SQL
- 檢視SQL的執行計劃方法SQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- baseline固定SQL執行計劃SQL
- 控制執行計劃之-SQL Profile(一)SQL
- 獲取SQL執行計劃的方式:SQL
- 使用PL/SQL檢視執行計劃SQL
- 用sql profile來固定執行計劃SQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 檢視sql執行計劃--set autotraceSQL
- 清除SQL語句的執行計劃SQL
- Oracle手動固定SQL執行計劃OracleSQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 透過10053事件分析一個SQL執行計劃事件SQL
- 執行計劃-1:獲取執行計劃
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- SQL最佳化 —— 讀懂執行計劃SQL