oracle ASH

Active Session History (ASH)

ASH每秒都對History v$session_wait + v$session + extras 進行取樣,記錄活動會話的events,由程式MMNL
來完成

隱藏引數控制
_ash_enable TRUE
To enable or disable Active Session sampling and flushing 控制是否使用ash

_ash_sampling_interval 1000
Time interval between two successive Active Session samples in millisecs 控制取樣間隔(單位ms)

ASH 取樣資訊存ash buffer中(存在sga,shared pool中),這部分記憶體是可以被複用的
SQL> select * from v$sgastat where name like '%ASH%';

POOL NAME BYTES
------------ ------------------------------ ----------
shared pool ASH buffers 4194304

ASH Buffer Size
- Min 1M and Max 30M
- Max( Min ( No. of CPU * 2 M, 5% of SHARED_POOL_SIZE, 2% of
SGA_TARGET) , 1M)
- Hidden parameter “_ASH_SIZE” Please Don’t change it
- ASH Buffers Data is flushed to AWR when buffers are 66% filled by MMNL process ~~~可以看到ash buffer 66%時候開始重新整理到awr
- Hidden parameter “_ASH_EFLUSH_TRIGGER” Please Don’t change it
- ASH Buffers are filled with 1 Sec Samples from Active Session-state information
- Hidden parameter “_ASH_SAMPLING_INTERVAL” Please Don’t change it
- Hidden parameter “_ASH_SAMPLE_ALL” Please Don’t change it

one out of 10 ASH sampled Record of each Session is pushed to AWR
Hidden parameter “_ASH_DISK_FILTER_RATIO=10” Please Don’t change it ~~~~MMNL將ash buffer資料寫出到disk時候,寫出資料佔取樣資料的10%(一次都寫出有壓力)

#Flushed every hour to disk or when buffer 2/3 full (it protects itself so you can relax)(kyle hailey 說意思就是66%)

ASH BUFFER取值範圍1-30M(這個30M 並不是個上限,實際上我遇到很多情況ASH BUFFER 遠遠超過了 30M)

ash buffer size=Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ],_ash_size ]
而kyle hailey說 Circular Buffer - 1M to 128M (~2% of SGA) 我還是比較相信他的

_ash_size 1048618
To set the size of the in-memory Active Session History buffers

以上引數都是不建議修改的

ASH SIZING
Avg row around 150bytes
3600 secs in an hour
~1/2 Meg per Active Session per hour
That’s generally over an hour of ASH

Session 的幾個狀態
1.idle
Ex : SQL*Net Message from Client
All Idle Events:
select name from v$event_name where
wait_class='Idle‘;
2.CPU
ASH: SESSION_STATE = “ON CPU”
ASH: wait_time > 0

3.wating
ASH: SESSION_STATE=‘WAITING’
ASH: WAIT_TIME=0
WAIT_CLASS
Administrative
Application
Cluster
Commit
Concurrency
Configuration
Network
Other
Scheduler
System I/O
800+ WAIT

4.IO
ASH:
       SESSION_STATE=‘WAITING’
            and
       WAIT_CLASS=‘User I/O’

v$active_session_history 是用VIEW來檢視ash資訊,具體內容可以分成幾大部分幫助我們理解
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3)
# when 什麼時候(取樣時間)

SESSION_ID                                NUMBER
SESSION_SERIAL#                           NUMBER
USER_ID                                   NUMBER
SERVICE_HASH                              NUMBER
SESSION_TYPE                              VARCHAR2(10)
PROGRAM                                   VARCHAR2(64)
MODULE                                    VARCHAR2(48)
ACTION                                    VARCHAR2(32)
CLIENT_ID                                 VARCHAR2(64)
#session, 指定時間內的session 資訊

SESSION_STATE VARCHAR2(7)
WAIT_TIME NUMBER
#State 這個session的 state,上面列出的那些

EVENT                                     VARCHAR2(64)
EVENT_ID                                  NUMBER
EVENT#                                    NUMBER
SEQ#                                      NUMBER
P1                                        NUMBER
P2                                        NUMBER
P3                                        NUMBER
WAIT_TIME                                 NUMBER
TIME_WAITED                               NUMBER
CURRENT_OBJ#                              NUMBER
CURRENT_FILE#                             NUMBER
CURRENT_BLOCK#                            NUMBER0
#Wait 在等待什麼

SQL_ID                                    VARCHAR2(13)
SQL_CHILD_NUMBER                          NUMBER
SQL_PLAN_HASH_VALUE                       NUMBER
SQL_OPCODE                                NUMBER
QC_SESSION_ID                             NUMBER
QC_INSTANCE_ID                            NUMBER
#sql與之相關的sql資訊

TIME_WAITED NUMBER
#Duration,持續了多長時間

#ASH一些常用的查詢
1.top cpu (5分鐘內)
Select
  session_id,
  count(*)
from
  v$active_session_history
where
  session_state= 'ON CPU' and
  SAMPLE_TIME > sysdate – (5/(24*60))
group by
  session_id
order by
   count(*) desc;

2.Top Waiting Session(5分鐘內)
Select
  session_id,
  count(*)
from
  v$active_session_history
where
  session_state=‘WAITING’ and
  SAMPLE_TIME > SYSDATE - (5/(24*60))
group by
  session_id
order by
      count(*) desc;

3.Top Categories of Resource usage – IO, CPU, WAIT(TOP SQL)

select
     ash.SQL_ID ,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
         v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc

4.top session
select
     ash.session_id,
     ash.session_serial#,
     ash.user_id,
     ash.program,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING',
        decode(en.wait_class,'User I/O',1, 0 ), 0))    "WAITING" ,
     sum(decode(ash.session_state,'WAITING',
        decode(en.wait_class,'User I/O',1, 0 ), 0))    "IO" ,
     sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
        v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1))

5.Top Session w/ Username(Top Session Finding a Rogue User)
select
        /* if sid not found in v$session then disconnected */
        decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
                                                        "STATUS",
        topsession.session_id             "SESSION_ID",
        u.name "NAME",
        topsession.program                  "PROGRAM",
        max(topsession.CPU)              "CPU",
        max(topsession.WAITING)       "WAITING",
        max(topsession.IO)                  "IO",
        max(topsession.TOTAL)           "TOTAL"
        from (   previous query   )        topsession,
                                                        v$session s,
                                                        user$ u
   where
                    u.user# =topsession.user_id and
                   /* outer join to v$session because the session might be disconnected */
                   topsession.session_id         = s.sid         (+) and
                   topsession.session_serial# = s.serial#   (+)
   group by topsession.session_id, topsession.session_serial#, topsession.user_id,
                   topsession.program, s.username,s.sid,s.paddr,u.name
   order by max(topsession.TOTAL) desc

ASH 的家族表
current:v$session_wait
10 samples(最近10次取樣):v$session_wait_history
hour:v$active_session_history
7 days (disk):wrh$active_session_history

另外還可以使用指令碼
ASH Report in Text or HTML format using
$ORACLE_HOME/rdbms/admin/ashrpt.sql -- Report for Specified Duration
$ORACLE_HOME/rdbms/admin/ashrpti.sql -- Report for Specified duration and for Specified DB and Instance

ASH Report
- Top Events
- Load Profile
- Top SQL
- Top Sessions
- Top Objects/Files/Latches
- Activity Over Time
- You can Dump ASH content to File
SQL> oradebug setmypid
SQL> oradebug dump ashdump 5 -- This will dump last 5 minute content

ash是在記憶體中儲存的,當達到一定條件將儲存到DISK上
首先MMNL 程式每秒對v$session,v$session_wait 活動SESSion 進行取樣儲存到 ash buffers中(反映的view為
v$active_session_history),然後若buffer滿了66%(_ASH_EFLUSH_TRIGGER控制)還是有MMNL寫入到 workload repository中(反映view為wrh$_active_session_history
,dba_hist_active_sess_history),另外到達一小時mmon將ash buffer中內容寫到wrh$_active_session_history,dba_hist_active_sess_history

#oracle 10g還引入了一個view v$session_wait_history(用於記錄活動的session最近的10次等待)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-671718/，如需轉載，請註明出處，否則將追究法律責任。

oracle ASH

相關文章