ASH侧重于当前数据库中活动会话的信息分析。
[oracle@linux oracle]$ sqlplus / as sysdba @?/rdbms/admin/ashrpt.sql
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 1 06:36:43 2011
Copyright (c) , Oracle.
All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current Instance
~~~~~~~~~~~~~~~~
Inst Num Instance
----------- ------------ -------- ------------
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:
--生成html格式的ash报告。
Type Specified:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Inst Num DB Name
------------ -------- ------------ ------------ ------------
Defaults to current database
Using database id:
Defaults to current instance
Using instance number: 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available:
01-11月-11 03:34:14
183 mins in the past]
Latest ASH sample available:
01-11月-11 06:36:43
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
To specify relative begin time: (start with '-' sign)
-[HH24:]MI
Examples: -1:15
(SYSDATE - 1 Hr 15 Mins)
(SYSDATE - 25 Mins)
Defaults to -15 mins
输入 begin_time 的值:
--采集30分钟以前的数据。
Report begin time specified: -30
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
输入 duration 的值:
--这里默认是当前时间。
Report duration specified:
Using 01-11月-11 06:07:51 as report begin time
Using 01-11月-11 06:38:17 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
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_.html.
To use this name,
press &return& to continue, otherwise enter an alternative.
输入 report_name 的值:
/u01/app/oracle/ash.html
--生成报告的路径。
Report written to /u01/app/oracle/ash.html
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：427761次
积分：9744
积分：9744
排名：第636名
原创：539篇
转载：82篇
评论：51条
(2)(1)(1)(1)(3)(15)(15)(38)(6)(3)(2)(5)(2)(8)(1)(8)(7)(19)(8)(2)(1)(26)(7)(10)(16)(7)(10)(50)(14)(4)(33)(50)(57)(24)(1)(2)(5)(13)(26)(1)(16)(8)(6)(24)(35)(30)ASH报告生成详解_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
ASH报告生成详解
A​S​H​报​告​生​成​详​解
阅读已结束，如果下载本文需要使用
想免费下载本文？
把文档贴到Blog、BBS或个人站等：
普通尺寸(450*500pix)
较大尺寸(630*500pix)
你可能喜欢Oracle AWR与ASH性能报告深入解析_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
喜欢此文档的还喜欢
Oracle AWR与ASH性能报告深入解析
O​r​a​c​l​e​ ​A​W​R​与​A​S​H​性​能​报​告​深​入​解​析
阅读已结束，如果下载本文需要使用
想免费下载本文？
把文档贴到Blog、BBS或个人站等：
普通尺寸(450*500pix)
较大尺寸(630*500pix)
你可能喜欢一, 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
Copyright (c) , Oracle.& All rights reserved.
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
----------- ------------ -------- ------------
& 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
------------ -------- ------------ ------------ ------------
* &&&&&&& 1 TEST&&&&&&&& test&&&&&&&& even.oracle.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& com
Defaults to current database
Using database id:
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_.html
上面报告输出的路径是自己写的，不写会输出当前用户的目录下。我的是在/home/oracle/ash_.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:&&&&&
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:
(TNS V1-V3)
17:09:22.366
*** SERVICE NAME:(SYS$USERS)
17:09:22.365
*** SESSION ID:(150.615)
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&&&
17:08:04.&,156,802,61,&&,0,0,,1,0,166,1,0,0,,,0,0,,2,,0,0,0,&
(J000)&,& &,& &,&&
&&&ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END&&&
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,
FORCE_MATCHING_SIGNATURE, PROGRAM, MODULE, ACTION, CLIENT_ID
&&&ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END&&&
&&&ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN&&&
17:09:26.&,147,334,61,&&,,0,166,1,0,0,,345,1,,,0,31756,0,&OMS&,&OEM.SystemPool&,&&,&&
17:09:26.&,166,1,0,&&,0,0,,0,,0,0,0,,,0,0,,,1,0,31484,0,&
(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,
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&&&
17:09:26.&,147,334,61,&&,,0,166,1,0,0,,345,1,,,0,31756,0,&OMS&,&OEM.SystemPool&,&&,&&
17:09:26.&,166,1,0,&&,0,0,,0,,0,0,0,,,0,0,,,1,0,31484,0,&
(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,
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&&&
17:09:26.&,147,334,61,&&,,0,166,1,0,0,,345,1,,,0,31756,0,&OMS&,&OEM.SystemPool&,&&,&&
17:09:26.&,166,1,0,&&,0,0,,0,,0,0,0,,,0,0,,,1,0,31484,0,&
(LGWR)&,&&,&&,&&
17:08:04.&,156,802,61,&&,0,0,,1,0,166,1,0,0,,,0,0,,2,,0,0,0,&
(J000)&,& &,& &,&&
&&&ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END&&&
四, 使用sql检索ASH数据
SELECT&& sql_id, count(*),round(count(*)/sum(count(*)) over (), 2) pctload
FROM&&&& v$active_session_history
WHERE&&& sample_time & sysdate -1/24/60 and
&&&&&&&& session_type && 'BACKGROUND'
GROUP BY sql_id
ORDER BY count(*)
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：120511次
积分：2749
积分：2749
排名：第5736名
原创：157篇
转载：11篇
(1)(2)(5)(5)(7)(6)(3)(1)(10)(24)(7)(4)(8)(1)(4)(3)(2)(2)(3)(7)(5)(6)(10)(35)(7)