Blogia
tecnolakis

DBA_HIST_ACTIVE_SESS_HISTORY

- Oracle

DBA_HIST_ACTIVE_SESS_HISTORY




11.2.0.1

Name                                        Null?    Type
------------------------------------------- -------- ---------------
SNAP_ID                                     NOT NULL NUMBER
DBID                                        NOT NULL NUMBER
INSTANCE_NUMBER                             NOT NULL NUMBER
SAMPLE_ID                                   NOT NULL NUMBER
SAMPLE_TIME                                 NOT NULL TIMESTAMP(3)
SESSION_ID                                  NOT NULL NUMBER
SESSION_SERIAL#                                      NUMBER
SESSION_TYPE                                         VARCHAR2(10)
FLAGS                                                NUMBER
USER_ID                                              NUMBER
SQL_ID                                               VARCHAR2(13)
IS_SQLID_CURRENT                                     VARCHAR2(1)
SQL_CHILD_NUMBER                                     NUMBER
SQL_OPCODE                                           NUMBER
SQL_OPNAME                                           VARCHAR2(64)
FORCE_MATCHING_SIGNATURE                             NUMBER
TOP_LEVEL_SQL_ID                                     VARCHAR2(13)
TOP_LEVEL_SQL_OPCODE                                 NUMBER
SQL_PLAN_HASH_VALUE                                  NUMBER
SQL_PLAN_LINE_ID                                     NUMBER
SQL_PLAN_OPERATION                                   VARCHAR2(64)
SQL_PLAN_OPTIONS                                     VARCHAR2(64)
SQL_EXEC_ID                                          NUMBER
SQL_EXEC_START                                       DATE
PLSQL_ENTRY_OBJECT_ID                                NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID                            NUMBER
PLSQL_OBJECT_ID                                      NUMBER
PLSQL_SUBPROGRAM_ID                                  NUMBER
QC_INSTANCE_ID                                       NUMBER
QC_SESSION_ID                                        NUMBER
QC_SESSION_SERIAL#                                   NUMBER
EVENT                                                VARCHAR2(64)
EVENT_ID                                             NUMBER
SEQ#                                                 NUMBER
P1TEXT                                               VARCHAR2(64)
P1                                                   NUMBER
P2TEXT                                               VARCHAR2(64)
P2                                                   NUMBER
P3TEXT                                               VARCHAR2(64)
P3                                                   NUMBER
WAIT_CLASS                                           VARCHAR2(64)
WAIT_CLASS_ID                                        NUMBER
WAIT_TIME                                            NUMBER
SESSION_STATE                                        VARCHAR2(7)
TIME_WAITED                                          NUMBER
BLOCKING_SESSION_STATUS                              VARCHAR2(11)
BLOCKING_SESSION                                     NUMBER
BLOCKING_SESSION_SERIAL#                             NUMBER
BLOCKING_INST_ID                                     NUMBER
BLOCKING_HANGCHAIN_INFO                              VARCHAR2(1)
CURRENT_OBJ#                                         NUMBER
CURRENT_FILE#                                        NUMBER
CURRENT_BLOCK#                                       NUMBER
CURRENT_ROW#                                         NUMBER
TOP_LEVEL_CALL#                                      NUMBER
TOP_LEVEL_CALL_NAME                                  VARCHAR2(64)
CONSUMER_GROUP_ID                                    NUMBER
XID                                                  RAW(8)
REMOTE_INSTANCE#                                     NUMBER
TIME_MODEL                                           NUMBER
IN_CONNECTION_MGMT                                   VARCHAR2(1)
IN_PARSE                                             VARCHAR2(1)
IN_HARD_PARSE                                        VARCHAR2(1)
IN_SQL_EXECUTION                                     VARCHAR2(1)
IN_PLSQL_EXECUTION                                   VARCHAR2(1)
IN_PLSQL_RPC                                         VARCHAR2(1)
IN_PLSQL_COMPILATION                                 VARCHAR2(1)
IN_JAVA_EXECUTION                                    VARCHAR2(1)
IN_BIND                                              VARCHAR2(1)
IN_CURSOR_CLOSE                                      VARCHAR2(1)
IN_SEQUENCE_LOAD                                     VARCHAR2(1)
CAPTURE_OVERHEAD                                     VARCHAR2(1)
REPLAY_OVERHEAD                                      VARCHAR2(1)
IS_CAPTURED                                          VARCHAR2(1)
IS_REPLAYED                                          VARCHAR2(1)
SERVICE_HASH                                         NUMBER
PROGRAM                                              VARCHAR2(64)
MODULE                                               VARCHAR2(48)
ACTION                                               VARCHAR2(32)
CLIENT_ID                                            VARCHAR2(64)
MACHINE                                              VARCHAR2(64)
PORT                                                 NUMBER
ECID                                                 VARCHAR2(64)
TM_DELTA_TIME                                        NUMBER
TM_DELTA_CPU_TIME                                    NUMBER
TM_DELTA_DB_TIME                                     NUMBER
DELTA_TIME                                           NUMBER
DELTA_READ_IO_REQUESTS                               NUMBER
DELTA_WRITE_IO_REQUESTS                              NUMBER
DELTA_READ_IO_BYTES                                  NUMBER
DELTA_WRITE_IO_BYTES                                 NUMBER
DELTA_INTERCONNECT_IO_BYTES                          NUMBER
PGA_ALLOCATED                                        NUMBER
TEMP_SPACE_ALLOCATED                                 NUMBER




select time_waited, p1, p2,p3 from dba_hist_active_sess_history where event='log file parallel write'
union all
select time_waited, p1, p2,p3 from v$active_session_history where event='log file parallel write'
/

select distinct event from v$active_session_history
order by event;

i/o sizes 

ASH is heavily heavily leaning toward long events, so for I/O sizes these will be on the high side, but it is useful for showing MBRC and Direct Read sizes. 

col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*)  cnt
from dba_hist_active_sess_history
--from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
and dbid=&DBID
group by event
order by event
/


AAS Graphs

Output looks like


TO_CHAR(        AAS PCT1 FIRST           PCT2 SECOND          GRAPH
-------- ---------- ---- --------------- ---- --------------- -------------------------------------------
15 15:00          7   46 db file sequent   19 CPU             +++++++ooooooooooooo4ooooooooooo------
15 16:00          6   53 db file sequent   31 CPU             +++++++++ooooooooooo4oooooooo---
15 17:00          7   48 db file sequent   41 CPU             +++++++++++++++ooooo4oooooooooooooooo-
15 18:00          5   55 CPU               37 db file sequent ++++++++++++++oooooo4oooooo-
15 19:00          1   64 CPU               21 db file sequent ++o                 4
15 20:00          1   63 CPU               19 read by other s ++++o-              4
15 21:00          2   31 db file sequent   24 CPU             ++ooo----           4
15 22:00          3   35 CPU               24 db file scatter +++++ooooooo---     4
15 23:00          6   29 log file sync     25 db file sequent ++++ooooooooo-------4-------------
16 00:00          7   52 db file sequent   27 CPU             ++++++++++oooooooooo4ooooooooooooooo--
16 01:00          4   57 CPU               36 db file sequent +++++++++++oooooooo 4
16 02:00          6   38 db file sequent   21 CPU             ++++++oooooooooooo--4---------
16 03:00          3   69 db file sequent   20 CPU             +++ooooooooooo      4
16 04:00          0   45 db file sequent   28 CPU             o                   4
16 05:00          1   58 db file sequent   24 CPU             +ooo                4
16 06:00          1   41 db file sequent   39 CPU             +oo                 4
16 07:00          0   57 CPU               21 db file sequent +                   4
16 08:00          0   57 CPU               28 db file scatter +                   4
16 09:00          0   69 db file sequent   24 CPU             +oo                 4
16 10:00          2   79 db file sequent   13 CPU             +ooooooo            4
16 11:00          3   76 db file sequent   12 CPU             +ooooooooooo        4

The "graph" on the right shows the load over time each line is an hour by default. The "+" represent CPU, "o" represent I/O and "-" represent a wait.
The columns "FIRST" and "SECOND" represent the top two things happening on the database. 

avactn - just v$active_session_history dump (don't use dba_hist_active_sess_history , no time , dump all )


Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80

col aveact format 999.99
col graph format a30
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas1 format 9.99
col aas2 format 9.99


select to_char(start_time,'DD HH24:MI:SS'),
       samples,
       --total,
       --waits,
       --cpu,
       round(fpct * (total/&v_secs),2) aas1,
       decode(fpct,null,null,first) first,
       round(spct * (total/&v_secs),2) aas2,
       decode(spct,null,null,second) second,
        substr(substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
        p.value  ||
        substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,total))    waits
  from (
   select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        v$active_session_history ash
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact,
  v$parameter p
where p.name='cpu_count'
order by start_time
/

aveactn - v$active_session_history and dba_hist_active_sess_history (no DBID specified)


Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80 

col aveact format 999.99
col graph format a30
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas1 format 9.99
col aas2 format 9.99


select to_char(start_time,'DD HH24:MI:SS'),
       samples,
       --total,
       --waits,
       --cpu,
       round(fpct * (total/&v_secs),2) aas1,
       decode(fpct,null,null,first) first,
       round(spct * (total/&v_secs),2) aas2,
       decode(spct,null,null,second) second,
        substr(substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
        p.value  ||
        substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct 
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,total))    waits
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        v$active_session_history ash
     where
               sample_time > sysdate - &v_days
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
union all
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
               sample_time > sysdate - &v_days
         and sample_time < ( select min(sample_time) from v$active_session_history)
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact,
  v$parameter p
where p.name='cpu_count'
order by start_time
/



aveactnd - dba_hist_active_sess_history (input DBID)


Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80 

col aveact format 999.99
col graph format a80
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas1 format 9.99
col aas2 format 9.99
col pct1 format 999
col pct2 format 999
col first format  a15
col second format  a15

Def p_value=4


select to_char(start_time,'DD HH24:MI'),
       --samples,
       --total,
       --waits,
       --cpu,
       round((total/&v_secs)) aas,
       --round(fpct * (total/&v_secs),2) aas1,
       fpct*100  pct1,
       decode(fpct,null,null,first) first,
       --round(spct * (total/&v_secs),2) aas2,
       spct*100 pct2,
       decode(spct,null,null,second) second,
    -- substr, ie trunc, the whole graph to make sure it doesn't overflow
        substr(
       -- substr, ie trunc, the graph below the # of CPU cores line
           -- draw the whole graph and trunc at # of cores line
       substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),0,(&p_value * &v_bars)) ||
        &p_value  ||
       -- draw the whole graph, then cut off the amount we drew before the # of cores
           substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),(&p_value * &v_bars),( &v_graph-&v_bars*&p_value) )
        ,0,&v_graph)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct 
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(io) io
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,
                        'db file sequential read',0,
                        'db file scattered read',0,
                        'db file parallel read',0,
                        'direct path read',0,
                        'direct path read temp',0,
                        'direct path write',0,
                        'direct path write temp',0, total)) waits
     , sum(decode(event,'db file sequential read',total,
                                  'db file scattered read',total,
                                  'db file parallel read',total,
                                  'direct path read',total,
                                  'direct path read temp',total,
                                  'direct path write',total,
                                  'direct path write temp',total, 0)) io
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
        --       sample_time > sysdate - &v_days
        -- and sample_time < ( select min(sample_time) from v$active_session_history)
           dbid=&DBID
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact
order by start_time
/


aveactnds - dba_hist_active_sess_history (input DBID and SQL_ID)



Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80 

col aveact format 999.99
col graph format a80
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas format 999.99
col aas1 format 9.99
col aas2 format 9.99
col pct1 format 999
col pct2 format 999
col first format  a15
col second format  a15

Def p_value=4

select to_char(start_time,'DD HH24:MI'),
       --samples,
       --total,
       --waits,
       --cpu,
       (total/&v_secs) aas,
       --round(fpct * (total/&v_secs),2) aas1,
       fpct*100  pct1,
       decode(fpct,null,null,first) first,
       --round(spct * (total/&v_secs),2) aas2,
       spct*100 pct2,
       decode(spct,null,null,second) second,
    -- substr, ie trunc, the whole graph to make sure it doesn't overflow
        substr(
       -- substr, ie trunc, the graph below the # of CPU cores line
           -- draw the whole graph and trunc at # of cores line
       substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),0,(&p_value * &v_bars)) ||
        &p_value  ||
       -- draw the whole graph, then cut off the amount we drew before the # of cores
           substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),(&p_value * &v_bars),( &v_graph-&v_bars*&p_value) )
        ,0,&v_graph)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct 
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(io) io
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,
                        'db file sequential read',0,
                        'db file scattered read',0,
                        'db file parallel read',0,
                        'direct path read',0,
                        'direct path read temp',0,
                        'direct path write',0,
                        'direct path write temp',0, total)) waits
     , sum(decode(event,'db file sequential read',total,
                                  'db file scattered read',total,
                                  'db file parallel read',total,
                                  'direct path read',total,
                                  'direct path read temp',total,
                                  'direct path write',total,
                                  'direct path write temp',total, 0)) io
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
        --       sample_time > sysdate - &v_days
        -- and sample_time < ( select min(sample_time) from v$active_session_history)
           dbid=&DBID
      and  sql_id='&SQL_ID'
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact
order by start_time
/

aveactndp - dba_hist_active_sess_history (input DBID and PROGRAM)



Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80 

col aveact format 999.99
col graph format a80
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas format 999.99
col aas1 format 9.99
col aas2 format 9.99
col pct1 format 999
col pct2 format 999
col first format  a15
col second format  a15

Def p_value=4

select to_char(start_time,'DD HH24:MI'),
       --samples,
       --total,
       --waits,
       --cpu,
       (total/&v_secs) aas,
       --round(fpct * (total/&v_secs),2) aas1,
       fpct*100  pct1,
       decode(fpct,null,null,first) first,
       --round(spct * (total/&v_secs),2) aas2,
       spct*100 pct2,
       decode(spct,null,null,second) second,
    -- substr, ie trunc, the whole graph to make sure it doesn't overflow
        substr(
       -- substr, ie trunc, the graph below the # of CPU cores line
           -- draw the whole graph and trunc at # of cores line
       substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),0,(&p_value * &v_bars)) ||
        &p_value  ||
       -- draw the whole graph, then cut off the amount we drew before the # of cores
           substr(
         rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
             rpad('o',round((io*&v_bars)/&v_secs),'o')  ||
             rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
             rpad(' ',&p_value * &v_bars,' '),(&p_value * &v_bars),( &v_graph-&v_bars*&p_value) )
        ,0,&v_graph)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct 
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(io) io
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,
                        'db file sequential read',0,
                        'db file scattered read',0,
                        'db file parallel read',0,
                        'direct path read',0,
                        'direct path read temp',0,
                        'direct path write',0,
                        'direct path write temp',0, total)) waits
     , sum(decode(event,'db file sequential read',total,
                                  'db file scattered read',total,
                                  'db file parallel read',total,
                                  'direct path read',total,
                                  'direct path read temp',total,
                                  'direct path write',total,
                                  'direct path write temp',total, 0)) io
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
        --       sample_time > sysdate - &v_days
        -- and sample_time < ( select min(sample_time) from v$active_session_history)
           dbid=&DBID
      and  program like '&PROGRAM%'
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact
order by start_time
/


top SQL for DBID

col type for a10
col "CPU" for 999999.9
col "IO" for 999999.9
select * from (
select
     ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from dba_hist_active_sess_history ash,
     audit_actions aud
where SQL_ID is not NULL
   and ash.dbid=&DBID
   and ash.sql_opcode=aud.action
   -- and ash.sample_time > sysdate - &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name 
order by sum(decode(session_state,'ON CPU',1,1))   desc
) where  rownum < 10
/
 output looks like

SQL_ID         PLAN_HASH TYPE                   CPU       WAIT        IO      TOTAL
------------- ---------- ---------------- --------- ---------- --------- ----------
fgzp9yqqjcjvm  707845071 UPDATE                25.0         95    4081.0       4201
8u8y8mc1qxd98  131695425 SELECT                18.0         57    3754.0       3829
cfk8gy594h42s 3743737989 SELECT              2021.0         17      82.0       2120
cnx6ht8bdmf4c          0 PL/SQL EXECUTE       546.0        367     868.0       1781
gyj8wh7vx960y 1736948211 SELECT               197.0         11    1227.0       1435
1wmz1trqkzhzq 1384060092 SELECT               639.0         20     679.0       1338
5vjzz8f5ydqm7 1375932572 SELECT               538.0          0     541.0       1079
8w08jp8urfj6t 3134135242 SELECT               118.0         10     945.0       1073

IO sizes


for multiblock reads, it's good to have an idea of what the I/O sizes are. The following query gives an upper bound. NOTE the averages and even mins can be highly misleading but the max should be a good indicator.

col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*)  cnt
from dba_hist_active_sess_history
--from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
and dbid=&DBID
group by event
order by event
/
QA

EVENT                             MN         AV         MX        CNT
------------------------- ---------- ---------- ---------- ----------
db file scattered read             2         16         16        892
db file sequential read            1          1          1        105
direct path read                   1          1          1          1
direct path write                  1          1          1          2
direct path write temp             4         29         31         17

Force importing a in AWR

best idea is to load AWR into an repositor of the same version higher, but this will force the load in some incompatible situations



insert into  WRH$_ACTIVE_SESSION_HISTORY (
SNAP_ID,
DBID,
INSTANCE_NUMBER,
SAMPLE_ID,
SAMPLE_TIME,
SESSION_ID,
SESSION_SERIAL#,
USER_ID,
SQL_ID,
SQL_CHILD_NUMBER,
SQL_PLAN_HASH_VALUE,
SERVICE_HASH,
SESSION_TYPE,
SQL_OPCODE,
QC_SESSION_ID,
QC_INSTANCE_ID,
CURRENT_OBJ#,
CURRENT_FILE#,
CURRENT_BLOCK#,
SEQ#,
EVENT_ID,
P1,
P2,
P3,
WAIT_TIME,
TIME_WAITED,
PROGRAM,
MODULE,
ACTION,
CLIENT_ID,
FORCE_MATCHING_SIGNATURE,
BLOCKING_SESSION,
BLOCKING_SESSION_SERIAL#,
XID,
CONSUMER_GROUP_ID,
PLSQL_ENTRY_OBJECT_ID,
PLSQL_ENTRY_SUBPROGRAM_ID,
PLSQL_OBJECT_ID,
PLSQL_SUBPROGRAM_ID,
QC_SESSION_SERIAL#,
REMOTE_INSTANCE#,
SQL_PLAN_LINE_ID,
SQL_PLAN_OPERATION#,
SQL_PLAN_OPTIONS#,
SQL_EXEC_ID,
SQL_EXEC_START,
TIME_MODEL,
TOP_LEVEL_SQL_ID,
TOP_LEVEL_SQL_OPCODE,
CURRENT_ROW#,
FLAGS,
BLOCKING_INST_ID,
ECID,
TM_DELTA_TIME,
TM_DELTA_CPU_TIME,
TM_DELTA_DB_TIME,
DELTA_TIME,
DELTA_READ_IO_REQUESTS,
DELTA_WRITE_IO_REQUESTS,
DELTA_READ_IO_BYTES,
DELTA_WRITE_IO_BYTES,
DELTA_INTERCONNECT_IO_BYTES,
PGA_ALLOCATED,
TEMP_SPACE_ALLOCATED,
TOP_LEVEL_CALL#,
MACHINE,
PORT)
select 
SNAP_ID,
DBID,
INSTANCE_NUMBER,
SAMPLE_ID,
SAMPLE_TIME,
SESSION_ID,
SESSION_SERIAL#,
USER_ID,
SQL_ID,
SQL_CHILD_NUMBER,
SQL_PLAN_HASH_VALUE,
SERVICE_HASH,
SESSION_TYPE,
SQL_OPCODE,
QC_SESSION_ID,
QC_INSTANCE_ID,
CURRENT_OBJ#,
CURRENT_FILE#,
CURRENT_BLOCK#,
SEQ#,
EVENT_ID,
P1,
P2,
P3,
WAIT_TIME,
TIME_WAITED,
PROGRAM,
substr(MODULE,1,48),
substr(ACTION,1,32),
CLIENT_ID,
FORCE_MATCHING_SIGNATURE,
BLOCKING_SESSION,
BLOCKING_SESSION_SERIAL#,
XID,
CONSUMER_GROUP_ID,
PLSQL_ENTRY_OBJECT_ID,
PLSQL_ENTRY_SUBPROGRAM_ID,
PLSQL_OBJECT_ID,
PLSQL_SUBPROGRAM_ID,
QC_SESSION_SERIAL#,
REMOTE_INSTANCE#,
SQL_PLAN_LINE_ID,
SQL_PLAN_OPERATION#,
SQL_PLAN_OPTIONS#,
SQL_EXEC_ID,
SQL_EXEC_START,
TIME_MODEL,
TOP_LEVEL_SQL_ID,
TOP_LEVEL_SQL_OPCODE,
CURRENT_ROW#,
FLAGS,
BLOCKING_INST_ID,
ECID,
TM_DELTA_TIME,
TM_DELTA_CPU_TIME,
TM_DELTA_DB_TIME,
DELTA_TIME,
DELTA_READ_IO_REQUESTS,
DELTA_WRITE_IO_REQUESTS,
DELTA_READ_IO_BYTES,
DELTA_WRITE_IO_BYTES,
DELTA_INTERCONNECT_IO_BYTES,
PGA_ALLOCATED,
TEMP_SPACE_ALLOCATED,
TOP_LEVEL_CALL#,
MACHINE,
PORT
from AWR_STAGE.WRH$_ACTIVE_SESSION_HISTORY ;

0 comentarios