Scripts Monitoração – Oracle

Neste artigo irei mostrar um script que encontrei no dba-oracle. Este script retorna informações uteis para um check no banco de dados.

Este script utilizará o spool (link documentação Oracle) para salvar em um arquivo txt os resultados.

Os checks feitos pelo script:

• Uptime: tempo em que a instância está aberta;
• Object Status: mostra o status dos objetos;
• Invalid Objects: mostra os objetos que estão com o status inválido (entenda mais sobre invalid object);
• Recover Files: mostra os arquivos que foram utilizados no Recover do Oracle;
• Partitioned Table: mostra as tabelas particionadas na base de dados;
• Free space in Tablespace: mostra o espaço livre das tablespaces;
• Max Extents Status: mostra o status das Max Extents;
• Any partitioned Object approaching to max extents: mostra quais objetos particionados estão próximos de chegar ao limite do seu Max Extens;
• Block Contention: mostra quais os blocks contetion que estão ocorrendo na base de dados;
• Latch Contention: mostra quias os latch contetion que estão ocorrendo na base de dados;
• Top 10 Queries using more disk reads: mostra as 10 queries que estão consumindo mais disco neste momento;
• Top 10 Queries using more buffer reads: mostra as 10 queries que estão consumindo mais buffer neste momento;
• Buffer cache hit ratio: mostra o volume de buffers consumidos pela CPU no Oracle;
• Reloads/Pins in v$librarycache: mostra o volume de Reloads/Pings na view LibraryCache;
• Get miss ratio from v$rowcache: mostra mais algumas informações sobre Ratio (mesmo link no tópico anterior);
• Redo log space request ratio in v$sysstat: mostra o espaço requerido pelo ratio dos redo logs;
• RBS Contention: mostra o RBS Contention na base de dados;
• Sorts stats: mostra os sorts stats na base de dados;
• Datafiles I/O: mostra os arquivos (datafiles) das tablespaces.

Script: 

set linesize 132 
set pagesize 1000
 
spool report.txt
 
prompt #### Up Time ####
 
 
select 'Hostname      : ' || host_name
      ,'Instance Name : ' || instance_name
      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||
       trunc( 24*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))) || ' hour(s) ' ||
       mod(trunc(1440*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
       mod(trunc(86400*((sysdate-startup_time) - 
       trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_$instance;
prompt 
prompt 
prompt ################################################################################
 
 
 
prompt .>>>>>>    Object status 
 
select object_type,status,count(*) cnt from user_objects  group by  object_type,status;
prompt 
prompt 
prompt ################################################################################
 
 
 
prompt .>>>>>>  Invalid objects 
 
 
select owner, object_type, substr(object_name,1,30) object_name
from sys.dba_objects
where status='INVALID'
order by object_type;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>    Recover files
 
select * from sys.v_$recover_file;
prompt 
prompt 
prompt ################################################################################
 
prompt .>>>>>> partitioned table
 
select table_name from user_tables where partitioned='YES';
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Free space in Tablespace 
 
 
Select tablespace_name,sum(bytes/1024/1024) from sys.dba_free_space group by tablespace_name;
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>Max Extents Status
 
select segment_name, segment_type, extents, max_extents from sys.dba_segments where max_extents-extents<100;
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>>  Any partitioned Object approaching to max extents
 
 
 
SELECT PARTITION_NAME,EXTENTS,MAX_EXTENTS,NEXT_EXTENT,max_extents-nvl(next_extent,0) FROM sys.dba_segments 
where max_extents-nvl(next_extent,0) < 1000
and partition_name is not null;
prompt 
prompt 
prompt ################################################################################
 
prompt .>>>>>>  Block Contention
 
SELECT class,sum(count) total_waits, sum(time) total_time FROM v$waitstat GROUP BY class;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Latch Contention
 
 
SELECT a.name,100.*b.sleeps/b.gets ratio1,100.*b.immediate_misses/decode((b.immediate_misses+b.immediate_gets),0,1) ratio2 
FROM v$latchname a, v$latch b WHERE
a.latch# = b.latch# AND b.sleeps > 0;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Top 10 Queries using more disk reads
 
    select disk_reads, sql_text from  
(    select disk_reads, sql_text from v$sqlarea
     where disk_reads > 10000 order by disk_reads desc) 
where    rownum < 11; 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Top 10 Queries using more buffer reads
 
 
select  buffer_gets,sql_text from v$sqlarea where buffer_gets>10000 and rownum < 11 order by buffer_gets desc;
 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Buffer cache hit ratio 
 
select (1- (sum(decode(a.name,'physical reads',value,0)))/
        (sum(decode(a.name,'db block gets',value,0)) +
        sum(decode(a.name,'consistent gets',value,0)))) * 100 pct
        from v$sysstat a;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Reloads/Pins in v$librarycache 
 
select sum(pins) "executions",sum(reloads) "cache Misses",sum(reloads)/sum(pins)*100 "Ratio" from v$librarycache;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Get miss ratio from v$rowcache  
 
select (sum(getmisses)/sum(gets)) * 100 "Hit Ratio" from v$rowcache;
 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> Redo log space request ratio in v$systat 
 
select (req.value*5000)/entries.value "ratio" 
from v$sysstat req,v$sysstat entries 
where req.name='redo log space requests' and entries.name='redo entries' ;
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> RBS Contention
 
 
select sum(waits)/sum(gets)*100 from v$rollstat; 
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> sorts stats
 
 
Select * from v$sysstat where name like '%sorts%';
 
prompt 
prompt 
prompt ################################################################################
 
 
prompt .>>>>>> datafiles I/O
 
 
select name, phyrds, phywrts from v$datafile df, v$filestat fs where df.file# = fs.file# order by phyrds;  
 
prompt 
prompt 
prompt 
prompt 
 
spool off

Qualquer duvida referente às views que estão no script, você pode acessar este link. Neste link você pode procurar a view, que ele irá mostrar todas as descrições dos campos retornados.

Deixe um comentário