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?”
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!).
Posted in 11g, Oracle database internals, Performance tuning, shared_pool | Tagged: bucket, durations, heapdump_freelist.ksh, ksmchdur, ksmchidx, oracle, oracle performance, performance logfile dump, sga heap, shared pool, shared_pool_free_lists_9i.sql, x$ksmsp | 2 Comments »
Posted by Riyaj Shamsudeen on August 6, 2009
After reading my earlier post on shared pool A stroll through shared pool heap , one of my client contacted me with an interesting ORA-4031 issue. Client was getting ORA-4031 errors and shared pool size was over 4GB ( in a RAC environment). Client DBA queried v$sgastat to show that there is plenty of free memory in the shared pool. We researched the issue and it is worth blogging. Client DBA was confused as to how there can be ORA-4031 errors when the shared pool free memory is few GBs.
At this point, it is imperative to take heapdump in level 2 and Level 2 is for the shared pool heap dump. [ Please be warned that it is not advisable to take shared pool heap dumps excessively, as that itself can cause performance issue. During an offline conversation, Tanel Poder said that heapdump can freeze instance as his clients have experienced.]. This will create a trace file in user_dump_dest destination and that trace file is quite useful in analyzing the contents of shared pool heap. Tanel Poder has an excellent script heapdump_analyzer . I modified that script adding code for aggregation at hea, extent and type levels to debug this issue further and it is available as heapdump_dissect.ksh . ( with a special permission from Tanel to publish this script.)
Shared pool review
You can read much more about shared pool in my earlier blog entry posted above. Just as a cursory review, shared pool is split in to multiple sub heaps. In 10g, each of those sub heaps are divided in to even smaller sub heaps, let’s call it mini-heaps. For example, in this specific database, there are three sub heaps. Each of those sub heaps are further split in to four mini-heaps (1,0), (1,1), (1,2) and (1,3) each.
Posted in Oracle database internals, Performance tuning, RAC | Tagged: durations, heapdump, KGH:NO ACCESS, ORA-4031, oracle performance, sga heap, shared pool, shared pool heapdump, _enable_shared_pool_durations, _ksmg_granule_size | 16 Comments »
Posted by Riyaj Shamsudeen on January 15, 2009
Last week, we were discussing about increasing shared_pool_reserved_size to combat a performance issue(bug) in a conference call. I thought, it was a common knowledge that shared_pool reserved area is part of a shared_pool and surprisingly it is not-so-common.
In this blog, we will discuss about shared_pool and shared_pool reserved area internals. First, we will discuss about details specific to release 9i and then discuss changes in later releases 10g/11g.
We will use oradebug command to dump the heap with level 2. Level 2 is to dump shared_pool heap in to a trace file.
oradebug dump heapdump 2
Above command generates a trace file and we will walk through the trace file and review various areas closely.
In this test instance, we have a bigger SGA. Shared_pool (6GB) and shared_pool_reserved_size values are printed below.
SQL> show parameter shared_pool
shared_pool_reserved_size big integer 629145600
shared_pool_size big integer 6442450944
Trace file analysis
Posted in Oracle database internals, Performance tuning | Tagged: latches, oracle performance, sga heap, shared_pool_reserved_size, shared_pool_size, x$ksmsp, x$ksmspr, x$ksmss, _shared_pool_reserved_min_alloc | 12 Comments »