To Check the Performance

rem ***********************************************************************rem Program: dba_dbperf_w.sqlrem Purpose: Oracle 9.X Weekly Reportrem Author: Aleksandr Lembrikov, Ernst & Young, LLP, IT IAS (created)rem Aramando Plascencia, WEA Corporation, Time Warner Music (tested)rem Date: 31-Aug-2006rem Comments:rem This script will run against Oracle Version 9.x and is usedrem to assist the auditor or DBA in documenting the databaserem environment. Most statements use dba tables or the v$ viewsrem so it is recommended this script be run by a DBA.remrem Note: Some sql scripts have been gathered from Oracle manuals andrem the author wishes to acknowledge this fact upfront.remrem Parameters:rem This script takes as a parameter the database sid.rem Audit Trail:rem **************************************************************************remset linesize 90set pagesize 999set verify offset feedback offset maxdata 4000set arraysize 1rem
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
accept s_id prompt 'Enter Oracle SID:'
spool dba_dbperf_w_&&s_id
set heading offselect 'V9 Weekly Database Report for SID=&&s_id generated on ' to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by ' userfrom sys.dual/set heading onpromptprompt *********************************************************************prompt * Tuning: Contention for dispatchersprompt *** Add more dispatchers if busy rate is > 50%prompt *prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, network, status, (busy/(busy+idle))*100 Pct_Busyfrom v$dispatcherorder by name, network/promptprompt *********************************************************************prompt * Tuning: Contention for multi-threaded serversprompt * Add more mts servers if busy rate is > 50%prompt * prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, status, requests, (busy/(busy+idle))*100 Pct_Busyfrom v$shared_serverorder by name/promptprompt *********************************************************************prompt * Database export informationprompt *********************************************************************
select * from sys.dba_exp_filesorder by timestamp/promptprompt *********************************************************************prompt * Buffer cache hit ratioprompt * If hit ratio is less than 70% then increase db_block_buffers.prompt * HitRatio=(1-(physical reads/(db block gets+consistent gets)))*100prompt *********************************************************************column name format a33 heading "TYPE"column value format 999,999,999,999column hitratio format 999.99 heading "Hit Ratio"
select name, valuefrom v$sysstatwhere name in ('db block gets', 'consistent gets', 'physical reads')order by name/select ((cur.value+con.value)/(( cur.value+con.value)+phy.value))*100 hitratiofrom v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'/promptprompt *********************************************************************prompt * Library cache reload ratioprompt * This ratio should be very low indicating that the library cacheprompt * is large enough. If high, increase shared_pool_size.prompt *********************************************************************column libcache format 99.99 heading 'Library CacheReload %'
select sum(reloads)/sum(pins) *100 libcachefrom v$librarycache/promptprompt *********************************************************************prompt * Library cache hit ratioprompt * GETHITRATIO is number of GETHITS/GETSprompt * PINHIT RATIO is number of PINHITS/PINS prompt * A number close to 1 indicates that most objects have been cachedprompt *********************************************************************column namespace format a20 heading 'NAME'column gets format 9999999999 heading 'GETS'column gethits format 9999999999 heading 'GETHITS'column gethitratio format 999.99 heading 'GET HITRATIO'column pins format 9999999999 heading 'PINS'column pinhits format 999999999 heading 'PINHITS'column pinhitratio format 999.99 heading 'PIN HITRATIO'
select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratiofrom v$librarycache/promptprompt *********************************************************************prompt * Data dictionary cache miss ratioprompt * Miss ratio should be below 5% to keep the data dictionaryprompt * cached in the SGA. Increase shared_pool_size to improve.prompt *********************************************************************column dictcache format 999.99 heading 'Dictionary CacheMiss Ratio %'
select sum(getmisses)/sum(gets) * 100 dictcachefrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratioprompt * Hit Ratio = sum(gets)/(sum(gets)+sum(getmisses))prompt *********************************************************************column hitratio format 999.99 heading "Hit Ratio"
select sum(gets)/(sum(gets)+sum(getmisses)) hitratiofrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratio by componentprompt *********************************************************************column a format a17 heading 'Cache'column b format 99999990 heading 'Gets'column c format 99999990 heading 'Misses'column d format 990 heading '%Miss'column e format 9990 heading 'Usage'column f format 99990 heading 'Count'column g format 990 heading '%Usage'select substr(parameter,4,17) a, gets b, getmisses c, decode(gets,0,0,(getmisses*100)/gets) d, usage e, count f, (usage*100)/count gfrom v$rowcachewhere count <> 0order by 1/promptprompt *********************************************************************prompt * In memory and disk sortsprompt * To make best use of sort memory the initial extent of yourprompt * sort-work tablespace should be sufficient to hold atprompt * least one sort run from memory, to reduce dynamic spaceprompt * allocation. Ratio of disk sorts to memory sorts shouldprompt * be less than 5%. Increase sort area size if higher.prompt *********************************************************************column name format a33 heading "SORT TYPE"column value format 999,999,999,999column parmval format a20
select name, valuefrom v$sysstatwhere name in ('sorts (memory)', 'sorts (disk)','sorts (rows)')/select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"from v$sysstat a, v$sysstat bwhere a.name = 'sorts (disk)' and b.name = 'sorts (memory)'/set heading offselect 'init.ora sort_area_size :', value "parmval"from v$parameter where name = 'sort_area_size'/promptprompt *********************************************************************prompt * Display cursor memoryprompt *********************************************************************
set heading onselect sum(250 * users_opening) "Open Cursor Memory"from v$sqlarea/promptprompt *********************************************************************prompt * Display memory set aside for packages prompt *********************************************************************
select sum(sharable_mem) "Shareable Memory"from v$db_object_cachewhere type = 'PACKAGE' OR type = 'PACKAGE_BODY'/promptprompt *********************************************************************prompt * Loads to db object cacheprompt *********************************************************************
select sum(loads), sum(executions), round((sum(loads) / sum(executions)) * 100, 4) Load_Percentfrom sys.v_$db_object_cache/promptprompt *********************************************************************prompt * Free buffer waitsprompt * The following script lists the number of waits forprompt * free buffers to be made available for new data being prompt * brought into the buffer cache. The output is often a sign prompt * of an untuned DBWR or a buffer cache that is too small. prompt *********************************************************************
select name, value from v$sysstat where name = 'free buffer waits'/promptprompt *********************************************************************prompt * Library cache contentionprompt *********************************************************************column name format a25 heading " TYPE"
select substr(name,1,25) name, gets, misses,immediate_gets, immediate_misses from v$latchwhere (misses > 0 or immediate_misses > 0) and name like 'library cach%'/promptprompt *********************************************************************prompt * Redo latch contentionprompt * There is contention if ratio of misses to getsprompt * exceeds 1%, or ratio of immediate_misses to theprompt * sum of immediate_gets + immediate_misses exceeds 1%prompt *********************************************************************column name format a25 heading 'Latch Type'column gets format 9999999999 heading 'Gets'column misses format 9999999 heading 'Misses'column immediate_gets format 999999999 heading 'Immediate Gets 'column immediate_misses format 999999999 heading 'Immediate Misses'
select name, gets, misses, immediate_gets, immediate_missesfrom v$latchwhere name like 'redo%'/promptprompt *********************************************************************prompt * Wait statisticsprompt * These numbers will tell you where there is contention inprompt * in the system. There will usually be some contention inprompt * any system, but if the ratio of waits for a particular operationprompt * starts to rise, you may need to add additional resources,prompt * such as more database buffers, log buffers, or rollbacks.prompt * Waits for any class should not be more 1% of total requests.prompt *********************************************************************column class heading 'Class Type'column count format 99,999,999 heading 'Occurrences'
select class, countfrom v$waitstatwhere count > 0order by class/promptprompt *********************************************************************prompt * Tablespace storage allocation and percent of space used ifprompt * used greater than 60%prompt *********************************************************************column "TBLSPC" format A25 heading 'TABLESPACE'column "ALLOC" format 999,999,999,999 heading 'ALLOCATED'column "USED" format 999,999,999,999 heading 'USED'column "UNUSED" format 999,999,999,999 heading 'UNUSED'column "USEDPCT" format 999.99
select u.tblspc "TBLSPC", a.fbytes "ALLOC", u.ebytes "USED", a.fbytes - u.ebytes "UNUSED", (u.ebytes/a.fbytes)*100 "USEDPCT"from (select tablespace_name tblspc, sum(bytes) ebytes from sys.dba_extents group by tablespace_name) u, (select tablespace_name tblspc, sum(bytes) fbytes from sys.dba_data_files group by tablespace_name) awhere u.tblspc = a.tblspc and (u.ebytes/a.fbytes)*100 > 60order by USEDPCT desc/promptprompt **************************************************************prompt * Tablespace fragmentation information (chunks > 2)prompt * If a ts has a large number of free chunks, thisprompt * indicates that it is a candidate for re-organization, asprompt * there may still be a lot of free space within the ts butprompt * it is all in small pieces (a Swiss cheese ts )!! Oracleprompt * will attempt to coalesce small contiguous free spaceprompt * extents but it incurs significant overhead to do so.prompt**************************************************************column fbytes format 999,999,999,999 heading 'Free Bytes'column fblocks format 999,999,999 heading 'Free DB Blocks'column kount format 99,999 heading 'Free Chunks'
select tablespace_name, sum(bytes) fbytes, sum(blocks) fblocks, count(*) kountfrom dba_free_spacegroup by tablespace_namehaving count(*) > 2order by tablespace_name/promptprompt**************************************************************prompt* Log history for the past dayprompt**************************************************************
select sequence#, first_change#, first_time, next_change#from sys.v_$log_historywhere first_time > sysdate-1/promptprompt *********************************************************************prompt * Tuning I/O: Physical reads, writesprompt * If phyblkrd is significantly larger than phyrds then a fullprompt * tablespace scan is occurring......prompt * prompt ********************************************************************* column fname format a25 trunc heading "Database Data Files"column phyrds format 999999999column phyblkrd format 999999999column phywrts format 999999999column phyblkwrt format 999999999column IO format 999,999,999 heading "TOTAL IO"
select substr(df.name,1,6)substr(df.name,15,19) fname, fs.phyrds, fs.phyblkrd, fs.phywrts, fs.phyblkwrt, fs.phyrds+fs.phywrts IOfrom v$datafile df, v$filestat fswhere df.file# = fs.file#order by IO desc/promptprompt *********************************************************************prompt * Detailed information on SGA and memoryprompt *********************************************************************column name format a33 heading "SGA Component"
select * from v$sgastatorder by pool, name/promptprompt **********************************************************************prompt * Executions of objects in the shared pool - most executions firstprompt **********************************************************************column owner format a10column oname format a40 trunc heading "OBJECT"
select owner, name' - 'type oname, executions, decode(substr(kept,1,1),'Y', ' ', '<<<> 50 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by executions desc/promptprompt *********************************************************************prompt * Loads of objects in the shared pool - most loads firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, loads, sharable_mem from v$db_object_cachewhere loads > 3 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by loads desc/promptprompt *********************************************************************prompt * Memory usage of shared pool - biggest firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, sharable_memfrom v$db_object_cachewhere sharable_mem > 10000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by sharable_mem desc/promptprompt *********************************************************************prompt * Objects that are keptprompt *********************************************************************column owner format a10column name format a25 heading "OBJECT"
select owner, name, executions, pins, keptfrom v$db_object_cachewhere kept = 'YES'order by owner, name/promptprompt *********************************************************************prompt * Rollback segment contentionprompt *********************************************************************column name format a20 heading "ROLLBACK SEGMENT"
select name, gets, waits, ((gets - waits) * 100)/gets hitratiofrom v$rollstat S, v$rollname Rwhere S.usn = R.usnorder by 1;promptprompt *********************************************************************prompt * Recommendation to add rollback segmentsprompt *********************************************************************column n1 format a15set heading off
select 'Try adding ' sum(decode(waits,0,0,1)) nl, 'rollback segments to avoid rollback header contention. 'from v$rollstat/promptprompt *********************************************************************prompt * Total number of rollback waits since the instance startedprompt *********************************************************************set heading on
select class, count from v$waitstatwhere class like '%undo%'/promptprompt *********************************************************************prompt * Rollback segment extends/shrinksprompt *********************************************************************
select usn, extends, shrinks, wraps, hwmsize, aveshrinkfrom v$rollstat;promptprompt *********************************************************************prompt * Redo log size problems (values should be near 0)prompt *********************************************************************column name format a35 heading 'Redo Log Stats'
select name, valuefrom v$sysstatwhere name in ('redo log space requests','redo buffer allocation retries')/promptprompt *********************************************************************prompt * Oracle User Ids with SYSTEM as their default or temp tablespacesprompt *prompt * Ensure that the default tablespace is NOT SYSTEM for any userprompt * except for SYSprompt *********************************************************************column username format a15 heading "USER"column default_ts format a15 heading "DEFLTTS"column temp_ts format a10 heading "TEMPTS"
select username, user_id, default_tablespace default_ts, temporary_tablespace temp_ts, createdfrom sys.dba_userswhere default_tablespace = 'SYSTEM' OR temporary_tablespace = 'SYSTEM'order by username/set heading off
select 'V9 End of Weekly Report 'to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by 'userfrom sys.dual/spool offexit