报告内容:top等待事件,top sql,top sql命令类型,top session内容. 具体实现方式: sqlgt; @$oracle_home/rdbms/admin/ashrpt.sql
报告内容:top等待事件,top sql,top sql命令类型,top session内容.
具体实现方式:
sql> @$oracle_home/rdbms/admin/ashrpt.sql
current instance
~~~~~~~~~~~~~~~~
db id db name inst num instance
----------- ------------ -------- ------------
631770879 ora10 1 ora10
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
------------ -------- ------------ ------------ ------------
* 631770879 1 ora10 ora10 linux
defaults to current database
using database id: 631770879
defaults to current instance
using instance number: 1
ash samples in this workload repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
oldest ash sample available: 12-mar-08 12:22:57 [ 10246 mins in the past]
latest ash sample available: 19-mar-08 15:05:08 [ 4 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: 14:40 --输入开始时间
report begin time specified: 14:40
enter duration in minutes starting from begin time:
defaults to sysdate - begin_time
press enter to analyze till current time
enter value for duration: 20 --输入要统计的时间间隔
report duration specified: 20
using 19-mar-08 14:40:00 as report begin time
using 19-mar-08 15:00: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:
ext
----
.txt
specify the report name
~~~~~~~~~~~~~~~~~~~~~~~
the default report file name is ashrpt_1_0319_1500.txt. to use this name,
press to continue, otherwise enter an alternative.
enter value for report_name: --这里直接按回车,或者更改文件名.
using the report name ashrpt_1_0319_1500.txt
summary of all user input
-------------------------
format : text
db id : 631770879
inst num : 1
begin time : 19-mar-08 14:40:00
end time : 19-mar-08 15:00:00
slot width : default
report targets : 0
report name : ashrpt_1_0319_1500.txt
产生的报告内容如下:
oracle@linux:/server/soft> cat ashrpt_1_0319_1500.txt
ash report for ora10/ora10
db name db id instance inst num release rac host
------------ ----------- ------------ -------- ----------- --- ------------
ora10 631770879 ora10 1 10.2.0.1.0 no linux
cpus sga size buffer cache shared pool ash buffer size
---- ------------------ ------------------ ------------------ ------------------
1 160m (100%) 72m (45.0%) 68m (42.5%) 2.0m (1.3%)
analysis begin time: 19-mar-08 14:40:00
analysis end time: 19-mar-08 15:00:00
elapsed time: 20.0 (mins)
sample count: 29
average active sessions: 0.02
avg. active session per cpu: 0.02
report target: none specified
top user events db/inst: ora10/ora10 (mar 19 14:40 to 15:00)
avg active
event event class % activity sessions
----------------------------------- --------------- ---------- ----------
cpu + wait for cpu cpu 51.72 0.01
db file sequential read user i/o 6.90 0.00
null event other 6.90 0.00
-------------------------------------------------------------
top background events db/inst: ora10/ora10 (mar 19 14:40 to 15:00)
avg active
event event class % activity sessions
----------------------------------- --------------- ---------- ----------
cpu + wait for cpu cpu 17.24 0.00
os thread startup concurrency 17.24 0.00
activity over time db/inst: ora10/ora10 (mar 19 14:40 to 15:00)
-> 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
-------------------- -------- ------------------------------ -------- -------
14:40:00 (2.0 min) 15 cpu + wait for cpu 14 48.28
db file sequential read 1 3.45
14:42:00 (2.0 min) 5 cpu + wait for cpu 3 10.34
os thread startup 2 6.90
14:44:00 (2.0 min) 3 db file sequential read 1 3.45
null event 1 3.45
os thread startup 1 3.45
14:46:00 (2.0 min) 3 os thread startup 2 6.90
cpu + wait for cpu 1 3.45
14:50:00 (2.0 min) 1 cpu + wait for cpu 1 3.45
14:56:00 (2.0 min) 1 cpu + wait for cpu 1 3.45
14:58:00 (2.0 min) 1 null event 1 3.45
-------------------------------------------------------------
end of report
注:ash 的信息,以v$session为基础,每秒采样一次,记录活动会话等待的事件. 可以在v$active_session_hisotry视图中访问到相关的信息.
更多oracle相关信息见oracle 专题页面 ?tid=12
，