Shared pool freelists (and durations)

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?”

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:

1. shared_pool_free_lists.sql to show freespace fragmentation through SQL.
2. 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 .

COLLABORATE 2009 presentation: 11g performance new feature.

I just presented about Oracle 11g new features specific to performance in COLLABORATE 2009, Orlando Florida. You can download presentation and paper from here:

11g performance specific new features – presentation
11g performance specific new features – paper

I met couple of new friends and many familiar faces.

Catherin Devlin introduced sqlpython tool to me. This tool is an user friendly replacement for sqlplus with many UNIX like goodies. This tool will be an useful addition to command line tools. You can see installation instructions for sqlpython here.

I also attended an interesting application performance panel discussion with fellow Oakie Mark W Farnheim, Mike Brown and Sandra Vucinic. We discussed few customer issues.

An interesting problem discussion is worth mentioning. A client clones production database to development database using full rapidclone methodology. But, access plans for a SQL statement is different between production and development, even though everything was exactly the same. I am hoping, that client will send 10053 trace files from both databases and I will blog about it if I get anything from that client. I think, bind peeking is causing the plan difference, but need to analyze 10053 trace file to confirm that. Another possibility is that, may be , he is not cloning Oracle Software and some software patches are not existing in cloned environment. Need trace files to proceed further here.

Of course, I met few other friends Jeremy Schneider, Dan Norris to mention few.

DOUG presentation: Why does optimizer hate my SQL?

I presented about Cost based optimizer explaining why some times CBO chose inefficient access plan, even though, there is an efficient plan in the search space. This entry is to post presentation slides and they can be downloaded from Why_optimizer_hates_my_sql

.

Update: Updated presentation after Greg’s comments.
Update2: Further bug fixes in presentation and script.
Update3: Updates after Randolf’s comments.

Lock table followed by DDL?

My client DBA wrote a small script: To drop a column to a busy table. This application is mostly an OLTP application and table is modified heavily. It is hard to get a lock on the table, even though DDL to drop the column will be quick completing within sub-seconds. Following script was tried in the middle of night so as to minimize production impact.

set serveroutput on size 100000
declare
begin
      -- lock table in exclusive mode
      execute immediate 'lock table t1 in exclusive mode';
      dbms_output.put_line ('**** Table locked. Dropping column ****' ) ;
      -- If we lock it, drop the column
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

Logic of the above script is a) wait for 5 minutes to lock the table in exclusive mode, before timing out. b) If we acquire exclusive lock on that table, then add a column.

Do you see any issues with this script?

So, did we add that column ?

Correlation, nocorrelation and extended stats

I blogged about extended stats in my earlier blog, extended stats, and also documented that as an investigation in Investigations: extended stats and multi-column correlation. I was testing extended stats further and ran in to some interesting situations.

Extended stats can be used to store correlation between columns. Correlation between two columns needs to detect at least, two properties of the column values:

1. Correlated column values
2. Uncorrelated column values

Let’s explore this further.
Test case