[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点
--定时分析,肯定会选中分析.
