[20180822]session_cached_cursors and Sub Leng 0.txt

[20180822]session_cached_cursors and Sub Leng 0.txt

--//a few days ago to test the problem of refreshing shared pools and parent-child cursors,
--//Link: http://blog.itpub.net/267265/viewspace-2200066/=>[20180813] refresh shared pool and parent-child cursor. txt
--//I test if the statement is cached when the shared pool is flushed,
--//child cursor, parent cursor heap 0, child cursors are not cleared. and kglhdlmd=1
--//Child cursor Heap 0, child cursor heap 6 is cleared.
--//night look at the <oracle kernel technology >, when it comes to caching cursors, the memory of the child cursor heap 0 is not overwritten. The process scans the LRU to find the overwritten chunk, if
--//now a child cursor heap 0, it will check if there is a number 1th library cache lock on its corresponding child cursor handle. If so, it will be removed from the LRU, and the child cursor heap 6 will not have this "pending
--//". That is, in this way, a parent-child cursor, a parent cursor heap 0, a child cursor, and a child cursor heap 0 are not cleared, only the child cursor heap 6 is cleared.
--//is a little different from refreshing a shared pool operation, except that the child cursor heap 0 is cleared, and I repeat the validation to see:

1. Environment
[Email protected]> @ 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

[Email protected]> show parameter session_cached_cursors
NAME TYPE VALUE
---------------------- ------- -----
Session_cached_cursors Integer 50

[Email protected]> show parameter open_cursors
NAME TYPE VALUE
------------ ------- -----
Open_cursors Integer 300

[Email protected]> alter system set open_cursors=50000 scope=memory;
System altered.

--//Note This parameter is set to exit before it takes effect!!

2. Test:

--//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;
--//determine sql_id=4xamnunv51w9j, you can query the V$sql view to determine.

[Email protected]> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT kglhdadr kglhdpar C40 kglhdlmd KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 n0_6_16 N20 kglnahsh KGLOBT03 kglobt 09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ----- ----- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- -- --------
Child cursor handle address 000000007d4b8000 000000007d4b8390 SELECT * FROM dept where deptno=10 0 0 0 000000 007d07e040 000000007d07e838 4528 12144 3067 19739 19739 911274289 4xamnunv51w9j 0
Parent cursor Handle Address 000000007d4b8390 000000007d4b8390 SELECT * FROM dept where deptno=10 0 0 0 000000 007d0d6318 4720 0 0 4720 4720 911274289 4xamnunv51w9j 65535

--//set up a test script that simply parses the SQL statement and does not execute. This consumes the shared pool memory using the previous set open_cursor parameter.
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
/

3. Execute the test script:
[Email protected]> @ ac.sql
--//and so on, error!!
Declare
*
ERROR at line 1:
Ora-04031:unable to allocate bytes of shared memory ("Shared Pool", "Select Job, Nvl2 (Last_date, ...", "Sqla", "tmp")
Ora-06512:at "SYS. Dbms_sql ", Line 1199
Ora-06512:at Line 9

[Email protected]> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT kglhdadr kglhdpar C40 kglhdlmd KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 n0_6_16 N20 kglnahsh KGLOBT03 kglobt 09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ----- ----- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- -- --------
Child cursor handle address 000000007d4b8000 000000007d4b8390 SELECT * FROM dept where deptno=10 1 0 0 000000 007d07e040 4528 0 3067 7595 7595 911274289 4xamnunv51w9j 0
Parent cursor Handle Address 000000007d4b8390 000000007d4b8390 SELECT * FROM dept where deptno=10 1 0 0 000000 007d0d6318 4720 0 0 4720 4720 911274289 4xamnunv51w9j 65535

--//Indeed, the child cursor heap 0 is not cleared. But it's a little odd that refreshing a shared pool can clear the child cursor heap 0, not understood.

[Email protected]> alter system flush Shared_pool;
System altered.

[Email protected]> @ &r/sharepool/shp4 4xamnunv51w9j 0
TEXT kglhdadr kglhdpar C40 kglhdlmd KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 n0_6_16 N20 kglnahsh KGLOBT03 kglobt 09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ----- ----- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- -- --------
Child cursor handle address 000000007d4b8000 000000007d4b8390 SELECT * FROM dept where deptno=10 1 0 1 00 0 0 3067 3067 3067 911274289 4xamnunv51w9j 0
Parent cursor Handle Address 000000007d4b8390 000000007d4b8390 SELECT * FROM dept where deptno=10 1 0 1 000000 007d0d6318 4720 0 0 4720 4720 911274289 4xamnunv51w9j 65535

[20180822]session_cached_cursors and Sub Leng 0.txt