[20180828]關於引數cursor_space_for_time(10g).txt
[20180828]關於引數cursor_space_for_time(10g).txt
--//昨天測試session_cached_cursors不等於0的情況下,如果會話已經快取了遊標,這樣即使其它語句消耗共享池,
--//父子游標,父遊標堆0,子游標,子游標堆0都沒有清除,僅僅子游標堆6會被清除.
--//今天補充測試cursor_space_for_time=true的情況.好像這個引數在10g之前還有效.因為11g以後使用metux來代替部分latch,
--//不在支援此引數.
1.環境:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -------
cursor_space_for_time boolean FALSE
SYS@test> alter system set open_cursors=50000 scope=memory;
System altered.
--//建立測試指令碼,僅僅分析sql語句,不執行.這樣利用設定引數open_cursor=50000,消耗共享池記憶體.
create table a1( id1 number,id2 number);
$ cat ac.sql
declare
msql varchar2(500);
mcur number;
mstat number;
begin
for i in 1 .. 49000 loop
mcur := dbms_sql.open_cursor;
msql := 'select id1 from a1 where id2='||to_char(i);
dbms_sql.parse(mcur,msql,dbms_sql.native);
-- mstat := dbms_sql.execute(mcur);
end loop;
end;
/
2.測試:
--//cursor_space_for_time=false
--//session 1:
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//確定sql_id=4xamnunv51w9j,可以查詢v$sql檢視確定.
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000076B394C8 0000000076B396F0 select * from dept where deptno=10 1 0 0 0000000076B39408 00000000774C6FE8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000076B396F0 0000000076B396F0 select * from dept where deptno=10 1 0 0 0000000076B39630 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//注意10.2.0.4版本也是一樣,當前執行的sql語句KGLHDLMD=1.注意現在是cursor_space_for_time=FALSE的情況.
--//session 1:
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2018-08-28 09:17:30
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000076B394C8 0000000076B396F0 select * from dept where deptno=10 0 0 0 0000000076B39408 00000000774C6FE8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000076B396F0 0000000076B396F0 select * from dept where deptno=10 0 0 0 0000000076B39630 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=0.
--//補充測試:
--//session 2:
SYS@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -------
cursor_space_for_time boolean FALSE
--//session 1,執行多次快取會話遊標.
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select sysdate from dual;
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B1D0 00000000770A2190 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//session 3:
SCOTT@test> @ ac.sql
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 576 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","ckydef : kkdlcky")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 9
--//等,報ora-04031錯誤!!
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B1D0 00 3664 0 1803 5467 5467 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 0 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游標控制程式碼地址的KGLHDLMD=1.
--//當會話快取遊標以後,在cursor_space_for_time=false的情況下.共享池記憶體不足時,並不能清除父子游標控制程式碼地址,父遊標堆0,子游標堆0.而子游標堆6的資訊清除了.
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007672B290 000000007672B4B8 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 000000007672B4B8 000000007672B4B8 select * from dept where deptno=10 1 0 1 000000007672B3F8 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游標控制程式碼地址,父遊標堆0都沒有清除.並且注意父遊標控制程式碼地址的KGLHDLMD=1.其他KGLHDIVC=1表示什麼不是很清楚.
--//子游標堆0,子游標堆6清除.
--//感覺10g與11g下alter system flush shared_pool;存在不同,10g下,僅僅父遊標控制程式碼地址的KGLHDLMD=1.
--//而11g下,父子游標控制程式碼地址的KGLHDLMD=1.
3.修改引數cursor_space_for_time看看:
SYS@test> alter system set cursor_space_for_time=true scope=spfile;
System altered.
--//修改引數重啟資料庫.略.
--//session 1:
SCOTT@test> show parameter cursor_space_for_time
NAME TYPE VALUE
--------------------- ------- -----
cursor_space_for_time boolean TRUE
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 1 2 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 1 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//對比前面的情況可以發現KGLHDLMD=1外,子游標控制程式碼地址的KGLHDPMD=2.KGLHDPMD表示library cahce pin模式.
--//KGLHDLMD表示library cache lock模式. 1=null,2=共享模式 3=獨佔模式.
--//session 1:
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2018-08-28 09:24:34
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 0 0 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 0 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//當前sql語句<>'4xamnunv51w9j'時,KGLHDLMD=0.
--//session 1,執行多次快取會話遊標.
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select sysdate from dual;
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 000000007773EC58 00000000777521F0 select * from dept where deptno=10 1 0 0 000000007773BE60 000000007773C1C8 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 00000000777521F0 00000000777521F0 select * from dept where deptno=10 1 0 0 0000000077752130 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=1.
4.執行測試指令碼:
--//session 1:
SCOTT@test> alter system set open_cursors=50000 scope=memory;
System altered.
--//退出再進入,不然不會生效:
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
--//注:前面的測試我在session 1執行ac.sql前退出過1次(要使alter system set open_cursors=50000 scope=memory;生效),
--//導致sql_id='4xamnunv51w9j'遊標已經退出回話快取,雖然我又執行多次快取該遊標,但是不應該在該回話呼叫ac.sql.
--//這樣測試有點問題.要快取遊標的這個回話不要退出.重來測試.
--//session 1:
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select * from dept where deptno=10;
select sysdate from dual;
--//不退出.
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E4A7C0 0000000071E65C80 3664 8088 1803 13555 13555 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//KGLHDLMD=0.
--//session 3:
SCOTT@test> @ ac.sql
--//等,報ora-04031錯誤!!
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select type#,blocks,extents,...","Typecheck","coldef: qcopCreateCol")
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 9
--//session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E4A7C0 00 3664 0 1803 5467 5467 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 0 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//我感覺與會話快取遊標一樣,父子游標的KGLHDLMD=1.
--//當會話快取遊標以後,在cursor_space_for_time=true的情況下.共享池記憶體不足時,並不能清除父子游標控制程式碼地址,父遊標堆0,子游標堆0.而子游標堆6的資訊清除了.
--//重新整理共享池看看.
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 1 0 1 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
--//父子游標控制程式碼地址,父遊標堆0都沒有清除.並且注意父遊標控制程式碼地址的KGLHDLMD=1.其他KGLHDIVC=1表示什麼不是很清楚.
--//子游標堆0,子游標堆6清除.
--//感覺10g與11g下alter system flush shared_pool;存在不同,10g下,僅僅父遊標控制程式碼地址的KGLHDLMD=1.
--//而11g下,父子游標控制程式碼地址的KGLHDLMD=1.
--//session 1退出後,session 2:
SYS@test> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000072C7CDD8 0000000071E6E388 select * from dept where deptno=10 0 0 1 00 00 0 0 1803 1803 1803 911274289 4xamnunv51w9j 0
父遊標控制程式碼地址 0000000071E6E388 0000000071E6E388 select * from dept where deptno=10 0 0 1 0000000071E1C618 00 2812 0 0 2812 2812 911274289 4xamnunv51w9j 65535
總結:
感覺設定cursor_space_for_time=true與session_cached_cursors差別不大,不做比較,總之無法徹底清除乾淨從共享池.
測試還是亂,
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2213257/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201113]測試CURSOR_SPACE_FOR_TIME(10g).txt
- [20201116]測試CURSOR_SPACE_FOR_TIME(10g)(補充).txt
- [20190102]關於字串的分配問題(10g).txt字串
- [20221212]關於pre_page_sga引數的問題.txt
- [20191204]hugepage相關引數含義.txt
- [20220913]hugepage相關引數含義.txt
- Oracle 10g expdp attach引數體驗Oracle 10g
- [20180828]exadata--豆腐渣系統的保護神.txt
- [20201116]測試CURSOR_SPACE_FOR_TIME=false(11g).txtFalse
- [20180413]bash 位置引數.txt
- 2.6.9.1 關於 COMPATIBLE初始化引數
- Rman關於filesperset引數的介紹
- [20190530]DISABLE TABLE LOCK(10g).txt
- [20190215]那個更快(10g).txt
- [20200620]expdp impdp exclude引數.txt
- 關於Oracle 10g ASM磁碟大小的限制Oracle 10gASM
- 關於隱藏引數：_no_recovery_through_resetlogs
- 關於MySQL引數，這些你要知道MySql
- [20180412]logminer使用問題(10g).txt
- [20190917]oracle引數deferred屬性.txtOracle
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- [20200220]windows設定keepalive引數.txtWindows
- [20210826]核心引數kernel.sem.txt
- [20210209]修改CPU_COUNT引數.txt
- 關於xtrabackup --slave-info引數的說明
- 關於Jmeter引數化的編碼問題JMeter
- python疑問5：位置引數，預設引數，可變引數，關鍵字引數，命名關鍵字引數區別Python
- [20191202]關於hugepages相關問題.txt
- [20190409]latch get 引數where and why.txt
- [20190417]隱含引數_SPIN_COUNT.txt
- [20180308]測試ARG_MAX引數.txt
- [20210310]db_lost_write_protect引數.txt
- Shell 中 $ 關於指令碼引數的幾種用法指令碼
- 2.7.6.1 關於改變初始化引數的值
- 關於 groutine 喚醒中 skipframes 引數不理解
- 工具類，關於手工讀取 properties檔案引數
- [201804012]關於hugepages 3.txt
- [20180306]關於DEFERRED ROLLBACK.txt