1.什么是ASH
ASH 英文全程是：Active Session History；
中文名字可以叫做：活动会话历史信息记录。
ASH是以V$SESSION视图为基础，每秒采样一次，目的是记录活动会话的等待事件。这样，就给我们这些DBA一种“找后账”的途径。
ASH是快速诊断数据库问题的一种参考途径，与AWR功效相仿。
2.记录一下ASH报告的生成过程
通过这个实验，完整记录一下生成ASH报告的过程。以便于有一个感性的认识。
ora10g@testsecdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 9 11:10:51 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, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sys@ora10g> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
4014372368 ORA10G 1 ora10g
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
------------ -------- ------------ ------------ ------------
* 4014372368 1 ORA10G ora10g testsecdb
Defaults to current database
Using database id: 4014372368
Defaults to current instance
Using instance number: 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 02-Sep-09 16:51:55 [ 9739 mins in the past]
Latest ASH sample available: 09-Sep-09 11:10:36 [ 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: 09/09/09 09:10:00
Report begin time specified: 09/09/09 09: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: 30
Report duration specified: 30
Using 09-Sep-09 09:10:00 as report begin time
Using 09-Sep-09 09:40:00 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_0909_0940.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: ash_report
Using the report name ash_report
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 4014372368
Inst num : 1
Begin time : 09-Sep-09 09:10:00
End time : 09-Sep-09 09:40:00
Slot width : Default
Report targets : 0
Report name : ash_report
ASH Report For ORA10G/ora10g
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORA10G 4014372368 ora10g 1 10.2.0.3.0 NO testsecdb
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
4 1,024M (100%) 760M (74.2%) 248M (24.2%) 8.0M (0.8%)
Analysis Begin Time: 09-Sep-09 09:10:00
Analysis End Time: 09-Sep-09 09:40:00
Elapsed Time: 30.0 (mins)
Sample Count: 15
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.00
Report Target: None specified
Top User Events DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
log file sync Commit 13.33 0.00
CPU + Wait for CPU CPU 6.67 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
control file parallel write System I/O 46.67 0.00
CPU + Wait for CPU CPU 20.00 0.00
log file parallel write System I/O 13.33 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
control file parallel write 46.67 "3","3","3" 46.67
files block# requests
log file parallel write 13.33 "2","4","2" 13.33
files blocks requests
log file sync 13.33 "3799","0","0" 6.67
buffer# NOT DEFINED NOT DEFINED
"4429","0","0" 6.67
-------------------------------------------------------------
Top Service/Module DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 80.00 UNNAMED 80.00
ora10g TOAD 9.7.2.5 6.67 UNNAMED 6.67
SYS$USERS 6.67 6.67
EM_PING 6.67 AGENT_STATUS_MARKE 6.67
-------------------------------------------------------------
Top Client IDs DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '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 1 6.67 0.00
-------------------------------------------------------------
Top SQL Statements DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
141kncypu4abz 1494918175 6.67 CPU + Wait for CPU 6.67
Select table_owner, table_name, db_link from sys.all_synonyms where synonym_name
=:Objectname and wner=:Name
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '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
----------------------------------------------------------------- ----------
SYSMAN.EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS 6.67
SYSMAN.MGMT_JOB_ENGINE.PROCESS_EMD_QUEUE_ENTRIES 6.67
-------------------------------------------------------------
Top Sessions DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '# 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
-------------------- ------------------------------ ------------------ --------
165, 1 46.67 control file parallel write 46.67
SYS oracle@testsecdb (CKPT) 7/1,800 [ 0%] 0
166, 1 20.00 log file parallel write 13.33
SYS oracle@testsecdb (LGWR) 2/1,800 [ 0%] 0
CPU + Wait for CPU 6.67
1/1,800 [ 0%] 0
167, 1 13.33 CPU + Wait for CPU 13.33
SYS oracle@testsecdb (DBW0) 2/1,800 [ 0%] 0
147, 4114 6.67 CPU + Wait for CPU 6.67
SYSTEM toad.exe 1/1,800 [ 0%] 0
158,19271 6.67 log file sync 6.67
SYSMAN oracle@testsecdb (J000) 1/1,800 [ 0%] 0
-------------------------------------------------------------
Error encountered in Top Blocking Sessions
While executing SQL statement: Len = 6845
SELECT dbms_ash_internal.get_sid_str(ash.blocking_session, ash.blocki
ng_session_serial#) as sess, ash.dim1_percentage, ash.event, ash.dim
12_percentage, dbms_ash_internal.get_blksid_user( ash.blocking_s
ession, ash.blocking_session_serial#), dbms_ash_internal.get_blk
sid_program( ash.blocking_session, ash.blocking_session_seri
al#, 30), dbms_ash_internal.get_blksid_active( ash.blocking_sess
ion, ash.blocking_session_serial#, 18), dbms_ash_internal.get_bl
ksid_xids( ash.blocking_session, ash.blocking_session_serial
#) FROM ( SELECT d12aa_ash.* FROM ( SELECT d12gb_ash.*, (
dim1_count*100/:ash_num_rows) as dim1_percentage, dense_rank()
over (order by dim1_count desc, blocking
_session, blocking_session_serial# ) as dim1_rank FROM ( SELE
CT blocking_session, blocking_session_serial# , event ,
count(*) as dim12_count, (count(*) * 100 / :ash_num_row
s) as dim12_percentage, rank() over (partition by
blocking_session, blocking_session_serial#
order by count(*) desc, event
) as dim12_rank, sum(count(*)) over (partition by
blocking_session, blocking_session_serial#)
as dim1_count FROM ( SELECT unified_ash.* FROM ( ( SELECT d.dbid
, inst.instance_number, 0 as snap_id, a.sample_id, a.sample_tim
e, a.session_id, a.session_serial#, a.session_type,
a.session_state, a.user_id, a.sql_id, a.sql_opcode, a
.sql_child_number, a.sql_plan_hash_value, a.plsql_entry_object_
id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_sub
program_id, a.service_hash, a.qc_session_id, a.qc_instance_id,
nvl(a.event, 'CPU + Wait for CPU') as event, nvl(a.event
_id, 1) as event_id, nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id, a.seq#,
a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text,
a.wait_time, a.time_waited, a.xid, a.blocking_session,
a.blocking_session_serial#, a.blocking_session_status, a
.current_obj#, a.current_file#, a.current_block#, a.program, a.
module, a.action, a.client_id FROM V$ACTIVE_SESSION_HISTORY a,
V$DATABASE d, V$INSTANCE inst WHERE 1=1 and :as
h_enable_mem_view = 1 and a.sample_time between :ash_mem_
Encountered error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small
Continuing to Next Section...
Top Sessions running PQs DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> 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:10:00 (2.0 min) 2 log file parallel write 1 6.67
log file sync 1 6.67
09:12:00 (3.0 min) 1 control file parallel write 1 6.67
09:15:00 (3.0 min) 1 control file parallel write 1 6.67
09:18:00 (3.0 min) 3 CPU + Wait for CPU 3 20.00
09:21:00 (3.0 min) 2 CPU + Wait for CPU 1 6.67
control file parallel write 1 6.67
09:24:00 (3.0 min) 2 control file parallel write 2 13.33
09:30:00 (3.0 min) 3 control file parallel write 1 6.67
log file parallel write 1 6.67
log file sync 1 6.67
09:33:00 (3.0 min) 1 control file parallel write 1 6.67
-------------------------------------------------------------
End of Report
Report written to ash_report
sys@ora10g>
同样的时间段，也可以使用html的格式生成报告。
3.使用EM生成ASH报告的方法
另外一种生成html的方法是使用Oracle Enterprise Manager工具。方法很简单，只需要进入到“Performance”，点击“Run ASH Report”稍等片刻即可得到相应的报告。
附一份在OEM中生成的ASH报告，进一步加深感性认识。
var _resetfooNames=["event"];var _foo_Validations=['new SimpleDateFormat(\'M/d/yy\').validate(%value%)'];function _fooValidater(form){var fl = _multiValidate(form,[0,"startDate",0,1,0,"endDate",0,1]);if (fl.length>0){alert('Form. validation failures:'+fl);return false;}else{return true;}}var _foo_Labels={'startDate':'Start Date','endDate':'End Date'};var _foo_Formats=['Value "%value%" in "%label%" is not a date.'];function _submitOnEnter(e, frm) { var kc; if(window.event) kc=window.event.keyCode; else if(e) kc=e.which; else return true; return (kc!=13);}
4.小结
使用ASH报告可以很便捷的获取系统当前及短期“逝去”的运行情况，对于诊断系统性能问题有很大的帮助。
Good luck.
secooler
09.09.09
-- The End --
ASH 英文全程是：Active Session History；
中文名字可以叫做：活动会话历史信息记录。
ASH是以V$SESSION视图为基础，每秒采样一次，目的是记录活动会话的等待事件。这样，就给我们这些DBA一种“找后账”的途径。
ASH是快速诊断数据库问题的一种参考途径，与AWR功效相仿。
2.记录一下ASH报告的生成过程
通过这个实验，完整记录一下生成ASH报告的过程。以便于有一个感性的认识。
ora10g@testsecdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 9 11:10:51 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, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sys@ora10g> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
4014372368 ORA10G 1 ora10g
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
------------ -------- ------------ ------------ ------------
* 4014372368 1 ORA10G ora10g testsecdb
Defaults to current database
Using database id: 4014372368
Defaults to current instance
Using instance number: 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 02-Sep-09 16:51:55 [ 9739 mins in the past]
Latest ASH sample available: 09-Sep-09 11:10:36 [ 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: 09/09/09 09:10:00
Report begin time specified: 09/09/09 09: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: 30
Report duration specified: 30
Using 09-Sep-09 09:10:00 as report begin time
Using 09-Sep-09 09:40:00 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_0909_0940.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: ash_report
Using the report name ash_report
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 4014372368
Inst num : 1
Begin time : 09-Sep-09 09:10:00
End time : 09-Sep-09 09:40:00
Slot width : Default
Report targets : 0
Report name : ash_report
ASH Report For ORA10G/ora10g
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORA10G 4014372368 ora10g 1 10.2.0.3.0 NO testsecdb
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
4 1,024M (100%) 760M (74.2%) 248M (24.2%) 8.0M (0.8%)
Analysis Begin Time: 09-Sep-09 09:10:00
Analysis End Time: 09-Sep-09 09:40:00
Elapsed Time: 30.0 (mins)
Sample Count: 15
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.00
Report Target: None specified
Top User Events DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
log file sync Commit 13.33 0.00
CPU + Wait for CPU CPU 6.67 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
control file parallel write System I/O 46.67 0.00
CPU + Wait for CPU CPU 20.00 0.00
log file parallel write System I/O 13.33 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
control file parallel write 46.67 "3","3","3" 46.67
files block# requests
log file parallel write 13.33 "2","4","2" 13.33
files blocks requests
log file sync 13.33 "3799","0","0" 6.67
buffer# NOT DEFINED NOT DEFINED
"4429","0","0" 6.67
-------------------------------------------------------------
Top Service/Module DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 80.00 UNNAMED 80.00
ora10g TOAD 9.7.2.5 6.67 UNNAMED 6.67
SYS$USERS 6.67 6.67
EM_PING 6.67 AGENT_STATUS_MARKE 6.67
-------------------------------------------------------------
Top Client IDs DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '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 1 6.67 0.00
-------------------------------------------------------------
Top SQL Statements DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
141kncypu4abz 1494918175 6.67 CPU + Wait for CPU 6.67
Select table_owner, table_name, db_link from sys.all_synonyms where synonym_name
=:Objectname and wner=:Name
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '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
----------------------------------------------------------------- ----------
SYSMAN.EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS 6.67
SYSMAN.MGMT_JOB_ENGINE.PROCESS_EMD_QUEUE_ENTRIES 6.67
-------------------------------------------------------------
Top Sessions DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> '# 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
-------------------- ------------------------------ ------------------ --------
165, 1 46.67 control file parallel write 46.67
SYS oracle@testsecdb (CKPT) 7/1,800 [ 0%] 0
166, 1 20.00 log file parallel write 13.33
SYS oracle@testsecdb (LGWR) 2/1,800 [ 0%] 0
CPU + Wait for CPU 6.67
1/1,800 [ 0%] 0
167, 1 13.33 CPU + Wait for CPU 13.33
SYS oracle@testsecdb (DBW0) 2/1,800 [ 0%] 0
147, 4114 6.67 CPU + Wait for CPU 6.67
SYSTEM toad.exe 1/1,800 [ 0%] 0
158,19271 6.67 log file sync 6.67
SYSMAN oracle@testsecdb (J000) 1/1,800 [ 0%] 0
-------------------------------------------------------------
Error encountered in Top Blocking Sessions
While executing SQL statement: Len = 6845
SELECT dbms_ash_internal.get_sid_str(ash.blocking_session, ash.blocki
ng_session_serial#) as sess, ash.dim1_percentage, ash.event, ash.dim
12_percentage, dbms_ash_internal.get_blksid_user( ash.blocking_s
ession, ash.blocking_session_serial#), dbms_ash_internal.get_blk
sid_program( ash.blocking_session, ash.blocking_session_seri
al#, 30), dbms_ash_internal.get_blksid_active( ash.blocking_sess
ion, ash.blocking_session_serial#, 18), dbms_ash_internal.get_bl
ksid_xids( ash.blocking_session, ash.blocking_session_serial
#) FROM ( SELECT d12aa_ash.* FROM ( SELECT d12gb_ash.*, (
dim1_count*100/:ash_num_rows) as dim1_percentage, dense_rank()
over (order by dim1_count desc, blocking
_session, blocking_session_serial# ) as dim1_rank FROM ( SELE
CT blocking_session, blocking_session_serial# , event ,
count(*) as dim12_count, (count(*) * 100 / :ash_num_row
s) as dim12_percentage, rank() over (partition by
blocking_session, blocking_session_serial#
order by count(*) desc, event
) as dim12_rank, sum(count(*)) over (partition by
blocking_session, blocking_session_serial#)
as dim1_count FROM ( SELECT unified_ash.* FROM ( ( SELECT d.dbid
, inst.instance_number, 0 as snap_id, a.sample_id, a.sample_tim
e, a.session_id, a.session_serial#, a.session_type,
a.session_state, a.user_id, a.sql_id, a.sql_opcode, a
.sql_child_number, a.sql_plan_hash_value, a.plsql_entry_object_
id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_sub
program_id, a.service_hash, a.qc_session_id, a.qc_instance_id,
nvl(a.event, 'CPU + Wait for CPU') as event, nvl(a.event
_id, 1) as event_id, nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id, a.seq#,
a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text,
a.wait_time, a.time_waited, a.xid, a.blocking_session,
a.blocking_session_serial#, a.blocking_session_status, a
.current_obj#, a.current_file#, a.current_block#, a.program, a.
module, a.action, a.client_id FROM V$ACTIVE_SESSION_HISTORY a,
V$DATABASE d, V$INSTANCE inst WHERE 1=1 and :as
h_enable_mem_view = 1 and a.sample_time between :ash_mem_
Encountered error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small
Continuing to Next Section...
Top Sessions running PQs DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORA10G/ora10g (Sep 09 09:10 to 09:40)
-> 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:10:00 (2.0 min) 2 log file parallel write 1 6.67
log file sync 1 6.67
09:12:00 (3.0 min) 1 control file parallel write 1 6.67
09:15:00 (3.0 min) 1 control file parallel write 1 6.67
09:18:00 (3.0 min) 3 CPU + Wait for CPU 3 20.00
09:21:00 (3.0 min) 2 CPU + Wait for CPU 1 6.67
control file parallel write 1 6.67
09:24:00 (3.0 min) 2 control file parallel write 2 13.33
09:30:00 (3.0 min) 3 control file parallel write 1 6.67
log file parallel write 1 6.67
log file sync 1 6.67
09:33:00 (3.0 min) 1 control file parallel write 1 6.67
-------------------------------------------------------------
End of Report
Report written to ash_report
sys@ora10g>
同样的时间段，也可以使用html的格式生成报告。
3.使用EM生成ASH报告的方法
另外一种生成html的方法是使用Oracle Enterprise Manager工具。方法很简单，只需要进入到“Performance”，点击“Run ASH Report”稍等片刻即可得到相应的报告。
附一份在OEM中生成的ASH报告，进一步加深感性认识。
 |
| |||||||
 | | |||||||
| ||||||||
| Database Instance: ora10g > Run ASH Report | Logged in As SYS |
| Run ASH Report |
|
| Specify the time period for the report. |  |
|
| ||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Top Client IDsNo data exists for this section of the report. Back to Load Profile Top SQL Command Types
Back to Load Profile
Top SQLBack to TopTop SQL Statements
|
Top SQL using literals
No data exists for this section of the report.
Complete List of SQL Text
Top PL/SQL Procedures
No data exists for this section of the report.
Top Sessions
Back to TopTop Sessions
- '# 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 |
|---|---|---|---|---|---|---|---|
| 132, 2518 | 50.00 | CPU + Wait for CPU | 50.00 | DBSNMP | OMS | 5/300 [ 2%] | 0 |
| 167, 1 | 30.00 | CPU + Wait for CPU | 30.00 | SYS | oracle@testsecdb (DBW0) | 3/300 [ 1%] | 0 |
| 165, 1 | 20.00 | control file parallel write | 20.00 | SYS | oracle@testsecdb (CKPT) | 2/300 [ 1%] | 0 |
Back to Top Sessions
Back to Top
Top Blocking Sessions
No data exists for this section of the report.
Back to Top Sessions
Back to Top
Top Sessions running PQs
No data exists for this section of the report.
Back to Top Sessions
Back to Top
Top Objects/Files/Latches
Back to Top
Top DB Objects
No data exists for this section of the report.
Back to Top Objects/Files/Latches
Back to Top
Top DB Files
No data exists for this section of the report.
Back to Top Objects/Files/Latches
Back to Top
Top Latches
No data exists for this section of the report.
Back to Top Objects/Files/Latches
Back to Top
Activity Over Time
- 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 Time (Duration) | Slot Count | Event | Event Count | % Event |
|---|---|---|---|---|
| 12:12:20 (40 secs) | 1 | control file parallel write | 1 | 10.00 |
| 12:14:00 (1.0 min) | 2 | CPU + Wait for CPU | 2 | 20.00 |
| 12:15:00 (1.0 min) | 6 | CPU + Wait for CPU | 6 | 60.00 |
| 12:16:00 (1.0 min) | 1 | control file parallel write | 1 | 10.00 |
End of Report
| ||
|  | |
| ||
| ||
| Database | | | Setup | | | Preferences | | | Help | | | Logout |
| Copyright © 1996, 2006, Oracle. All rights reserved. |
| About Oracle Enterprise Manager 10g Database Control |
4.小结
使用ASH报告可以很便捷的获取系统当前及短期“逝去”的运行情况，对于诊断系统性能问题有很大的帮助。
Good luck.
secooler
09.09.09
-- The End --
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-614228/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-614228/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
