[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點
--定時分析,肯定會選中分析.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2131774/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170703]SQL語句分析執行過程.txtSQL
- Oracle-監控sql語句的過載率OracleSQL
- [20150724]無法通過sql_id找到sql語句.txtSQL
- [20150403]修正sql語句.txtSQL
- SQL語句優化（轉載）SQL優化
- 透過sql語句分析足彩SQL
- 通過sql語句分析足彩SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- mysql執行sql語句過程MySql
- SQL語句的處理過程SQL
- [20191011]通過bash計算sql語句的sql_id.txtSQL
- [20150803]無法通過sql_id找到sql語句2.txtSQL
- [20150803]無法通過sql_id找到sql語句3.txtSQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- SQL語句執行過程詳解SQL
- CoreData執行過程的sql語句SQL
- sql語句執行過程小結SQL
- SQL語句的處理過程修正SQL
- 剖析SQL語句的執行過程SQL
- Oracle SQL 語句的執行過程OracleSQL
- [20191101]通過zsh計算sql語句的sql_id.txtSQL
- SQL語句SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- [20141013]奇怪的sql語句.txtSQL
- [20150527]跟蹤單個sql語句.txtSQL
- [20121101]tkprof抽取sql語句.txtSQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- 15個常用sql語句 分支 迴圈 子查詢 儲存過程 事務 常用函式 sql語句執行過程（轉載備用）SQL儲存過程函式
- 主動優化高負載SQL語句優化負載SQL
- 一條sql語句的執行過程SQL
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 通過java來格式化sql語句JavaSQL
- 透過java來格式化sql語句JavaSQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL