set pages 200

spool c:\TEMP\tuning.lst

-- Este programa apresenta a situacao de uma terminada instance dentro dos requerimentos 
-- que a Oracle Corporation exige para considerar uma base de dados equilibrada(afinada)


prompt " Database Block Buffers Hit Ratio "
prompt  O Resultado deve ser maior que 95%
select (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
/

prompt " Apresenta a quantidade de memoria disponivel em um determinado momento "
prompt  Se a disponibilidade=0, uma solucao seria aumentar o parametro DB_BLOCK_BUFFERS
col name format a50
col value format a20
select name,value from v$parameter where name='db_block_buffers'
/

prompt " Dictionary Cache Hit Ratio "
prompt  O Resultado deve ser maior que 95%
select ((1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100) "Hit Ratio"
from v$rowcache
where ((gets + getmisses) <> 0)
/


prompt " Verifica se o Database Buffers(DB BLOCK BUFFER) tem tamanho suficiente "
prompt  Deve ser acima de 95%
column phys     format 99,999,999,999 heading 'Physical Reads'
column gets     format 99,999,999,999 heading 'DB Block Gets'
column con_gets format 99,999,999,999 heading 'Consistent Gets'
column hitratio format 9,999.999      heading 'Hit Ratio'
select sum(decode(name,'physical reads',value,0)) "phys",
       sum(decode(name,'db block gets',value,0)) "gets",
       sum(decode(name,'consistent gets',value,0)) "con_gets",
       100 * (1 - sum(decode(name,'physical reads',value,0)) /
       (sum(decode(name,'db block gets',value,0)) +
        sum(decode(name,'consistent gets',value,0)))) "hitratio"
from v$sysstat
/

prompt " This query looks at the row cache in detail and places an * by those values
prompt that have miss ratio greater than 10% "
column parameter     format a20         heading 'Data Dictionary Area'
column gets          format 99,999,999,999 heading 'Total|Requests'
column getmisses     format 99,999,999,999 heading 'Misses'
column modifications format 99,999,999     heading 'DMLs|(I,U,D)'
column flushes       format 99,999,999     heading 'Flushes|Disk'
column getmiss_ratio format 9,999.99       heading 'Miss|Ratio'
prompt "Shared Pool Row Cache Usage"
select parameter, gets, getmisses, modifications, flushes,
       (getmisses / decode(gets,0,1,gets)) getmiss_ratio,
       decode(trunc((getmisses / decode(gets,0,1,gets)),1),.0,' ','*') " "
from v$rowcache
where ( gets + getmisses ) <> 0
order by gets desc
/ 


prompt " Detecting file I/O balacing problems "
col PHYRDS   format 9,999,999,999
col PHYWRTS  format 9,999,999,999
prompt "Disk Balancing Report"
col READTIM  format 9,999,999,999
col WRITETIM format 9,999,999,999
col name format a50
select name, phyrds, phywrts, readtim, writetim
from   v$filestat a, v$dbfile b
where a.file# = b.file#
order by readtim desc
/

prompt -- Shared Pool Detail Break Down
prompt --------------------------------------------------------------------------*
prompt R-free   This is SHARED_POOL_RESERVED_SIZE (Default 5% of SP)
prompt R-freea  This is probably reserved memory that has been used but free-able
prompt free     This is the amount of contiguous free memory available
prompt freeabl  This is probably memory that has been used but is freeable
prompt perm     This is free memory not yet moved to the free area for use
prompt recr     I am not sure what this is. Possibly reserved memory for Oracle
prompt --------------------------------------------------------------------------*

 

prompt " How much memory is left for SHARED_POOL"
col value for 9,999,999,999,999 heading "Shared Pool Size"
col bytes for 9,999,999,999,999 heading "Free Bytes"
select to_number(v$parameter.value) value, v$sgastat.bytes,
       (v$sgastat.bytes/v$parameter.value) * 100 "Percent Free"
from   v$sgastat, v$parameter
where  v$sgastat.name = 'free memory'
and    v$parameter.name = 'shared_pool_size'
/

prompt " This Query identifies the use of Individual Library Cache Parameters to
prompt diagnose Shared Pool use "
prompt Pin Hit Ratio must be close to one(1)
prompt Misses can be reduced by writing identical SQL statements.
set pages 24
prompt "Shared Pool Library Cache Usage"
column namespace   format a20         heading 'Entity'
column pins        format 99,999,999,999 heading 'Executions'
column pinhits     format 99,999,999,999 heading 'Hits'
column pinhitratio format 999.99        heading 'PinHit|Ratio'
column reloads     format 99,999,999     heading 'Reloads'
column reloadratio format 9.9999       heading 'Reload|Ratio'
select namespace, pins, pinhits, pinhitratio, reloads,
       (reloads / decode(pins,0,1,pins)) reloadratio
from v$librarycache
/

prompt " Shared Pool Library Cache Hit Ratio "
prompt O Resultado deve ser maior que 95%
select sum(pins) "Hits",
       sum(reloads) "Misses",
       (sum(pins) / (sum(pins) + sum(reloads)) * 100) "Hit Ratio %"
from v$librarycache
/

prompt " If the Reload Ratio is not zero, then there are statements that are being
prompt "aged out" that are later needed and brought back into memory. If the Reload
prompt Ratio is above 1%, you should probably increase the SHARED_POOL_SIZE
select sum(pins) "Hits",
       sum(Reloads) "Misses",
       ((sum(reloads) / sum(pins)) * 100) "Reloads %"
from v$librarycache
/

prompt " Query to know if there is contention of LOG BUFFER "
prompt  if Space Request Ratio > 0,02% - Increase the LOG BUFFER
select a.value "Redo Log Space Requests", b.value "Redo Entries",
decode(a.value,0,1,a.value)*100/b.value "Space Request Ratio"
from v$sysstat a, v$sysstat b
where a.name = 'redo log space requests'
and   b.name = 'redo entries'
/

prompt " Query to get memory and disk sorts "
select a.value "Disk Sorts", b.value "Memory Sorts",
       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 b
where a.name = 'sorts (disk)'
and   b.name = 'sorts (memory)'
/


prompt " Verifica contencao de Rollbacks "
prompt Se XACTS for regularmente maior que 1, aumentar numero de Rollbacks.
prompt Se WAITS for maior que 0, aumentar numero de Rollbacks
prompt Se possivel tentar deixar o numero de user per rollback = 1
select substr(a.name,1,10), b.extents, b.rssize, b.xacts,
       b.waits, b.gets, optsize, status
from v$rollname a, v$rollstat b
where a.usn = b.usn
/

prompt " Apresenta a utilizacao dos Rollbacks "
select substr(segment_name,1,10) "Rollback",
       substr(tablespace_name,1,10) "Tablespace",
       bytes/1024/1024 "Usados MB",
       blocks "Blocos",
       extents "Extents"
from dba_segments
where segment_type = 'ROLLBACK'
/

spool off