Shulou(Shulou.com)05/31 Report--

This article mainly introduces why oracle 10.2.0.5 will only get child#=1 shared pool latch, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

1. You can use oradebug dump heapdump 3 to dump the structure information of the shared pool.

This level is generally 3, and the cost of 6 is a little high.

2. The syntax is as follows:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug dump heapdump 3

Statement processed.

SQL > oradebug tracefile_name

/ home/ora10g/admin/ora10g/udump/ora10g_ora_6533.trc

3. The TRC file structure of the dump shared pool is as follows:

Part one: LATCH information

KGH Latch Directory Information

Ldir state: 2 Last allocated slot: 77

Slot [1] Latch: 0xa4222c98 Index: 2 Flags: 3 State: 2 next: (nil)

The second part: HEAP information. You can see a total of 5 heaps. The number of corresponding subpools is controlled by the parameter _ kghdsidx_count.

HEAP DUMP heap name= "sga heap" desc=0x60000058

Extent sz=0x47c0 alt=216 het=32767 rec=9 flg=-126 opc=0

Parent= (nil) owner= (nil) nex= (nil) xsz=0x160

Ds for latch 1: 0x60034fe0 0x60036838 0x60038090-it can be seen that 3 latch are required to protect its child heap

Ds for latch 2: 0x6003e808 0x60040060 0x600418b8

Ds for latch 3: 0x60048030 0x60049888 0x6004b0e0

Ds for latch 4: 0x60051858 0x600530b0 0x60054908

Ds for latch 5: 0x6005b080 0x6005c8d8 0x6005e130 0x6005f988-4 latch is required to protect its child heap

Reserved granule count 0 (granule size 16777216)

The third part: the specific information of each heap mentioned above, and TRC the following information is based on the sub-heap of each heap, and the other sub-heap structure is the same.

HEAP DUMP heap name= "sga heap (1J 0)" desc=0x60034fe0

Extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0

Parent= (nil) owner= (nil) nex= (nil) xsz=0x1000000

Latch set 1 of 5

Durations enabled for this heap

Reserved granules for root 0 (granule size 16777216)

It can be seen that the sub-heap consists of zones, which in turn include multiple CHUNK

The fourth part is a BUCKET list of free lists.

FREE LISTS:

Bucket 0 size=32

Bucket 1 size=40

Bucket 2 size=48

Bucket 3 size=56

Bucket 4 size=64

Bucket 5 size=72

Bucket 6 size=80

Bucket 7 size=88

Bucket 8 size=96

Bucket 9 size=104

Middle strategy

Bucket 250 size=12352

Bucket 251 size=12360

Bucket 252 size=16408

Bucket 253 size=32792

Bucket 254 size=65560

In other words, the management of free space is managed by BUCKET, and the CHUNK address information that can be allocated or recycled is stored in the corresponding BUCKET. The specific BUCKET to be stored depends on the size of the CHUNK and matches the corresponding BUCKET.

Part V: a preliminary space list BUCKET list (same as part IV)

RESERVED FREE LISTS:

Reserved bucket 0 size=32

Reserved bucket 1 size=4400

Reserved bucket 2 size=8216

Reserved bucket 3 size=8696

Reserved bucket 4 size=8704

Reserved bucket 5 size=8712

Reserved bucket 6 size=8720

Reserved bucket 7 size=9368

Reserved bucket 8 size=9376

Reserved bucket 9 size=12352

Reserved bucket 10 size=12360

Reserved bucket 11 size=16408

Reserved bucket 12 size=32792

Reserved bucket 13 size=65560

Part VI: a list of chunk that can be rebuilt or reused for those who do not live in PIN (lru priority, LRU needs to be studied), including a lot of CHUNK as follows

UNPINNED RECREATABLE CHUNKS (lru first):

Chunk 0a3bd5420 sz= 56 recreate "fixed allocatio" latch=0x9e5c8db0-CHUNK address, size, status and type, LATCH address corresponding to CHUNK, which can be associated with LATCH in the first part of the TRC file after searching in the TRC file.

Chunk 0a3bc7fb8 sz= 56 recreate "fixed allocatio" latch=0x9e5c7d10-fixed allocatio corresponds to the ksmchcom of x$ksmsp, which can be understood as the name of CHUNK

Middle strategy

Chunk 0a3ba1a78 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3ba1848 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

SEPARATOR

Chunk 0a3bb2340 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3bb2110 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Middle strategy

Chunk 0a3b631e0 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62fb0 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62d80 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62b50 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Part 7: permanent or persistent CHUNK list, same as above, including many CHUNK, but here is only one CHUNK, and its type is PERM, and there is no LATCH protection

PERMANENT CHUNKS:

Chunk 09e0cd000 sz= 15937536 perm "perm" alo=8424224

Permanent space = 15937536

4. You can query X$KSMSP for the information of shared pool CHUNK.

SQL > select addr,ksmchidx,ksmchcom,ksmchptr,KSMCHCLS,ksmchsiz,ksmchtyp,ksmchdur from x$ksmsp where ksmchcom='fixed allocatio' and ksmchsiz=56 and KSMCHCLS='recr' and ksmchptr='00000000A3BD5420'

ADDR KSMCHIDX KSMCHCOM KSMCHPTR KSMCHCLS KSMCHSIZ KSMCHTYP KSMCHDUR

--

00002B0CBA8B5548 1 fixed allocatio 00000000A3BD5420 recr 56 72 2

5, if HANG SHARED POOL LATCH,oradebug dump heapdump will live in HANG

6. SHARED POOL LATCH was not found in the TRC file.

7. The total available space of the corresponding part is listed after each part of the above TRC file.

test

SQL > select * from v$version where rownum=1

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi

-- dump shared pool shared pool

SQL > oradebug setmypid

Statement processed.

SQL > oradebug dump heapdump 3

Statement processed.

SQL > oradebug tracefile_name

/ home/ora10g/admin/ora10g/udump/ora10g_ora_6533.trc

-- TRC file

-the first part is some information about latch.

KGH Latch Directory Information

Ldir state: 2 Last allocated slot: 77

Slot [1] Latch: 0xa4222c98 Index: 2 Flags: 3 State: 2 next: (nil)

Slot [2] Latch: 0xa4222d78 Index: 3 Flags: 3 State: 2 next: (nil)

Slot [3] Latch: 0x6000a6c0 Index: 4 Flags: 3 State: 2 next: (nil)

Middle strategy

Slot [75] Latch: 0x600270b0 Index: 1 Flags: 3 State: 2 next: 0x600e85c0

Slot [76] Latch: 0x6002abf0 Index: 2 Flags: 3 State: 2 next: (nil)

Slot [77] Latch: 0x60031378 Index: 3 Flags: 3 State: 2 next: 0x600e81b8

-the second part is the information of heap. You can see a total of 5 heap heaps. (note: _ kghdsidx_count=5, heap is a memory structure for allocating memory)

HEAP DUMP heap name= "sga heap" desc=0x60000058

Extent sz=0x47c0 alt=216 het=32767 rec=9 flg=-126 opc=0

Parent= (nil) owner= (nil) nex= (nil) xsz=0x160

Ds for latch 1: 0x60034fe0 0x60036838 0x60038090-it can be seen that 3 latch are required to protect its child heap

Ds for latch 2: 0x6003e808 0x60040060 0x600418b8

Ds for latch 3: 0x60048030 0x60049888 0x6004b0e0

Ds for latch 4: 0x60051858 0x600530b0 0x60054908

Ds for latch 5: 0x6005b080 0x6005c8d8 0x6005e130 0x6005f988-4 latch is required to protect its child heap

Reserved granule count 0 (granule size 16777216)

The third part is the specific information of each sub-heap mentioned above, which can be described only by one sub-heap, and the others are the same.

It is known that:

1, the first four heaps, each with 3 sub-heaps

The last heap, with four sub-heaps.

2 the following contents of the TRC file are based on the sub-heap of each heap, and my analysis is based on this.

The third part is explained in detail below, that is, the first sub-heap of the first heap, that is, sga heap (1), where 1 represents the first heap and 0 represents the first sub-heap.

HEAP DUMP heap name= "sga heap (1J 0)" desc=0x60034fe0

Extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-126 opc=0

Parent= (nil) owner= (nil) nex= (nil) xsz=0x1000000

Latch set 1 of 5

Durations enabled for this heap

Reserved granules for root 0 (granule size 16777216)

You can see that under the sub-heap is a zone extent.

EXTENT 0 addr=0x9e000000

Below the visible area extent are many chunk

Chunk 09e000058 sz= 48 R-freeable "reserved stoppe"-each chunk includes address, size, status and type

Chunk 09e000088 sz= 839496 R-free ""

Chunk 09e0ccfd0 sz= 48 R-freeable "reserved stoppe"

Chunk 09e0cd000 sz= 15937536 perm "perm" alo=8424224

Total heap size = 16777128-- this push size is the sum of all the chunk sizes above.

You can see that there is a free list of many bucket, the number and size of each bucket, totaling 254bucket

FREE LISTS:

Bucket 0 size=32

Bucket 1 size=40

Bucket 2 size=48

Bucket 3 size=56

Bucket 4 size=64

Bucket 5 size=72

Bucket 6 size=80

Bucket 7 size=88

Bucket 8 size=96

Bucket 9 size=104

Middle strategy

Bucket 250 size=12352

Bucket 251 size=12360

Bucket 252 size=16408

Bucket 253 size=32792

Bucket 254 size=65560

Total free space = 0

Then there is a prepared free list with the same format as above, which also records a lot of bucket

RESERVED FREE LISTS:

Reserved bucket 0 size=32

Reserved bucket 1 size=4400

Reserved bucket 2 size=8216

Reserved bucket 3 size=8696

Reserved bucket 4 size=8704

Reserved bucket 5 size=8712

Reserved bucket 6 size=8720

Reserved bucket 7 size=9368

Reserved bucket 8 size=9376

Reserved bucket 9 size=12352

Reserved bucket 10 size=12360

Reserved bucket 11 size=16408

Reserved bucket 12 size=32792

Reserved bucket 13 size=65560

For the unused chunk in the chunk in the above area extent, pay attention to the ""

Chunk 09e000088 sz= 839496 R-free ""

And visible chunk information is recorded in each bucket.

Indicate that the size of the free space prepared above is 839496, which is exactly which chunk mentioned above.

Total reserved free space = 839496

A list of chunk that can be rebuilt or reused for those who do not live in PIN (lru priority, LRU needs to be studied), including a lot of CHUNK below

UNPINNED RECREATABLE CHUNKS (lru first):

Chunk 0a3bd5420 sz= 56 recreate "fixed allocatio" latch=0x9e5c8db0-CHUNK address, size, status and type, LATCH address corresponding to CHUNK, which can be associated with LATCH in the first part of the TRC file after searching in the TRC file.

Chunk 0a3bc7fb8 sz= 56 recreate "fixed allocatio" latch=0x9e5c7d10-fixed allocatio corresponds to the ksmchcom of x$ksmsp, which can be understood as the name of CHUNK

Middle strategy

Chunk 0a3ba1a78 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3ba1848 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

SEPARATOR

Chunk 0a3bb2340 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3bb2110 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Middle strategy

Chunk 0a3b631e0 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62fb0 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62d80 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Chunk 0a3b62b50 sz= 560 recreate "KQR PO" latch=0x9e5c7d10

Indicate the size of the space not occupied by PIN mentioned above.

Unpinned space = 221984 rcr=78 trn=322

The list of permanent or persistent CHUNK, same as above, includes many CHUNK, but here there is only one CHUNK, its type is PERM, and there is no LATCH protection.

PERMANENT CHUNKS:

Chunk 09e0cd000 sz= 15937536 perm "perm" alo=8424224

Permanent space = 15937536

Indicate the size of the above permanent CHUNK space

Let's continue the analysis.

-- x$ksmsp records information about chunk in the shared pool. You can see a total of 20917 CHUNK.

SQL > select count (*) from x$ksmsp

COUNT (*)

-

20917

Query the first CHUNK in the list of chunk that can be rebuilt or reused without PIN.

SQL > select addr,ksmchidx,ksmchcom,ksmchptr,KSMCHCLS,ksmchsiz,ksmchtyp,ksmchdur from x$ksmsp where ksmchcom='fixed allocatio' and ksmchsiz=56 and KSMCHCLS='recr' and ksmchptr='00000000A3BD5420'

ADDR KSMCHIDX KSMCHCOM KSMCHPTR KSMCHCLS KSMCHSIZ KSMCHTYP KSMCHDUR

--

00002B0CBA8B5548 1 fixed allocatio 00000000A3BD5420 recr 56 72 2

LATCH corresponds to V$LATCH_chidlren in the first part of the TRC file visible below, and note: ADDR is lowercase, do not use uppercase, otherwise the information cannot be queried

SQL > select addr,latch#,level#,name from v$latch_children where lower (addr) like'% a4222c98%'

ADDR LATCH# LEVEL# NAME

00000000A4222C98 29 0 ksfv messages

But still can't find shared pool latch.

Increase the DUMP level and see if you can find shared pool latch.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug dump heapdump 10

ORA-00085: current call does not exist

SQL > oradebug dump heapdump 6

Statement processed.

SQL > oradebug tracefile_name

/ home/ora10g/admin/ora10g/udump/ora10g_ora_8143.trc

Still can not find shared pool latch, change the way of thinking, first HANG shared pool latch, and then check the DUMP file to see if there is, if not, it is my analysis idea is wrong

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

SQL > oradebug setmypid

Statement processed.

But fortunately, if HANG SHARED POOL LATCH finds out that oradebug dump heapdump 6 also lives in hang.

SQL > oradebug dump heapdump 6

You can only restore SHARED POOL LATCH in PRELIM mode first.

[ora10g@seconary ~] $sqlplus-prelim'/ as sysdba'

SQL*Plus: Release 10.2.0.5.0-Production on Thu Nov 19 07:37:53 2015

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL > oradebug poke 0x00000000600E7AF0 4 0

ORA-00074: no process has been specified

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7AF0 4 0

BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF

AFTER: [0600E7AF0, 0600E7AF4) = 00000000

It is found that heapdump 3 can not be HANG.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug dump heapdump 3

Statement processed.

In this way, HANG lives in the shared pool latch of CHILD#=2, depending on the situation, and finally finds that he will also live in HANG, probably because it is not a sub-pool, and in-depth reasons need to be studied.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7B90 4 1

BEFORE: [0600E7B90, 0600E7B94) = 00000000

AFTER: [0600E7B90, 0600E7B94) = 00000001

Thank you for reading this article carefully. I hope the article "Why oracle 10.2.0.5 only gets the shared pool latch of child#=1" shared by the editor will be helpful to you. At the same time, I also hope you will support us and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.