What's shared pool duration ?, Sharedduration
This article is the moderator's reply after a PUB asks a question.
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 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.
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.
$ Cut-c53-67: duration1-0 | sort | uniq-c | sort-rn | head
7127 kqr po <-- "kqr po" row cache parent objects are very common in duration 0
2826 KGLHD <-- Library cache handles are common too
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 <-- library cache handles
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 <-- not fair to cut columns including "random" addresses
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 <-- So cut without the address part, we see 8197 entries for heap 0
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 ^ <-- remove the address part, we see most entries are SQL Area
1022 KGLS ^
757 PLMCD
454 PLDIA
You can see what type of usage is the most common in each duration.
