ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle ASH

oracle ASH

原创 Linux操作系统 作者:fufuh2o 时间:2010-08-24 14:30:52 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427779