[20210407]分析sql语句的共享内存段3.txt
1.环境：
SCOTT@book> @ 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> show parameter session_cached_cursors
NAME TYPE VALUE
---------------------- ------- ------
session_cached_cursors integer 50
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//不要退出也不要执行任何命令。通过其它方式确定sql_id=80baj2c2ur47u。
2.分析：
--//session 2:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0001.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07bd2c510 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7da95818
Chunk 07c520228 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07d642b08 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
Chunk 07da950a8 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
Chunk 07dc95040 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7da95818
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 07c520228 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
SEPARATOR
Unpinned space = 11428616 rcr=2733 trn=3515
--//session 2:
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CCC09F8 000000007CF3C868 select * from dept where deptno=20 1 0 0 000000007DB84688 000000007DA95818 4488 12144 3067 19699 19699 95129850 80baj2c2ur47u 0
parent handle address 000000007CF3C868 000000007CF3C868 select * from dept where deptno=20 1 0 0 000000007D947600 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
SYS@book> @ sharepool/shp3 000000007D947600
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007D947600')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007D947600')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF22F2528 6703 1 1 1 KGLH0^5ab90fa 000000007DA950A8 4096 recr 4095 000000007D947600
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007D947600', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF22FF1E8 7316 1 1 1 KGLDA 000000007D947598 240 freeabl 0 00
--//KSMCHPTR=000000007DA950A8 ,也就是parent cursor的堆0.
SYS@book> @ sharepool/shp3 000000007DB84688
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007DB84688')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007DB84688')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF22C9998 9101 1 1 1 KGLH0^5ab90fa 000000007D642B08 4096 recr 4095 000000007DB84688
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007DB84688', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF2304A80 6113 1 1 1 KGLDA 000000007DB84620 248 freeabl 0 00
--//KSMCHPTR=000000007D642B08 ,也就是child cursor的堆0.
SYS@book> @ sharepool/shp3 000000007DA95818
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007DA95818')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007DA95818')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF23175A0 5579 1 1 1 SQLA^5ab90fa 000000007DC95040 4096 freeabl 0 000000007DA95818
00007F4FF23D4348 18741 1 1 1 SQLA^5ab90fa 000000007C520228 4096 recr 4095 000000007DA95818
00007F4FF24DA748 23147 1 1 1 SQLA^5ab90fa 000000007BD2C510 4096 freeabl 0 000000007DA95818
--//3个KSMCHPTR，与前面看到SQLA^5ab90fa那行信息对应。也就是指向chile cursor的堆6.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007DA95818', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF22F0EE0 6708 1 1 1 KGLH0^5ab90fa 000000007DA950A8 4096 recr 4095 000000007D947600
--//从这里可以看出KSMCHCOM中的KGLH0表示堆0(无法区分父子），SQLA表示堆6.
--//另外注意一个细节。
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0001.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07bd2c510 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7da95818
Chunk 07c520228 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07d642b08 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
Chunk 07da950a8 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
Chunk 07dc95040 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7da95818
UNPINNED RECREATABLE CHUNKS (lru first):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chunk 07c520228 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
SEPARATOR
Unpinned space = 11428616 rcr=2733 trn=3515
--//你可以发现child cursor的堆6在UNPINNED RECREATABLE CHUNKS.也就是刷新共享池，堆6会清除。
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CCC09F8 000000007CF3C868 select * from dept where deptno=20 1 0 1 00 00 0 0 3067 3067 3067 95129850 80baj2c2ur47u 0
parent handle address 000000007CF3C868 000000007CF3C868 select * from dept where deptno=20 1 0 1 000000007D947600 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
--//child cursor的堆6都清除了。child cursor的堆0也清除了。
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0002.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0002.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07d642b08 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
Chunk 07da950a8 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space = 979984 rcr=185 trn=429
--//对比前面的你可以发现实际上child cursor的堆0还在，仅仅child cursor的堆0的描述符清除了。这个实际上是因为我会话里面还在
--//pin住这条语句，没有执行其它语句。
--//session 1 顺便执行其它语句：
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
95129850 80baj2c2ur47u 0 5ab90fa
--//session 2：
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0003.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0003.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07da950a8 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 07da950a8 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
SEPARATOR
Unpinned space = 3161496 rcr=619 trn=1077
--//child cursor的堆0清除，另外可以发现parent cursor的堆0进入UNPINNED RECREATABLE CHUNKS (lru first)。
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0004.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0004.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space = 876624 rcr=170 trn=360
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
no rows selected
--//这样才能彻底清除干净。
3.如果已经在会话中执行多次呢？
--//session_cached_cursors = 50.
--//session 1 ,执行如下多次。
select * from dept where deptno=20;
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
95129850 80baj2c2ur47u 0 5ab90fa
--//session 2：
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0005.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0005.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07d192ce8 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07d1cb728 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07d1cc728 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Chunk 07d192ce8 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
Unpinned space = 2657480 rcr=576 trn=937
--//不再展开分析。你可以发现执行多次后child cursor的堆0以及child cursor的堆6 类型为recreate的显示在SEPARATOR之下。
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0006.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CD7D040 000000007D138148 select * from dept where deptno=20 1 0 1 00 00 0 0 3067 3067 3067 95129850 80baj2c2ur47u 0
parent handle address 000000007D138148 000000007D138148 select * from dept where deptno=20 1 0 1 000000007CB47E98 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0006.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> parent cursor的堆0.
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) --> child cursor的堆0.
SEPARATOR
Unpinned space = 929784 rcr=182 trn=361
--//parent cursor的堆0还在，child cursor的堆0也在，堆6清除。
--//session 2:
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0007.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0007.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space = 878064 rcr=177 trn=356
--//继续重复:
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0008.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space = 877000 rcr=176 trn=354
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CD7D040 000000007D138148 select * from dept where deptno=20 1 0 1 00 00 0 0 3067 3067 3067 95129850 80baj2c2ur47u 0
parent handle address 000000007D138148 000000007D138148 select * from dept where deptno=20 1 0 1 000000007CB47E98 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
--//可以发现无论如何清不干净。让我很奇怪的是child cursor的堆0一直没有释放。
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0009.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CD7D040 000000007D138148 select * from dept where deptno=20 1 0 1 000000007D138088 000000007DBF1B70 4512 12144 3067 19723 19723 95129850 80baj2c2ur47u 0
parent handle address 000000007D138148 000000007D138148 select * from dept where deptno=20 1 0 1 000000007CB47E98 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
SYS@book> @ sharepool/shp3 000000007D138088
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('000000007D138088')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007D138088')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF2332840 2670 1 1 1 KGLH0^5ab90fa 000000007E127518 4096 recr 4095 000000007D138088
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007D138088', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F4FF2489368 10598 1 1 1 KGLDA 000000007D138020 248 freeabl 0 00
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0009.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Chunk 07ddb21b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07ddb31b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil) -> child cursor 的堆0实际上还是原来的地址。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
SEPARATOR
Unpinned space = 1355296 rcr=261 trn=531
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0010.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0010.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Chunk 07ddb21b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07ddb31b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
UNPINNED RECREATABLE CHUNKS (lru first):
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
SEPARATOR
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Unpinned space = 11289048 rcr=1839 trn=4432
--//session 1:
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
--//session 2:
Statement processed.
SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0011.trc
SYS@book> oradebug dump heapdump 2
Statement processed.
$ egrep -i "HEAP DUMP|SEPARATOR|5ab90fa|UNPINNED" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34519_0011.trc
HEAP DUMP heap name="sga heap" desc=0x60001190
HEAP DUMP heap name="sga heap(1,0)" desc=0x6005a8f0
Chunk 07dbf1400 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Chunk 07ddb21b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07ddb31b0 sz= 4096 freeable "SQLA^5ab90fa " ds=0x7dbf1b70
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Chunk 07df64550 sz= 4096 recreate "SQLA^5ab90fa " latch=(nil)
Chunk 07e127518 sz= 4096 recreate "KGLH0^5ab90fa " latch=(nil)
Unpinned space = 11297784 rcr=1840 trn=4434
--//注意看chunk位置的变化。
4.附上测试脚本：
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
KGLHDLMD,
KGLHDPMD,
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;
$ cat sharepool/shp3.sql
prompt
prompt select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchptr=hextoraw('&1');
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
prompt select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
--select * from x$ksmsp where ksmchptr in (
--SELECT x
-- FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x
-- FROM x$ksmsp a )
-- WHERE '&1' between x and ksmchptr);
-- oracle do not support raw compare
--SELECT * FROM x$ksmsp WHERE '&1' between KSMCHPTR and TO_CHAR(TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ,'0xxxxxxxxxxxxxxx');
prompt
prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
prompt
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
$ cat tix.sql
--@@saveset
column _ti_sequence noprint new_value _ti_sequence
set feedback off heading off
select trim(to_char( &&_ti_sequence + 1 , '0999' )) "_ti_sequence" from dual;
alter session set tracefile_identifier="&&_ti_sequence";
set feedback on heading on
set termout off
column tracefile noprint new_value trc
select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
(select spid||case when traceid is not null then '_'||traceid else null end
from v$process where addr = (select paddr from v$session
where sid = (select sid from v$mystat
where rownum = 1
)
)
) || '.trc' tracefile
from v$parameter where name = 'user_dump_dest';
set termout on
--@@loadset
prompt New tracefile_identifier=&&trc
col tracefile clear
set feedback 6 heading on
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2766826/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
