Jump to Navigation

227 - Oracle Memory Usage & Health Check

In terms of memory configuration on DB, you check your applications aspect.

* Sort and Hash join --> Need to increase PGA.
* Multiple & Variety SQLs --> Need to increase SGA.

< SGA >

1. Check if Buffer hit ratio > 95% or not

SQL> SELECT NAME, DB_BLOCK_GETS, CONSISTENT_GETS, PHYSICAL_READS,TRUNC((1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100,2) "HIT(%)" FROM V$BUFFER_POOL_STATISTICS;

2. Check if Library cache ratio > 95% or not

SQL> SELECT SUM(PINS) SUM_PINS, SUM(RELOADS) SUM_RELOADS, ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2) || '%' HIT_RATIO FROM V$LIBRARYCACHE;

3. Check if Dictionaly cache ratio > 95% or not

SQL> SELECT SUM(GETS) SUM_GETS, SUM(GETMISSES) SUM_GETMISSES, ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) || '%' AS HIT_RATIO FROM V$ROWCACHE;

 

< PGA >

1. The SQLs which have disk accesses while its sort :

SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS,ELAPSED_TIME,DIRECT_WRITES,SORTS FROM V$SQL WHERE DIRECT_WRITES > 0 AND SORTS > 0;

SQL> SELECT SQL_ID,EXECUTIONS,ELAPSED_TIME,DIRECT_WRITES,SORTS FROM V$SQL WHERE DIRECT_WRITES > 0 AND SORTS > 0;

2. Check the instance

SQL> SELECT NAME,VALUE FROM V$SYSSTAT WHERE STATISTIC# IN (349,350,351);

3. Check each session :

SQL> SELECT A.SID, B.NAME, A.VALUE FROM V$SESSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# AND A.STATISTIC# IN (349,350,351) ORDER BY SID,NAME;

 

 



Main menu 2

Story | by Dr. Radut