Monday, January 16, 2012

Keeping PL/SQL in Oracle Memory

Lets start with the problem!

If an application calls a BIG pl/sql block into a memory, it may result in kicking out several other cached SQL statements because of LRU (least recently used) algorithm. Now the subsequent call will increase the 'reloads'. To understand the concept about 'reload', please see my article athttp://www.bash-dba.com/2012/01/measuring-shared-pool-performance.html

That's where the reserved area of shared pool comes in. This area is called Shared Pool Reserved Area which is set by SHARED_POOL_RESERVED_SIZE parameter. The size of the reserved pool can be up 50 % of the shared_pool_size.

We can also get a help on properly setting the shared_pool_reserved_size by querying the dynamic performance view called V$SHARED_POOL_RESERVED as below

If you see the following then probably your reserved_pool_size is over congigured

Request_misses shows constantly 0 or static

The value shown in FREE_SPACE is more than 50 % of the shared_pool_size

Any non-zero or steady increase size of REQUEST_FAILURE shows that reserved area is too small.

We can use the below script to find out the size of shared_pool and see if the FREE_SPACE is more than 50 % of it.

SQL> SELECT pool, sum(bytes) "SIZE"

2 FROM v$sgastat

3 WHERE pool = ’shared pool’

4 GROUP BY pool;

POOL SIZE

----------- ---------------------

shared pool 838860800

From that we know that our free_space column value in v$SHARED_POOL_RESERVED is less than 50 % of the shared pool. So we are good here!

Now the process of adding the PL/SQL code permanently into the memory is call pinning. Once pinned, the PL/SQL code will remain in a portion of shared pool allocated by shared_pool_reserved parameter until the instance is bounced.

Since, now we have the basic understanding of shared pool reserved area and reasons for its configuration, lets now configure it in two simple steps

1. Build DBMS_SHARED_POOL package as its not installed by default by running dbmspool.sql script

SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

2. Use the dbms_shared_pool two procedures (KEEP or UNKEEP) to pin the object in memory as below

SQL> EXECUTE DBMS_SHARED_POOL.KEEP (‘PREOCEDURE_TO_BE_PINNED’);

Once done you can verify your steps above by query the below mentioned view