oracle ASH

最新推荐文章于 2025-05-29 08:38:47 发布

cuiye1492

最新推荐文章于 2025-05-29 08:38:47 发布

阅读量189

点赞数

文章标签：数据库

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

转载于:http://blog.itpub.net/12020513/viewspace-671718/