[20150812]11g子游标obsolete.txt

[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推出了补丁修正这个错误。

时间: 2024-09-20 15:02:56

[20150812]11g子游标obsolete.txt的相关文章

[20120421] cursor_sharing=similar和子光标问题.txt

[20120421] cursor_sharing=similar和子光标问题.txt 如果设置cursor_sharing=similar如果存在直方图会产生大量子光标,11GR2的新特性ACS可以很好的解决问题.自己做一些测试说明cursor_sharing=similar产生大量子光标的问题. SQL> select * from v$version; BANNER --------------------------------------------------------------

父游标、子游标及共享游标

        游标是数据库领域较为复杂的一个概念,因为游标包含了shared cursor和session cursor.两者有其不同的概念,也有不同的表现形式.共享游标的概念易于与SQL语句中定义的游标相混淆.本文主要描述解析过程中的父游标,子游标以及共享游标,即shared cursor,同时给出了游标(session cursor)的生命周期以及游标的解析过程的描述.                有关游标的定义,声明,与使用请参考:PL/SQL 游标            有关硬解析与

[20170516]11G use_large_pages参数2.txt

[20170516]11G use_large_pages参数2.txt //前面我提到如果设置use_large_pages=auto.设置页面大小不足时,oracle会oradism经常修改内核参数vm.nr_hugepages. //忘记测试是否在退出后可以收回.链接如下: http://blog.itpub.net/267265/viewspace-2135210/ --//不知道什么回事,以前写的,忘记发了,补上. 1.环境 SYS@book> @ &r/ver1 PORT_STR

[20150929]11g关于行链接.txt

[20150929]11g关于行链接.txt --曾经写过block record flag: http://blog.itpub.net/267265/viewspace-1753924/ http://blog.itpub.net/267265/viewspace-1753933/ --总结如下: #define KDRHFK 0x80 Cluster Key                                    =>使用K表示 #define KDRHFC 0x40 Clu

[20170207]11G审计日志清除.txt

[20170207]11G审计日志清除.txt --//11G缺省打开了许多审计,比如登录审计(我个人建议仅仅审计不成功的登录,特别对登录密集的系统),如果系统上线时没有关闭或者取 --//消一些审计,sys.aud$在system表空间,会导致空间异常增加,而且占用system表空间不是很合理.必须建立良好的监测清理机制. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---

[20150511]关于11G Direct Path Read.txt

[20150511]关于11G Direct Path Read.txt --前一阵子被别人问到升级到11G要注意那些细节,我给对方讲了审计,用户口令管理(大小写),sql tuning advisor,auto space advisor, --以及Direct Path Read等问题,对方问了很多Direct Path Read相关的问题. --实际上就是讲简单就是一些表大于一定的情况下,读取数据从磁盘,绕过buffer cache的情况. --如果有用户使用Direct Path Rea

[20170410]11G ora_sql_txt是否有效.txt

[20170410]11G ora_sql_txt是否有效.txt --链接问的问题: http://www.itpub.net/thread-2086256-1-1.html --我写的测试脚本,自己测试看看: 1.环境: SYS@test> @ ver1 PORT_STRING                    VERSION        BANNER                                                                    

[20160201]db_link与子光标问题.txt

[20160201]db_link与子光标问题.txt --生产系统遇到一个关于db_link产生大量子光标问题,当cursor_sharing=force的情况下,通过测试说明. --注:这个问题我的测试仅仅存在10.2.0.4,11.2.0.4没有这个问题. 1.环境: SCOTT@test> @&r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -----

[20161130]11g跟踪文件位置.txt

[20161130]11g跟踪文件位置.txt --11G使用adrci管理跟踪文件,支持XML,不再像10g那样的目录管理方式. --实际上在安装时仅仅设置参数diagnostic_dest.看看我的测试机器. 1.环境: SYS@book> @ &r/ver1 PORT_STRING         VERSION     BANNER ------------------- ----------- --------------------------------------------