oracle 加权久期,谁能详细解释一下shared pool duration特性

本帖最后由 Yong Huang 于 2015-2-13 10:28 编辑

A shared pool has subpools (starting with 9i) and each subpool has sub-subpools or durations (starting with 10gR2 I think). It's easy to understand why there're subpools; each subpool is managed pretty much independently, with its own LRU list etc., and all subpools have the same functionality. But durations are different. Each one holds different types of objects. According to heapdump, as shown in Jonathan Lewis's "Oracle Core Essential Internals" p.184, dictionary cache is in duration 1, heap 0 (cursor head or parent cursor) in duration 2, SQLArea (heap 6) in duration 3.[note] Different types of objects have different characteristics. Within each type, objects have similar characteristics, such as size requirement, how long they stay in memory, etc. Oracle's work on shared pool durations is kind of like Solaris or Linux kernel slab. A chunk of memory is allocated from a specific duration (slab) depending on what function you want to use this memory chunk for. There're of course differences. A Solaris or Linux kernel has many slabs, while there're only 4 Oracle shared pool durations. Each slab is exclusively dedicated to one type of objects, such as inode_cache, nfs_page. A duration can contain various types of objects; the rule about what shared pool objects are in what duration is not very strict.

Other than Jonathan Lewis's book, this note

Bug 14311437 : ORA-600 [5351], ORA-600 [4000] AND ORA-4031 OCCURING IN GOLDEN GATE AND TIMESTEN

talks about the concept of durations, and more or less satisfy my curiosity why it's called "duration", a term that suggests time or how long something lasts.

One paragraph (Solution c) in

LCK temporarily stuck waiting for latch 'Child row cache objects' (Doc ID 843638.1)

also talks about durations.

The fixed table showing durations is x$ksmsp_nwex. You can find a query using this table in

Bug 14020215 : ORA-4031 WITH 7 SUBPOOLS AND DURATIONS THEN CPU SPIKES TO 100%

______________

[note]

On an 11.2.0.3 database, I have durations enabled (_enable_shared_pool_durations is TRUE). If you don't use ASMM nor AMM, you may have to manually set this parameter to true and bounce the database. I create a level 2 heapdump (oradebug dump heapdump 2) and manually split the trace file into four files, each for one duration of subpool 1. Then I aggregate on the memory chunk usage string and see what type of usage is the most common in each duration.

$ cut -c53-67 duration1-0 | sort | uniq -c | sort -rn | head

7127 KQR PO        

2826 KGLHD        

287

28

27 SQLA^c22c7d28

27 KGLH0^c22c7d28

23 SQLA^7e6b9434

23 KGLH0^7e6b9434

20 SQLA^a2ac011a

20 perm

$ cut -c53-67 duration1-1 | sort | uniq -c | sort -rn | head

10578 KGLHD        

7127 KQR PO

6588 KGLDA

2703

1217 ges resource

183

143 parameter table

72 name-service

28 KGLNA

19 KKSSP

$ cut -c53-67 duration1-2 | sort | uniq -c | sort -rn | head

8546

247

191 KGLH0^9e6af5b8        

53 KGLH0^52ccb2f2

52 KGLH0^d7bcc960

49 KGLH0^c22c7d28

49 KGLH0^1a8436ae

45 KGLH0^a2ac011a

35 KGLH0^d9085754

35 KGLH0^c5be8292

$ cut -c53-57 duration1-2 | sort | uniq -c | sort -rn | head -5

8546

8197 KGLH0               

247

175 PRTMV

115 PRTDS

$ cut -c53-67 duration1-3 | sort | uniq -c | sort -rn | head

21323

2810 SQLA^b3947bfc

1168 SQLA^52ccb2f2

1104 SQLA^d7bcc960

1020 SQLA^1a8436ae

401 SQLA^8bfc3f48

297 SQLA^c22c7d28

270

240 SQLA^a2ac011a

234 SQLA^b91ee9fa

[oracle@dctrpdbms3b trace]$ cut -c53-57 duration1-3 | sort | uniq -c | sort -rn | head -5

21323

18019 SQLA^               

1022 KGLS^

757 PLMCD

454 PLDIA