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 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 »