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报告。
ASH 报告生成示例
ASH组件以v$active_session_history视图为基础，生成ASH报表，ASH报表与statspack类似，可以提供以下信息：
Top User Events
Top Background Events
Top Event P1/P2/P3 Values
Top Service/Module
Top Client IDs
Top SQL Command Types
Top SQL using literals
Top Blocking Sessions
Top DB Objects
Top DB Files
Top Latches
Activity Over Time
报表间隔时间可以精确到分钟，因而ASH可以提供比STATSPACK或AWR更详细的关于历史会话的信息，可以作为statspack或awr的补充。ASH报告通过@$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本生成，包括hmtl和text两种格式。
SYS@anqing1(rac1)> @?/rdbms/admin/ashrpt.sql;
-- 调用脚本
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
715014091 ANQING 1 anqing1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
-- 选择生成的ASH 报告类型，是text 还是html
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
715014091 1 ANQING anqing singledb
* 715014091 1 ANQING anqing1 rac1
715014091 2 ANQING anqing2 rac2
Defaults to current database
Using database id: 715014091
Defaults to current instance
Using instance number: 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 19-May-11 14:49:59 [ 10585 mins in the past]
Latest ASH sample available: 26-May-11 23:14:34 [ 1 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: 8:00
-- 输入ASH 开始的时间，时间格式上面的示例有说明
Report begin time specified: 8: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 duration specified:
Using 26-May-11 08:00:00 as report begin time
Using 26-May-11 23:15:12 as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WATI_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0526_2315.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /u01/daveash.txt
-- 输入ASH 报告的名称，可以指定生成的目录，默认情况是当前登陆sqlplus的目录。 这里的扩展最好加上，如果不加扩展名，扩展名会变成lst. 但不影响数据。
Using the report name /u01/daveash.txt
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 715014091
Inst num : 1
Begin time : 26-May-11 08:00:00
End time : 26-May-11 23:15:12
Slot width : Default
Report targets : 0
Report name : /u01/daveash
ASH Report For ANQING/anqing1
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ANQING 715014091 anqing1 1 10.2.0.4.0 YES rac1
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
1 272M (100%) 144M (52.9%) 96M (35.3%) 2.0M (0.7%)
Analysis Begin Time: 26-May-11 08:00:00
Analysis End Time: 26-May-11 23:15:12
Elapsed Time: 915.2 (mins)
Sample Count: 795
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
latch free Other 8.55 0.00
CPU + Wait for CPU CPU 7.17 0.00
log file sync Commit 2.26 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
control file sequential read System I/O 36.73 0.01
control file parallel write System I/O 28.30 0.00
log file parallel write System I/O 4.91 0.00
CPU + Wait for CPU CPU 3.40 0.00
db file parallel write System I/O 3.40 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
control file sequential read 36.73 "0","11","1" 21.01
file# block# blocks
"0","3","1" 11.45
"0","4","1" 1.64
control file parallel write 28.30 "2","3","2" 28.30
files block# requests
latch free 8.68 "817412680","389","0" 8.55
address number tries
log file parallel write 4.91 "2","2","2" 1.13
files blocks requests
db file parallel write 3.40 "1","0","2147483647" 3.40
requests interrupt timeout
-------------------------------------------------------------
Top Service/Module DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 80.25 UNNAMED 80.25
SYS$USERS UNNAMED 9.43 UNNAMED 9.43
DBMS_SCHEDULER 5.16 MYJOB 4.91
1.89 1.89
racgimon@rac1 (TNS V1-V3 1.01 UNNAMED 1.01
-------------------------------------------------------------
Top Client IDs DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
-> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs
with the given SQL Command Type found over all the ASH samples
in the analysis period
Distinct Avg Active
SQL Command Type SQLIDs % Activity Sessions
---------------------------------------- ---------- ---------- ----------
SELECT 7 1.38 0.00
-------------------------------------------------------------
Top SQL Statements DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
4gd6b1r53yt88 N/A 1.38 PX Deq: reap credit 0.75
** SQL Text Not Available **
531sc6y5xdd41 N/A 1.01 SQL*Net break/reset to client 0.63
** SQL Text Not Available **
-------------------------------------------------------------
Top SQL using literals DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
-> '# Samples Active' shows the number of ASH samples in which the session
was found waiting for that particular event. The percentage shown
in this column is calculated with respect to wall clock time
and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
the PQ slave activity into the session issuing the PQ. Refer to
the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
160, 1 39.87 control file parallel write 28.30
SYS oracle@rac1 (CKPT) 225/55K [ 0%] 0
control file sequential read 11.45
91/55K [ 0%] 0
167, 1 24.91 control file sequential read 22.64
SYS oracle@rac1 (LMON) 180/55K [ 0%] 0
CPU + Wait for CPU 1.51
12/55K [ 0%] 0
148,23561 8.55 latch free 8.55
SYS oracle@rac1 (J002) 68/55K [ 0%] 0
161, 1 4.91 log file parallel write 4.91
SYS oracle@rac1 (LGWR) 39/55K [ 0%] 0
162, 1 3.40 db file parallel write 3.40
SYS oracle@rac1 (DBW0) 27/55K [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
-> Blocking session activity percentages are calculated with respect to
waits on enqueues, latches and "buffer busy" only
-> '% Activity' represents the load on the database caused by
a particular blocking session
-> '# Samples Active' shows the number of ASH samples in which the
blocking session was found active.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the blocking session was found active.
Blocking Sid % Activity Event Caused % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
161, 1 2.26 log file sync 2.26
SYS oracle@rac1 (LGWR) 130/55K [ 0%] 0
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
Max Sample
Latch % Latch Blocking Sid % Activity Wait secs
------------------------------ ---------- --------------- ---------- ----------
# Waits # Sampled Wts # Sampled Wts # Sampled Wts # Sampled Wts
Sampled < 10ms 10ms - 100ms 100ms - 1s > 1s
-------------- -------------- -------------- -------------- --------------
latch: JS slv state obj latch 8.55 Held Shared 8.55 0.000000
0 0 0 0 0
-------------------------------------------------------------
Activity Over Time DB/Inst: ANQING/anqing1 (May 26 08:00 to 23:15)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period
Slot Event
Slot Time (Duration) Count Event Count % Event
-------------------- -------- ------------------------------ -------- -------
08:00:00 (84.0 min) 67 control file sequential read 27 3.40
control file parallel write 20 2.52
CPU + Wait for CPU 8 1.01
09:24:00 (92.0 min) 71 control file sequential read 23 2.89
control file parallel write 22 2.77
CPU + Wait for CPU 10 1.26
10:56:00 (92.0 min) 64 control file sequential read 28 3.52
control file parallel write 22 2.77
CPU + Wait for CPU 4 0.50
12:28:00 (92.0 min) 66 control file sequential read 29 3.65
control file parallel write 21 2.64
CPU + Wait for CPU 11 1.38
14:00:00 (92.0 min) 56 control file sequential read 30 3.77
control file parallel write 22 2.77
SQL*Net break/reset to client 1 0.13
15:32:00 (92.0 min) 77 control file sequential read 29 3.65
control file parallel write 22 2.77
CPU + Wait for CPU 15 1.89
17:04:00 (92.0 min) 76 control file sequential read 35 4.40
control file parallel write 25 3.14
CPU + Wait for CPU 4 0.50
18:36:00 (92.0 min) 159 latch free 68 8.55
control file sequential read 33 4.15
control file parallel write 22 2.77
20:08:00 (92.0 min) 80 control file sequential read 33 4.15
control file parallel write 24 3.02
CPU + Wait for CPU 5 0.63
21:40:00 (92.0 min) 73 control file sequential read 25 3.14
control file parallel write 24 3.02
CPU + Wait for CPU 10 1.26
23:12:00 (3.2 min) 6 CPU + Wait for CPU 4 0.50
control file parallel write 1 0.13
log file parallel write 1 0.13
-------------------------------------------------------------
End of Report
Report written to /u01/daveash.txt
SYS@anqing1(rac1)>
这个就是用ashrpt.sql脚本来生成报告