11.2.0.3中Oracle引入了_cursor_obsolete_threshold隐藏参数，默认为100，表示一个parent cursor最多可以有100个child cursor;如果SQL需要第101个child cursor,则将该parent cursor and its 100 child cursor设置为obsoleted,并且重新生成新的parent cursor. 但是那些过期的child cursor不会从v$sql中消失，dbms_shared_pool.purge也无法将这些过期的child cursor flush出去,Oracle正在fix这个bug.
eg:
-bash-3.2$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-11-28_12-40-02PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2012-11-28_12-40-02PM.txt
——————————————————————————–
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 13923374 : applied on Thu Sep 20 14:10:21 CST 2012
Unique Patch ID: 14893407
Patch description: “Database Patch Set Update : 11.2.0.3.3 (13923374)”
Created on 14 Jun 2012, 23:16:11 hrs PST8PDT
Sub-patch 13696216; “Database Patch Set Update : 11.2.0.3.2 (13696216)”
Sub-patch 13343438; “Database Patch Set Update : 11.2.0.3.1 (13343438)”
From v$sqlarea only return one row:
SQL> select address,hash_value from v$sqlarea where sql_id=’27svyp3s52cu0′
ADDRESS HASH_VALUE
—————- ———-
0700001DBB270900 4031853376
you can only purge this cursor:
SQL> exec dbms_shared_pool.purge(‘0700001DBB270900,4031853376′,’C’);
PL/SQL procedure successfully completed.
but if you want to purge like this:
SQL> select distinct address,hash_value from v$sql where sql_id=’27svyp3s52cu0′;
ADDRESS HASH_VALUE
—————- ———-
0700001DBE384CD8 4031853376
0700001DBD3119A0 4031853376
0700001DBC063368 4031853376
0700001DBDB39548 4031853376
0700001DBC993898 4031853376
0700001DBC75A2E8 4031853376
0700001DBCC34B20 4031853376
0700001DBB270900 4031853376
0700001DBCFFE730 4031853376
0700001DBCB15A70 4031853376
0700001DBFA741A8 4031853376
0700001DBC4FEA60 4031853376
0700001DBE3F49F8 4031853376
0700001DBB4D1938 4031853376
14 rows selected
SQL> exec dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’);
begin dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’); end;
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1
SQL> exec dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’);
BEGIN dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’); END;
*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1
extended statistics for this cursor:
Shared Cursors Statistics Total Parses 29,918 Hard Parses 1,665 Child Cursors 1,478 Loaded Plans 1,478 Invalidations 1,473 Largest Cursor Size (KB) 292.43 All Cursor Size (KB) 399,376.98 First Load Time Nov 23, 2012 4:26:13 AM GMT+08:00 Last Load Time Nov 28, 2012 1:10:10 PM GMT+08:00 Execution Statistics Total Per Execution Per Row Executions 1,264,476 1 1.00 Elapsed Time (sec) 18,717.60 0.01 0.01 CPU Time (sec) 1,087.67 <0.01 <0.01 Buffer Gets 114,482,137 90.54 90.54 Disk Reads 2,241,885 1.77 1.77 Direct Writes 0 0.00 0.00 Rows 1,264,476 1.00 1 Fetches 0 0.00 0.00 Other Statistics Executions that Fetched all Rows (%) 100.00 Average Persistent Mem (KB) 45.90 Average Runtime Mem (KB) 41.75 Serializable Aborts 0 Remote No Obsolete Yes Child Latch Number 0
针对这个问题要说明一下,这是一条简单的insert语句 但是bind value达到了150+ 对于这种设计不可避免的出现了bind variable graduation。另外由于一条insert需要耗费14ms,此sql已经成为了这个系统的top1 sql,并且在11.2.0.3中遇到了这个bug(so sad ~)。由此可见表设计的重要性。目前在国内,为了满足业务的需求,无休止的为表添加column依然成为习惯。不过当你的系统已经接近瓶颈时候,你就会发现当初的决定是多么的愚蠢。
Bug 14127231 dbms_shared_pool.purge raised ora-6570 on obsoleted child cursors
This note gives a brief overview of bug 14127231.
The content was last updated on: 06-JUL-2012
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected => 11.2.0.3Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in ? 12.1 (Future Release)
11.2.0.4 (Future Patch Set)Symptoms: Related To:
Error May Occur
ora-6570 PL/SQL (DBMS Packages)
dbms_shared_poolDescription
dbms_shared_pool.purge() returns ora-6570 when trying to purge an obsolete object.Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References
Bug:14127231 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article
————–
SQL> alter system set "_cursor_obsolete_threshold"=3 scope=spfile;
System altered.
SQL>
SQL> startup force;
SQL> set linesize 300
SQL> select sql_text,sql_id,address,hash_value,is_obsolete from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';
SQL_TEXT SQL_ID ADDRESS HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v) 6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v) 6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v) 6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v) 6cvmu7dwnvxwj 000000011ECCE110 2035152785 N
SQL>
SQL>
Sys:
SQL> exec dbms_shared_pool.purge('000000011ECD0220,2035152785','C');
PL/SQL procedure successfully completed.
SQL>
SQL> select sql_text,sql_id,address,hash_value,is_obsolete from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';
SQL_TEXT SQL_ID ADDRESS HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v) 6cvmu7dwnvxwj 000000011ECCE110 2035152785 N
SQL>
SQL>
SQL> exit更新：在Patch 14127231中 已经修复这一bug 这个CPU针对solaris 和 linux 11.2.0.3.x.最经也release了 AIX 64 bit的patch.
