ASH基于v$session每秒进行一次采样，记录当前活动会话等待事件（不活动的不采样）
ASH通过内存所记录的数据是有限的，为了保存历史记录，引入了AWR，ASH信息采样被写到AWR中，ASH写满了以后会将信息写到AWR负载库，ASH数据完全写出是不可能的，一般只写收集数据量的10%，
ASH内存中的信息通过v$active_session_history查询，而已经写出到AWR负载库的ASH信息，通过AWR基础表（wrh$active_session_hist)查询。
下面举一个例子生成ASH报告
ASH以v$active_session_history视图为基础，生成ASH报表
ASH报告时间精确到分钟，提供比AWR更详细的历史会话信息，作为对AWR的补充
HZH@hzh%11gR2>@?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
609310700 HZH 1 hzh
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html‘ for an HTML report, or ‘text‘ for plain text
Defaults to ‘html‘
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 609310700 1 HZH hzh SLES-10-01
* 609310700 1 HZH hzh bogon
Defaults to current database
Using database id: 609310700
Enter instance numbers. Enter ‘ALL‘ for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 16-Oct-14 22:45:10 [ 667 mins in the past]
Latest ASH sample available: 17-Oct-14 09:51:38 [ 0 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: 09:30
Report begin time specified: 09:30
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration:
Report duration specified:
Using 17-Oct-14 09:30:00 as report begin time
Using 17-Oct-14 09:51:54 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 WAIT_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_1017_0951.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/ash_kingsql
Using the report name /home/oracle/ash_kingsql
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 609310700
Inst num : 1
Begin time : 17-Oct-14 09:30:00
End time : 17-Oct-14 09:51:54
Slot width : Default
Report targets : 0
Report name : /home/oracle/ash_kingsql
ASH Report For HZH/hzh
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
HZH 609310700 hzh 1 11.2.0.1.0 NO SLES-10-01
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
1 858M (100%) 540M (62.9%) 300M (35.0%) 2.0M (0.2%)
Analysis Begin Time: 17-Oct-14 09:30:00
Analysis End Time: 17-Oct-14 09:51:54
Elapsed Time: 21.9 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 246
Average Active Sessions: 0.19
Avg. Active Session per CPU: 0.19
Report Target: None specified
//这个位置以下的内容都是重点！
Top User Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 34.15 0.06
log buffer space Configuration 2.85 0.01
log file switch (checkpoint incompl Configuration 1.63 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
log file parallel write System I/O 24.80 0.05
CPU + Wait for CPU CPU 16.26 0.03
db file async I/O submit System I/O 9.35 0.02
control file parallel write System I/O 4.07 0.01
Log archive I/O System I/O 2.03 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
log file parallel write 24.80 "1","3","1" 8.54
files blocks requests
"1","4","1" 3.25
"1","1","1" 1.63
db file async I/O submit 9.35 "20","0","0" 1.63
requests interrupt timeout
"15","0","0" 1.22
"23","0","0" 1.22
control file parallel write 4.07 "2","21","2" 1.22
files block# requests
Log archive I/O 2.03 "1","256","4294967295" 2.03
count intr timeout
-------------------------------------------------------------
Top Service/Module DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 59.35 UNNAMED 59.35
SYS$USERS SQL*Plus 36.59 UNNAMED 36.59
sqlplus@SLES-10-01 (TNS 2.03 UNNAMED 2.03
UNNAMED 1.22 UNNAMED 1.22
-------------------------------------------------------------
Top Client IDs DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
-> ‘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
---------------------------------------- ---------- ---------- ----------
PL/SQL EXECUTE 1 20.73 0.04
INSERT 2 15.04 0.03
SELECT 9 3.66 0.01
-------------------------------------------------------------
Top Phases of Execution DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 39.43 0.07
PLSQL Execution 3.66 0.01
Hard Parse 2.03 0.00
Parse 2.03 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
764a3gsmzf4r1 N/A 1 20.73
CPU + Wait for CPU 17.89 ** Row Source Not Available ** 17.89
** SQL Text Not Available **
N/A 1 20.73
log buffer space 2.44 ** Row Source Not Available ** 2.44
** SQL Text Not Available **
fph62wwsqnnrk N/A 28 14.63
CPU + Wait for CPU 12.60 ** Row Source Not Available ** 12.60
INSERT INTO HZH3 VALUES (:B1 )
N/A 28 14.63
log file switch (checkpoint in 1.22 ** Row Source Not Available ** 1.22
INSERT INTO HZH3 VALUES (:B1 )
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Sampled #
SQL ID PlanHash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
764a3gsmzf4r1 N/A 1 20.73
** Row Source Not Available ** 20.73 CPU + Wait for CPU 17.89
** SQL Text Not Available **
fph62wwsqnnrk N/A 28 14.63
** Row Source Not Available ** 14.63 CPU + Wait for CPU 12.60
INSERT INTO HZH3 VALUES (:B1 )
-------------------------------------------------------------
Top SQL using literals DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Module Action % Activ Event
------------------------------ -------------------------------- ------- --------
sqlplus@SLES-10-01 (TNS V1-V3) 2.03 CPU + Wa
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
-> ‘PL/SQL entry subprogram‘ represents the application‘s top-level
entry-point(procedure, function, trigger, package initialization
or RPC call) into PL/SQL.
-> ‘PL/SQL current subprogram‘ is the pl/sql subprogram being executed
at the point of sampling . If the value is ‘SQL‘, it represents
the percentage of time spent executing SQL for the particular
plsql entry subprogram
PLSQL Entry Subprogram % Activity
----------------------------------------------------------------- ----------
PLSQL Current Subprogram % Current
----------------------------------------------------------------- ----------
SYS.DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT 2.03
SQL 2.03
-------------------------------------------------------------
Top Java Workload DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
Call Type Count % Activity Avg Active
---------------------------------------- ---------- ---------- ----------
V8 Bundled Exec 95 38.62 0.07
-------------------------------------------------------------
Top Sessions DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
-> ‘# 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
-------------------- ------------------------------ ------------------ --------
49, 25 36.59 CPU + Wait for CPU 31.71
HZH sqlplus@SLES-10-01 (TNS V1-V3) 78/1,314 [ 6%] 31
log buffer space 2.44
6/1,314 [ 0%] 6
log file switch (checkpoint in 1.63
4/1,314 [ 0%] 1
11, 1 31.30 log file parallel write 24.80
SYS oracle@SLES-10-01 (LGWR) 61/1,314 [ 5%] 0
CPU + Wait for CPU 5.28
13/1,314 [ 1%] 0
10, 1 9.76 db file async I/O submit 9.35
SYS oracle@SLES-10-01 (DBW0) 23/1,314 [ 2%] 0
22, 1 4.88 CPU + Wait for CPU 3.25
SYS oracle@SLES-10-01 (ARC3) 8/1,314 [ 1%] 0
18, 5 4.47 CPU + Wait for CPU 2.44
SYS oracle@SLES-10-01 (ARC0) 6/1,314 [ 0%] 0
Log archive I/O 1.22
3/1,314 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
-> 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 (Inst) % Activity Event Caused % Event
-------------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
11, 1( 1) 5.28 log buffer space 2.85
SYS oracle@SLES-10-01 (LGWR) 77/1,314 [ 6%] 0
log file switch (checkpoint in 1.63
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: HZH/hzh (Oct 17 09:30 to 09:51)
-> 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
-------------------- -------- ------------------------------ -------- -------
09:36:00 (2.0 min) 1 control file sequential read 1 0.41
09:38:00 (2.0 min) 3 db file sequential read 2 0.81
CPU + Wait for CPU 1 0.41
09:40:00 (2.0 min) 5 CPU + Wait for CPU 5 2.03
09:42:00 (2.0 min) 2 db file async I/O submit 2 0.81
09:44:00 (2.0 min) 1 CPU + Wait for CPU 1 0.41
09:46:00 (2.0 min) 1 CPU + Wait for CPU 1 0.41
09:48:00 (2.0 min) 232 CPU + Wait for CPU 115 46.75
log file parallel write 61 24.80
db file async I/O submit 21 8.54
09:50:00 (1.9 min) 1 CPU + Wait for CPU 1 0.41
-------------------------------------------------------------
End of Report
Report written to /home/oracle/ash_kingsql
原文：http://blog.itpub.net/28389881/viewspace-1301521/