如何 DUMP ASH 信息至跟踪文件

Oracle Database 10g引入的ASH特性是一个非常有用的功能，通过如下方法可以将存储在内存中的ASH数据转储到跟踪文件中：

SQL> oradebug setmypid
SQL> oradebug dump ashdump 10

可以通过时间定义指定ashdump的时间段，以下是一个示范：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 6 14:28:48 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 10;
Statement processed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

我们可以摘录一点ASH的转储信息：

3116225484,1,52859006,"08-06-2009 14:20:30.098953000",373,17302,47,"fqhct705w82uh",2,1894391199,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10026,1413697536,1,0,1411,0,12100769058337668941,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858999,"08-06-2009 14:20:23.028953000",371,52708,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10404,1413697536,1,0,422,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858999,"08-06-2009 14:20:23.028953000",373,17302,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10008,1413697536,1,0,455,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858994,"08-06-2009 14:20:17.978953000",409,54797,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10212,1413697536,1,0,424,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858993,"08-06-2009 14:20:16.968953000",425,43871,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10349,1413697536,1,0,461,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858989,"08-06-2009 14:20:12.928953000",373,17302,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,9992,1413697536,1,0,390,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858985,"08-06-2009 14:20:08.888953000",371,52708,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10372,1413697536,1,0,408,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858981,"08-06-2009 14:20:04.848953000",425,43871,47,"fqhct705w82uh",2,1894391199,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10343,1413697536,1,0,1100,0,12100769058337668941,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858974,"08-06-2009 14:19:57.778943000",371,52708,47,"fqhct705w82uh",2,1894391199,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10366,1413697536,1,0,1371,0,12100769058337668941,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858961,"08-06-2009 14:19:44.648943000",425,43871,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10333,1413697536,1,0,369,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858955,"08-06-2009 14:19:38.578943000",443,1,0,"",0,0,165959219,2,0,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,866018717,42089,300,0,0,2929731,0,0,"oracle@db480-5.hurray.com.cn (PSP0)","","",""
3116225484,1,52858953,"08-06-2009 14:19:36.558943000",409,54797,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10164,1413697536,1,0,401,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858948,"08-06-2009 14:19:31.508943000",442,1,0,"",0,0,165959219,2,0,0, 0,0, 0,4294967292,0,0,0,,4294967295,0,0,1736567536,45360,0,0,0,0,12,0,"oracle@db480-5.hurray.com.cn (LMON)","","",""
3116225484,1,52858942,"08-06-2009 14:19:25.448943000",389,15338,38,"",0,0,3427055676,1,0,42264, 15,42264, 3,4294967291,0,0,0,,4294967295,0,0,2587381521,3,18,21474836,0,1561,0,0,"oracle@db480-5.hurray.com.cn (J000)"," "," ",""
3116225484,1,52858937,"08-06-2009 14:19:20.398943000",371,52708,47,"fqhct705w82uh",2,1894391199,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,2067390145,10351,1413697536,1,0,3,0,12100769058337668941,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858937,"08-06-2009 14:19:20.398943000",373,17302,47,"fqhct705w82uh",2,1894391199,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,2067390145,9963,1413697536,1,0,3,0,12100769058337668941,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858927,"08-06-2009 14:19:10.308943000",425,43871,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10301,1413697536,1,0,421,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858923,"08-06-2009 14:19:06.268943000",369,33615,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,28,1413697536,1,0,430,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
3116225484,1,52858923,"08-06-2009 14:19:06.268943000",371,52708,47,"a3nt1guzn3hd5",2,2626559475,1974830977,1,3,0, 0,0, 0,4294967291,0,0,0,,4294967295,0,0,1421975091,10324,1413697536,1,0,400,0,14160797024472787515,"oracle@eygle.com (TNS V1-V3)","oracle@eygle.com (TNS V1-V3)","",""
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>

转储文件以逗号分隔符写出，可以通过sqlldr加载入数据库。
可以对照v$active_session_history视图来了解这些记录信息的含义：

SQL> desc v$active_session_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAMPLE_ID                                          NUMBER
 SAMPLE_TIME                                        TIMESTAMP(3)
 SESSION_ID                                         NUMBER
 SESSION_SERIAL#                                    NUMBER
 USER_ID                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 SQL_PLAN_HASH_VALUE                                NUMBER
 FORCE_MATCHING_SIGNATURE                           NUMBER
 SQL_OPCODE                                         NUMBER
 PLSQL_ENTRY_OBJECT_ID                              NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
 PLSQL_OBJECT_ID                                    NUMBER
 PLSQL_SUBPROGRAM_ID                                NUMBER
 SERVICE_HASH                                       NUMBER
 SESSION_TYPE                                       VARCHAR2(10)
 SESSION_STATE                                      VARCHAR2(7)
 QC_SESSION_ID                                      NUMBER
 QC_INSTANCE_ID                                     NUMBER
 BLOCKING_SESSION                                   NUMBER
 BLOCKING_SESSION_STATUS                            VARCHAR2(11)
 BLOCKING_SESSION_SERIAL#                           NUMBER
 EVENT                                              VARCHAR2(64)
 EVENT_ID                                           NUMBER
 EVENT#                                             NUMBER
 SEQ#                                               NUMBER
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_TIME                                          NUMBER
 TIME_WAITED                                        NUMBER
 XID                                                RAW(8)
 CURRENT_OBJ#                                       NUMBER
 CURRENT_FILE#                                      NUMBER
 CURRENT_BLOCK#                                     NUMBER
 PROGRAM                                            VARCHAR2(48)
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 CLIENT_ID                                          VARCHAR2(64)

可以看到Oracle顺序记录的ASH信息是非常精简和紧密的，其效率极高，这些采样数据和我们经常在OS上看到的采样数据极其相似。

不论从OS到数据库，大家的采样思路和诊断数据收集的理念是完全相同的。

-The End-