SCRIPTS PARA MONITORAR PERFORMANCE

[+] Script para verificar sumário de utilização de um Snapshot AWR
select dbid,
 min(begin_time), max(end_time),
 sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
 sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
 sum(case metric_name when 'Redo Generated Per Sec' then average end) Redo_Bytes_per_sec,
 sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
 sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
 sum(case metric_name when 'Redo Writes Per Sec' then average end) Physical_redo_IOPS,
 sum(case metric_name when 'Current OS Load' then average end) OS_LOad,
 sum(case metric_name when 'CPU Usage Per Sec' then average end) DB_CPU_Usage_per_sec,
 sum(case metric_name when 'Host CPU Utilization (%)' then average end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
 sum(case metric_name when 'Network Traffic Volume Per Sec' then average end) Network_bytes_per_sec,
 sum(case metric_name when 'Current Logons Count' then average end) Current_Logons_Count,
 sum(case metric_name when 'Average Active Sessions' then average end) Average_Active_Sessions,
 sum(case metric_name when 'User Transaction Per Sec' then average end) User_Transaction_Per_Sec,
 snap_id
 from dba_hist_sysmetric_summary
 where dbid in ('91247438','4258904334','13746638','4181403534','4231213136','4103902736','4153712336','4026401936','1287883134')
 and begin_time > '13-10-2011 10:59'
 group by snap_id,dbid
 order by dbid,snap_id;
______________________________________________________________________
[+] Script para verificar total de Sessões do AWR
select distinct(snap_id), min(sample_time) "Inicio", max(sample_time) "Fim", count(session_id) "Total Sessoes"
 from dba_hist_active_sess_history group by snap_id order by 1;
______________________________________________________________________
[+] Script para verificar MEDIA DE I/O em Datafiles
col filesystem form a22 col phyrds form 999,999,999,999 col phyblkrd form 999,999,999,999 col phywrts form 999,999,999,999 col phyblkwrt form 999,999,999,999 col AvgRD form 999.99 col AvgWT form 999.99 col AvgBLKWT form 999.99 col AvgBLKRD form 999.99 break on report comp avg of avgrd on report comp avg of avgwt on report comp avg of avgblkwt on report comp avg of avgblkrd on report comp sum of phyrds on report comp sum of phyblkrd on report comp sum of phyblkwrt on report comp sum of phywrts on report select substr(df.file_name,1,22) filesystem, sum(f.phyrds) phyrds, sum(f.phyblkrd) phyblkrd , sum(f.phyrds) / sum(phyrds+phywrts) AvgRD, sum(f.phywrts) / sum(phyrds+phywrts) AvgWT, sum(f.phywrts) phywrts, sum(f.phyblkwrt) phyblkwrt, sum(f.phyblkwrt) / sum(f.phyblkrd + f.phyblkwrt) AvgBLKWT, sum(f.phyblkrd) / sum(f.phyblkrd + f.phyblkwrt) AvgBLKRD from v$filestat f , dba_data_files df where f.file#=df.file_id group by substr(df.file_name,1,22);
______________________________________________________________________
[+] Script para verificar contenção em Segmentos de UNDO
col name format a30
 col gets format 9,999,999
 col waits format 9,999,999
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 Caso o ratio de waits e gets esteja maior que 1% ou 2%
 prompt considerar criar mais segmentos de Rollback (Caso 9i)
prompt Outra maneira de medir utilização de segmentos UNDO:
 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' ;
______________________________________________________________________
[+] Script para verificar contenção em REDO
select name||' = '||value
 from v$sysstat
 where name = 'redo log space requests'
 /
prompt
 prompt Esse valor deve ser perto de 0. Caso este valor aumente constantemente
 prompt alguns processos podem esperar até que exista espaço livre na LOG_BUFFER.
 prompt Caso isto ocorra frequentemente, recomenda-se aumentar o parâmetro LOG_BUFFER.
 prompt
______________________________________________________________________
[+] Script para verificar HIT/RATIO de Dictionary Cache
prompt
 prompt Atentar para as colunas gethitratio e pinhit ratio
 prompt
 prompt GETHITRATIO é o número de GETHTS/GETS
 prompt PINHIT RATIO é o numero de PINHITS/PINS
 prompt Ambos devem se aproximar de "1"
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 99999999 heading 'PINHITS'
 column pinhitratio format 999.99 heading 'PIN HIT|RATIO'
select namespace, gets, gethits,
 gethitratio, pins, pinhitratio
 from v$librarycache ;
______________________________________________________________________
[+] Script para verificar HIT/RATIO de Dictionary Cache - Resumido
prompt
 prompt Data Dictionary Ratio
 prompt
 prompt Considere manter este valor abaixo de 5%
 prompt Caso esteja abaixo, aumentar SHARED_POOL_SIZE
 column dictcache format 999.99 heading 'Dictionary Cache | Ratio %'
select sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache
 from v$rowcache ;
______________________________________________________________________
[+] Script para verificar Status da SGA
col name format a40
select name, bytes
 from v$sgastat
 /
set head off
select 'total of SGA '||sum(bytes)
 from v$sgastat ;
______________________________________________________________________
[+] Script para verificar Status I/O de todos os datafiles
set pagesize 200;
 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 a70 heading 'DataFile Name'
 column writetim format 99999999 heading 'Write|Time'
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 ;
______________________________________________________________________
[+] Script para verificar maiores eventos em WAIT
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
 ;
______________________________________________________________________
[+] Script para verificar GETS e WAITS em segmentos UNDO
break on report
compute sum of gets waits writes on report
select rownum, extents, rssize,
 xacts, gets, waits, writes
 from v$rollstat
 order by rownum
 ;
______________________________________________________________________
[+] Script para verificar area de SORT (Ordenação)
prompt SORT AREA SIZE VALUES:
 prompt
 prompt Para garantir que a area de sort seja corretamente usada deve-se manter
 prompt espaco suficiente na tablespace temporaria correspondente.
 prompt Manter o parametro sort_area_retained_size = 0 ira forçar o oracle a liberar
 prompt a area de SORT imediatamente quando uma instrucao SQL com ordenacao terminar.
 prompt Isso ajuda reduzir consumo desnecessario de area TEMPORARIA
 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)')
 ;
______________________________________________________________________
[+] 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 Mínimo necessario para SHARED_POOL
set numwidth 15
 column shared_pool_size format 999,999,999
 column sum_obj_size format 999,999,999
 column sum_sql_size format 999,999,999
 column sum_user_size format 999,999,999
 column min_shared_pool format 999,999,999,999
 select to_number(value) shared_pool_size,
 sum_obj_size,
 sum_sql_size,
 sum_user_size,
 (sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
 from (select sum(sharable_mem) sum_obj_size
 from v$db_object_cache where type <> 'CURSOR'),
 (select sum(sharable_mem) sum_sql_size
 from v$sqlarea),
 (select sum(250 * users_opening) sum_user_size
 from v$sqlarea), v$parameter
 where name = 'shared_pool_size';
______________________________________________________________________
[+] Script para verificar conumo de PGA (No momento da Coleta)
break on report
 compute sum of value on report
 select u.username, s.sid, n.name as statistic, s.value as bytes
 from sys.gv_$session u, sys.gv_$sesstat s, sys.gv_$statname n
 where u.sid = s.sid
 and u.username is not null
 and s.statistic# = n.statistic#
 and n.name = 'session pga memory'
 order by s.value desc, u.username, s.sid
 ;
______________________________________________________________________
[+] Script para verificar conumo de UGA (No momento da Coleta)
break on report
 compute sum of value on report
 select u.username, s.sid, n.name as statistic, s.value as bytes
 from sys.gv_$session u, sys.gv_$sesstat s, sys.gv_$statname n
 where u.sid = s.sid
 and u.username is not null
 and s.statistic# = n.statistic#
 and n.name = 'session uga memory'
 order by s.value desc, u.username, s.sid
 ;
______________________________________________________________________
[+] Script para verificar conumo de PGA (Desde ultimo STARTUP)
break on report
 compute sum of value on report
 select u.username, s.sid, n.name as statistic, s.value as bytes
 from sys.gv_$session u, sys.gv_$sesstat s, sys.gv_$statname n
 where u.sid = s.sid
 and u.username is not null
 and s.statistic# = n.statistic#
 and n.name = 'session pga memory max'
 order by s.value desc, u.username, s.sid
 ;
______________________________________________________________________
[+] Script para verificar conumo de UGA (Desde ultimo STARTUP)
break on report
 compute sum of value on report
 select u.username, s.sid, n.name as statistic, s.value as bytes
 from sys.gv_$session u, sys.gv_$sesstat s, sys.gv_$statname n
 where u.sid = s.sid
 and u.username is not null
 and s.statistic# = n.statistic#
 and n.name = 'session uga memory max'
 order by s.value desc, u.username, s.sid
 ;
______________________________________________________________________
[+] Script para verificar operações de ordenação em memória (Desde ultimo STARTUP)
break on report
 compute sum of value on report
 select u.username, s.sid, n.name as statistic, s.value as bytes
 from sys.gv_$session u, sys.gv_$sesstat s, sys.gv_$statname n
 where u.sid = s.sid
 and u.username is not null
 and s.statistic# = n.statistic#
 and n.name = 'sorts (memory)'
 order by s.value desc, u.username, s.sid
 ;
______________________________________________________________________
[+] Script para verificar sessões atuais com mais leituras físicas
select /*+ no_merge */
 s.sid, s.username, io.*
 from v$sess_io io
 , v$session s
 where s.sid = io.sid
 and s.type != 'BACKGROUND'
 and io.physical_reads > 50000
 order by io.physical_reads
 ;
______________________________________________________________________
[+] Script para verificar sessões atuais ordenadas por consumo em evento WAIT
set feedback off
 set linesize 200 pagesize 10000 verify off
define topN=5
 define topStat=%
 define pctMinimo=1
set lines 154
 column sid format 99999
 column top_stat_name format a35 heading 'STATISTIC_NAME' trunc
 column sess_status format a4 heading 'STATUS' truncate
 column username_osuser format a22 heading 'USERNAME/OSUSER'
 column minutos format 9999 heading 'MIN'
 column statistic_value format 999g999g999g990 heading 'STATISTIC_VAL'
 column conn_time heading 'TEMPO DE|CONEXAO'
 column rank format 99
break on top_stat_name skip 1
 compute sum of pct on statistic_name
select statistic_name as top_stat_name
 , rank
 , pct
 , sid
 , user_name
 || decode( osuser, null, null, '/')
 || substr( osuser, 1+instr( osuser, '\') )
 as username_osuser
 , lpad( decode( trunc(sysdate - logon_time)
 , 0, null
 , trunc(sysdate - logon_time) || 'd, ' )
 || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
 , 'SSSSS' )
 , 'hh24"h "mi"m"' )
 , 12 )
 as conn_time
 , round( last_call_et/60, 1) as minutos
 , decode( status, 'ACTIVE', 'ATIVO', 'INACTIVE', 'INATIVO', status)
 as sess_status
 , statistic_value
 from ( select sn.name as statistic_name
 , row_number() over ( partition by sn.name
 order by sn.name, sv.value desc)
 as rank
 , 100 * ratio_to_report(sv.value) over (partition by sn.name) as pct
 , sv.value as statistic_value
 , decode( ss.username
 , NULL, substr( ss.program
 , instr(upper(ss.program), '(') )
 , ss.username )
 as user_name
 , ss.*
 from v$sesstat sv
 , v$statname sn
 , v$session ss
 where sn.statistic# = sv.statistic#
 and ss.sid = sv.sid
 and ss.username != 'SYS'
 and ss.type != 'BACKGROUND'
 and ss.status != 'KILLED'
 and sv.value > 0
 and sn.statistic#
 in ( 9 ---session logical reads
 , 10 ---session stored procedure space
 , 14 ---cluster wait time
 , 15 ---concurrency wait time
 , 16 ---application wait time
 , 17 ---user I/O wait time
 , 20 ---session uga memory
 , 21 ---session uga memory max
 , 25 ---session pga memory
 , 26 ---session pga memory max
 , 27 ---enqueue timeouts
 --- , 28 ---enqueue waits
 --- , 29 ---enqueue deadlocks
 --- , 30 ---enqueue requests
 , 31 ---enqueue conversions
 --- , 32 ---enqueue releases
 , 33 ---global enqueue gets sync
 , 34 ---global enqueue gets async
 , 35 ---global enqueue get time
 , 36 ---global enqueue releases
 --- , 37 ---physical read total IO requests
 --- , 38 ---physical read total multi block requests
 --- , 39 ---physical read total bytes
 --- , 40 ---physical write total IO requests
 --- , 41 ---physical write total multi block requests
 --- , 42 ---physical write total bytes
 --- , 43 ---IPC CPU used by this session
 --- , 44 ---gcs messages sent
 , 45 ---ges messages sent
 -- , 46 ---global enqueue CPU used by this session
 , 47 ---db block gets
 , 48 ---db block gets from cache
 , 49 ---db block gets direct
 , 50 ---consistent gets
 , 51 ---consistent gets from cache
 , 52 ---consistent gets - examination
 , 53 ---consistent gets direct
 --- , 54 ---physical reads
 --- , 55 ---physical reads cache
 --- , 56 ---physical reads direct
 --- , 57 ---physical read IO requests
 , 58 ---physical read bytes
 /* , 59 ---db block changes
 , 60 ---consistent changes
 , 61 ---recovery blocks read
 , 62 ---physical writes
 , 63 ---physical writes direct
 , 64 ---physical writes from cache
 , 65 ---physical write IO requests
 , 66 ---physical write bytes
 , 67 ---physical writes non checkpoint
 , 68 ---summed dirty queue length
 , 69 ---DBWR checkpoint buffers written
 , 70 ---DBWR thread checkpoint buffers written
 , 71 ---DBWR tablespace checkpoint buffers written
 , 72 ---DBWR parallel query checkpoint buffers written
 , 73 ---DBWR object drop buffers written
 , 74 ---DBWR transaction table writes
 , 75 ---DBWR undo block writes
 , 76 ---DBWR revisited being-written buffer
 , 77 ---DBWR make free requests
 , 78 ---DBWR lru scans
 , 79 ---DBWR checkpoints
 , 80 ---DBWR fusion writes
 , 81 ---prefetch clients - keep
 , 82 ---prefetch clients - recycle
 , 83 ---prefetch clients - default
 , 84 ---prefetch clients - 2k
 , 85 ---prefetch clients - 4k
 , 86 ---prefetch clients - 8k
 , 87 ---prefetch clients - 16k
 , 88 ---prefetch clients - 32k
 , 89 ---change write time
 , 90 ---redo synch writes
 , 91 ---redo synch time
 , 92 ---exchange deadlocks
 , 93 ---free buffer requested
 , 94 ---dirty buffers inspected
 , 95 ---pinned buffers inspected
 , 96 ---hot buffers moved to head of LRU
 , 97 ---free buffer inspected
 , 98 ---commit cleanout failures: write disabled
 , 99 ---commit cleanout failures: block lost
 , 100 ---commit cleanout failures: cannot pin
 , 101 ---commit cleanout failures: hot backup in progress
 , 102 ---commit cleanout failures: buffer being written
 , 103 ---commit cleanout failures: callback failure
 , 104 ---commit cleanouts
 , 105 ---commit cleanouts successfully completed
 , 106 ---recovery array reads
 , 107 ---recovery array read time
 , 108 ---CR blocks created
 , 109 ---current blocks converted for CR
 , 110 ---switch current to new buffer
 , 111 ---write clones created in foreground
 , 112 ---write clones created in background
 , 113 ---write clones created for recovery
 , 114 ---physical reads cache prefetch
 , 115 ---physical reads prefetch warmup
 , 116 ---prefetched blocks aged out before use
 , 117 ---prefetch warmup blocks aged out before use
 , 118 ---prefetch warmup blocks flushed out before use
 , 119 ---physical reads retry corrupt
 , 120 ---physical reads direct (lob)
 , 121 ---physical reads direct temporary tablespace
 , 122 ---physical writes direct (lob)
 , 123 ---physical writes direct temporary tablespace
 , 124 ---cold recycle reads
 , 125 ---shared hash latch upgrades - no wait
 , 126 ---shared hash latch upgrades - wait
 , 127 ---physical reads for flashback new
 , 128 ---calls to kcmgcs
 , 129 ---calls to kcmgrs
 , 130 ---calls to kcmgas
 , 131 ---calls to get snapshot scn: kcmgss
 , 132 ---redo blocks read for recovery
 , 133 ---redo entries
 , 134 ---redo size
 , 135 ---redo buffer allocation retries
 , 136 ---redo wastage
 , 137 ---redo writer latching time
 , 138 ---redo writes
 , 139 ---redo blocks written
 , 140 ---redo write time
 , 141 ---redo log space requests
 , 142 ---redo log space wait time
 , 143 ---redo log switch interrupts
 , 144 ---redo ordering marks
 , 145 ---redo subscn max counts
 */ , 146 ---gc cr blocks served
 , 147 ---gc cr block build time
 , 148 ---gc cr block flush time
 , 149 ---gc cr block send time
 , 150 ---gc current blocks served
 , 151 ---gc current block pin time
 , 152 ---gc current block flush time
 , 153 ---gc current block send time
 , 154 ---gc cr blocks received
 , 155 ---gc cr block receive time
 , 156 ---gc current blocks received
 , 157 ---gc current block receive time
 , 158 ---gc blocks lost
 , 159 ---gc claim blocks lost
 , 160 ---gc blocks corrupt
 , 161 ---gc CPU used by this session
 /* , 162 ---total number of slots
 , 163 ---instance recovery database freeze count
 , 164 ---background checkpoints started
 , 165 ---background checkpoints completed
 , 166 ---number of map operations
 , 167 ---number of map misses
 , 168 ---flashback log writes
 , 169 ---serializable aborts
 , 170 ---transaction lock foreground requests
 , 171 ---transaction lock foreground wait time
 , 172 ---transaction lock background gets
 , 173 ---transaction lock background get time
 , 174 ---undo change vector size
 , 175 ---transaction tables consistent reads - undo records
 , 176 ---transaction tables consistent read rollbacks
 , 177 ---data blocks consistent reads - undo records applie
 , 178 ---no work - consistent read gets
 , 179 ---cleanouts only - consistent read gets
 , 180 ---rollbacks only - consistent read gets
 , 181 ---cleanouts and rollbacks - consistent read gets
 , 182 ---RowCR attempts
 , 183 ---RowCR hits
 , 184 ---RowCR - row contention
 , 185 ---RowCR - resume
 , 186 ---rollback changes - undo records applied
 , 187 ---transaction rollbacks
 , 188 ---immediate (CURRENT) block cleanout applications
 , 189 ---immediate (CR) block cleanout applications
 , 190 ---deferred (CURRENT) block cleanout applications
 , 191 ---commit txn count during cleanout
 , 192 ---active txn count during cleanout
 , 193 ---cleanout - number of ktugct calls
 , 194 ---immediate CR cleanouts (index blocks)
 , 195 ---deferred CUR cleanouts (index blocks)
 , 196 ---Commit SCN cached
 , 197 ---Cached Commit SCN referenced
 , 198 ---auto extends on undo tablespace
 , 199 ---drop segment calls in space pressure
 , 200 ---total number of undo segments dropped
 , 201 ---doubling up with imu segment
 , 202 ---tune down retentions in space pressure
 , 203 ---steps of tune down ret. in space pressure
 , 204 ---space was found by tune down
 , 205 ---space was not found by tune down
 , 206 ---commit batch/immediate requested
 , 207 ---commit batch requested
 , 208 ---commit immediate requested
 , 209 ---commit batch/immediate performed
 , 210 ---commit batch performed
 , 211 ---commit immediate performed
 , 212 ---commit wait/nowait requested
 , 213 ---commit nowait requested
 , 214 ---commit wait requested
 , 215 ---commit wait/nowait performed
 , 216 ---commit nowait performed
 , 217 ---commit wait performed
 , 218 ---global undo segment hints helped
 , 219 ---global undo segment hints were stale
 , 220 ---local undo segment hints helped
 , 221 ---local undo segment hints were stale
 , 222 ---undo segment header was pinned
 , 223 ---total number of times SMON posted
 , 224 ---SMON posted for undo segment recovery
 , 225 ---SMON posted for txn recovery for other instances
 , 226 ---SMON posted for instance recovery
 , 227 ---SMON posted for undo segment shrink
 , 228 ---SMON posted for dropping temp segment
 , 229 ---SMON posted for supplemental logging cleanup
 , 230 ---IMU commits
 , 231 ---IMU Flushes
 , 232 ---IMU contention
 , 233 ---IMU recursive-transaction flush
 , 234 ---IMU undo retention flush
 , 235 ---IMU ktichg flush
 , 236 ---IMU bind flushes
 , 237 ---IMU mbu flush
 , 238 ---IMU pool not allocated
 , 239 ---IMU CR rollbacks
 , 240 ---IMU undo allocation size
 , 241 ---IMU Redo allocation size
 , 242 ---IMU- failed to get a private strand
 , 243 ---Misses for writing mapping
 */ , 244 ---table scans (short tables)
 , 245 ---table scans (long tables)
 , 246 ---table scans (rowid ranges)
 , 247 ---table scans (cache partitions)
 , 248 ---table scans (direct read)
 , 249 ---table scan rows gotten
 -- , 250 ---table scan blocks gotten
 , 251 ---table fetch by rowid
 , 252 ---table fetch continued row
 /* , 253 ---cluster key scans
 , 254 ---cluster key scan block gets
 , 255 ---rows fetched via callback
 , 256 ---queue update without cp update
 , 257 ---leaf node splits
 , 258 ---leaf node 90-10 splits
 , 259 ---branch node splits
 , 260 ---failed probes on index block reclamation
 , 261 ---recursive aborts on index block reclamation
 , 262 ---native hash arithmetic execute
 , 263 ---native hash arithmetic fail
 , 264 ---lob reads
 , 265 ---lob writes
 */ , 266 ---lob writes unaligned
 , 267 ---index fast full scans (full)
 , 268 ---index fast full scans (rowid ranges)
 , 269 ---index fast full scans (direct read)
 , 270 ---index fetch by key
 , 271 ---index scans kdiixs1
 /* , 272 ---queue splits
 , 273 ---queue flush
 , 274 ---queue position update
 , 275 ---queue single row
 , 276 ---queue ocp pages
 , 277 ---queue qno pages
 , 278 ---heap block compress
 , 279 ---sql area purged
 , 280 ---sql area evicted
 , 281 ---CCursor + sql area evicted
 , 282 ---session cursor cache hits
 , 283 ---session cursor cache count
 , 284 ---java call heap total size
 , 285 ---java call heap total size max
 , 286 ---java call heap used size
 , 287 ---java call heap used size max
 , 288 ---java call heap live size
 , 289 ---java call heap live size max
 , 290 ---java call heap object count
 , 291 ---java call heap object count max
 , 292 ---java call heap live object count
 , 293 ---java call heap live object count max
 , 294 ---java call heap gc count
 , 295 ---java call heap collected count
 , 296 ---java call heap collected bytes
 , 297 ---java session heap used size
 , 298 ---java session heap used size max
 , 299 ---java session heap live size
 , 300 ---java session heap live size max
 , 301 ---java session heap object count
 , 302 ---java session heap object count max
 , 303 ---java session heap live object count
 , 304 ---java session heap live object count max
 , 305 ---java session heap gc count
 , 306 ---java session heap collected count
 , 307 ---java session heap collected bytes
 */ , 308 ---cursor authentications
 , 309 ---queries parallelized
 , 310 ---DML statements parallelized
 , 311 ---DDL statements parallelized
 , 312 ---DFO trees parallelized
 -- , 313 ---Parallel operations not downgraded
 , 314 ---Parallel operations downgraded to serial
 /* , 315 ---Parallel operations downgraded 75 to 99 pct
 , 316 ---Parallel operations downgraded 50 to 75 pct
 , 317 ---Parallel operations downgraded 25 to 50 pct
 , 318 ---Parallel operations downgraded 1 to 25 pct
 , 319 ---PX local messages sent
 , 320 ---PX local messages recv'd
 , 321 ---PX remote messages sent
 , 322 ---PX remote messages recv'd
 , 323 ---buffer is pinned count
 , 324 ---buffer is not pinned count
 , 325 ---no buffer to keep pinned count
 , 326 ---table lookup prefetch client count
 , 327 ---workarea memory allocated
 , 328 ---workarea executions - optimal
 , 329 ---workarea executions - onepass
 , 330 ---workarea executions - multipass
 , 331 ---parse time cpu
 , 332 ---parse time elapsed
 , 333 ---parse count (total)
 , 334 ---parse count (hard)
 , 335 ---parse count (failures)
 */ , 336 ---frame signature mismatch
 , 337 ---execute count
 , 338 ---bytes sent via SQL*Net to client
 , 341 ---bytes sent via SQL*Net to dblink
 , 342 ---bytes received via SQL*Net from dblink
 , 344 ---sorts (memory)
 , 345 ---sorts (disk)
 , 346 ---sorts (rows)
 /* , 347 ---OTC commit optimization attempts
 , 348 ---OTC commit optimization hits
 , 349 ---OTC commit optimization failure - setup
 */ , 350 ---OS User time used
 , 351 ---OS System time used
 , 352 ---OS Maximum resident set size
 , 353 ---OS Integral shared text size
 , 354 ---OS Integral unshared data size
 , 355 ---OS Integral unshared stack size
 , 356 ---OS Page reclaims
 , 357 ---OS Page faults
 , 358 ---OS Swaps
 , 359 ---OS Block input operations
 , 360 ---OS Block output operations
 , 361 ---OS Socket messages sent
 , 362 ---OS Socket messages received
 , 363 ---OS Signals received
 , 364 ---OS Voluntary context switches
 , 365 ---OS Involuntary context switches
 )
 )
 where trunc(pct) > &pctMinimo -- percentuais maiores que pctMinimo
 and rank < 1+&topN -- "N" maiores sessoes que consomem aquele recurso and upper(statistic_name) like upper('%&topStat.%') ;
 ______________________________________________________________________
[+] Script para verificar sessões atuais ordenadas por consumo em evento I/O
clear breaks compute
 set feedback 6
 undefine topN topStat topUser
set feedback off
 set linesize 155 pagesize 10000 verify off
define topN=3
 define topStat=%
 define pctMinimo=1
set lines 154
 column sid format 99999
 column top_stat_name format a55 heading 'STATISTIC_NAME' trunc
 column sess_status format a4 heading 'STATUS' truncate
 column username_osuser format a20 heading 'USERNAME/OSUSER'
 column minutos format 9999 heading 'MIN'
 column statistic_value format 9g999g999g990 heading 'STATISTIC_VAL'
 column conn_time heading 'LOGADO A'
 column rank format 99
break on top_stat_name skip 1
 compute sum of pct on statistic_name
select statistic_name as top_stat_name
 , rank
 , pct
 , sid
 , decode( username, null, '('||bg_name||')', username)
 || decode( osuser, null, null, '/')
 || substr( osuser, 1+instr( osuser, '\') )
 as username_osuser
 , lpad( decode( trunc(sysdate - logon_time)
 , 0, null
 , trunc(sysdate - logon_time) || 'd, ' )
 || to_char ( to_date( trunc( 86400 * ((sysdate-logon_time) - trunc(sysdate - logon_time)) )
 , 'SSSSS' )
 , 'hh24"h "mi"m"' )
 , 10 )
 as conn_time
 , round( last_call_et/60, 1) as minutos
 , decode( status, 'ACTIVE', 'ATIVO', 'INACTIVE', 'INATIVO', status)
 as sess_status
 , statistic_value
 from ( select sn.name as statistic_name
 , row_number() over ( partition by sn.name
 order by sn.name, sv.value desc)
 as rank
 , 100 * ratio_to_report(sv.value) over (partition by sn.name) as pct
 , sv.value as statistic_value
 , bg.name as bg_name
 , ss.*
 from v$sesstat sv
 , v$statname sn
 , v$session ss
 , v$bgprocess bg
 where sn.statistic# = sv.statistic#
 and ss.sid = sv.sid
 and ss.paddr = bg.paddr(+)
 and ss.type != 'BACKGROUND'
 and ( ( status = 'INACTIVE' and last_call_et/60 < 5 ) or status = 'ACTIVE' ) and sv.value > 0
 and sn.name
 in ( 'consistent gets'
 , 'db block gets'
 , 'physical reads'
 , 'physical writes'
 -- , 'physical reads direct'
 -- , 'physical writes direct'
 -- , 'bytes sent via SQL*Net to client'
 -- , 'bytes received via SQL*Net from dblink'
 -- , 'bytes sent via SQL*Net to dblink'
 -- , 'enqueue releases'
 -- , 'enqueue requests'
 , 'enqueue waits'
 -- , 'recursive cpu usage'
 -- , 'session logical reads'
 -- , 'session pga memory'
 -- , 'session uga memory'
 , 'session pga memory max'
 , 'session uga memory max'
 , 'execute count'
 , 'parse count (hard)'
 -- , 'parse time cpu'
 -- , 'parse time elapsed'
 -- , 'parse count (total)'
 -- , 'sorts (disk)'
 -- , 'sorts (memory)'
 , 'sorts (rows)'
 , 'table scans (long tables)'
 , 'table fetch continued row'
 )
 )
 where trunc(pct) > &pctMinimo -- percentuais maiores que pctMinimo
 and rank < 1+&topN -- "N" maiores sessoes que consomem aquele recurso and upper(statistic_name) like upper('%&topStat.%') ; ______________________________________________________________________ [+] Script para verificar instruções SQL que consomem mais LOAD do Banco de Dados column sql format a100 set linesize 155 set pagesize 200 prompt This script shows SQL statement that account for more than prompt a certain percentage of disk reads. prompt column executes format 9999999 break on load on executes select substr( to_char( 100 * s.disk_reads / t.total_disk_reads, '99.00' ), 2 ) || '%' load, s.executions executes, p.sql_text sql from ( select sum(disk_reads) total_disk_reads from sys.v_$sql where command_type != 47 ) t, sys.v_$sql s, sys.v_$sqltext p where 100 * s.disk_reads / t.total_disk_reads > 2.5 and
 s.disk_reads > 50 * s.executions and
 s.command_type != 47 and
 p.address = s.address
 order by
 1, s.address, p.piece
 ;
______________________________________________________________________
[+] Script para verificar instruções SQL mais executadas
break on sql_text
 col sql_text for a60
 col "CPU (Minutos)" for 99.99
SELECT * FROM
 (SELECT sql_text,
 executions, rows_processed, rows_processed/executions "Rows/Exec",
 disk_reads, round(((cpu_time/1000000)/60),2) "CPU (Minutes)"
 FROM V$SQLAREA
 WHERE executions > 100
 ORDER BY executions DESC)
 WHERE rownum

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 PLSQL SCRIPTS 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