作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


ASH新特性:

如果说v$session_wait_history是一小步,那么ASH则是Oracle迈出根本变革的一大步。

从Oracle 10g开始,Oracle引入了ASH新特性,也就是活动session历史信息记录(Active Session History,ASH)ASH以v$session为基础,每秒钟采样一次,记录活动会话等待的事件。因为记录所有会话的活动是非常昂贵的,所以不活动的会话不会被采样,这一点从ASH的“A”上就可以看出。采样工作由Oracle 10g新引入的一个后台进程MMNL来完成。

是否启用ASH功能,受一个内部隐含参数控制:

sys@CCDB> @GetHidPar.sql
Enter value for par: ash_enable
old   4: AND x.ksppinm LIKE '%&par%'
new   4: AND x.ksppinm LIKE '%ash_enable%'
NAME                           VALUE     DESCRIB
------------------------------ --------- ------------------------------------------------------------
_right_outer_hash_enable       TRUE      Right Outer/Semi/Anti Hash Enabled
_ash_enable                    TRUE      To enable or disable Active Session sampling and flushing

而采样时间同样由另一个内部隐含参数决定:

sys@CCDB> @GetHidPar.sql
Enter value for par: ash_sampling
old   4: AND x.ksppinm LIKE '%&par%'
new   4: AND x.ksppinm LIKE '%ash_sampling%'
NAME                      VALUE    DESCRIB
------------------------- -------- ------------------------------------------------------------
_ash_sampling_interval    1000     Time interval between two successive Active Session samples
                                   in millisecs
1000毫秒,正好是1秒的时间。

很多人可能更关心性能,如果频繁的采样是否会极大地影响数据库的性能呢?采样的性能影响无疑是存在的,但是因为Oracle的采样工具可以直接访问Oracle 10g内部结构,所以是极其高效的,对于性能的影响也非常小,这也正是Oracle提供优化或诊断工具的优势所在。

ASH信息被设计为在内存中滚动的,在需要的时候早期的信息是会被覆盖的。ASH记录的信息可以通过v$active_session_history视图来访问,对于每个活动SESSION,每次采样会在这个视图中记录一行信息。

这部分内存在SGA中分配:

sys@CCDB> select * from v$sgastat where name like '%ASH%';
POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  ASH buffers                       8388608

注意ASH buffers的最小值为1MB,最大值不超过30MB,大小安装以下算法分配:
Max(Min(cpu_count*2MB,5%*SHARED_POOL_SIZE,30MB),1MB)
在以上公式中,如果SHARED_POOL_SIZE未显示设置,则限制为2%*SGA_TARGET。这一算法在Oracle 10g的不同版本中,可能不同。根据这个算法,我的采样分配的ASH Buffers为8MB。

sys@CCDB> show parameter cpu_count
NAME             TYPE          VALUE
---------------- ------------- ---------
cpu_count        integer       4
sys@CCDB> show parameter sga      
NAME             TYPE          VALUE
---------------- ------------- ---------
lock_sga         boolean       FALSE
pre_page_sga     boolean       FALSE
sga_max_size     big integer   804M
sga_target       big integer   0
sys@CCDB> show parameter shared_pool_size
NAME                 TYPE           VALUE
-------------------- -------------- ----------
shared_pool_size     big integer    0

记录在SGA中的ASH信息,可以通过v$session_wait_history进行查询:

sys@CCDB> desc v$session_wait_history
Name                           Null?    Type
------------------------------ -------- -----------------
SID                                     NUMBER
SEQ#                                    NUMBER
EVENT#                                  NUMBER
EVENT                                   VARCHAR2(64)
P1TEXT                                  VARCHAR2(64)
P1                                      NUMBER
P2TEXT                                  VARCHAR2(64)
P2                                      NUMBER
P3TEXT                                  VARCHAR2(64)
P3                                      NUMBER
WAIT_TIME                               NUMBER
WAIT_TIME_MICRO                         NUMBER
TIME_SINCE_LAST_WAIT_MICRO              NUMBER

可以通过Oracle提供的工具生成ASH的报告,报告可以以几分钟为跨度对数据库进行精确分析:也可以以数小时或数天为时间跨度,为数据库提供概要分析。

生成ASH报告主要可以通过两种方式:脚本方式和OEM图形方式。
1. 脚本方式:
调用$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本,回答一系列问题之后,就可以生成一个ASH报告,报告包括TOP等待事件、TOP SQL、TOP SQL命令类型、TOP Sessions等内容,摘录部分报告内容如下:

调用ashrpt.sql脚本:
sys@CCDB> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
3317656585 CCDB                1 ccdb
......
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库可用的采样数据:
Oldest ASH sample available:  24-Oct-09 21:00:07   [ ###### mins in the past]
Latest ASH sample available:  14-Mar-10 18:30:13   [      0 mins in the past]
......

用户定义概要如下:
Summary of All User Input
-------------------------
Format         : TEXT
DB Id          : 3317656585
Inst num       : 1
Begin time     : 14-Mar-10 18:15:16
End time       : 14-Mar-10 18:30:18
Slot width     : Default
Report targets : 0
Report name    : ashrpt_1_0314_1830.txt

生成的报告如下:
ASH Report For CCDB/ccdb

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
CCDB          3317656585 ccdb                1 11.1.0.6.0  NO  MWSG1

CPUs           SGA Size       Buffer Cache        Shared Pool    ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
   4      1,529M (100%)       912M (59.6%)       673M (44.0%)        8.0M (0.5%)

          Analysis Begin Time:   14-Mar-10 18:15:16
            Analysis End Time:   14-Mar-10 18:30:18
                 Elapsed Time:        15.0 (mins)
            Begin Data Source:   V$ACTIVE_SESSION_HISTORY
              End Data Source:   V$ACTIVE_SESSION_HISTORY
                 Sample Count:       1,493
      Average Active Sessions:        1.66
  Avg. Active Session per CPU:        0.41
                Report Target:   None specified

Top User Events                    DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
db file sequential read             User I/O             84.66       1.40
CPU + Wait for CPU                  CPU                   8.31       0.14
log file sync                       Commit                1.00       0.02
          -------------------------------------------------------------

Top Background Events              DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)

                                                               Avg Active
Event                               Event Class     % Activity   Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU                  CPU                   3.28       0.05
log file parallel write             System I/O            1.34       0.02
          -------------------------------------------------------------
......

Top SQL Command Types              DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)
......

                                           Distinct            Avg Active
SQL Command Type                             SQLIDs % Activity   Sessions
---------------------------------------- ---------- ---------- ----------
SELECT                                           31      91.36       1.51
          -------------------------------------------------------------

Top Phases of Execution            DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)

                                          Avg Active
Phase of Execution             % Activity   Sessions
------------------------------ ---------- ----------
SQL Execution                       89.15       1.48
          -------------------------------------------------------------

Top SQL with Top Events    DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)

                                                        Sampled #
                 SQL ID             Planhash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Event                          % Event Top Row Source                    % RwSrc
------------------------------ ------- --------------------------------- -------
          dudnpc7h1yrm1           2796488188                  306          20.70
db file sequential read          19.56 TABLE ACCESS - BY INDEX ROWID       10.25
select t.*,a.name as rankName,b.client_config,b.client_config_diff from MEMBER
t join member_rank a on t.rank=a.member_rank_id join member_client b on t.membe
r_id=b.member_id and b.member_client_id=1 where t.MEMBER_ID=:1
......

Top Sessions                       DB/Inst: CCDB/ccdb  (Mar 14 18:15 to 18:30)
......

End of Report
Report written to ashrpt_1_0314_1830.txt

2. OEM图形方式:略。

- The End -