Q: Why shared pool duration or sub-sub-pool? 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 named duration1-[0-3], 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 <-- "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 <-- lots of library cache handles in duration 1 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 include "random" numbers (SQL hash values) in uniq -c 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 random part; we see 8197 entries for heap 0 in duration 2 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 random part; we see most entries in duration 3 are SQL Area 1022 KGLS^ 757 PLMCD 454 PLDIA (http://www.itpub.net/thread-1906818-1-1.html)