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; --当天首先先了解几个视
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---------- 131elapsed: 00:00:00.00drop table big *error at line 1:ora-00942: table or view does not existelapsed: 00:00:00.03elapsed: 00:00:00.31elapsed: 00:00:00.34elapsed: 00:00:00.29elapsed: 00:00:00.73elapsed: 00:00:01.75elapsed: 00:00:10.59elapsed: 00:00:24.62elapsed: 00:00:00.01 big_m---------- 522elapsed: 00:00:00.36 count(*)---------- 4650368elapsed: 00:00:26.70------------session：2-----------------sql> @getash_sidenter value for sid: 131session_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 164 rows selected.elapsed: 00:00:00.40sql> @getsql_sqlidenter 1 for curr sql, 2 for hist sql,default 1:enter value for sqlid: fqcxb1n33642xsql_fulltext---------------------------------------------------------------------------------------------select count(*) from bigelapsed: 00:00:00.00elapsed: 00:00:00.00sql> @getobj_identer value for dblink:enter value for obj_id: 76851owner object_name object_type created status-------------------- ------------------------------ ------------------- ------------------- -------scott big table 2014-11-20 15:56:23 valid1 row selected.elapsed: 00:00:00.01sql> @getobj_fbenter value for file_id: 4enter value for block_id: 60256owner segment_name segment_ty--------------- -------------------- ----------scott big table1 row selected.elapsed: 00:00:00.37--------------------------@脚本----------------------@big @sidset feedback offdrop 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_sidcol p_name for a15col p_value for a15select 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 instanceselect /*+parallel(a,8)*/instance_number,count(*) from dba_hist_active_sess_history a where a.snap_id=67421 group by instance_number;--top eventselect /*+parallel(a,8)*/event,count(*) from dba_hist_active_sess_history a where a.snap_id=67421and instance_number=2and 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:07:12','yyyy-mm-dd hh24:mi:ss')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: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 programorder by count(*) desc;