oracle ASH

fufuh2o發表於2010-08-24
Oracle

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章