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