STATSPACK alert report for the DBA

Here is the 'STATSPACK_ALERT.SQL' script:

--***********************************************************
--
-- STATSPACK alert report for the DBA
--
-- Created 8/4/2000 by Donald K. Burleson
-- www.dba-oracle.com
--
-- This script is provided free-of-charge by Don Burleson
--
-- This script accepts the "number of days back" as an imput parameter
--
-- This script can be scheduled to run daily via cron or OEM
-- and e-mail the results to the on-call DBA
--
--***********************************************************
 
set pages 9999;
set feedback off;
set verify off;
--***********************************************************
-- Alert when data buffer hit ratio is below threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When the data buffer hit ratio falls below 90%, you
prompt should consider adding to the db_block_buffer init.ora parameter
prompt
prompt See p. 171 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999
select
to_char(snap_time,'yyyy-mm-dd HH24'),
-- a.value + b.value "logical_reads",
-- c.value "phys_reads",
-- d.value "phys_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) 
"BUFFER HIT RATIO"
from 
perfstat.stats$sysstat a, 
perfstat.stats$sysstat b, 
perfstat.stats$sysstat c, 
perfstat.stats$sysstat d,
perfstat.stats$snapshot sn
where
(round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) ) < 90
and
snap_time > sysdate-&1
and
a.snap_id = sn.snap_id
and
b.snap_id = sn.snap_id
and
c.snap_id = sn.snap_id
and
d.snap_id = sn.snap_id
and
a.statistic# = 39
and
b.statistic# = 38
and
c.statistic# = 40
and
d.statistic# = 41
;
 
 
 
--***********************************************************
-- Alert when total disk sorts are below threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When there are high disk sorts, you should investigate
prompt increasing sort_area_size, or adding indexes to force index_full scans
prompt
prompt See p. 167 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .99999
select 
to_char(snap_time,'yyyy-mm-dd HH24'),
a.value sorts_memory,
b.value sorts_disk,
(b.value/a.value) ratio
from
perfstat.stats$sysstat a,
perfstat.stats$sysstat b,
perfstat.stats$snapshot sn
where
-- Where there are more than 200 disk sorts per hour
b.value > 200
and
snap_time > sysdate-&1
and
a.snap_id = sn.snap_id
and
b.snap_id = sn.snap_id
and
a.name = 'sorts (memory)'
and
b.name = 'sorts (disk)'
;
 
 
 
--***********************************************************
-- Alert when total I/O wait count is above threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt When there is high I/O waits, disk bottlenecks may exist
prompt Run iostats to find the hot disk and shuffle files to
prompt remove the contention 
prompt
prompt See p. 191 "High Performance Oracle8 Tuning" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
break on snapdate skip 2
column snapdate format a16
column filename format a40
select 
to_char(snap_time,'yyyy-mm-dd HH24') snapdate,
filename, 
wait_count 
from
perfstat.stats$filestatxs fs,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
fs.snap_id = sn.snap_id
and
wait_count > 800
;
 
--***********************************************************
-- Alert when average buffer busy waits exceed threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt Buffer Bury Waits may signal a high update table with too
prompt few freelists. Find the offending table and add more freelists. 
prompt
prompt See p. 134 "Oracle SAP Administration" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column buffer_busy_wait format 999,999,999
select 
to_char(snap_time,'yyyy-mm-dd HH24'),
avg(buffer_busy_wait) buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics fs,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
fs.snap_id = sn.snap_id
having
avg(buffer_busy_wait) > 100
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
 
--***********************************************************
-- Alert when total redo log space requests exceed threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt High redo log space requests indicate a need to increase
prompt the log_buffer parameter
prompt
prompt
prompt ***********************************************************
prompt
prompt
column redo_log_space_requests format 999,999,999
select 
to_char(snap_time,'yyyy-mm-dd HH24') snap_date,
a.value redo_log_space_requests
from
perfstat.stats$sysstat a,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
a.value > 300
and
a.snap_id = sn.snap_id
and
a.name = 'redo log space requests'
;
 
--***********************************************************
-- Alert when table_fetch_continued_row exceeds threshold
--***********************************************************
prompt
prompt
prompt ***********************************************************
prompt Table fetch continued row indicates chained rows, or fetches of
prompt long datatypes (long raw, blob)
prompt 
prompt Investigate increasing db_block_size or reorganizing tables
prompt with chained rows.
prompt
prompt See p. 381 "High Performance Oracle8 Tuning" by Don Burleson
prompt See p. 102 "Oracle SAP Administration" by Don Burleson
prompt
prompt ***********************************************************
prompt
prompt
column table_fetch_continued_row format 999,999,999
select 
to_char(snap_time,'yyyy-mm-dd HH24'),
avg(a.value) table_fetch_continued_row
from
perfstat.stats$sysstat a,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
a.snap_id = sn.snap_id
and
a.name = 'table fetch continued row'
having 
avg(a.value) > 100000
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
Here is a sample alert report for a real shop:
***********************************************************
When the data buffer hit ratio falls below 90%, you
should consider adding to the db_block_buffer init.ora parameter
See p. 171 "High Performance Oracle8 Tuning" by Don Burleson
***********************************************************
 
 
TO_CHAR(SNAP_ BUFFER HIT RATIO
------------- ----------------
2000-08-02 16 83
2000-08-03 02 85
2000-08-03 09 78
2000-08-03 16 81
2000-08-03 17 70
2000-08-03 18 66
2000-08-04 00 84
2000-08-04 01 80
2000-08-04 02 80
2000-08-04 19 87
2000-08-05 02 85
 
***********************************************************
When there are high disk sorts, you should investigate
increasing sort_area_size, or adding indexes to force index_full scans
See p. 167 "High Performance Oracle8 Tuning" by Don Burleson
***********************************************************
 
 
TO_CHAR(SNAP_ SORTS_MEMORY SORTS_DISK RATIO
------------- ------------ ------------ -------
2000-08-04 12 354,196 215 .00061
2000-08-04 13 368,676 223 .00060
2000-08-04 14 413,448 250 .00060
2000-08-04 15 463,245 261 .00056
2000-08-04 16 501,521 277 .00055
2000-08-04 17 558,022 292 .00052
2000-08-04 18 571,862 299 .00052
 
***********************************************************
When there is high I/O waits, disk bottlenecks may exist
Run iostats to find the hot disk and shuffle files to
remove the contention
See p. 191 "High Performance Oracle8 Tuning" by Don Burleson
***********************************************************
 
 
SNAPDATE FILENAME WAIT_COUNT
---------------- ---------------------------------------- ----------
2000-08-02 21 /DSN/ORA1.DB.GLD01.DB1 1226
/DSN/ORA1.DB.GLD02.DB1 1122
/DSN/ORA1.DB.GLX01.DB1 1672
/DSN/ORA1.DB.GLX02.DB1 1438
 
2000-08-02 22 /DSN/ORA1.DB.GLD01.DB1 1226
/DSN/ORA1.DB.GLD02.DB1 1122
/DSN/ORA1.DB.GLX01.DB1 1672
/DSN/ORA1.DB.GLX02.DB1 1438
 
2000-08-02 23 /DSN/ORA1.DB.GLD01.DB1 1226
/DSN/ORA1.DB.GLD02.DB1 1122
/DSN/ORA1.DB.GLX01.DB1 1672
/DSN/ORA1.DB.GLX02.DB1 1438
 
2000-08-03 00 /DSN/ORA1.DB.GLD01.DB1 1226
/DSN/ORA1.DB.GLD02.DB1 1122
/DSN/ORA1.DB.GLX01.DB1 1672
/DSN/ORA1.DB.GLX02.DB1 1438
 
2000-08-03 01 /DSN/ORA1.DB.GLD01.DB1 1226
/DSN/ORA1.DB.GLD02.DB1 1122
/DSN/ORA1.DB.GLX01.DB1 1672
/DSN/ORA1.DB.GLX02.DB1 1438
 
2000-08-03 06 /DSN/ORA1.DB.GLD01.DB1 1078
 
2000-08-03 07 /DSN/ORA1.DB.GLD01.DB1 1078
 
2000-08-03 08 /DSN/ORA1.DB.GLD01.DB1 1078
 
2000-08-04 02 /DSN/ORA1.DB.GLD01.DB1 1652
/DSN/ORA1.DB.GLD02.DB1 3323
/DSN/ORA1.DB.GLX01.DB1 1998
/DSN/ORA1.DB.GLX02.DB1 1625
 
 
 
 
 
 
***********************************************************
Buffer Bury Waits may signal a high update table with too
few freelists. Find the offending table and add more freelists.
See p. 134 "Oracle SAP Administration" by Don Burleson
***********************************************************
 
 
TO_CHAR(SNAP_ BUFFER_BUSY_WAIT
------------- ----------------
2000-08-02 21 1,111
2000-08-02 22 1,111
2000-08-02 23 1,111
2000-08-03 00 1,111
2000-08-03 01 1,111
2000-08-03 06 355
2000-08-03 07 355
2000-08-03 08 355
2000-08-03 21 1,734
2000-08-03 22 1,734
2000-08-03 23 1,734
2000-08-04 00 1,734
2000-08-04 01 1,734
2000-08-04 02 1,734
2000-08-04 17 100
2000-08-04 18 100
2000-08-04 21 631
2000-08-04 22 631
2000-08-04 23 631
2000-08-05 00 631
2000-08-05 01 631
 
***********************************************************
High redo log space requests indicate a need to increase
the log_buffer parameter
 
***********************************************************
 
 
SNAP_DATE REDO_LOG_SPACE_REQUESTS
------------- -----------------------
2000-08-02 14 4,628
2000-08-02 15 4,633
 
***********************************************************
Table fetch continued row indicates chained rows, or fetches of
long datatypes (long raw, blob)
Investigate increasing db_block_size or reorganizing tables
with chained rows.
See p. 381 "High Performance Oracle8 Tuning" by Don Burleson
See p. 102 "Oracle SAP Administration" by Don Burleson
***********************************************************
 
 
TO_CHAR(SNAP_ TABLE_FETCH_CONTINUED_ROW
------------- -------------------------
2000-08-03 15 121,823
2000-08-04 14 141,600
2000-08-04 15 187,639
2000-08-04 16 198,852
2000-08-04 17 200,268
2000-08-04 18 200,555
Data Buffer hit ratio calculation script
set pages 9999;
column logical_reads format 999,999,999
column phys_reads format 999,999,999
column phys_writes format 999,999,999
column "BUFFER HIT RATIO" format 999
select
to_char(snap_time,'yyyy-mm-dd HH24'),
-- a.value + b.value "logical_reads",
-- c.value "phys_reads",
-- d.value "phys_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) 
"BUFFER HIT RATIO"
from 
perfstat.stats$sysstat a, 
perfstat.stats$sysstat b, 
perfstat.stats$sysstat c, 
perfstat.stats$sysstat d,
perfstat.stats$snapshot sn
where
a.snap_id = sn.snap_id
and
b.snap_id = sn.snap_id
and
c.snap_id = sn.snap_id
and
d.snap_id = sn.snap_id
and
a.statistic# = 39
and
b.statistic# = 38
and
c.statistic# = 40
and
d.statistic# = 41
--group by
--to_char(snap_time,'yyyy-mm-dd HH24')
;

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 STATSPACK 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