[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,也就是統計資訊馬上生效.
select * from dept where deptno=10 1 1 N 1 5
--//可以發現INVALIDATIONS=1,增加1次.注意這時并沒有實行,也就是分析參數No_Invalidate => false,子光标立馬失效.
子遊标句柄位址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10 000000007CCD8C80 000000007D373CD8 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j 0
--從顯示看不出來.
--查詢底層視圖定義對應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;
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;
子遊标句柄位址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10 1 000000007D076B30 000000007D373CD8 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j 0
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.
select * from dept where deptno=10 0 2 N 1 1
子遊标句柄位址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10 1 000000007D076B30 00 4488 0 3067 7555 7555 911274289 4xamnunv51w9j 0
--//子光标堆6 KGLOBHD6=00.(這個是段位址)
--//再次執行:
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
--//這樣操作僅僅清除子光标的堆0,2^0=1,實際上堆6僅僅是堆0下面的對象,清除了堆0,實際上堆6也一帶清除.
no rows selected
--查詢v$sql已經無法查詢到.
子遊标句柄位址 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'.
子遊标句柄位址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10 2 000000007D076B30 000000007D373CD8 4488 12144 3067 19699 19699 911274289 4xamnunv51w9j 0
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:
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);
--//沒有變化.
--//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> alter system set "_optimizer_invalidation_period" = 10 scope=memory;
System altered.
--//等10秒後執行:
SCOTT@book> host sleep 10
--再次執行:
子遊标句柄位址 000000007C54D2B0 000000007CB82AF8 select * from dept where deptno=10 2 000000007D076B30 000000007D373CD8 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j 0
select * from dept where deptno=10 1 4 N 2 2
--//可以發現并沒有變化,僅僅執行次數增加,再次執行:
子遊标句柄位址 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
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>
--------------------------------------------------
CHILD_ADDRESS = 000000007CB38E70
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
REASON =
--這也是我們生産系統看到的産生這種類似大量的子光标的原因,因為這些表記錄類似seq号的東西,每次取都要更新它,這樣每到晚上10點
--定時分析,肯定會選中分析.
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)