Script AWR: Capacity Planning

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.

5 comentários em “Script AWR: Capacity Planning

  1. 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

  2. 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

Deixe um comentário

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 )

Foto do Google+

Você está comentando utilizando sua conta Google+. 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 )

w

Conectando a %s