Oracle ASH(Active Session History) 说明

在Oracle 10g中，推出了一些与DB 性能相关的功能，比如AWR, ADDM, STA, 已经这篇讲的ASH（Active Session History）。

关于其他的几个功能说明，参考我之前的Blog：

Oracle AWR 介绍

http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4682300.aspx

Oracle ADDM 自动诊断监视工具介绍

http://blog.csdn.net/tianlesoftware/archive/2010/05/28/5630942.aspx

如何用 SQL Tuning Advisor (STA) 优化SQL语句

http://blog.csdn.net/tianlesoftware/archive/2010/05/28/5630888.aspx

一. 官网说明

Performance tuning and problem diagnosis are the two most challenging and important management tasks that any database administrator performs. In line with the primary drive of the server manageability effort, the Autometic Database Diagnostic Monitor (ADDM) attempts to make, performing these two tasks, a lot simpler and easier. ADDM employs an iterative top-down approach and drives a rule-based expert system, to identify bottlenecks in a system and suggest relevant recommendations to tackle them.

ASH acquires the information it requires to sample the active session’s activity from the database kernel’s session state objects. The quantity of information sampled by ASH could be quite voluminous, and therefore, ASH maintains a fixed sized circular buffer in the database System Global Area (SGA). The fixed sized circular buffer will be allocated during database start-up time. Since the information collected by the ASH infrastructure compliments the information present in SWRF snapshots, and can be used for drilldown purposes during problem diagnosis or performance tuning, the ASH data is also periodically flushed to disk.

-- ASH 的数据也会定期的写入磁盘

The flushing and purging policies of ASH, including the way ASH respects SWRF baselines, are completely tied with SWRF policies. Still, flushing the entire content of ASH on to disk could be too populous to be feasible, and therefore, only one out of every ten active session samples will be flushed to disk.

-- 将整个ASH 内容频繁的写入磁盘不容易，所以只有1/10的active session 数据写入磁盘

In addition to ADDM using the ASH to achieve its objectives, the ASH contents will also be displayed in the Oracle Enterprise Manager (EM) / Mozart [OEM-SWRF] performance screen. The graph that summarizes the ASH contents in the EM performance screen will be a stacked graph showing the distribution of the elapsed database time onto various wait times and CPU time, during every minute. The ASH infrastructure will provide a single V$ view (V$ACTIVE_SESSION_HISTORY), to view the in-memory contents of ASH.

--可以通过V$ACTIVE_SESSION_HISTORY 视图查看内存中ASH 的信息

The ASH infrastructure will also provide a way to dump the in-memory contents of its circular buffer onto an external file in a human readable format. The ASH dump file can be transported to another system, imported onto a user table, and analyzed using the ADDM in that system.

-- 也可以将内存中的ASM 数据dump 到外部文件，并转移到其他系统进行分析。

ASH Memory Size

==============
Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]

--最小1M，最大30M

Catagoreising the Active Session

============================
i. Present inside a user call
ii. Not a recursive session
iii. Not waiting for the ‘IDLE’ wait-event
iv. If it is a background process, not waiting for its usual timer-event
v. If it is a parallel slave, not waiting for the PX_IDLE wait event.

So will not see any info if a process is waitting for "SQL*Net message from client".

online 和offline 分析示例

Let start with ONLINE analysis
========================
I have open two session. first one (SID 16) is running the DML like

SQL> delete test where rowid='AAAKB9AAEAAAAAiAAA';

From second session (SID 15) run the same DML, and it is obvious that second session will wait for first session to commit.

Lets check the info in V$ACTIVE_SESSION_HISTORY.
------------------------------------------------
Run the following script.
SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# from v$active_session_history ash, v$event_name enm where ash.event#=enm.event# and SESSION_ID=&SID and SAMPLE_TIME>=(sysdate-&minute/(24*60));

Input is

Enter value for sid: 15
Enter value for minute: 1 /* How many minutes activity you want to see */

output is 59 lines as it is wiatting more than 1 minute more than 1 minute

SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#
---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
continue .............
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34

So you have object details with problem info.

Lets do the OFFLINE analysis of ASH
==============================
So if your are not able to find the problem online, you can dump the ASH to a trace.

Command would be like below: where level means minute. lets dump for 10 minutes history

1. SQL> alter session set events 'immediate trace name ashdump level 10';
or
2. SQL> alter system set events 'immediate trace name ashdump level 10';
or
3. SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;

So you will get the trace file in udump.

Output of trace would be like

<<>>
2594829169,1,161390,"07-18-2003 16:05:21.098717000",13,1,0,"",65535,0,0,2,0,0,0,4294967295,0,0,2,35,100,0,0,10
05855,0,"oracle@usunrat21 (MMNL)","","",""

Oracle has provide an utility under $ORACLE_HOME/rdbms/demo (Location may change), by which you can upload the ASH trace dump to a database table and do the analysis.

Please see the following Note
Note 555303.1 ashdump* scripts and post-load processing of MMNL traces
for the scripts.

Script Details
============

1. "ashdump_loader" -> Main script to run with one argument. Argument is the the name of tracefile. (available in 10g only)
2. "ashdump_table.sql" -> It would be called by the main script to create the table
called SYSTEM.ACTIVE_SESSION_HISTORY_DUMP.
3. "ashdump_sqlldr.ctl" -> It is the third script called by main script to load the trace into
table called SYSTEM.ACTIVE_SESSION_HISTORY_DUMP.

注意：You must use the same oracle version to compare the OFFLINE trace with the other view.

二. ASH 说明

ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。

ASH buffers 的最小值为1MB,最大值不超过30MB。内存中记录数据。期望值是记录一小时的内容。

ASH报告生成脚本：@?/rdbms/admin/ashrpt.sql

ASH内存记录数据始终是有限的，为了保存历史数据，引入了自动负载信息库(Automatic Workload Repository ,AWR) 由后台进程MMON完成。ASH信息同样被采集写出到AWR负载库中。由于内存不是足够的，所以MMNL进程在ASH写满后会将信息写出到AWR负载库中。ASH全部写出是不可接受的，所以一般只写入收集的10%的数据量，而且使用direct-path insert完成，尽量减少日志的生成，从而最小化数据库性能影响。

内存中的ASH 信息可以通过V$ACTIVE_SESSION_HISTORY查询，而写出到AWR负载库的ASH信息，可以通过AWR的基础表wrh$active_session_hist查询，wrh$active_session_hist是一个分区表，Oracle会自动进行数据清理。

一般来说，我们在监控数据库时，如果是当前正在发生的问题，我们可以通过v$session+v$sqlarea来找出性能最差的SQL语句。如果在一个小时以内发生的我们可以通过生成ASH报告来找出SQL。如果是1小时以上或几天我们可以通过AWR报告来找出几小时，几天以来最影响系统的SQL语句。ADDM报告基于AWR库，默认可以保存30天的ADDM报告。

生活不是用来挥霍的

Oracle ASH(Active Session History) 说明

公告