SCRIPTS - PERFORMANCE BANCO DE DADOS ORACLE

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

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';
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;
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;
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;
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 ; 
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;
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;
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;
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;
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);
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');
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;
/
col mb_free for 999,999,999.999

select initcap(pool) pool, bytes / 1024 / 1024 mb_free
  from v$sgastat
 where name = 'free memory';
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' )
/
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)');
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;
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
  FROM DBA_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'TABLE_NAME'
 ORDER BY COLUMN_NAME;
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;
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;
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.

Um comentário:

Página inicial

Translate

# Suporte

# ACE Program

#Oracle

#Oracle
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

#Blog reconhecido

#ARTICULISTA

Marcadores

Postagens populares