This
Script is obviously NOT written by me. One of my best buddy shared this with
me.
So
im sharing this with you.
Step
1. Make a sql script-file using VI editor.
SQL> exit
[oracle@oracleasm1 u01]$
[oracle@oracleasm1 u01]$ vi hc_script.sql
Step
2. Paste the below Content.
title 'SYSTEM STATISTICS' select 'LIBRARY CACHE STATISTICS:' from dual; ttitle off select 'PINS - # of times an item in the library cache was executed - '|| sum(pins), 'RELOADS - # of library cache misses on execution steps - '|| sum (reloads), 'RELOADS / PINS * 100 = '||round((sum(reloads) / sum(pins) * 100),2)||'%' from v$librarycache / prompt Increase memory until RELOADS is near 0 but watch out for prompt Paging/swapping prompt To increase library cache, increase SHARED_POOL_SIZE prompt prompt ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size. prompt prompt Library Cache Misses indicate that the Shared Pool is not big prompt enough to hold the shared SQL area for all concurrently open cursors. prompt If you have no Library Cache misses (PINS = 0), you may get a small prompt increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which prompt prevents ORACLE from deallocating a shared SQL area while an prompt application prompt cursor associated with it is open. prompt prompt For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user. prompt prompt------------------------------------------------------------------------ column xn1 format a50 column xn2 format a50 column xn3 format a50 column xv1 new_value xxv1 noprint column xv2 new_value xxv2 noprint column xv3 new_value xxv3 noprint column d1 format a50 column d2 format a50 prompt HIT RATIO: prompt prompt Values Hit Ratio is calculated against: prompt select lpad(name,20,' ')||' = '||value xn1, value xv1 from v$sysstat where name = 'db block gets' / select lpad(name,20,' ')||' = '||value xn2, value xv2 from v$sysstat where name = 'consistent gets' / select lpad(name,20,' ')||' = '||value xn3, value xv3 from v$sysstat b where name = 'physical reads' / set pages 60 select 'Logical reads = db block gets + consistent gets ', lpad ('Logical Reads = ',24,' ')||to_char(&xxv1+&xxv2) d1 from dual / select 'Hit Ratio = (logical reads - physical reads) / logical reads', lpad('Hit Ratio = ',24,' ')|| round( (((&xxv2+&xxv1) - &xxv3) / (&xxv2+&xxv1))*100,2 )||'%' d2 from dual / prompt If the hit ratio is less than 60%-70%, increase the initialization prompt parameter DB_BLOCK_BUFFERS. ** NOTE: Increasing this parameter will prompt increase the SGA size. prompt prompt------------------------------------------------------------------------ col name format a30 col gets format 9,999,999,999,999 col waits format 9,999,999,999,999 prompt ROLLBACK CONTENTION STATISTICS: prompt prompt GETS - # of gets on the rollback segment header prompt WAITS - # of waits for the rollback segment header set head on; select name, waits, gets from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn / set head off select 'The average of waits/gets is '|| round((sum(waits) / sum(gets)) * 100,2)||'%' From v$rollstat / prompt prompt If the ratio of waits to gets is more than 1% or 2%, consider prompt creating more rollback segments prompt prompt Another way to gauge rollback contention is: prompt column xn1 format 9999999999 column xv1 new_value xxv1 noprint set head on select class, count from v$waitstat where class in ('system undo header', 'system undo block', 'undo header', 'undo block' ) / set head off select 'Total requests = '||sum(count) xn1, sum(count) xv1 from v$waitstat / select 'Contention for system undo header = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat where class = 'system undo header' / select 'Contention for system undo block = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat where class = 'system undo block' / select 'Contention for undo header = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat where class = 'undo header' / select 'Contention for undo block = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat where class = 'undo block' / prompt prompt If the percentage for an area is more than 1% or 2%, consider prompt creating more rollback segments. Note: This value is usually very prompt small prompt and has been rounded to 4 places. prompt prompt------------------------------------------------------------------------ prompt REDO CONTENTION STATISTICS: prompt prompt The following shows how often user processes had to wait for space in prompt the redo log buffer: select name||' = '||value from v$sysstat where name = 'redo log space requests' / prompt prompt This value should be near 0. If this value increments consistently, prompt processes have had to wait for space in the redo buffer. If this prompt condition exists over time, increase the size of LOG_BUFFER in the prompt init.ora file in increments of 5% until the value nears 0. prompt ** NOTE: increasing the LOG_BUFFER value will increase total SGA size. prompt prompt ----------------------------------------------------------------------- col name format a15 col gets format 99999999999 col misses format 999999999999 col immediate_gets heading 'IMMED GETS' format 999999999999 col immediate_misses heading 'IMMED MISS' format 999999999999 col sleeps format 999999 prompt LATCH CONTENTION: prompt prompt GETS - # of successful willing-to-wait requests for a latch prompt MISSES - # of times an initial willing-to-wait request was unsuccessful prompt IMMEDIATE_GETS - # of successful immediate requests for each latch prompt IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latch prompt SLEEPS - # of times a process waited and requests a latch after an prompt initial willing-to-wait request prompt prompt If the latch requested with a willing-to-wait request is not prompt available, the requesting process waits a short time and requests prompt again. prompt If the latch requested with an immediate request is not available, prompt the requesting process does not wait, but continues processing prompt set head on select name, gets, misses, immediate_gets, immediate_misses, sleeps from v$latch where name in ('redo allocation', 'redo copy') / set head off select 'Ratio of MISSES to GETS: '|| round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%' from v$latch where name in ('redo allocation', 'redo copy') / select 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '|| round((sum(immediate_misses)/ (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%' from v$latch where name in ('redo allocation', 'redo copy') / prompt prompt If either ratio exceeds 1%, performance will be affected. prompt prompt Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number of prompt processes copying information on the redo allocation latch. prompt prompt Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contention prompt for redo copy latches. rem rem This shows the library cache reloads rem set head on prompt prompt------------------------------------------------------------------------ prompt prompt Look at gethitratio and pinhit ratio prompt prompt GETHITRATIO is number of GETHTS/GETS prompt PINHIT RATIO is number of PINHITS/PINS - number close to 1 indicates prompt that most objects requested for pinning have been cached. Pay close prompt attention to PINHIT RATIO. prompt column namespace format a20 heading 'NAME' column gets format 99999999999 heading 'GETS' column gethits format 99999999999 heading 'GETHITS' column gethitratio format 999.99 heading 'GET HIT|RATIO' column pins format 99999999999 heading 'PINHITS' column pinhitratio format 999.99 heading 'PIN HIT|RATIO' select namespace, gets, gethits, gethitratio, pins, pinhitratio from v$librarycache / rem rem rem This looks at the dictionary cache miss rate rem prompt prompt------------------------------------------------------------------------ prompt THE DATA DICTIONARY CACHE: prompt prompt prompt Consider keeping this below 5% to keep the data dictionary cache in prompt the SGA. Up the SHARED_POOL_SIZE to improve this statistic. **NOTE: prompt increasing the SHARED_POOL_SIZE will increase the SGA. prompt column dictcache format 999.99 heading 'Dictionary Cache | Ratio %' select sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache from v$rowcache / prompt prompt------------------------------------------------------------------------ prompt prompt SYSTEM EVENTS: prompt prompt Not sure of the value of this section yet but it looks interesting. prompt col event format a37 heading 'Event' col total_waits format 999999999999 heading 'Total|Waits' col time_waited format 99999999999999 heading 'Time Wait|In Hndrds' col total_timeouts format 9999999999 heading 'Timeout' col average_wait heading 'Average|Time' format 999999999.999 set pages 999 select * from v$system_event / prompt prompt------------------------------------------------------------------------ rem rem rem This looks at the sga area breakdown rem prompt THE SGA AREA ALLOCATION: prompt prompt prompt This shows the allocation of SGA storage. Examine this before and prompt after making changes in the INIT.ORA file which will impact the SGA. prompt col name format a40 select name, bytes from v$sgastat / set head off select 'total of SGA '||sum(bytes) from v$sgastat / prompt prompt------------------------------------------------------------------------ rem rem Displays all the base session statistics rem set head on set pagesize 110 column name format a55 heading 'Statistic Name' column value format 9,999,999,999,999,999 heading 'Result' column statistic# format 999999999 heading 'Stat#' ttitle center 'Instance Statistics' skip 2 prompt prompt Below is a dump of the core Instance Statistics that are greater than0. prompt Although there are a great many statistics listed, the ones of greatest prompt value are displayed in other formats throughout this report. Of prompt interest here are the values for: prompt prompt cumulative logons prompt(# of actual connections to the DB since last startup - good prompt volume-of-use statistic) prompt prompt #93 table fetch continued row prompt (# of chained rows - will be higher if there are a lot of long fields prompt if the value goes up over time, it is a good signaller of general prompt database fragmentation) prompt select statistic#, name, value from v$sysstat where value > 0 / prompt prompt ----------------------------------------------------------------------- set pages 66; set space 3; set heading on; prompt prompt Parse Ratio usually falls between 1.15 and 1.45. If it is higher, then prompt it is usually a sign of poorly written Pro* programs or unoptimized prompt SQL*Forms applications. prompt prompt Recursive Call Ratio will usually be between prompt prompt 7.0 - 10.0 for tuned production systems prompt 10.0 - 14.5 for tuned development systems prompt prompt Buffer Hit Ratio is dependent upon RDBMS size, SGA size and prompt the types of applications being processed. This shows the %-age prompt of logical reads from the SGA as opposed to total reads - the prompt figure should be as high as possible. The hit ratio can be raised prompt by increasing DB_BUFFERS, which increases SGA size. By turning on prompt the "Virtual Buffer Manager" (db_block_lru_statistics = TRUE and prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters), prompt you can determine how many extra hits you would get from memory as prompt opposed to physical I/O from disk. **NOTE: Turning these on will prompt impact performance. One shift of statistics gathering should be enough prompt to get the required information. prompt ttitle left 'Ratios for this Instance' skip 2 column pcc heading 'Parse|Ratio' format 99.99 column rcc heading 'Recsv|Cursr' format 99.99 column hr heading 'Buffer|Ratio' format 999,999,999,999,999.999 column rwr heading 'Rd/Wr|Ratio' format 999,999,999.9 column bpfts heading 'Blks per|Full TS' format 999,999,999 REM Modified for O7.1 to reverse 'cumulative opened cursors' to REM 'opened cursors cumulative' REM was:sum(decode(a.name,'cumulative opened cursors',value, .00000000001)) REM pcc, REM and:sum(decode(a.name,'cumulative opened cursors',value,.00000000001)) REM rcc, select sum(decode(a.name,'parse count',value,0)) / sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) pcc, sum(decode(a.name,'recursive calls',value,0)) / sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) rcc, (1-(sum(decode(a.name,'physical reads',value,0)) / sum(decode(a.name,'db block gets',value,.00000000001)) + sum(decode(a.name,'consistent gets',value,0))) * (-1)) hr, sum(decode(a.name,'physical reads',value,0)) / sum(decode(a.name,'physical writes',value,.00000000001)) rwr, (sum(decode(a.name,'table scan blocks gotten',value,0)) - sum(decode(a.name,'table scans (short tables)',value,0)) * 4) / sum(decode(a.name,'table scans (long tables)',value,.00000000001)) bpfts from v$sysstat a / prompt prompt ----------------------------------------------------------------- prompt This looks at overall i/o activity against individual prompt files within a tablespace prompt prompt Look for a mismatch across disk drives in terms of I/O prompt prompt Also, examine the Blocks per Read Ratio for heavily accessed prompt TSs - if this value is significantly above 1 then you may have prompt full tablescans occurring (with multi-block I/O) prompt prompt If activity on the files is unbalanced, move files around to balance prompt the load. Should see an approximately even set of numbers across files prompt set pagesize 100; set space 1 column pbr format 999999999999 heading 'Physical|Blk Read' column pbw format 9999999999 heading 'Physical|Blks Wrtn' column pyr format 9999999999 heading 'Physical|Reads' column readtim format 999999999999 heading 'Read|Time' column name format a40 heading 'DataFile Name' column writetim format 999999999999 heading 'Write|Time' ttitle center 'Tablespace Report' skip 2 compute sum of f.phyblkrd, f.phyblkwrt on report select fs.name name, f.phyblkrd pbr, f.phyblkwrt pbw, f.readtim, f.writetim from v$filestat f, v$datafile fs where f.file# = fs.file# order by fs.name / prompt prompt ----------------------------------------------------------------- prompt GENERATING WAIT STATISTICS: prompt prompt This will show wait stats for certain kernel instances. This prompt may show the need for additional rbs, wait lists, db_buffers prompt ttitle center 'Wait Statistics for the Instance' skip 2 column class heading 'Class Type' column count heading 'Times Waited' format 99,999,999,999,999 column time heading 'Total Times' format 99,999,999,999,999 select class, count, time from v$waitstat where count > 0 order by class / prompt prompt Look at the wait statistics generated above (if any). They will prompt tell you where there is contention in the system. There will prompt usually be some contention in any system - but if the ratio of prompt waits for a particular operation starts to rise, you may need to prompt add additional resource, such as more database buffers, log buffers, prompt or rollback segments prompt prompt ----------------------------------------------------------------- prompt ROLLBACK STATISTICS: prompt ttitle off; set linesize 80 column extents format 999,999 heading 'Extents' column rssize format 999,999,999,999 heading 'Size in|Bytes' column optsize format 999,999,999,999 heading 'Optimal|Size' column hwmsize format 99,999,999,999 heading 'High Water|Mark' column shrinks format 999,999 heading 'Num of|Shrinks' column wraps format 999,999 heading 'Num of|Wraps' column extends format 999,999,999 heading 'Num of|Extends' column aveactive format 999,999,999,999 heading 'Average size|Active Extents' column rownum noprint select rssize, optsize, hwmsize, shrinks, wraps, extends, aveactive from v$rollstat order by rownum / prompt prompt ----------------------------------------------------------------- set linesize 80 break on report compute sum of gets waits writes on report ttitle center 'Rollback Statistics' skip 2 select rownum, extents, rssize, xacts, gets, waits, writes from v$rollstat order by rownum / ttitle off set heading off prompt prompt ----------------------------------------------------------------- prompt prompt SORT AREA SIZE VALUES: prompt prompt To make best use of sort memory, the initial extent of your Users prompt sort-work Tablespace should be sufficient to hold at least one sort prompt run from memory to reduce dynamic space allocation. If you are getting prompt a high ratio of disk sorts as opposed to memory sorts, setting prompt sort_area_retained_size = 0 in init.ora will force the sort area to be prompt released immediately after a sort finishes. prompt column value format 999,999,999,999 select 'INIT.ORA sort_area_size: '||value from v$parameter where name like 'sort_area_size' / select a.name, value from v$statname a, v$sysstat where a.statistic# = v$sysstat.statistic# and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)') / prompt prompt ----------------------------------------------------------------- set heading on set space 2 prompt prompt This looks at Tablespace Sizing - Total bytes and free bytes prompt ttitle center 'Tablespace Sizing Information' Skip 2 column tablespace_name format a30 heading 'TS Name' column sbytes format 9,999,999,999,999 heading 'Total Bytes' column fbytes format 9,999,999,999,9999 heading 'Free Bytes' column kount format 999,999 heading 'Ext' compute sum of fbytes on tablespace_name compute sum of sbytes on tablespace_name compute sum of sbytes on report compute sum of fbytes on report break on report select a.tablespace_name, a.bytes sbytes, sum(b.bytes) fbytes, count(*) kount from dba_data_files a, dba_free_space b where a.file_id = b.file_id group by a.tablespace_name, a.bytes order by a.tablespace_name / set linesize 80 prompt prompt A large number of Free Chunks indicates that the tablespace may need prompt to be defragmented and compressed. prompt prompt ----------------------------------------------------------------- set heading off ttitle off column value format 99,999,999,999,999 select 'Total Physical Reads', value from v$sysstat where statistic# = 39 / prompt prompt If you can significantly reduce physical reads by adding incremental prompt data buffers...do it. To determine whether adding data buffers will prompt help, set db_block_lru_statistics = TRUE and prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters. prompt You can determine how many extra hits you would get from memory as prompt opposed to physical I/O from disk. **NOTE: Turning these on will prompt impact performance. One shift of statistics gathering should be enough prompt to get the required information. prompt set heading on clear computes ttitle off prompt prompt ----------------------------------------------------------------- prompt CHECKING FOR FRAGMENTED DATABASE OBJECTS: prompt prompt Fragmentation report - If number of extents is approaching Maxextents, prompt it is time to defragment the table. prompt column owner noprint new_value owner_var column segment_name format a30 heading 'Object Name' column segment_type format a9 heading 'Table/Indx' column sum(bytes) format 999,999,999,999 heading 'Bytes Used' column count(*) format 999,999 heading 'No.' break on owner skip page 2 ttitle center 'Table Fragmentation Report' skip 2 - left 'creator: ' owner_var skip 2 select a.owner, segment_name, segment_type, sum(bytes), max_extents, count(*) from dba_extents a, dba_tables b where segment_name = b.table_name having count(*) > 3 group by a.owner, segment_name, segment_type, max_extents order by a.owner, segment_name, segment_type, max_extents / ttitle center 'Index Fragmentation Report' skip 2 - left 'creator: ' owner_var skip 2 select a.owner, segment_name, segment_type, sum(bytes), max_extents, count(*) from dba_extents a, dba_indexes b where segment_name = index_name having count(*) > 3 group by a.owner, segment_name, segment_type, max_extents order by a.owner, segment_name, segment_type, max_extents / prompt prompt ----------------------------------------------------------------- spool off
Step
3. Execute the script.
SQL > @/u01/hc_script.sql
See Also : Health Check – When the Application Users complaints for an issue