Script AWR: Capacity Planning

m04m Oracle, Tips 26 de março de 2012 1 minuto

Agora que notei que não está mais disponível a nota do Metalink:

Doc ID: 422414.1 – Manual querying AWR for trend analysis and capacity planning

Este script permite analisar o comportamento da BD em vários níveis (I/O, CPU, Load e etc) e assim antecipar-se a eventuais bottlenecks mediante ao cresimento da carga. E também é interessante para gerar uns gráficos 😛

Segue o script abaixo:

alter session set nls_date_format=’dd-mm-yyyy hh24:mi’;

select 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,
sum(case metric_name when ‘Network Traffic Volume Per Sec’ then average end) Network_bytes_per_sec,
snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;

Este script também está disponível no blog do Alejandro Vargas.

Com a tag
doc id
metalink

Publicado por m04m

“Somos poeiras de estrelas” - Carl Sagan Ver todos os posts de m04m

Publicado 26 de março de 2012

5 comentários em “Script AWR: Capacity Planning”

José Miguel Maia disse:

26 de março de 2012 às 15:36

Bastante útil para despite e análise de problemas.
Very Nice.

Responder
Rodrigo Garcia disse:

24 de junho de 2012 às 17:43

Olá aproveito para deixar um script que se baseia na informação AWR e que nos dá indicadores de eficiência da base ao longo do tempo:

WITH datas as
(
select
to_date(‘02032011 00:09′,’ddmmyyyy hh24:mi’) inicio,
to_date(‘02032011 12:00′,’ddmmyyyy hh24:mi’) fim
from dual
)
select
S1.INSTANCE_NUMBER,
trunc(SN.begin_interval_time,’hh24′) timest,
round(100*(1-((S1.value-S2.value)/(S3.value-S4.value))),2) “Data Buffer Hit Ratio”,
round(100*(1-(
(select sum(wait_count) from dba_hist_waitstat where snap_id=S1.snap_id and dbid= S1.dbid and instance_number=S1.instance_number)-
(select sum(wait_count) from dba_hist_waitstat where snap_id=S2.snap_id and dbid= S2.dbid and instance_number=S2.instance_number)
)/(S3.value-S4.value)),2) “Buffer no wait”,
round(100*
case when
(
((select sum(pinhits) from dba_hist_librarycache where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(pinhits) from dba_hist_librarycache where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
/
((select sum(pins) from dba_hist_librarycache where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(pins) from dba_hist_librarycache where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
) 100 then 100 else
round(100*
(1-
(
((select sum(misses) from dba_hist_latch where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(misses) from dba_hist_latch where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
/
((select sum(gets) from dba_hist_latch where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(gets) from dba_hist_latch where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
)),2) end
“Latch Hit Ratio”,
round(100*(1-
(select bytes from dba_hist_sgastat where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number and pool in (‘shared pool’, ‘all pools’)
and name = ‘free memory’)
/
(select sum(bytes) from dba_hist_sgastat where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number and pool in (‘shared pool’, ‘all pools’)))
,2) “Memory Usage Percent”
from datas,
DBA_HIST_SYSSTAT S1,DBA_HIST_SYSSTAT S2, DBA_HIST_SYSSTAT S3,DBA_HIST_SYSSTAT S4,
DBA_HIST_SYSSTAT S5,DBA_HIST_SYSSTAT S6,
DBA_HIST_SYSSTAT S7,DBA_HIST_SYSSTAT S8,
DBA_HIST_SYSSTAT S9,DBA_HIST_SYSSTAT S10,
DBA_HIST_SYSSTAT S11,DBA_HIST_SYSSTAT S12,
DBA_HIST_SYSSTAT S13,DBA_HIST_SYSSTAT S14,
DBA_HIST_SYSSTAT S15,DBA_HIST_SYSSTAT S16,
dba_hist_snapshot SN
where
S1.dbid=SN.dbid and
S1.instance_number=SN.instance_number and
S1.snap_id=SN.snap_id and
S1.dbid=SN.dbid and
S2.snap_id = (select max(snap_id) from dba_hist_snapshot where snap_id = datas.inicio
and SN.begin_interval_time <= datas.fim
and S1.stat_name ='physical reads'
and S2.stat_name = S1.stat_name
and S3.snap_id=S1.snap_id
and S4.snap_id=S2.snap_id
and S3.stat_name ='session logical reads'
and S3.instance_number=S1.instance_number
and S3.instance_number=S4.instance_number
and S4.stat_name = S1.stat_name
and S5.snap_id=S1.snap_id
and S6.snap_id=S2.snap_id
and S5.stat_name ='redo entries'
and S6.stat_name = S5.stat_name
and S5.instance_number=S1.instance_number
and S6.instance_number=S5.instance_number
and S7.snap_id=S1.snap_id
and S8.snap_id=S2.snap_id
and S7.stat_name ='redo log space requests'
and S8.stat_name = S7.stat_name
and S7.instance_number=S1.instance_number
and S7.instance_number=S8.instance_number
and S9.snap_id=S1.snap_id
and S10.snap_id=S2.snap_id
and S9.stat_name ='sorts (memory)'
and S10.stat_name = S9.stat_name
and S9.instance_number=S1.instance_number
and S10.instance_number=S9.instance_number
and S11.snap_id=S1.snap_id
and S12.snap_id=S2.snap_id
and S11.stat_name ='sorts (disk)'
and S12.stat_name = S11.stat_name
and S11.instance_number=S1.instance_number
and S11.instance_number=S12.instance_number
and S13.snap_id=S1.snap_id
and S14.snap_id=S2.snap_id
and S13.stat_name ='parse count (hard)'
and S14.stat_name = S13.stat_name
and S13.instance_number=S1.instance_number
and S14.instance_number=S13.instance_number
and S15.snap_id=S1.snap_id
and S16.snap_id=S2.snap_id
and S15.stat_name ='parse count (total)'
and S16.stat_name = S15.stat_name
and S15.instance_number=S1.instance_number
and S15.instance_number=S16.instance_number

Responder
zumbanacaneca disse:

24 de junho de 2012 às 17:45

Deixo também um scrit baseado em AWR que nos dá varios indicadores de eficiência da base:

WITH datas as
(
select
to_date(‘02032011 00:09′,’ddmmyyyy hh24:mi’) inicio,
to_date(‘02032011 12:00′,’ddmmyyyy hh24:mi’) fim
from dual
)
select
S1.INSTANCE_NUMBER,
trunc(SN.begin_interval_time,’hh24′) timest,
round(100*(1-((S1.value-S2.value)/(S3.value-S4.value))),2) “Data Buffer Hit Ratio”,
round(100*(1-(
(select sum(wait_count) from dba_hist_waitstat where snap_id=S1.snap_id and dbid= S1.dbid and instance_number=S1.instance_number)-
(select sum(wait_count) from dba_hist_waitstat where snap_id=S2.snap_id and dbid= S2.dbid and instance_number=S2.instance_number)
)/(S3.value-S4.value)),2) “Buffer no wait”,
round(100*
case when
(
((select sum(pinhits) from dba_hist_librarycache where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(pinhits) from dba_hist_librarycache where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
/
((select sum(pins) from dba_hist_librarycache where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(pins) from dba_hist_librarycache where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
) 100 then 100 else
round(100*
(1-
(
((select sum(misses) from dba_hist_latch where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(misses) from dba_hist_latch where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
/
((select sum(gets) from dba_hist_latch where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number)-
(select sum(gets) from dba_hist_latch where snap_id = S2.snap_id and dbid = S2.dbid and instance_number = S2.instance_number))
)),2) end
“Latch Hit Ratio”,
round(100*(1-
(select bytes from dba_hist_sgastat where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number and pool in (‘shared pool’, ‘all pools’)
and name = ‘free memory’)
/
(select sum(bytes) from dba_hist_sgastat where snap_id = S1.snap_id and dbid = S1.dbid and instance_number = S1.instance_number and pool in (‘shared pool’, ‘all pools’)))
,2) “Memory Usage Percent”
from datas,
DBA_HIST_SYSSTAT S1,DBA_HIST_SYSSTAT S2, DBA_HIST_SYSSTAT S3,DBA_HIST_SYSSTAT S4,
DBA_HIST_SYSSTAT S5,DBA_HIST_SYSSTAT S6,
DBA_HIST_SYSSTAT S7,DBA_HIST_SYSSTAT S8,
DBA_HIST_SYSSTAT S9,DBA_HIST_SYSSTAT S10,
DBA_HIST_SYSSTAT S11,DBA_HIST_SYSSTAT S12,
DBA_HIST_SYSSTAT S13,DBA_HIST_SYSSTAT S14,
DBA_HIST_SYSSTAT S15,DBA_HIST_SYSSTAT S16,
dba_hist_snapshot SN
where
S1.dbid=SN.dbid and
S1.instance_number=SN.instance_number and
S1.snap_id=SN.snap_id and
S1.dbid=SN.dbid and
S2.snap_id = (select max(snap_id) from dba_hist_snapshot where snap_id = datas.inicio
and SN.begin_interval_time <= datas.fim
and S1.stat_name ='physical reads'
and S2.stat_name = S1.stat_name
and S3.snap_id=S1.snap_id
and S4.snap_id=S2.snap_id
and S3.stat_name ='session logical reads'
and S3.instance_number=S1.instance_number
and S3.instance_number=S4.instance_number
and S4.stat_name = S1.stat_name
and S5.snap_id=S1.snap_id
and S6.snap_id=S2.snap_id
and S5.stat_name ='redo entries'
and S6.stat_name = S5.stat_name
and S5.instance_number=S1.instance_number
and S6.instance_number=S5.instance_number
and S7.snap_id=S1.snap_id
and S8.snap_id=S2.snap_id
and S7.stat_name ='redo log space requests'
and S8.stat_name = S7.stat_name
and S7.instance_number=S1.instance_number
and S7.instance_number=S8.instance_number
and S9.snap_id=S1.snap_id
and S10.snap_id=S2.snap_id
and S9.stat_name ='sorts (memory)'
and S10.stat_name = S9.stat_name
and S9.instance_number=S1.instance_number
and S10.instance_number=S9.instance_number
and S11.snap_id=S1.snap_id
and S12.snap_id=S2.snap_id
and S11.stat_name ='sorts (disk)'
and S12.stat_name = S11.stat_name
and S11.instance_number=S1.instance_number
and S11.instance_number=S12.instance_number
and S13.snap_id=S1.snap_id
and S14.snap_id=S2.snap_id
and S13.stat_name ='parse count (hard)'
and S14.stat_name = S13.stat_name
and S13.instance_number=S1.instance_number
and S14.instance_number=S13.instance_number
and S15.snap_id=S1.snap_id
and S16.snap_id=S2.snap_id
and S15.stat_name ='parse count (total)'
and S16.stat_name = S15.stat_name
and S15.instance_number=S1.instance_number
and S15.instance_number=S16.instance_number

Responder
1. m04m disse:
  
  25 de junho de 2012 às 8:33
  
  Muito Bom…! Obg 😉
  
  Responder
Fabricio disse:

30 de agosto de 2012 às 3:52

grande Márcio, como sempre trazendo informações e scripts realmente úteis e interessantes. Parabéns meu camarada !!!

Responder