首页 > 代码库 > 如何利用ash监控会话

如何利用ash监控会话

ash是非常有效的监控工具之一,1秒抓一次
select max(sample_time)over(),min(sample_time)over() from dba_hist_active_sess_history;  --8天   
select max(sample_time)over(),min(sample_time)over() from v$active_session_history;  --当天

首先先了解几个视图:
V$ACTIVE_SESSION_HISTORY: 是ASH的核心,用以记录活动SESSION的历史等待信息,每秒采样一次,这部分内容记录在内存中,期望值是记录一个小时的内容。
WRH#_ACTIVE_SESSION_HISTORY : 是V$ACTIVE_SESSION_HISTORY在AWR的存储地。 
V$ACTIVE_SESSION_HISTORY: 中的信息会被定期(每小时一次)的刷新到负载库中,并缺省保留一个星期用于分析。
DBA_HIST_ACTIVE_SESS_HISTORY: 视图是WRH#_ACTIVE_SESSION_HISTORY视图和其他几个视图的联合展现,通常通过这个视图进行历史数据的访问。

------------------------------------
--V$ACTIVE_SESSION_HISTORY的监控:--
------------------------------------

------------session:1-----------------


SQL> @big

       SID
----------
       131

Elapsed: 00:00:00.00
drop table big
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.03
Elapsed: 00:00:00.31
Elapsed: 00:00:00.34
Elapsed: 00:00:00.29
Elapsed: 00:00:00.73
Elapsed: 00:00:01.75
Elapsed: 00:00:10.59
Elapsed: 00:00:24.62
Elapsed: 00:00:00.01

     BIG_M
----------
       522
Elapsed: 00:00:00.36

  COUNT(*)
----------
   4650368
Elapsed: 00:00:26.70


------------session:2-----------------

SQL> @getash_sid
Enter value for sid: 131

SESSION_ID NAME                                                             P_NAME          P_VALUE      SQL_ID    WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#
---------- ---------------------------------------------------------------- --------------- --------------- ------------- ---------- ------------ ------------- ---------
       131 db file sequential read                                          file#           1            d2wbn28rdk8z4     0          547             1           3604
                                                                            block#          53206
                                                                            blocks          1

       131 db file sequential read                                          file#           1            d2wbn28rdk8z4     0           -1             0              0
                                                                            block#          3009
                                                                            blocks          1

       131 db file scattered read                                           file#           4            03b71c07nsc1a     0          134             1           1064
                                                                            block#          4845
                                                                            blocks          8

       131 log buffer space                                                                 0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch completion                                                       0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           6046
                                                                                            0
                                                                                            0

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4           4995
                                                                            block#          4995
                                                                            blocks          5

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           7170
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4           7170
                                                                                            0
                                                                                            0

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4           8578
                                                                            block#          8578
                                                                            blocks          126

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          12802
                                                                            block#          12802
                                                                            blocks          126

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4          12930
                                                                                            0
                                                                                            0

       131 db file sequential read                                          file#           1            aq32z6wjx1s4h     0        65921           201           3585
                                                                            block#          2854
                                                                            blocks          1

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          14084
                                                                            block#          14084
                                                                            blocks          124

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          18436
                                                                            block#          18436
                                                                            blocks          128

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          19972
                                                                            block#          19972
                                                                            blocks          128

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          21252
                                                                            block#          21252
                                                                            blocks          124

       131 db file parallel read                                            files           1            aq32z6wjx1s4h     0        76851             4          23424
                                                                            blocks          29
                                                                            requests        29

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4          24320
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4          24320
                                                                                            0
                                                                                            0

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          25856
                                                                            block#          25856
                                                                            blocks          128

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          27652
                                                                            block#          27652
                                                                            blocks          124

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          29312
                                                                            block#          29312
                                                                            blocks          32

       131 Disk file operations I/O                                         FileOperation   2            aq32z6wjx1s4h     0        76851             4          29952
                                                                            fileno          0
                                                                            filetype        2

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          30724
                                                                            block#          30724
                                                                            blocks          124

       131 db file scattered read                                           file#           4            aq32z6wjx1s4h     0        76851             4          34530
                                                                            block#          34530
                                                                            blocks          14

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4          35716
                                                                                            0
                                                                                            0

       131 log file switch (checkpoint incomplete)                                          0            aq32z6wjx1s4h     0        76851             4          35716
                                                                                            0
                                                                                            0

       131 Disk file operations I/O                                         FileOperation   5            aq32z6wjx1s4h     0        76851             4          37632
                                                                            fileno          0
                                                                            filetype        2

       131 db file sequential read                                          file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          4999
                                                                            blocks          1

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          10344
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          17409
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          22083
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          28549
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          34733
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          39217
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          45114
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          48836
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0            9             1          86832
                                                                            block#          52391
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4           5196
                                                                            block#          5196
                                                                            blocks          6

       131 db file sequential read                                          file#           4            fqcxb1n33642x     0        76851             4           8261
                                                                            block#          8261
                                                                            blocks          1

       131 db file sequential read                                          file#           4            fqcxb1n33642x     0        76851             4          11318
                                                                            block#          11318
                                                                            blocks          1

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          14489
                                                                            block#          14489
                                                                            blocks          56

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          17935
                                                                            block#          17935
                                                                            blocks          50

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          21195
                                                                            block#          21195
                                                                            blocks          20

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          25170
                                                                            block#          25170
                                                                            blocks          2

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          28453
                                                                            block#          28453
                                                                            blocks          34

       131 db file sequential read                                          file#           4            fqcxb1n33642x     0        76851             4          33067
                                                                            block#          33067
                                                                            blocks          1

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          36991
                                                                            block#          36991
                                                                            blocks          13

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          41616
                                                                            block#          41616
                                                                            blocks          21

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          44055
                                                                            block#          44055
                                                                            blocks          8

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4           2723
                                                                            block#          2723
                                                                            blocks          44

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          50056
                                                                            block#          50056
                                                                            blocks          9

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          53658
                                                                            block#          53658
                                                                            blocks          102

       131 db file scattered read                                           file#           4            fqcxb1n33642x     0        76851             4          56580
                                                                            block#          56580
                                                                            blocks          128

       131 db file sequential read                                          file#           4            fqcxb1n33642x     0        76851             4          60256
                                                                            block#          60256
                                                                            blocks          1


64 rows selected.

Elapsed: 00:00:00.40
SQL> @getsql_sqlid
Enter 1 for curr sql, 2 for hist sql,default 1:

Enter value for sqlid: fqcxb1n33642x

SQL_FULLTEXT
---------------------------------------------------------------------------------------------
select count(*) from big
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
SQL> @getobj_id
Enter value for dblink:
Enter value for obj_id: 76851

OWNER                OBJECT_NAME                    OBJECT_TYPE         CREATED             STATUS
-------------------- ------------------------------ ------------------- ------------------- -------
SCOTT                BIG                            TABLE               2014-11-20 15:56:23 VALID

1 row selected.

Elapsed: 00:00:00.01
SQL> @getobj_fb
Enter value for file_id: 4
Enter value for block_id: 60256

OWNER           SEGMENT_NAME         SEGMENT_TY
--------------- -------------------- ----------
SCOTT           BIG                  TABLE

1 row selected.

Elapsed: 00:00:00.37




--------------------------@脚本--------------------


--@big 

@sid
set feedback off
drop table big;
create table big as select * from dba_objects;
insert into big select * from big;
/
/
/
/
/
commit;
select SUM(bytes) / 1024 / 1024  big_M  from dba_segments where segment_name = 'BIG';
select count(*) from big;
set feedback on


--@getash_sid
col p_name for a15
col p_value for a15
select SESSION_ID,
       NAME,
       P1TEXT||chr(10)||P2TEXT||chr(10)||P3TEXT p_name,
       p1||chr(10)||p2||chr(10)||p3 p_value,
       sql_id,
       WAIT_TIME,
       CURRENT_OBJ#,
       CURRENT_FILE#,
       CURRENT_BLOCK#
  from v$active_session_history ash, v$event_name enm
 where ash.event# = enm.event#
   and SESSION_ID = &sid
 order by sample_time; 
 
 

----------------------------------------
--DBA_HIST_ACTIVE_SESS_HISTORY的监控:--
----------------------------------------

--查当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

--根据时间找到snap_id(8天内,1小时前),因为基表非常大,利用snap_id的索引才能快速查询
select distinct snap_id from dba_hist_snapshot b where to_date('2014-11-20 14:08:12','yyyy-mm-dd hh24:mi:ss') between b.begin_interval_time and b.end_interval_time;

--top instance
select /*+parallel(a,8)*/instance_number,count(*) from dba_hist_active_sess_history a where a.snap_id=67421 group by instance_number;

--top event
select /*+parallel(a,8)*/event,count(*) from dba_hist_active_sess_history a 
where a.snap_id=67421
and instance_number=2
and sample_time>=to_timestamp('2014-11-20 14:07:12','yyyy-mm-dd hh24:mi:ss')
and sample_time<=to_timestamp('2014-11-20 14:08:12','yyyy-mm-dd hh24:mi:ss')
group by event
order by count(*) desc;

--top user
select /*+parallel(a,8)*/user_id,(select username from dba_users b where b.user_id=a.user_id) username,count(*) from dba_hist_active_sess_history a 
where a.snap_id=67421
and instance_number=2
and sample_time>=to_timestamp('2014-11-20 14:07:12','yyyy-mm-dd hh24:mi:ss')
and sample_time<=to_timestamp('2014-11-20 14:08:12','yyyy-mm-dd hh24:mi:ss')
group by user_id
order by count(*) desc;

--top sql
select /*+parallel(a,8)*/sql_id,count(*) from dba_hist_active_sess_history a 
where a.snap_id=67421
and instance_number=2
and sample_time>=to_timestamp('2014-11-20 14:07:12','yyyy-mm-dd hh24:mi:ss')
and sample_time<=to_timestamp('2014-11-20 14:08:12','yyyy-mm-dd hh24:mi:ss')
group by sql_id
order by count(*) desc;

--   select SQL_TEXT from dba_hist_sqltext where sql_id='49p4hfj6azw19';


--top program
select /*+parallel(a,8)*/program,count(*) from dba_hist_active_sess_history a 
where a.snap_id=67421
and instance_number=2
and sample_time>=to_timestamp('2014-11-20 14:07:12','yyyy-mm-dd hh24:mi:ss')
and sample_time<=to_timestamp('2014-11-20 14:08:12','yyyy-mm-dd hh24:mi:ss')
group by program
order by count(*) desc;



如何利用ash监控会话