Introduction:
This document is about KEEPing frequently used database objects like PROCEDURES, PACKAGES, SEQUENCES, TRIGGERS and CURSORS in the SHARED_POOL of SGA. Caching/KEEPing frequently
used objects in SHARED POOL will help improve the database performance.
Tested Environment: Oracle 9.2, Windows 2000 Server.
Connect to the database as sys:-
SQL> connect sys/**** as sysdba
Step#1: Create a table sp_obj_tab to hold the objects that need to be cached:-
SQL> CREATE TABLE sys.sp_obj_tab (
objownr VARCHAR2(64),
objname VARCHAR2(1000),
objtype CHAR(1),
listdte DATE,
keepdte DATE,
remarks CHAR(30));
Step#2: Take a look at script shared_pool_mgmt.SQL and run it, which will create a package called shared_pool_mgmt:-
SQL> @shared_pool_mgmt.SQL
Step#3: Create the below triggers which runs during database startup and shutdown:-
SQL> CREATE OR REPLACE TRIGGER db_shutdown_sp_obj_list
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.SHARED_POOL_MGMT.list_obj_table;
END;
/
SQL> CREATE OR REPLACE TRIGGER db_startup_sp_obj_keep
AFTER STARTUP ON DATABASE
BEGIN
sys.SHARED_POOL_MGMT.keep_obj_startup;
END;
/
Step#4: Schedule a dbms job that runs at regular intervals, the below once runs at 5 pm every day:-
SQL> VARIABLE JOBNO NUMBER;
SQL> BEGIN
DBMS_JOB.SUBMIT(JOB=>:JOBNO,
WHAT=>'shared_pool_mgmt.keep_obj_periodic;',
NEXT_DATE=>TRUNC(SYSDATE)+1+17/24,
INTERVAL=>'TRUNC(SYSDATE)+1+17/24');
COMMIT;
END;
/
SQL> PRINT JOBNO;
Below is the script, mentioned in Step#2, copy and paste it in a file called shared_pool_mgmt.SQL :-
CREATE OR REPLACE PACKAGE shared_pool_mgmt AS
---------------------------------------------------
---- Name: SHARED_POOL_MGMT
---- Schema: SYS
---- Created by: BabuMani
---- Created on: Dec-05-2006
-- Description: This package helps database
-- administrators to KEEP frequently used database
-- objects like PROCEDURES, PACKAGES, SEQUENCES,
-- TRIGGERS and CURSORS in the SHARED_POOL of SGA.
---------------------------------------------------
PROCEDURE list_obj_table;
-- PROCEDURE LIST_OBJ_TABLE: Identifies the top 10
-- objects based on various system resource
-- consuming factors and writes them in a table
-- called SP_OBJ_TAB. This table is populated
-- during database shutdown. This procedure is run
-- using database shutdown trigger (BEFORE
-- SHUTDOWN ON DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_startup;
-- PROCEDURE KEEP_OBJ_STARTUP: Reads the objects
-- listed in table SP_OBJ_TAB during database
-- startup and KEEP them (except CURSORS)in
-- SHARED_POOL. This procedure is run using
-- database startup trigger (AFTER STARTUP ON
-- DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_periodic;
-- PROCEDURE KEEP_OBJ_PERIODIC: It refreshes the
-- list of objects in table SP_OBJ_TAB and KEEP
-- them (including CURSORS) in SHARED_POOL.
-- Suggested to run this procedure at scheduled
-- intervals.
---------------------------------------------------
END shared_pool_mgmt;
/
CREATE OR REPLACE PACKAGE BODY shared_pool_mgmt AS
PROCEDURE list_obj_table IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM sys.sp_obj_tab;
COMMIT;
INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
SELECT DISTINCT owner,name,DECODE(type,'TRIGGER','R','P'),SYSDATE,'Listed to Keep in Shared Pool'
FROM v$db_object_cache
WHERE type IN ('PACKAGE','PROCEDURE','PACKAGE BODY','TRIGGER')
AND loads >= 1 AND executions > 0
UNION
SELECT DISTINCT owner,name,'Q',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$db_object_cache
WHERE type = 'SEQUENCE'
AND loads >= 1 ;
INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY executions DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY loads DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY buffer_gets DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY disk_reads DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY sharable_mem DESC)
WHERE ROWNUM<=10;
COMMIT;
END list_obj_table;
PROCEDURE keep_obj_startup IS
PRAGMA AUTONOMOUS_TRANSACTION;
own VARCHAR2(64);
nam VARCHAR2(1000);
typ VARCHAR2(1);
CURSOR cur1 IS
SELECT objownr, objname, objtype FROM SYS.sp_obj_tab WHERE objtype<>'C';
BEGIN
OPEN cur1;
LOOP
FETCH cur1 into own, nam, typ;
EXIT WHEN cur1%NOTFOUND;
SYS.DBMS_SHARED_POOL.KEEP(''|| own || '.' || nam || '',''||typ||'');
UPDATE sys.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
COMMIT;
END LOOP;
CLOSE cur1;
END keep_obj_startup;
PROCEDURE keep_obj_periodic IS
PRAGMA AUTONOMOUS_TRANSACTION;
own VARCHAR2(64);
nam VARCHAR2(1000);
typ VARCHAR2(1);
CURSOR cur2 IS
SELECT objownr, objname, objtype FROM SYS.sp_obj_tab;
BEGIN
shared_pool_mgmt.list_obj_table;
OPEN cur2;
LOOP
FETCH cur2 INTO own, nam, typ;
EXIT WHEN cur2%NOTFOUND;
IF typ='C' THEN
SYS.dbms_shared_pool.keep(''|| own ||'',''||typ||'');
ELSE
SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '',''||typ||'');
END IF;
UPDATE SYS.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
COMMIT;
END LOOP;
CLOSE cur2;
END keep_obj_periodic;
END shared_pool_mgmt;
/
Below are some Handy-Dandy queries:-
COLUMN pool FORMAT A25
SELECT pool||(' -- Total') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool
UNION
SELECT pool||(' --- Free') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE name='free memory' AND pool IS NOT NULL GROUP BY pool;
SELECT type,COUNT(*) COUNT FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;
SELECT DECODE(objtype,'R','Trigger','P','Package','C','Cursor','Q','Sequence',objtype) OBJTYPE, COUNT(*) COUNT FROM sp_obj_tab GROUP BY objtype ORDER BY 2 ;
COMPUTE SUM LABEL 'TOTAL ' OF COUNT ON REPORT
COMPUTE SUM LABEL 'TOTAL ' OF SHARABLE_MEM_K ON REPORT
BREAK ON REPORT
SELECT type,COUNT(*) COUNT, round(sum(SHARABLE_MEM)/1024) SHARABLE_MEM_K FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;
Library Cache Hit Ratio:
COMPUTE SUM LABEL 'TOTAL' OF PINHITS ON REPORT
COMPUTE SUM LABEL 'TOTAL' OF PINS ON REPORT
BREAK ON REPORT
 SELECT namespace
, pins
, pinhits
, reloads
, invalidations
FROM v$librarycache
ORDER BY namespace;
CLEAR COMPUTE
CLEAR BREAK
SELECT sum(pinhits) pinhits,
sum(pins) pins,
round((sum(pinhits)/sum(pins))*100,2) LIBRARY_CACHE_HIT_RATIO
FROM v$librarycache
ORDER BY namespace;
Dictionary Cache (Row Cache) Hit Ratio:
column parameter format a25
column RATIO format 999.9
column UPDATES format 999,999,999
SELECT parameter
, SUM(gets) gets
, SUM(getmisses) getmisses
, 100*SUM(gets - getmisses) / SUM(gets) RATIO
, SUM(modifications) UPDATES
FROM v$rowcache
WHERE gets > 0
GROUP BY parameter;
SELECT ROUND((SUM(gets - getmisses - fixed)) / SUM(gets)*100,2) "ROW CACHE" FROM v$rowcache;
Reference:
Oracle9i Database Performance Tuning Guide and Reference - Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool
Blog Archive
About Me
- babumani
- Database and GIS Consultant.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment