ITPub博客

首页 > 数据库 > Oracle > [20210407]分析sql语句的共享内存段3.txt

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

原创 Oracle 作者:lfree 时间:2021-04-07 11:41:20 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2907
  • 访问量
    6680936