欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

如何利用ash监控会话

程序员文章站 2022-06-06 14:16:17
...

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
----------
       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: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