[20170103]sql语句重载.txt

[20170103]sql语句重载.txt

--学习了解sql语句reload的情况,在表分析或者某个对象重新定义或者授权后,sql语句要重新分析.或者某些情况,共享内存不足,导致子光标的堆0,堆6清除.
--再次执行sql语句时,要重新生成执行计划,做一些简单探究:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
--sql_id='4xamnunv51w9j',执行5次.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10       000000007CCD8C80 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10       000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          1 N             0          5

2.测试:
--如果重新分析:
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'dept',Estimate_Percent => NULL,Method_Opt =>'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
--//注意:No_Invalidate => false,也就是统计信息马上生效.

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          1 N             1          5
--//可以发现INVALIDATIONS=1,增加1次.注意这时并没有实行,也就是分析参数No_Invalidate => false,子光标立马失效.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10       000000007CCD8C80 000000007D373CD8       4528      12144       3067     19739      19739  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10       000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535
--从显示看不出来.

--查询底层视图定义对应INVALIDATIONS是x$kglcursor_child.kglhdivc.
--修改shp4脚本定义:
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, '父游标句柄地址',
               '子游标句柄地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
       kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
       kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
       kglnahsh,
       kglobt03 ,
       kglobt09 
  FROM x$kglob
WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                1 000000007CCD8C80 000000007D373CD8       4528      12144       3067     19739      19739  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                1 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--父子光标都在,KGLHDIVC=1,再次执行:

SCOTT@test01p> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                1 000000007D076B30 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                1 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          2 N             1          1

--LOADS=2次数增加1,INVALIDATIONS不变.说明在分析表(参数No_Invalidate => false),子光标立即失效INVALIDATIONS增加,而再次执行时loads次数增加.执行次数回到1.

3.换1个方式测试:
$ cat flush_sql.sql
DECLARE
name varchar2(100);
version varchar2(3);
BEGIN
select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then
execute immediate
q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport
end if;

select address||','||hash_value into name from v$sqlarea where sql_id like '&1';

dbms_shared_pool.purge(name,'C',&2);

END;
/

SYS@book> @ &r/flush_sql 4xamnunv51w9j 64
PL/SQL procedure successfully completed.
--//这样仅仅清除子关闭的堆6,2^6=64.

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         0          2 N             1          1

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                1 000000007D076B30 00                     4488          0       3067      7555       7555  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                1 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535
--//子光标堆6 KGLOBHD6=00.(这个是段地址)

--//再次执行:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                1 000000007D076B30 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                1 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          3 N             1          2

--可以发现INVALIDATIONS=1(没有变化),而loads=3,又增加1次,因为堆6被清除,里面保存有执行计划,必须重新分析,而子光标没有失效.

4.再换1个方式测试:
SYS@book> @ &r/flush_sql 4xamnunv51w9j 1
PL/SQL procedure successfully completed.

--//这样操作仅仅清除子光标的堆0,2^0=1,实际上堆6仅仅是堆0下面的对象,清除了堆0,实际上堆6也一带清除.

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
no rows selected

--查询v$sql已经无法查询到.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 00               00                        0          0       3067      3067       3067  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--//可以发现KGLOBHD0,KGLOBHD6='00'.
--//再次执行:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 000000007D076B30 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          1

--//LOADS=4又增加1次.INVALIDATIONS=2,也增加1次.实际上在执行前KGLHDIVC已经等于2.

--//从测试可以看出仅仅仅仅清除堆0,堆6,再次执行sql语句时,INVALIDATIONS,loads才会增加.
--//而仅仅清除堆6,INVALIDATIONS不变,再次执行sql语句时,loads才会增加.
--//重新分析表时选择参数No_Invalidate => false,INVALIDATIONS立即增加1次,而sql语句再次执行时loads才增加.

--//如果一条语句LOADS次数很多,原因可能是分析表次数很多(有一些表天天都会分析),还有一种可能就是sharepool不足,也许问题还是
--//出在没有使用绑定变量,导致共享池内存吃紧.

5.测试分析No_Invalidate => DBMS_STATS.AUTO_INVALIDATE:
SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          1

execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'dept',Estimate_Percent =>NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE);


SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          1
--//没有变化.

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 000000007D076B30 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--//你可以发现INVALIDATIONS没有变化.这是因为链接:http://blog.itpub.net/267265/viewspace-742147
--//no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,分析表后,游标不会马上invalidate,已经存在的SQL的执行计划不会受新的统计信息影响。可以手工
--//DDL invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
--//Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。

SYS@book> @ &r/hide _optimizer_invalidation_period
NAME                           DESCRIPTION                                                 DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ----------------------------------------------------------- ------------- ------------- ------------
_optimizer_invalidation_period time window for invalidation of cursors of analyzed objects TRUE          18000         18000

--//缺省需要18000秒=5小时.设置10秒看看.

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          1

SYS@book> alter system set "_optimizer_invalidation_period" = 10 scope=memory;
System altered.

--//等10秒后执行:
SCOTT@book> host sleep 10

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          1

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 000000007D076B30 000000007D373CD8       4488      12144       3067     19699      19699  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

--再次执行:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 000000007D076B30 000000007D373CD8       4528      12144       3067     19739      19739  911274289 4xamnunv51w9j          0
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          2

--//可以发现并没有变化,仅仅执行次数增加,再次执行:
SCOTT@book> select * from dept where deptno=10;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> @ &r/sharepool/shp4 4xamnunv51w9j 0
old  18:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  18:  WHERE kglobt03 = '4xamnunv51w9j'  or kglhdpar='4xamnunv51w9j' or kglhdadr='4xamnunv51w9j' or KGLNAHSH= 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10                2 000000007D076B30 000000007D373CD8       4528      12144       3899     20571      20571  911274289 4xamnunv51w9j          0
子游标句柄地址 000000007CB38E70 000000007CB82AF8 select * from dept where deptno=10                0 000000007D0CE300 000000007D3740E0       4488      12144       3899     20531      20531  911274289 4xamnunv51w9j          1
父游标句柄地址 000000007CB82AF8 000000007CB82AF8 select * from dept where deptno=10                2 000000007CEC5F30 00                     4720          0          0      4720       4720  911274289 4xamnunv51w9j      65535

SYS@book> select sql_text,loaded_versions,loads,IS_OBSOLETE,INVALIDATIONS,executions from v$sql where sql_id='4xamnunv51w9j';
SQL_TEXT                                                     LOADED_VERSIONS      LOADS I INVALIDATIONS EXECUTIONS
------------------------------------------------------------ --------------- ---------- - ------------- ----------
select * from dept where deptno=10                                         1          4 N             2          2
select * from dept where deptno=10                                         1          1 N             0          1

--//可以发现生成新的子光标.从这里也可以看出如果一些表每天后台都分析,会产生许多子光标.因为缺省表分析参数是No_Invalidate=> DBMS_STATS.AUTO_INVALIDATE.
SYS@book> @ &r/share 4xamnunv51w9j
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''4xamnunv51w9j''',
SQL_TEXT                       = select * from dept where deptno=10
SQL_ID                         = 4xamnunv51w9j
ADDRESS                        = 000000007CB82AF8
CHILD_ADDRESS                  = 000000007C54D2B0
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1483404131</invalidation_window><ksugctm>1483404151</ksugctm></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from dept where deptno=10
SQL_ID                         = 4xamnunv51w9j
ADDRESS                        = 000000007CB82AF8
CHILD_ADDRESS                  = 000000007CB38E70
CHILD_NUMBER                   = 1
ROLL_INVALID_MISMATCH          = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

--这也是我们生产系统看到的产生这种类似大量的子光标的原因,因为这些表记录类似seq号的东西,每次取都要更新它,这样每到晚上10点
--定时分析,肯定会选中分析.

上一篇:pipework配置docker网桥网络


下一篇:Java锁---偏向锁、轻量级锁、自旋锁、重量级锁