[20150812]11g子游标obsolete.txt
--昨天听别人提到11.2.0.3下有一个bug，当子游标数量达到100后，如果产生101个子游标，那么父游标以及100个子游标会设置为obsoleted。
--重新生成新的父游标。但是那些过期的child cursor不会从v$sql中消失，dbms_shared_pool.purge也无法将这些过期的child cursor
--flush出去.
--正好，我目前的测试环境主要是11.2.0.3，自己测试看看。
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------- ------------------------------------------------ ---------------------- ---------------------- ----------------------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 100 100
--隐藏参数_cursor_obsolete_threshold默认为100，表示一个parent cursor最多可以有100个child cursor。
CREATE TABLE t (n NUMBER);
INSERT INTO t VALUES (1);
COMMIT;
execute dbms_stats.gather_table_stats(user,'t')
$ cat aa.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i;
FOR j IN 1..&&2
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j;
EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;
END LOOP;
END LOOP;
END;
/
2.开始执行测试：
SCOTT@test> select sid,event from v$session_wait where sid=580;
SID EVENT
---------- ----------------------------------------
580 SQL*Net message to client
SCOTT@test> @aa.sql 1 100
...
SCOTT@test> select sql_text,child_number,A.IS_OBSOLETE,A.IS_SHAREABLE from v$sql a where sql_id='5tjqf7sx5dzmj';
SQL_TEXT CHILD_NUMBER I I
------------------------------------------------------------ ------------ - -
SELECT count(*) FROM t 0 N Y
SELECT count(*) FROM t 1 N Y
SELECT count(*) FROM t 2 N Y
SELECT count(*) FROM t 3 N Y
SELECT count(*) FROM t 4 N Y
SELECT count(*) FROM t 5 N Y
...
SELECT count(*) FROM t 97 N Y
SELECT count(*) FROM t 98 N Y
SELECT count(*) FROM t 99 N Y
100 rows selected.
--如果我执行：
SCOTT@test> @aa 1 101
SCOTT@test> select sql_text,child_number,A.IS_OBSOLETE,A.IS_SHAREABLE from v$sql a where sql_id='5tjqf7sx5dzmj';
SQL_TEXT CHILD_NUMBER I I
------------------------------------------------------------ ------------ - -
SELECT count(*) FROM t 0 Y Y
SELECT count(*) FROM t 1 Y Y
SELECT count(*) FROM t 2 Y Y
SELECT count(*) FROM t 3 Y Y
SELECT count(*) FROM t 4 Y Y
SELECT count(*) FROM t 5 Y Y
....
SELECT count(*) FROM t 97 Y Y
SELECT count(*) FROM t 98 Y Y
SELECT count(*) FROM t 99 Y Y
SELECT count(*) FROM t 0 N Y
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
101 rows selected.
-- 上下对比可以发现字段IS_OBSOLETE开始是'N',而产生101个子游标后，最后一个CHILD_NUMBER=0, IS_OBSOLETE='N'.
-- 并且其它的IS_OBSOLETE都是'Y'.表示已经废除。
SCOTT@test> select sql_text,child_number,address,hash_value,A.IS_OBSOLETE,A.IS_SHAREABLE from v$sql a where sql_id='5tjqf7sx5dzmj' ;
SQL_TEXT CHILD_NUMBER ADDRESS HASH_VALUE I I
------------------------------------------------------------ ------------ ---------------- ---------- - -
SELECT count(*) FROM t 0 00000000ABCC3E70 978779761 Y Y
SELECT count(*) FROM t 1 00000000ABCC3E70 978779761 Y Y
SELECT count(*) FROM t 2 00000000ABCC3E70 978779761 Y Y
SELECT count(*) FROM t 3 00000000ABCC3E70 978779761 Y Y
SELECT count(*) FROM t 4 00000000ABCC3E70 978779761 Y Y
SELECT count(*) FROM t 5 00000000ABCC3E70 978779761 Y Y
....
SELECT count(*) FROM t 0 00000000B2FEF080 978779761 N Y
101 rows selected.
--以sys用户登陆执行:
SYS@test> exec dbms_shared_pool.purge('00000000ABCC3E70,978779761','C');
BEGIN dbms_shared_pool.purge('00000000ABCC3E70,978779761','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
--如果不能剔除，出现这种大量的子光标，将耗费大量的共享池内存。
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> select sql_text,child_number,address,hash_value,A.IS_OBSOLETE,A.IS_SHAREABLE from v$sql a where sql_id='5tjqf7sx5dzmj' ;
no rows selected
--刷新共享池可以。
3.产生多一点子游标看看。
SCOTT@test> @aa 10 100
SYS@test> select count(*) ,sum(decode(IS_obsolete,'Y',1,0)) n10 from v$sql a where sql_id='5tjqf7sx5dzmj' ;
COUNT(*) N10
---------- ---------------------
1000 900
--可以发现产生了1000个子光标，而900了已经废除。
--再加大看看。
SCOTT@test> @aa 100 100
SYS@test> select count(*) ,sum(decode(IS_obsolete,'Y',1,0)) n10 from v$sql a where sql_id='5tjqf7sx5dzmj' ;
COUNT(*) N10
---------- ---------------------
7200 7100
--共10000次执行，实际上共享内存不足还是会回收的。只不过没有这么快。
SCOTT@test> @aa 200 100
SYS@test> select count(*) ,sum(decode(IS_obsolete,'Y',1,0)) n10 from v$sql a where sql_id='5tjqf7sx5dzmj' ;
COUNT(*) N10
---------- ---------------------
6979 6879
--oracle推出了补丁修正这个错误。