среда, 4 февраля 2015 г.

dba_hist_system_event

Несколько последних снапшотов:
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
where end_interval_time between sysdate - interval '5' hour and sysdate
order by end_interval_time desc


Добавим  DBTIME:

select * from (
select begin_snap,
end_snap,
timestamp begin_timestamp,
inst,
to_char(a/1000000/60, '99990.9999') dbtime_mins
from
(
select
 e.snap_id end_snap,
 s.end_interval_time end_time,
 lag(e.snap_id) over (order by e.instance_number,e.snap_id) begin_snap,
 lag(s.end_interval_time) over (order by e.instance_number,e.snap_id) timestamp,
 s.instance_number inst,
 e.value,
 nvl(value-lag(value) over (order by e.instance_number,e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
 and e.instance_number = s.instance_number
 and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
 and stat_name = 'DB time'
)
where  end_time between sysdate - interval '10' hour and sysdate
-- order by begin_snap desc
)


Статистика по событию ожидания:

select event, round(sum(tm_delta_time)/1000000/60,1) delta_time_mint
,round(sum(tm_delta_cpu_time)/1000000/60,1) delta_cpu_mint
,round(sum(tm_delta_db_time)/1000000/60,1) delta_db_mint
from dba_hist_active_sess_history
where snap_id between 2716 and 2720 and
instance_number = 1 and event = 'db file sequential read'
group by event;


select * from (
    select
         WAIT_CLASS ,
         EVENT,
         count(sample_time) as EST_SECS_IN_WAIT
    from v$active_session_history
    where sample_time between sysdate - interval '10' hour and sysdate
    group by WAIT_CLASS,EVENT
    order by count(sample_time) desc
    )
where rownum < 11;


Статистика по sql_id из awr:


select sql_id,
       plan_hash_value,
       sum(execs) execs,
    -- sum(etime) etime,
       sum(etime)/sum(execs) avg_etime,
       sum(cpu_time)/sum(execs) avg_cpu_time,
       sum(lio)/sum(execs) avg_lio,
       sum(pio)/sum(execs) avg_pio
from (
      select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
      nvl(executions_delta,0) execs,
      elapsed_time_delta/1000000 etime,
      (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
       buffer_gets_delta lio,
       disk_reads_delta pio,
       cpu_time_delta/1000000 cpu_time,
       (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
       (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
-- and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5;


История плана запроса по awr:

select ss.snap_id,
       ss.instance_number node,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       nvl(executions_delta,0) execs,
      (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
      (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from  DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
      and ss.snap_id = S.snap_id
      and ss.instance_number = S.instance_number
      and executions_delta > 0
order by 1, 2, 3;







Статистики по SQL:

SELECT
    ss.snap_id, ss.instance_number node, ss.begin_interval_time, s.sql_id, s.plan_hash_value,
NVL(s.executions_delta,0) execs,
    (s.elapsed_time_delta/DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
    (s.buffer_gets_delta/DECODE(NVL(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio
FROM
    dba_hist_sqlstat s, dba_hist_snapshot ss
WHERE
    ss.snap_id = s.snap_id AND
    ss.instance_number = s.instance_number AND
    s.executions_delta > 0 AND
    s.sql_id = '271w1sktujfqk'
ORDER BY 1, 2, 3;


TOP по ASH:

select * from (
    select
         SQL_ID ,
         sum(decode(session_state,'ON CPU',1,0)) as CPU,
         sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
         sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
         sum(decode(session_state,'ON CPU',1,1)) as TOTAL
    from v$active_session_history
    where SQL_ID is not NULL
    group by sql_id
    order by sum(decode(session_state,'ON CPU',1,1))   desc
    )where rownum < 11;


Активность сессий за последний час:

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id
order by trunc(sample_time,'MI') desc;



select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
    (select min(sample_time) sample_time
    from  v$active_session_history ash
    ) start_time,
    (select max(sample_time) sample_time
    from  v$active_session_history
    ) end_time,
    v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;


select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
    (select min(sample_time) sample_time
    from  v$active_session_history ash
    where sample_time between sysdate-1/24 and sysdate) start_time,
    (select max(sample_time) sample_time
    from  v$active_session_history
    where sample_time between sysdate-1/24 and sysdate) end_time,
    v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;


Кумулятивная статистика (с момента старта экземпляра):

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
             
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
             
                he.event_name,
                he.total_waits,
                he.time_waited_micro
FROM 
                dba_hist_system_event he,
                dba_hist_snapshot sn
WHERE           sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                he.snap_id=sn.snap_id;



Средняя статистика за период (snap duration):

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
           
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
           
                bse.event_name,
                (ase.total_waits - bse.total_waits)  total_waits,
                to_char((ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits),'99990.9999') avg_wait
           
FROM
                dba_hist_system_event bse,
                dba_hist_system_event ase,
                dba_hist_snapshot sn
         
WHERE
                sn.end_interval_time between sysdate - interval '3' hour and sysdate and              
                bse.event_name = 'db file sequential read' and
                ase.event_name = bse.event_name and
                ase.snap_id = bse.snap_id +1 and
                ase.instance_number = 1 and
                bse.instance_number = ase.instance_number and
                ase.snap_id = sn.snap_id and
                ase.instance_number = sn.instance_number and
                nvl(ase.total_waits - bse.total_waits,1) > 0
ORDER BY    ase.snap_id;



Или так (с подзапросом):

SELECT
       snap_id,
       snap_time,
       snap_duration,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
FROM
       (

         SELECT
                sn.dbid,
                sn.instance_number inst_id,
                sn.snap_id snap_id,
                sn.begin_interval_time begin_snap,
                sn.end_interval_time end_snap,            
           
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
            
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
            
                bse.event_name event_name,
                bse.wait_class,
                (ase.total_waits_fg - bse.total_waits_fg)  total_waits,
                (round(ase.time_waited_micro_fg/1000000, 2) - round(bse.time_waited_micro_fg/1000000, 2))  time_waited
         FROM
                dba_hist_system_event bse,
                dba_hist_system_event ase,
                dba_hist_snapshot sn
          
         WHERE   sn.end_interval_time between sysdate - interval '1' hour and sysdate and
                 ase.snap_id = sn.snap_id and
                 ase.dbid=sn.dbid and
                 ase.snap_id = bse.snap_id +1 and
                 bse.instance_number = ase.instance_number and
                 ase.instance_number = 1 and
                 ase.event_name = bse.event_name
         )

WHERE
                 nvl(total_waits,1) > 0
       --          event_name = 'db file sequential read' and
       --          snap_id between 748 and 849
             
ORDER BY   inst_id,  snap_id;




Связываем с TIME MODEL:

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                (atm.value - btm.value)  / 1000000 db_time,
            
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
            
                -- average active session = db time / snap duration
                (((atm.value - btm.value) / 1000000) /
                (extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60))  aas,
            
                bse.event_name,
                (ase.total_waits - bse.total_waits) total_waits,
                (ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
            
FROM
               dba_hist_system_event bse,
               dba_hist_system_event ase,

               --- add time model
               dba_hist_sys_time_model btm,
               dba_hist_sys_time_model atm,
           
               dba_hist_snapshot sn
          
WHERE
               sn.end_interval_time between sysdate - interval '3' hour and sysdate and
               bse.event_name = 'db file sequential read' and
               ase.event_name = bse.event_name and
               ase.snap_id = bse.snap_id + 1 and
               ase.instance_number = 1 and
               bse.instance_number = ase.instance_number and
               ase.snap_id = sn.snap_id and
               ase.instance_number = sn.instance_number and
               nvl(ase.total_waits - bse.total_waits,1) > 0 and
           
               --- add time model
               atm.snap_id = btm.snap_id + 1  and
               atm.dbid = btm.dbid  and
               atm.instance_number = btm.instance_number  and
               atm.stat_id = btm.stat_id  and
               atm.snap_id = sn.snap_id  and
               btm.stat_name = 'DB time'

ORDER BY     ase.snap_id;




Но, существуют и аналитические функции:


SELECT    
                     snap_id,
                     end_interval_time,
                     lead(snap_id, 1) over (order by snap_id desc) previous_snap_id,
                     lag(snap_id, 1) over (order by snap_id desc) next_snap_id
FROM       
                     dba_hist_snapshot
WHERE     
                     end_interval_time > sysdate-1
ORDER BY             end_interval_time;


Используем partition by, если хотим разделить группы на подгруппы:

SELECT    
                     snap_id,
                     end_interval_time,
                     lead(snap_id, 1) over (partition by startup_time order by snap_id desc)  previous_snap_id,
                     lag(snap_id, 1) over (partition by startup_time order by snap_id desc)  next_snap_id,
                     startup_time
FROM       
                     dba_hist_snapshot
WHERE     
                     end_interval_time > sysdate-1
ORDER BY             end_interval_time;


Несколько примеров использования аналитических функций:

select   snaptime  "BeginTime",
            event_name  "Event",
            wtdelta  "Waits",
            todelta   "Timeouts",
            twdelta  "SecsWaited"
from (
 select snap_id,snaptime,event_name,therank,
  (waits-lag(waits,1,0)
        over (partition by event_name order by snap_id)) wtdelta,
  (timeouts-lag(timeouts,1,0)
        over (partition by event_name order by snap_id)) todelta,
  (time_waited-lag(time_waited,1,0)
        over (partition by event_name order by snap_id)) twdelta
 from (
   select s.snap_id,
      to_char(s.begin_interval_time,'DD-MON-RR HH24:MI') snaptime,
          event_name, sum(e.total_waits) waits,
          sum(e.total_timeouts) timeouts,
      sum(e.time_waited_micro)/1000000 time_waited,
          (rank() over (order by s.snap_id)) therank
   from dba_hist_system_event e,
        dba_hist_snapshot  s
   where s.snap_id = e.snap_id
   and s.end_interval_time between sysdate - interval '3' hour and sysdate
   and s.dbid = e.dbid
   and s.instance_number=e.instance_number
   and e.event_name like 'logl%'
   group by s.snap_id,
    to_char(s.begin_interval_time,'DD-MON-RR HH24:MI'),event_name
)
order by snap_id, twdelta desc)
where therank > 1;


Пример использования функции LAG() :
SELECT
       dbid,
       btime,
       round((time_ms_end - time_ms_beg) / nullif(count_end - count_beg,0),1) avg_ms
FROM (
SELECT
       sn.dbid,
       to_char(sn.begin_interval_time,'dd-mm-yyyy hh24:mi:ss')  btime,
       total_waits count_end,
       time_waited_micro / 1000 time_ms_end,
       LAG(se.time_waited_micro / 1000) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) time_ms_beg,
       LAG(se.total_waits) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) count_beg
FROM
       dba_hist_system_event se,
       dba_hist_snapshot sn
WHERE
       sn.end_interval_time between sysdate - interval '3' hour and sysdate and      
       sn.snap_id=se.snap_id
       and se.event_name in ('log file sync' )
       and  sn.dbid=se.dbid
)
ORDER BY btime;


Связываем с dba_hist_snapshot:

select
       snap_id,
       snap_time,
       snap_duration,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
from
              (select
                          sn.dbid,
                          sn.instance_number inst_id,
                          sn.snap_id,
                          sn.begin_interval_time begin_snap,
                          sn.end_interval_time end_snap,
                          (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                          to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
         
                          extract (second from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
          
                          se.event_name,
                          se.wait_class,
                          se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                          round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                          min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id

             from         dba_hist_system_event se,
                          dba_hist_snapshot sn

             where   sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                          se.instance_number=sn.instance_number
                          and se.snap_id=sn.snap_id
                          and se.dbid=sn.dbid
                 
                  )

 where
             inst_id = 1  and
             snap_id > min_snap_id and
             nvl(total_waits,1) > 0  and
             event_name = 'log file sync'
         
order by inst_id, snap_id;



Связываем с TIME MODEL:

select
       snap_id,
       snap_time,
       snap_duration,
       db_time,
       aas,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
from
              (select
                          sn.dbid,
                          sn.instance_number inst_id,
                          sn.snap_id,
                          sn.begin_interval_time begin_snap,
                          sn.end_interval_time end_snap,
                          (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                          to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                      
                           (tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 db_time,
       
                          extract (second from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
                      
                          -- average active session = db time / snap duration
                           (((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 ) /
                           (extract (second from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60))  aas,
                                          
                                                                        
                          se.event_name,
                          se.wait_class,
                          se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                          round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                          min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id

             from         dba_hist_system_event se,
                          dba_hist_snapshot sn,
                          dba_hist_sys_time_model tm

             where         sn.end_interval_time between sysdate - interval '3' hour and sysdate and 
                           se.instance_number=sn.instance_number and
                           se.snap_id=sn.snap_id and
                           se.dbid=sn.dbid  and
                       
                            --- add time model
                            tm.snap_id = sn.snap_id  and
                            tm.dbid = sn.dbid  and
                            tm.instance_number = sn.instance_number  and
                            tm.stat_name = 'DB time'
               )

 where
             inst_id = 1  and
             snap_id > min_snap_id and
             nvl(total_waits,1) > 0
             and event_name = 'log file sync'
       
order by inst_id, snap_id;


Вывести не более 5-ти топовых ожиданий, на каждый снапшот:

select *
from (
         select
                inst_id,
                snap_id,
                snap_time,
                snap_duration,
                db_time,
                aas,
                event_name "Event",
                total_waits "Waits",
                time_waited "Time(s)",
                round((time_waited/total_waits)*1000) "Avg wait(ms)",
                substr(wait_class, 1, 15) "Wait Class",
                dense_rank() over (partition by inst_id, snap_id order by time_waited desc) - 1 wait_rank
         from
                       (select
                                   sn.dbid,
                                   sn.instance_number inst_id,
                                   sn.snap_id,
                                   sn.begin_interval_time begin_snap,
                                   sn.end_interval_time end_snap,
                                   (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                                   to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                            
                                   to_char(
                                   (tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000, '99990.9999') db_time,
                               
                                   to_char(
                                   extract (second from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60, '99990.9999') snap_duration,
                            
                                   -- average active session = db time / snap duration
                                    to_char(
                                    (((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 ) /
                                    (extract (second from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)), '99990.9999')  aas,
                                                
                                                                              
                                   se.event_name,
                                   se.wait_class,
                                   se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                                   round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                                   min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
                                                       
                      from         dba_hist_system_event se,
                                   dba_hist_snapshot sn,
                                   dba_hist_sys_time_model tm
   
                      where         sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                                    se.instance_number=sn.instance_number and
                                    se.snap_id=sn.snap_id and
                                    se.dbid=sn.dbid  and
                             
                                     --- add time model
                                     tm.snap_id = sn.snap_id  and
                                     tm.dbid = sn.dbid  and
                                     tm.instance_number = sn.instance_number  and
                                     tm.stat_name = 'DB time'
                        )
   
          where
                      inst_id = 1  and
                      snap_id > min_snap_id and
                      nvl(total_waits,1) > 0
    )      
    
where  wait_rank <= 5
order by inst_id, snap_id;


Статистика по сессиям:


RDBMS Version: 10g.
Script shows following session statistic values:

    PGA Memory, in MB;
    CPU, used by session;
    Hard Parse, %;
    Physical read bytes, in MB;
    Physical write bytes, in MB;
    Redo size, in MB;
    Received from client, in MB;
    Sent to client, in MB.


 SELECT Logon_time,
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
            AS "PGA Memory, in MB",
         (SELECT VALUE
            FROM v$sesstat
           WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
            AS "CPU, used by session",
         ROUND ( (SELECT VALUE
                    FROM v$sesstat
                   WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
                / (SELECT DECODE (VALUE, 0, 1, VALUE)
                     FROM v$sesstat
                    WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
                2)
            AS "Hard Parse, %",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
            AS "Physical read bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
            AS "Physical write bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
            AS "Redo size, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
            AS "Received from client, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
            AS "Sent to client, in MB",
         SID,
         SERIAL#,
         v$session.STATUS,
         PROGRAM,
         USER#,
         USERNAME,
         COMMAND,
         OWNERID,
         OSUSER,
         PROCESS,
         MACHINE,
         OBJECT_NAME
    FROM    v$session
         LEFT OUTER JOIN
            DBA_OBJECTS
         ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
   WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
         --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;


Oracle 11g:
SELECT Logon_time,
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
          AS "PGA Memory, in MB",
       (SELECT VALUE
          FROM v$sesstat
         WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
          AS "CPU, used by session",
       ROUND ( (SELECT VALUE
                  FROM v$sesstat
                 WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
              / (SELECT DECODE (VALUE, 0, 1, VALUE)
                   FROM v$sesstat
                  WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
              2)
          AS "Hard Parse, %",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
          AS "Physical read bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
          AS "Physical write bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
          AS "Redo size, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
          AS "Received from client, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
          AS "Sent to client, in MB",
       SID,
       SERIAL#,
       v$session.STATUS,
       PROGRAM,
       USER#,
       USERNAME,
       COMMAND,
       OWNERID,
       OSUSER,
       PROCESS,
       MACHINE,
       OBJECT_NAME
  FROM    v$session
       LEFT OUTER JOIN
          DBA_OBJECTS
       ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
 WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
       --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;







Комментариев нет:

Отправить комментарий