SCRIPT DE DIAGNOSTICO PARA TUNING EM 8i E 9i

Description
The script does a performance healthchek on the instance and database
and makes suggestions on modifications that can be made if specific conditions exist.
The report should be run after the system has been up for at least 10 hours
and should be run on several occasions over a period of time to get a feel for
what the real condition of the database is. A one-time sample run on an
intactive system will not give an accurate picture of what is really occuring
within the database.

If the database is shut down on a nightly basis for backups, the script can be
run just prior to shutdown each night to enable trending analysis.

NOTE: STATSPACK reports are also a good alternative to indicate instance 
 efficiency statistics especially where performance issues are concerned.
 
 
References 
 
 
Script 
spool tuning_stats.txt 
 
ttitle '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 
col waits format 9,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 9999999 
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 9999999 
col misses format 9999999 
col immediate_gets heading 'IMMED GETS' format 9999999 
col immediate_misses heading 'IMMED MISS' format 9999999 
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 99999999 heading 'GETS' 
column gethits format 99999999 heading 'GETHITS' 
column gethitratio format 999.99 heading 'GET HIT|RATIO' 
column pins format 9999999 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 99999999 heading 'Total|Waits' 
col time_waited format 9999999999 heading 'Time Wait|In Hndrds' 
col total_timeouts format 999999 heading 'Timeout' 
col average_wait heading 'Average|Time' format 999999.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 heading 'Result' 
column statistic# format 9999 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 
column rwr heading 'Rd/Wr|Ratio' format 999,999.9 
column bpfts heading 'Blks per|Full TS' format 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 99999999 heading 'Physical|Blk Read' 
column pbw format 999999 heading 'Physical|Blks Wrtn' 
column pyr format 999999 heading 'Physical|Reads' 
column readtim format 99999999 heading 'Read|Time' 
column name format a40 heading 'DataFile Name' 
column writetim format 99999999 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 
column time heading 'Total Times' format 99,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 heading 'Extents' 
column rssize format 999,999,999 heading 'Size in|Bytes' 
column optsize format 999,999,999 heading 'Optimal|Size' 
column hwmsize format 99,999,999 heading 'High Water|Mark' 
column shrinks format 9,999 heading 'Num of|Shrinks' 
column wraps format 9,999 heading 'Num of|Wraps' 
column extends format 999,999 heading 'Num of|Extends' 
column aveactive format 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 
 
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 heading 'Total Bytes' 
column fbytes format 9,999,999,999 heading 'Free Bytes' 
column kount format 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 
 
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 heading 'Bytes Used' 
column count(*) format 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 
 
 
 

 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em FAST DIAG e marcado , , , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s