如何手动生成ASH报告

来源：互联网发布：精子能吃吗知乎编辑：程序博客网时间：2024/05/29 13:56

一, ASH 说明
      ASH 的数据也会定期的写入磁盘,但是将整个ASH 内容频繁的写入磁盘不容易，所以只有1/10的active session 数据写入磁盘,可以通过V$ACTIVE_SESSION_HISTORY 视图查看内存中ASH 的信息,也可以将内存中的ASM 数据dump 到外部文件，并转移到其他系统进行分析。ASH Memory Size[最小1M，最大30M]: Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ] ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。内存中记录数据。期望值是记录一小时的内容。
       ASH内存记录数据始终是有限的，为了保存历史数据，引入了自动负载信息库(Automatic Workload Repository ,AWR) 由后台进程MMON完成。ASH信息同样被采集写出到AWR负载库中。由于内存不是足够的，所以MMNL进程在ASH写满后会将信息写出到AWR负载库中。内存中的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报告。

相关查询试图：
v$session                              (当前正在发生)
v$session_wait              (当前正在等待)
v$session_wait_history        (会话最近的10次等待事件)
v$active_session_history      (内存中的ASH采集信息，理论为1小时)
wrh$_active_session_history   (写入AWR库中的ASH信息，理论为1小时以上)
dba_hist_active_sess_history   (根据wrh$_active_session_history生成的视图)

二, ASH 报告生成示例
1, 如果系统安装了oem的dbcontrol 或者安装了grid control,可以直接在Web页面生成ASH报告。下面我介绍的是如何通过手动的生成ASH报告。
ASH组件以v$active_session_history视图为基础，生成ASH报表，ASH报表与statspack类似, 报表间隔时间可以精确到分钟，因而ASH可以提供
比STATSPACK或AWR更详细的关于历史会话的信息，可以作为statspack或awr的补充。包括hmtl和text两种格式。可以提供以下信息：
Top SQL Command Types
Top SQL using literals
Top User Events
Top Background Events
Top Event P1/P2/P3 Values
Top Service/Module
Top Client IDs
Top Blocking Sessions
Top DB Objects
Top DB Files
Top Latches
Activity Over Time

2,生成ASH报告（调用@?/rdbms/admin/ashrpt.sql脚本）
[oracle@even ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 20 16:51:44 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> @?/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2100083002 TEST 1 test

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2100083002        1 TEST         test         even.oracle.
                                                com

Defaults to current database

Using database id: 2100083002

Defaults to current instance

Using instance number: 1

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available: 16-Dec-12 14:05:50 [ 5928 mins in the past]
Latest ASH sample available: 20-Dec-12 16:51:36 [ 2 mins in the past]

Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 10:00

-- 输入ASH 开始的时间，时间格式上面的示例有说明

Report begin time specified: 10:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration:

-- 输入ASH 结束时间，默认是SYSDATE - begin_time

...
....
....
Report written to /home/oracle/ash_20121228.html

上面报告输出的路径是自己写的，不写会输出当前用户的目录下。我的是在/home/oracle/ash_20121228.html。

导出为止ASH report就成功的生成了。

三, 把ASH转储到文件里
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10

到$ORALCE_BASE/admin/test/udump里找上面dump的文件
我的dump的文件是test_ora_18246.trc，下面是查看test_ora_18246.trc的信息。

[oracle@even udump]$ cat test_ora_18246.trc
/u01/app/oracle/admin/test/udump/test_ora_18246.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      even.oracle.com
Release:        2.6.18-164.el5
Version:        #1 SMP Thu Sep 3 02:16:47 EDT 2009
Machine:        i686
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 18246, image: oracle@even.oracle.com (TNS V1-V3)

*** 2012-12-20 17:09:22.366
*** SERVICE NAME:(SYS$USERS) 2012-12-20 17:09:22.365
*** SESSION ID:(150.615) 2012-12-20 17:09:22.365
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER,

SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL# QC_SESSION_ID,

QC_INSTANCE_ID, XID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_WAITED,

FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
2100083002,1,50281,"12-20-2012

17:08:04.732575000",156,802,61,"",0,0,3427055676,1,0,166,1,0,0,,4294967295,0,0,1328744198,2,5610,0,0,0,0,0,"oracle@even.oracle.com (J000)"," "," ",""
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
*** 2012-12-20 17:10:02.767
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER,

SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL# QC_SESSION_ID,

QC_INSTANCE_ID, XID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_WAITED,

17:09:26.613282000",147,334,61,"",65535,0,3237892936,1,0,166,1,0,0,,345,1,52049,1328744198,54444,5634,0,0,0,31756,0,"OMS","OEM.SystemPool","",""
2100083002,1,50359,"12-20-2012

17:09:26.613282000",166,1,0,"",0,0,165959219,2,0,4294967292,0,0,0,,4294967295,0,0,3999721902,12554,1,4,1,0,31484,0,"oracle@even.oracle.com

(LGWR)","","",""
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER,