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 <p>本文来源gao!%daima.com搞$代*!码9网(</p> 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;
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：如何利用ash监控会话
转载请注明原文链接：如何利用ash监控会话
