Oracle 10g Performance Tuning Scripts (Sure Shot)

Oracle 10g Performance Tuning Scripts (Sure Shot)
-----------------------------------------------------------


-- SHARED POOL TUNING

-- Library Cache Hit Ratio should be less than 90%. if not, increase the size of SHARED POOL

select gethitratio * 100 "Library Cache Hit Ratio"
  from v$librarycache
 where namespace = 'SQL AREA';


-- "RELOADS TO PINS RATIO" ratio should be less than 1 percent. Other wise SHARED_POOL_SIZE need to be increased.

SELECT SUM(PINS) "EXECUTIONS",
       SUM(RELOADS) "CACHE MISSES",
       (SUM(RELOADS) / SUM(PINS)) * 100 "RELOADS TO PINS RATIO"
  FROM V$LIBRARYCACHE;



-- "CACHE HIT RATIO" should be greaer than 90 percent

SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "CACHE HIT RATIO"
  FROM V$LIBRARYCACHE;
 

 
-- check invalidations

SELECT NAMESPACE,PINS,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE;

--take advice for shared pool size

SELECT SHARED_POOL_SIZE_FOR_ESTIMATE AS po_size, ESTD_LC_TIME_SAVED FROM V$SHARED_POOL_ADVICE;


-- DATA DICTIONARY CACHE TUNING

-- this ratio should be greater than 85% other wise need to increase SHARED_POOL_SIZE.


SELECT (1-(SUM(GETMISSES)/SUM(GETS)))*100 "DICTIONARY CACHE HIT RATIO" FROM V$ROWCACHE;

--get misses should be less than 15% OF GETS, other wise need to increase SHARED_POOL_SIZE.

SELECT PARAMETER,GETS,GETS*.15 "15% OF GETS", GETMISSES, (GETS*.15)-GETMISSES  FROM V$ROWCACHE;

** Thanks for reading this article. happy to help. have a good day.