[20210407]分析sql语句的共享内存段3.txt

来源：这里教程网时间：2026-03-03 16:34:49 作者：

[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

编辑推荐：

返回列表

[20210407]分析sql语句的共享内存段3.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推