Performance Tuning Part 1

1) PERFORMANCE TUNING

----SUGGESTED MINIMUM SHARED POOL SIZE;

SELECT  cr_shared_pool_size,sum_obj_size, sum_sql_size,sum_user_size,(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool

FROM (SELECT  SUM(sharable_mem) sum_obj_size FROM v$db_object_cache where type<> 'CURSOR'),

     (SELECT  sum(sharable_mem) sum_sql_size FROM v$sqlarea),

     (SELECT  sum(250*users_opening) sum_user_size FROM v$sqlarea),

     (SELECT  to_Number(b.ksppstvl) cr_shared_pool_size

         FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx and a.indx = c.indx  AND a.ksppinm ='__shared_pool_size' );

----1.0 Oracle BUFFER CACHE HIT RATIO - Oracle memory tuning

----Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value.

----(should be > 70, else increase db_block_buffers in init.ora)

SELECT  a.value + b.value "LOGICAL_READS", c.value "PHYS_READS",round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"

from v$sysstat a, v$sysstat b, v$sysstat c

where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;

-------------------------------/* OR */----------/* TUNING THE BUFFER CACHE HIT RATIO */--------------------------

--SELECT  100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"

--from  v$sysstat v1, v$sysstat v2, v$sysstat v3

--where v1.name = 'db block gets' and v2.name = 'consistent gets' and  v3.name = 'physical reads';

------------------------------/* OR */------

--------Goal is to have a Cache Hit Ratio greater than 90% -- if lower, increase value for DB_BLOCK_BUFFERS in the init&SID..ora file.

----SELECT  name, valueFROMv$sysstat

----where  name in ('db block gets', 'consistent gets', 'physical reads');

----------------------------------------

--------If the number of free buffers inspected is high or increasing, consider increasing the DB_BLOCK_BUFFERS parameter in the init&SID..ora file.

----------------in oracle 9 and others increase increate the initialisation parameter DB_CACHE_SIZE.

SELECT  'Current DB_BLOCK_BUFFERS value is ' || value FROM v$parameter WHERE name = 'db_block_buffers';

SELECT  name, value FROM v$sysstat WHERE name = 'free buffer inspected';

------------A high or increasing number of waits indicates that the db writer cannot keep up writing dirty buffers. Consider increasing the number of

----writers using the DB_WRITER_PROCESSES parameter in the init&SID..ora file.

SELECT  'Current DB_WRITER_PROCESSES value is ' || value FROM v$parameter WHERE name = 'db_writer_processes';

SELECT  event, total_waits FROM v$system_event WHERE event in   ('free buffer waits', 'buffer busy waits');

-----------------------------------------------

----If the LRU Hit percentage is less than 99%, consider adding more DB_WRITER_PROCESSES and increasing the DB_BLOCK_LRU_LATCHES parameter in the init&SID..ora file.

----------------SELECT  'Current DB_WRITER_PROCESSES value is ' || v1.value || chr(10) ||   'Current DB_BLOCK_LRU_LATCHES value is ' || v2.value

----------------   FROM     v$parameter v1,v$parameter v2

----------------  WHEREv1.name = 'db_writer_processes' and v2.name = 'db_block_lru_latches';

--------------

SELECT  name, 100 - (sleeps/gets * 100) "LRU Hit%" FROM v$latch WHERE name = 'cache buffers lru chain';

------------------------------/*  DATA DICTIONARY HIT RATIO  */--------------------------------------

--2.0 Calculate Oracle dictionary Cache Ratio - Oracle memory tuning

--Aim for a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column

--(should be higher than 90 else increase shared_pool_size in init.ora)

SELECT  parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

 FROM      v$rowcache

 WHERE  gets+getmisses <>0

group by parameter, gets, getmisses ;

------------------------------------

SELECT  sum(gets) "Data Dict. Gets", sum(getmisses) "Data Dict. cache misses", trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"

 FROM      v$rowcache;

----If the gethitratio is greater than .15 -- increase the SHARED_POOL_SIZE parameter in the init&SID..ora file.

SELECT  sum(gets) "totl_gets", sum(getmisses) "totl_get_misses",   sum(getmisses)/sum(gets)  "gethitratio"   FROM     v$rowcache;

SELECT  sum(gets) "totl_gets", sum(getmisses) "totl_get_misses",   sum(getmisses)/sum(gets) * 100 "gethitratio"   FROM     v$rowcache;

---------------------------------/* BUFFER , DICTIONARY , LIBRARY CACHE HIT RATIOS */-----------------------------------

SELECT  'Buffer Cache' NAME,

(1-(SUM(DECODE(NAME,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+ sum(decode(name,'consistent gets',value,0)))))*100 "HIT RATIO"

 FROM      V$SYSSTAT

union

SELECT  'Dictionary Cache',(1-(SUM(getmisses)/SUM(gets)))*100 "Hit Ratio" FROM     v$rowcache

union

SELECT  'Library Cache',sum(pins)/(sum(pins)+sum(reloads))*100 "Hit Ratio" FROM     v$librarycache;

------------------------------/*   Library Cache Section */------------------------------------------------------------------------------------------------------

--3.0 Calculate Individual Cache Hit Ratios - Oracle memory tuning --Reduce the Reloads and try to increase the hit ratios to above 85%

-------hit ratio should be > 70, and pin ratio > 70 ...

SELECT  namespace,gets,gethits,gethitratio,pins,pinhits,pinhitratio, reloads

 FROM      v$librarycache

 WHERE  gets+gethits+pins+pinhits>0;

-------------------------------------

SELECT  namespace, trunc(gethitratio * 100) "Hit ratio",

trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"

 FROM      v$librarycache;

-----------------------------------------/* SHARED POOL */----------------------------------------------

SELECT  'Current SHARED_POOL_SIZE value is ' || value FROM v$parameter WHERE name = 'shared_pool_size';

--------------------------------------------------------------

----Shared Pool Reserved space -- The goal is to have zero REQUEST_MISSES and REQUEST_FAILURES, so increase SHARED_POOL_RESERVED_SIZE in the init&SID..ora

----file if either of them are greater than 0.

 SELECT  request_misses, request_failures, last_failure_size FROM v$shared_pool_reserved;

--------------------------

 SELECT  'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,'Request Failures = '||REQUEST_FAILURES  Logic

 FROM          v$shared_pool_reserved

 WHERE      REQUEST_FAILURES > 0 and     0 != (SELECT      to_number(VALUE) FROM v$parameter WHERE    NAME = 'shared_pool_reserved_size')

union

SELECT  'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,'Request Failures = '||REQUEST_FAILURES Logic

 FROM          v$shared_pool_reserved

 WHERE      REQUEST_FAILURES < 5 and     0 != (SELECT      to_number(VALUE) FROM v$parameter WHERE    NAME = 'shared_pool_reserved_size');

------------------/* LIBRARY CACHE MISS RATIO */--------------------------

----4.0 Calculate the Oracle Library Cache Ratio for the Whole system - Oracle memory tuning

----This should be near 0.

----(If > .1, i.e., more than 1% of the pins resulted in reloads, then increase the shared_pool_size in init.ora)

----•Executions - The number of times a pin was requested for objects of this namespace.

----•Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the objectFROM    disk.

SELECT  sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcache FROM    v$librarycache;

--------------/* OR */----------------

----If reloads-to-pins ratio is greater than .01, increase SHARED_POOL_SIZE in the init&SID..ora file.

SELECT  sum(pins) "executions", sum(reloads) "CMWE(LC Misses)",(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO" FROM     v$librarycache;

----------------------"Cache misses while executing(LC Misses)" ="CMWE(LC Misses)"

------------------------------------------------

--------------The miss ratio should be less than 15%. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE.------------------

SELECT  sum(getmisses) / sum(gets) "Miss ratio" FROM     v$rowcache;

------------------------------------------------------------------------------

--------The hit ratio should be at least 85% (i.e. 0.85). The reload percent should be very low, 2% (i.e. 0.02) or less.

--------If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter

--------OPEN_CURSORS may also need to increased.---

SELECT  sum(pinhits) / sum(pins) "Hit Ratio",    sum(reloads) / sum(pins) "Reload percent"

 FROM      v$librarycache WHERE namespace in ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-----NOTE: If the Reload Ratio is above 1 percent then the SHARED_POOL_SIZE parameter should probably be increased.Like wise, if the Library cache hit rario

-----comes in below 95(The hit ratio should be at least 85% (i.e. 0.85)) percent then the SHARED_POOL_SIZE may need to be increased.

SELECT  sum(pins) "Hits" ,sum(reloads) "Misses",sum(pins) / (sum(pins)+sum(reloads)) "Hit Ratio" FROM     v$librarycache ;

-------The hit reatio is excellent and (over 99 percent) and does not require any increase in the SHARED_POOL_SIZE parameter

--------------------------------

----If ratio is greater than .15, consider increasing  SHARED_POOL_SIZE in the init&SID..ora file.

SELECT  sum(gets) "Total Gets", sum(getmisses) "Total Get Misses",   sum(getmisses)/sum(gets) "Ratio" FROM     v$rowcache;

--------------------------------------------/* HIT RATIO FOR MUTLIPLE POOLS */----------

----5.0 Calculating the hit ratio for multiple pools - Oracle tuning--To run this script you must get connect as user sys

SELECT  name,1 - ( physical_reads / ( db_block_gets +consistent_gets)) "HIT RATIO"  FROM sys.v$buffer_pool_statistics WHERE db_block_gets + consistent_gets > 0;

----------------------------------------/* SESSION LEVEL HIT RATIO */------

----6.0 Check Session Level Hit Ratio - Oracle tuning--The Hit Ratio should be higher than 90%

-------- When all application users are running in the server then we have to see the bufferhit ratio of all application users so that we can measure individuals performance

SELECT  Username,OSUSER,Consistent_Gets,Block_Gets,Physical_Reads,

100*( Consistent_Gets + Block_Gets - Physical_Reads)/( Consistent_Gets + Block_Gets ) "Hit Ratio %"

 FROM      V$SESSION,V$SESS_IO

 WHERE  V$SESSION.SID = V$SESS_IO.SID  and ( Consistent_Gets + Block_Gets )>0 and username is not null

order by Username,"Hit Ratio %";

----(0r)

SELECT  distinct username, (1-(physical_reads/(consistent_gets+block_gets)))*100 "Buffer Cache Hit Ratio"

 FROM      v$session se, v$sess_io si

 WHERE  se.sid = si.sid and (consistent_gets+block_gets) > 0 and username is not null

order by username;

---------------------------------------------------/* SESSION SPECIFIC MEMORY */-----------

------7.0 List Session Specific Memory - Oracle memory tuning---List the UGA and PGA used by each session on the server

SELECT  se.sid,n.name, max(se.value) maxmem

 FROM      v$sesstat se,v$statname n

 WHERE  n.statistic# = se.statistic#

and n.name in ('session pga memory','session pga memory max','session uga memory','session uga memory max')

group by n.name,se.sid

order by 3;

---------------------------------------------------/* SIZE OF STORED PROCEDURES */-----------------

----8.0 List the size of Oracle stored procedures and use it to tune Oracle shared pool - Oracle memory tuning--

----This script lists the size of stored objects

SELECT  count(name) num_instances ,type,sum(source_size) source_size,sum(parsed_size) parsed_size,sum(code_size) code_size,sum(error_size) error_size

,sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size) size_required

 FROM      dba_object_size

group by type

order by 2;

--------------------------------------------------

------9.0 Redo Latch Contention Monitor - Oracle memory tuning----Try to reduce the contention by reducing all the ratios to be less than 1

SELECT  name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,

Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

---------------------------------------------------/*  Tuning Sorts */-------------------------

------10.0 Oracle sorts Monitoring Scripts - Oracle memory tuning--Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10

SELECT  name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

SELECT DISK/MEMORY

FROM (SELECT  name, value MEMORY FROM v$sysstat WHERE name = 'sorts (memory)'),(SELECT  name, value DISK FROM v$sysstat WHERE name = 'sorts (disk)');

select * from v$parameter order by 2;

--SELECT  54/8193352 *100FROM    v$sysstat

-- WHERE  name IN ('sorts (memory)', 'sorts (disk)');

----The ratio of disk sorts to memory sorts should be less than 5%. Considerincreasing the SORT_AREA_SIZE parameter in the init&SID..ora file. You

----might also consider setting up separate temp tablespaces for frequent users of disk sorts.

SELECT  'Current SORT_AREA_SIZE value is ' || value FROM v$parameter WHERE name = 'sort_area_size';

SELECT  disk.value "Disk", mem.value "Mem", (disk.value/mem.value)  "Ratio"

 FROM      v$sysstat mem, v$sysstat disk

   WHERE  mem.name = 'sorts (memory)'  and disk.name = 'sorts (disk)';

------------------------------------------------/* SHARED MEMORY USAGE REPPORT */--------------

--Description:    Table Shared Memory Usage Report

--Code:

--SELECT  name inameFROM    v$database;

--SELECT  to_char(sysdate, 'Mon DD, YYYY HH24:MI') todayFROM    dual;

----btitle ce '======================================================' skip 1 -

----le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno

----ttitle center 'Database: ' instance ' - Table Usage Report' -

--SELECT  name inameFROM    v$database;

--SELECT  to_char(sysdate, 'Mon DD, YYYY HH24:MI') todayFROM    dual;

----btitle ce '======================================================' skip 1 -

----le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno

----ttitle center 'Database: ' instance ' - Table Usage Report' -

SELECT  c.loads, c.executions, c.owner||'.'||c.name name,t.tablespace_name tablespace, s.bytes/1024 KB, c.sharable_mem

 FROM dba_tables t,dba_segments s,v$db_object_cache c

 WHERE  c.type = 'TABLE' and c.executions > 0 and c.name = t.table_name and c.owner = t.owner and c.name = s.segment_name and c.owner = s.owner

order by 4, 1 desc, 2 desc, 3 ;