SCRIPTS - PERFORMANCE BANCO DE DADOS ORACLE

Área de scripts destinados ao monitoramento da performance do banco de dados.

[+] Script para verificar as recomendações de ajuste para o buffer cache

column c1   heading 'Cache Size (m)'        format 999,999,999,999
column c2   heading 'Buffers'               format 999,999,999
column c3   heading 'Estd Phys|Read Factor' format 999.90
column c4   heading 'Estd Phys| Reads'      format 999,999,999
 
select
   size_for_estimate          "Cache Size (m) "        ,
   buffers_for_estimate       "Buffers",
   estd_physical_read_factor  "Estd Phys|Read Factor",
   estd_physical_reads        "Estd Phys| Reads"      
from
   v$db_cache_advice
where
   name = 'DEFAULT'
and
   block_size  = (SELECT value FROM V$PARAMETER
                   WHERE name = 'db_block_size')
and
   advice_status = 'ON';

[+] Script para verificar as recomendações de ajuste para a shared pool

set lines  100
set pages  999
 
column        c1     heading 'Pool |Size(M)'
column        c2     heading 'Size|Factor'
column        c3     heading 'Est|LC(M)  '
column        c4     heading 'Est LC|Mem. Obj.'
column        c5     heading 'Est|Time|Saved|(sec)'
column        c6     heading 'Est|Parse|Saved|Factor'
column c7     heading 'Est|Object Hits'   format 999,999,999

SELECT
       shared_pool_size_for_estimate  c1,
       shared_pool_size_factor        c2,
       estd_lc_size                   c3,
       estd_lc_memory_objects         c4,
       estd_lc_time_saved                    c5,
       estd_lc_time_saved_factor             c6,
       estd_lc_memory_object_hits            c7
    FROM
      v$shared_pool_advice;

[+] Script para verificar as recomendações de ajuste para a PGA

column c1     heading 'Target(M)'
column c2     heading 'Estimated|Extra rw'
column c3     heading 'Estimated|Cache Hit %'
column c4     heading 'Estimated|Over-Alloc.'

 
SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) c1,
   ROUND(estd_extra_bytes_rw/(1024*1024))      c2,
   estd_pga_cache_hit_percentage         c3,
   estd_overalloc_count                  c4
FROM
   v$pga_target_advice;

[+] Script para verificar consumo de I/O por datafile

set pages 100
set lines 100

column "col1"  format a45      heading "Nome do Data File"  word_wrapped
column "col2"  format 999,999,990     heading "Blocos      |Fisicos     "
column "col3"  format 999,999,990     heading "Leituras    |Fisicas     "
column "col4"  format 999,999,990     heading "Gravacoes   |Fisicas     "
column "col5"  format 999,999,990     heading "Total       |de I/O      "

select 
 a.name         "col1" ,  
 b.phyblkrd              "col2" ,  
 b.phyrds               "col3" ,  
 b.phywrts        "col4" , 
 b.phyrds+b.phywrts       "col5"  
from
 v$datafile a,
 v$filestat b
where
 a.file#=b.file#
order by 3;

[+] Script para verificar consumo de I/O por tablespace

set feedback off

set lines 155
break on report
compute sum of WRITEPCT on report
compute sum of READPCT on report
compute sum of IOPCT on report

select * from
( SELECT TABLESPACE_NAME
, sum(PHYRDS) as PHYRDS
, sum(PHYWRTS) as PHYWRTS
, sum(PHYBLKRD) as PHYBLKRD
, sum(PHYBLKWRT) as PHYBLKWRT
, sum(READPCT) as READPCT
, sum(WRITEPCT) as WRITEPCT
, sum(IOPCT) as IOPCT
FROM
( SELECT
ts.name
as tablespace_name
, fs.PHYRDS
, fs.PHYWRTS
, fs.PHYBLKRD
, fs.PHYBLKWRT
, 100 * ratio_to_report(fs.PHYBLKRD) over () as readpct
, 100 * ratio_to_report(fs.PHYBLKWRT) over () as writepct
, 100 * ratio_to_report(fs.PHYBLKRD+fs.PHYBLKWRT) over () as iopct
FROM V$FILESTAT fs, V$DATAFILE df, v$tablespace ts, dba_tablespaces dt
WHERE fs.file# = df.file#
AND df.ts# = ts.ts#
AND dt.tablespace_name = ts.name )
GROUP BY tablespace_name
ORDER BY iopct desc )
where rownum < 11 ; 

[+] Script para verificar consumo de I/O por sessão

set lines 190
col name for a30
col module for a20
col program for a30
col username for a20

select a.sid, b.name, a.value, c.module, c.program, c.username
   from v$sesstat a, v$statname b, v$session c
  where a.STATISTIC# = b.STATISTIC#
    and b.STAT_ID in (1190468109, 2263124246)
    and a.sid = c.sid
    and type <> 'BACKGROUND'
  order by a.value;

[+] Script para verificar consumo de I/O por sessão 2

set lines 200
col username for a20

select nvl(ses.USERNAME,'ORACLE PROC') username,
 OSUSER os_user,
 PROCESS pid,
 ses.SID sid,
 SERIAL#,
 PHYSICAL_READS,
 BLOCK_GETS,
 CONSISTENT_GETS,
 BLOCK_CHANGES,
 CONSISTENT_CHANGES
from v$session ses, 
 v$sess_io sio
where  ses.SID = sio.SID
order  by PHYSICAL_READS, ses.USERNAME;

[+] Script para verificar o consumo de CPU por sessão

set lines 200
col username for a20

select  nvl(ss.USERNAME,'ORACLE PROC') username,
 se.SID,
 VALUE cpu_usage
from  v$session ss, 
 v$sesstat se, 
 v$statname sn
where   se.STATISTIC# = sn.STATISTIC#
and   NAME like '%CPU used by this session%'
and   se.SID = ss.SID
order   by VALUE asc;

[+] Script para verificar a utilização de memória por usuario e/ou sessão

break on username 
compute avg maximum minimum of mem_used_in_kb  on username

select
--   sid,
   username,
   round(total_user_mem/1024,2) mem_used_in_kb,
   round(100 * total_user_mem/total_mem,2) mem_percent
from
   (select
--      b.sid sid,
      nvl(b.username,p.name) username,
      sum(value) total_user_mem
   from
      sys.v_$statname c,
      sys.v_$sesstat a,
      sys.v_$session b,
      sys.v_$bgprocess p
   where
      a.statistic#=c.statistic# and
      p.paddr (+) = b.paddr and
      b.sid=a.sid and
      c.name in ('session pga memory','session uga memory')
   group by
      b.sid, nvl(b.username,p.name)),
   (select
      sum(value) total_mem
   from
      sys.v_$statname c,
      sys.v_$sesstat a
   where
      a.statistic#=c.statistic# and
      c.name in ('session pga memory','session uga memory'))
order by 1,2 desc;

[+] Script para verificar estatísticas do dicionário de dados

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT 
 parameter
 , sum(gets)
 , sum(getmisses)
 , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
 , sum(modifications) updates
FROM V$ROWCACHE
 WHERE gets > 0
GROUP BY rollup(parameter);

[+] Script para verificar a utilização do buffer cache

set lines 80

column "col1"  format a25    heading "BLOCOS BUFFER CACHE"
column "col2"  format 99,999,999,990   heading "Quantidade"
column "col3"  format 99,999,999,990   heading "TOTAL"

select 
 decode(state, 0,'Nao Usado',
   1,'Lido e Modificado',
   2,'Lido e nao Modificado',
   3,'Lido Correntemente',
     'Outros')   "col1",
 count(*)     "col2"
from 
 x$bh
group by
 decode(state, 0,'Nao Usado',
   1,'Lido e Modificado',
   2,'Lido e nao Modificado',
   3,'Lido Correntemente',
     'Outros');

[+] Script para verificar a utilização da shared pool

set serveroutput on;  
  
declare
  object_mem     number;
  shared_sql     number;
  cursor_mem     number;
  mts_mem        number;
  used_pool_size number;
  free_mem       number;
  pool_size      varchar2(512); -- same as V$PARAMETER.VALUE  
begin

  select sum(sharable_mem) into object_mem from v$db_object_cache;

  select sum(250 * users_opening) into cursor_mem from v$sqlarea;

  select sum(value)
    into mts_mem
    from v$sesstat s, v$statname n
   where s.statistic# = n.statistic#
     and n.name = 'session uga memory max';

  select bytes
    into free_mem
    from v$sgastat
   where name = 'free memory'
     and pool = 'shared pool';

  used_pool_size := round(1.3 * (object_mem + cursor_mem));

  select value
    into pool_size
    from v$parameter
   where name = 'shared_pool_size';

  -- Display results  
  dbms_output.put_line('Object mem:    ' || to_char(object_mem) ||
                       ' bytes');
  dbms_output.put_line('Cursors:       ' || to_char(cursor_mem) ||
                       ' bytes');
  -- dbms_output.put_line ('MTS session:   '||to_char (mts_mem) || ' bytes');  
  dbms_output.put_line('Free memory:   ' || to_char(free_mem) || ' bytes ' || '(' ||
                       to_char(round(free_mem / 1024 / 1024, 2)) || 'MB)');
  dbms_output.put_line('Shared pool utilization (total):  ' ||
                       to_char(used_pool_size) || ' bytes ' || '(' ||
                       to_char(round(used_pool_size / 1024 / 1024, 2)) ||
                       'MB)');
  dbms_output.put_line('Shared pool allocation (actual):  ' || pool_size ||
                       ' bytes ' || '(' ||
                       to_char(round(pool_size / 1024 / 1024, 2)) || 'MB)');
  dbms_output.put_line('Percentage Utilized:  ' ||
                       to_char(round(used_pool_size / pool_size * 100)) || '%');
end;
/

[+] Script para verificar os pool livres na SGA

col mb_free for 999,999,999.999

select initcap(pool) pool, bytes / 1024 / 1024 mb_free
  from v$sgastat
 where name = 'free memory';

[+] Script para verificar o percentual de parses (soft/hard)

set lines 155

select
  to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
  to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
  ( select value calls from v$sysstat where name = 'parse count (total)' ),
  ( select value hard  from v$sysstat where name = 'parse count (hard)' ),
 ( select value sess  from v$sysstat where name = 'session cursor cache hits' )
/

[+] Script para verificar a quantidade de sorts (memory/disk)

set lines 80

column "col1" format a70 

select 
 initcap(rpad(name,30,' ')||' = '||
 rpad(to_char(value,'999,999,999,990'),30,' ')) "col1"
from
 v$sysstat
where
 name in ('sorts (memory)','sorts (disk)');

[+] Script para verificar os waits de redo log buffer

set lines 155
set pages 500

column "col1" heading "Evento" format a50 
column "col2" heading "Número Esperas" format 99,999,999,999
column "col3" heading "Média ESPERA (S)" format 99,999.90

select 
 event                  "col1" ,
 sum(total_waits)       "col2" ,  
 avg(average_wait)/100   "col3" 
from
 v$system_event
where
 average_wait > 0
and event in 
(
 'Log archive I/O','log buffer space','log file sync','log file parallel write','log file sequential read','log file single write'
 ,'Log file init write','log file switch (archiving needed)','log file switch (checkpoint incomplete)','log file switch (clearing log file)'
    ,'log file switch completion','log file switch (private strand flush incomplete)','log switch/archive','log write(even)','log write(odd)'
)
group by event
order by 3 desc;

[+] Script para verificar detalhes da coleta de estatísticas sobre uma determinada tabela

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
  FROM DBA_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'TABLE_NAME'
 ORDER BY COLUMN_NAME;

[+] Script para verificar a utilização de library chace

set pagesize 58
set linesize 150

column namespace format a20 heading 'Entity'
column pins format 999,999,999 heading 'Executions'
column pinhits format 999,999,999 heading 'Hits'
column pinhitratio format 9.99 heading 'Hit|Ratio'
column reloads format 999,999 heading 'Reloads'
column reloadratio format .9999 heading 'Reload|Ratio'
column invalidations format 999999999 heading 'Invalidations'

select namespace,
       pins,
       pinhits,
       pinhitratio,
       reloads,
       reloads / decode(pins, 0, 1, pins) reloadratio,
       invalidations
  from v$librarycache;

[+] Script para verificar os loads na shared pool

set lines 190
col owner for a30
col object for a40

select  OWNER,
 NAME||' - '||TYPE object,
 LOADS
from  v$db_object_cache
where  LOADS > 3 
and  type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order  by LOADS;

[+] Script para verificar os top sql baseado em tempo de execução

set lines 190

SELECT * FROM 
 (SELECT 
  sql_id, 
  child_number, 
  disk_reads, 
  executions,
  elapsed_time,
  first_load_time, 
  last_load_time 
 FROM    v$sql 
ORDER BY elapsed_time DESC) 
WHERE ROWNUM < 10;

Caso você queira compartilhar algum script que não está na lista, entre em contato pelo e-mail anderson.graf87@gmail.com ou deixe um comentário.