TUNING SHARED POOL | Oracle DBA Tutorial pdf

Tuning shared pool

Shared pool consists of library cache and data dictionary cache. Library cache caches most recently used SQL and PL/SQL statements. Data dictionary cache caches data dictionary information. Shared pool is managed by a Least Recently Used algorithm.
Finding a matching SQL statement in shared pool is known as cache hit. For cache hit to occur, two SQL statements must be exactly same i.e. their ASCII value equivalent should be same. The aim of tuning shared pool is to maximize cache hit ratio.
=> Measuring shared pool performance
High cache hit ratio indicates that your application users are getting results of SQL and PL/SQL mostly from memory rather than reading from disk.
SELECT * FROM V$LIBRARYCACHE
The value in GETHITRATIO column for SQL AREA, TABLE/PROCEDURE, BODY and TRIGGER rows should be very close to 1 i.e. 100%.
Similarly PINHITRATIO should also be very close to 1.
GET is referred to parse lock, while PIN is referred to execution time locks.
A well-tuned OLTP system should have GETHITRATIO and PINHITRATIO 90% or higher for SQL AREA.
Data dictionary performance is measured by
SELECT 1-(SUM(GETMISSES)/SUM(GETS)) “Data dictionary hit
ratio” FROM V$ROWCACHE
This value should be over 0.85 for OLTP systems.

=> Improving shared pool performance

=> Add more memory to shared pool
The size of shared pool is determined by SHARED_POOL_SIZE initialization parameter.
SELECT POOL, SUM(BYTES) "SIZE" FROM V$SGASTAT WHERE POOL =
'shared pool' GROUP BY POOL
Use following command to change shared pool size
ALTER SYSTEM SET SHARED_POOL_SIZE=200M
=> Make space for large PL/SQL statements
You can set aside a reserved area in SGA for large PL/SQL packages. This area is controlled by SHARED_POOL_RESERVED_SIZE parameter.
Determine which packages are loaded into memory from following command
SELECT * FROM V$DB_OBJECT_CACHE WHERE TYPE IN ('PACKAGE',
'PACKAGE BODY')
=> Keep important PL/SQL code in memory
You can “pin” most frequently used PL/SQL code in memory. To do this :
1. Build DBMS_SHARED_POOL package by running $ORACLE_HOME
/rdbms/admin/dbmspool.sql script.
2. Load a package using EXECUTE
DBMS_SHARED_POOL.KEEP(‘PACKAGE_NAME’)
You can see which packages are pinned by issuing
SELECT OWNER, NAME, TYPE FROM V$DB_OBJECT_CACHE WHERE KEPT =
‘YES’
You must have very good knowledge of application to determine which objects to pin right after instance startup. You can audit PL/SQL packages, triggers, sequences etc. and find out which objects are most frequently accessed.
=> Code reuse
Use bind variables like SELECT * FROM CUSTOMER WHERE CUST_ID = :cust_id
Implement strict coding standard (i.e. capitalization, indentation etc.) so that chances of finding identical statement in memory will increase.
=> Tune cache specific initialization parameters
OPEN_CURSORS (default 50, increase if necessary)
CURSOR_SPACE_FOR_TIME (set to FALSE for Forms based applications)
SESSION_CACHED_CURSORS
CURSOR_SHARING (use FORCE, SIMILAR or default EXACT)