[20210407]分析sql語句的共享記憶體段3.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2766826/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- [20210412]分析會話佔用的共享記憶體段.txt會話記憶體
- nginx共享記憶體分析Nginx記憶體
- [20210421]分析會話佔用的共享記憶體段2.txt會話記憶體
- [20190104]ipcs檢視共享記憶體段.txt記憶體
- [20220119]超長sql語句補充3.txtSQL
- [20191220]關於共享記憶體段相關問題.txt記憶體
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- Golang 共享記憶體Golang記憶體
- POSIX 共享記憶體記憶體
- [Linux]共享記憶體Linux記憶體
- Linux共享記憶體的管理Linux記憶體
- [20201105]再分析sql語句.txtSQL
- [20210126]探究oracle記憶體分配3.txtOracle記憶體
- [20210902]為什麼會使用多個共享記憶體段.txt記憶體
- Qt共享記憶體QSharedMemoryQT記憶體
- Linux共享記憶體(二)Linux記憶體
- QT之共享記憶體QT記憶體
- nginx中共享記憶體的使用Nginx記憶體
- sql語句執行緩慢分析SQL
- [20211221]分析sql語句遇到的問題.txtSQL
- OpenResty 和 Nginx 的共享記憶體區是如何消耗實體記憶體的RESTNginx記憶體
- jsqlparser使用記錄---生成sql語句JSSQL
- MySQL 記錄所有執行了的 sql 語句MySql
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- SQL語句IN的用法SQL
- 記一個實用的sql查詢語句SQL
- 【記憶體管理】Oracle如何使用ASMM自動共享記憶體管理記憶體OracleASM
- PostgreSQL共享記憶體裡的內容（initCommunication）SQL記憶體
- [20181119]sql語句執行緩慢分析.txtSQL
- [20210923]sql語句佔用Sharable Memory分析.txtSQL
- Android匿名共享記憶體（Ashmem）原理Android記憶體
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- SQL SELECT 語句SQL