如何利用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;