Databases Reference
In-Depth Information
How to do it...
The following steps will demonstrate tuning of the Shared Pool:
1.
Connect to the database as
SYSDBA
:
CONNECT / AS SYSDBA
2.
Inspect which objects can be shared by querying the
V$DB_OBJECT_CACHE
dynamic performance view:
COL OWNER FOR A20
COL NAME FOR A30
COL TYPE FOR A20
SELECT OWNER, NAME, TYPE, SHARABLE_MEM
FROM V$DB_OBJECT_CACHE
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE',
'FUNCTION', 'TRIGGER')
AND KEPT = 'NO'
ORDER BY SHARABLE_MEM;
3.
Force a package to be kept in the shared pool:
EXEC SYS.DBMS_SHARED_POOL.KEEP('SYS.DBMS_SCHEDULER');
4.
Show the objects in the shared pool with a certain size:
SET SERVEROUTPUT ON
EXEC SYS.DBMS_SHARED_POOL.SIZES(500);
5.
Inspect the shared pool reserved memory:
SELECT * FROM V$SHARED_POOL_RESERVED;
6.
Inspect data dictionary cache statistics:
COL PARAMETER FOR A20
SELECT PARAMETER, GETS, GETMISSES,
(GETS-GETMISSES)*100/GETS AS "Hit Ratio",
MODIFICATIONS, FLUSHES
FROM V$ROWCACHE WHERE GETS > 0;
