Shared pool freelists (and durations)
Posted by Riyaj Shamsudeen on August 9, 2009
My earlier blog about shared pool duration got an offline response from one of my reader:
” So, you say that durations aka mini-heaps have been introduced from 10g onwards. I have been using Steve Adams’ script shared_pool_free_lists.sql. Is that not accurate anymore?”
</p
Shared pool free lists
I have a great respect for Steve Adams . In many ways, he has been a great virtual mentor and his insights are so remarkable.
Coming back to the question, I have used Steve’s script before and it is applicable prior to Oracle version 9i. In 9i, sub-heaps were introduced. Further, shared pool durations were introduced in Oracle version 10g. So, his script may not be applicable from version 9i onwards. We will probe this further in this blog.
This is the problem with writing anything about internals stuff, they tend to change from version to version and In many cases, our work can become obsolete in future releases(including this blog!).
In version 9i, each sub-heap of the shared_pool has its own free list. In version 10g and 11g, each duration in sub-heap has its own free list. This is visible through x$ksmsp and column x$ksmsp.ksmchdur indicates the duration that chunk belongs to. In 9i, that column always has a value of 1 (at least, that I have experimented so far). In 10g & 11g (up to 11.1.0.7), there are exactly 4 durations in each sub-heap and values range from 1-4 for this column ksmchdur. Each duration has its own free list.
Shared_pool_free_list.sql script
I couldn’t find any script depicting shared pool free list accurately for versions 9i and above. So, I wrote a small script to print shared pool free list information from x$ksmsp. [Warning: Accessing x$ksmsp would add more pressure on shared pool latches.] Complete script can be downloaded from orainternals.com script archive shared_pool_free_lists_9i.sql .
SQL> @"shared_pool_free_lists_9i.sql" KSMCHIDX KSMCHDUR BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST ---------- ---------- ---------- ---------- ----------- ------------ ---------- 1 1 1 1320 33 40 40 2 2160 45 48 48 3 1400 25 56 56 4 2240 35 64 64 5 2448 34 72 72 6 3360 42 80 80 7 792 9 88 88 8 672 7 96 96 9 1664 16 104 104 10 2352 21 112 112 11 1680 14 120 120 12 1792 14 128 128 13 4896 36 136 136 14 2016 14 144 144 15 3040 20 152 152 16 2880 18 160 160 88 736 1 736 736 252 45824 2 22912 22928 253 528212104 41 12883222 15597568 ...
Above output shows shared pool free list for sub-heap 1 (ksmchidx=1), duration 1(ksmchdur=1) more technically sga heap (1, 1). In a free list, free Chunks are attached to a bucket classified by chunk size. This facilitates chunk search so that a chunk of a specific size can be easily found. For example, bucket 7 holds chunks bigger than 88 bytes but smaller than 96 bytes. Bucket 8 has chunks bigger than 96 and smaller than 104. Say, for example, for an allocation request of chunk with size of 96 bytes, code can quickly look up bucket 8 and after to find a chunk of size at least 96 bytes. Above script is an excellent indicator to see if the shared pool is fragmented (In this case, shared pool is not fragmented since number of chunks are few).
Problem is that this script also does not depict free list information accurately 10g or 11g. In version 9i, bucket size between two adjacent buckets are 8 until size is less than 1672 bytes and then increases to 48, followed by near 8K difference and so the script works fine in 9i.
... case when ksmchsiz < 1672 then trunc((ksmchsiz-32)/8) when ksmchsiz < 4120 then trunc((ksmchsiz+7928)/48) when ksmchsiz < 8216 then 250 when ksmchsiz < 16408 then 251 when ksmchsiz < 32792 then 252 when ksmchsiz = 65560 then 253 end bucket,...
But, in 10g and 11g, that is not true anymore (at least that I can observe). For example, in the output below, after bucket 239, difference between two adjacent buckets went down to 8. So, the script method is not dependable. Still, I must insist, that script output is a pretty good indicator of shared pool free space fragmentation.
... Bucket 239 size=4024 48 Bucket 240 size=4096 72 Bucket 241 size=4104 8 Bucket 242 size=4120 16 Bucket 243 size=8216 4096 Bucket 244 size=16408 8192 Bucket 245 size=16888 480 Bucket 246 size=16896 8 Bucket 247 size=16904 ...
heapdump_freelist.ksh
Must we need more accurate picture, we can parse heapdump trace file to print it. I wrote another korn shell based awk script heapdump_freelist.sh . This script parses heapdump level 2 trace files and prints the shared pool free list information more accurately. This script works correctly in Oracle versions 9i,10g and 11g. This script accepts an heapdump level 2 trace file as an argument.
./heapdump_freelists.ksh DEV11g_ora_553.trc -- Heapdump freelist v1.00 by Riyaj Shamsudeen @OraInternals This script prints shared pool freelists details 1. /tmp/shared_pool_freelist.lst - Freelist information for various heaps Few lines from /tmp/shared_poool_freelist.lst shows that freelist information is printed at more granular level. ------------------------------------------ sga heap(1,1) ------------------------------------------ Bucket 1 size=40 Count= 75 Sum= 3000 Bucket 2 size=48 Count= 176 Sum= 8448 Bucket 3 size=56 Count= 72 Sum= 4032 Bucket 4 size=64 Count= 86 Sum= 5504 Bucket 5 size=72 Count= 173 Sum= 12456 ... Bucket 42 size=368 Count= 1 Sum= 368 Bucket 51 size=440 Count= 1 Sum= 440 ... Bucket 243 size=8216 Count= 3 Sum= 29904 Bucket 248 size=16912 Count= 1 Sum= 17208 Bucket 252 size=20552 Count= 2 Sum= 45152 Bucket 253 size=32792 Count= 1 Sum= 47064 Bucket 254 size=65560 Count= 6 Sum= 52860352 ------------------------------------------ sga heap(1,2) ------------------------------------------ Bucket 1 size=40 Count= 1 Sum= 40 Bucket 3 size=56 Count= 2 Sum= 112 Bucket 5 size=72 Count= 1 Sum= 72 ..
Summary
In summary, shared_pool_free_lists.sql is immensely helpful, but alas internal working details changes from version to version. We have added two scripts to print shared pool free list more accurately:
- shared_pool_free_lists.sql to show freespace fragmentation through SQL.
- heapdump_freelist.ksh to parse heapdump trace file and print free list information more accurately.
This blog is available in pdf format shared_pool_free_lists_and_duration.pdf .
Blogroll Report 07/08/2009 – 14/08/2009 « Coskan’s Approach to Oracle said
[…] Riyaj Shamsudeen – Shared pool freelists (and durations) […]
http://tinyurl.com/adfukeel11330 said
I actually think this blog post , “Shared pool freelists (and durations)
Oracle database internals by Riyaj”, fairly entertaining
plus the post was a good read. Regards-Sandra