Active Session History (ASH)：
The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
说白了，ASH里面记录的基本上全是V$ACTIVE_SESSION_HISTORY视图里面的东西。可以记录活动会话的等待事件。
ASH是快速诊断数据库问题的一种参考途径，与AWR功效相仿。
Running the ashrpt.sql Report：
和AWR一样，可以执行脚本来得到ASH报告。可以选在txt或者HTML。
SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
4213800794 ORA11G 1 ora11g
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
------------ -------- ------------ ------------ ------------
* 4213800794 1 ORA11G ora11g Ray
Defaults to current database
Using database id: 4213800794
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: 12-Mar-15 18:25:25 [ 1033 mins in the past]
Latest ASH sample available: 13-Mar-15 11:36:34 [ 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: -15 ---通过上述表述，我们很清楚的能看到ASH的开始时间有两种输入方式，一种是直接输入确定的时间。另外一种是在系统时间之前的多长时间
Report begin time specified: -15
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time --按enter来分析截至目前的时间
Enter value for duration:
Using 13-Mar-15 11:26:23 as report begin time
Using 13-Mar-15 11:43:10 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_0313_1143.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: ---对产生的ASH命名。
Using the report name ashrpt_1_0313_1143.txt
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 4213800794
Inst num : 1
Begin time : 13-Mar-15 11:26:23
End time : 13-Mar-15 11:43:10
Slot width : Default
Report targets : 0
Report name : ashrpt_1_0313_1143.txt
ASH Report For ORA11G/ora11g
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORA11G 4213800794 ora11g 1 11.2.0.3.0 NO Ray
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
1 593M (100%) 124M (20.9%) 228M (38.4%) 2.0M (0.3%)
Analysis Begin Time: 13-Mar-15 11:26:23
Analysis End Time: 13-Mar-15 11:43:10
Elapsed Time: 16.8 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 14
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 7.14 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 57.14 0.01
db file async I/O submit System I/O 14.29 0.00
log file parallel write System I/O 14.29 0.00
os thread startup Concurrency 7.14 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
db file async I/O submit 14.29 "1","0","0" 14.29
requests interrupt timeout
log file parallel write 14.29 "1","3","1" 7.14
files blocks requests
"1","5","1" 7.14
-------------------------------------------------------------
Top Service/Module DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 92.86 UNNAMED 92.86
SYS$USERS EM_PING 7.14 AGENT_STATUS_MARKE 7.14
-------------------------------------------------------------
Top Client IDs DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '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 2 14.29 0.00
-------------------------------------------------------------
Top Phases of Execution DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 7.14 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
089dbukv1aanh 1388734953 1 7.14
CPU + Wait for CPU 7.14 SELECT STATEMENT 7.14
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
aykvshm7zsabd 300462700 1 7.14
CPU + Wait for CPU 7.14 SORT - ORDER BY 7.14
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Sampled #
SQL ID PlanHash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
089dbukv1aanh 1388734953 1 7.14
SELECT STATEMENT 7.14 CPU + Wait for CPU 7.14
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
aykvshm7zsabd 300462700 1 7.14
SORT - ORDER BY 7.14 CPU + Wait for CPU 7.14
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '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 7.14
SQL 7.14
-------------------------------------------------------------
Top Java Workload DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '# 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
-------------------- ------------------------------ ------------------ --------
3, 1 50.00 CPU + Wait for CPU 50.00
SYS oracle@Ray (PSP0) 7/1,007 [ 1%] 0
10, 1 14.29 db file async I/O submit 14.29
SYS oracle@Ray (DBW0) 2/1,007 [ 0%] 0
11, 1 14.29 log file parallel write 14.29
SYS oracle@Ray (LGWR) 2/1,007 [ 0%] 0
15, 1 14.29 CPU + Wait for CPU 7.14
SYS oracle@Ray (MMON) 1/1,007 [ 0%] 0
os thread startup 7.14
1/1,007 [ 0%] 0
31, 351 7.14 CPU + Wait for CPU 7.14
SYS oracle@Ray (J000) 1/1,007 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> 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
-------------------- -------- ------------------------------ -------- -------
11:26:23 (1.6 min) 4 db file async I/O submit 2 14.29
CPU + Wait for CPU 1 7.14
log file parallel write 1 7.14
11:28:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:30:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:32:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:34:00 (2.0 min) 3 CPU + Wait for CPU 2 14.29
os thread startup 1 7.14
11:36:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:38:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:40:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:42:00 (1.2 min) 1 log file parallel write 1 7.14
-------------------------------------------------------------
End of Report
Report written to ashrpt_1_0313_1143.txt
如果实在RAC环境中，我们需要指定其中的某一个实例，我们就可以使用ashrpti.sql脚本来调取ASH报告。
The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
说白了，ASH里面记录的基本上全是V$ACTIVE_SESSION_HISTORY视图里面的东西。可以记录活动会话的等待事件。
ASH是快速诊断数据库问题的一种参考途径，与AWR功效相仿。
Running the ashrpt.sql Report：
和AWR一样，可以执行脚本来得到ASH报告。可以选在txt或者HTML。
SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
4213800794 ORA11G 1 ora11g
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
------------ -------- ------------ ------------ ------------
* 4213800794 1 ORA11G ora11g Ray
Defaults to current database
Using database id: 4213800794
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: 12-Mar-15 18:25:25 [ 1033 mins in the past]
Latest ASH sample available: 13-Mar-15 11:36:34 [ 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: -15 ---通过上述表述，我们很清楚的能看到ASH的开始时间有两种输入方式，一种是直接输入确定的时间。另外一种是在系统时间之前的多长时间
Report begin time specified: -15
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time --按enter来分析截至目前的时间
Enter value for duration:
Using 13-Mar-15 11:26:23 as report begin time
Using 13-Mar-15 11:43:10 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_0313_1143.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: ---对产生的ASH命名。
Using the report name ashrpt_1_0313_1143.txt
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 4213800794
Inst num : 1
Begin time : 13-Mar-15 11:26:23
End time : 13-Mar-15 11:43:10
Slot width : Default
Report targets : 0
Report name : ashrpt_1_0313_1143.txt
ASH Report For ORA11G/ora11g
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORA11G 4213800794 ora11g 1 11.2.0.3.0 NO Ray
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
1 593M (100%) 124M (20.9%) 228M (38.4%) 2.0M (0.3%)
Analysis Begin Time: 13-Mar-15 11:26:23
Analysis End Time: 13-Mar-15 11:43:10
Elapsed Time: 16.8 (mins)
Begin Data Source: V$ACTIVE_SESSION_HISTORY
End Data Source: V$ACTIVE_SESSION_HISTORY
Sample Count: 14
Average Active Sessions: 0.01
Avg. Active Session per CPU: 0.01
Report Target: None specified
Top User Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 7.14 0.00
-------------------------------------------------------------
Top Background Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 57.14 0.01
db file async I/O submit System I/O 14.29 0.00
log file parallel write System I/O 14.29 0.00
os thread startup Concurrency 7.14 0.00
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Event % Event P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1 Parameter 2 Parameter 3
-------------------------- -------------------------- --------------------------
db file async I/O submit 14.29 "1","0","0" 14.29
requests interrupt timeout
log file parallel write 14.29 "1","3","1" 7.14
files blocks requests
"1","5","1" 7.14
-------------------------------------------------------------
Top Service/Module DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 92.86 UNNAMED 92.86
SYS$USERS EM_PING 7.14 AGENT_STATUS_MARKE 7.14
-------------------------------------------------------------
Top Client IDs DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '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 2 14.29 0.00
-------------------------------------------------------------
Top Phases of Execution DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Avg Active
Phase of Execution % Activity Sessions
------------------------------ ---------- ----------
SQL Execution 7.14 0.00
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Sampled #
SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Event % Event Top Row Source % RwSrc
------------------------------ ------- --------------------------------- -------
089dbukv1aanh 1388734953 1 7.14
CPU + Wait for CPU 7.14 SELECT STATEMENT 7.14
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
aykvshm7zsabd 300462700 1 7.14
CPU + Wait for CPU 7.14 SORT - ORDER BY 7.14
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
Sampled #
SQL ID PlanHash of Executions % Activity
----------------------- -------------------- -------------------- --------------
Row Source % RwSrc Top Event % Event
---------------------------------------- ------- ----------------------- -------
089dbukv1aanh 1388734953 1 7.14
SELECT STATEMENT 7.14 CPU + Wait for CPU 7.14
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
aykvshm7zsabd 300462700 1 7.14
SORT - ORDER BY 7.14 CPU + Wait for CPU 7.14
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '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 7.14
SQL 7.14
-------------------------------------------------------------
Top Java Workload DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> '# 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
-------------------- ------------------------------ ------------------ --------
3, 1 50.00 CPU + Wait for CPU 50.00
SYS oracle@Ray (PSP0) 7/1,007 [ 1%] 0
10, 1 14.29 db file async I/O submit 14.29
SYS oracle@Ray (DBW0) 2/1,007 [ 0%] 0
11, 1 14.29 log file parallel write 14.29
SYS oracle@Ray (LGWR) 2/1,007 [ 0%] 0
15, 1 14.29 CPU + Wait for CPU 7.14
SYS oracle@Ray (MMON) 1/1,007 [ 0%] 0
os thread startup 7.14
1/1,007 [ 0%] 0
31, 351 7.14 CPU + Wait for CPU 7.14
SYS oracle@Ray (J000) 1/1,007 [ 0%] 0
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORA11G/ora11g (Mar 13 11:26 to 11:43)
-> 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
-------------------- -------- ------------------------------ -------- -------
11:26:23 (1.6 min) 4 db file async I/O submit 2 14.29
CPU + Wait for CPU 1 7.14
log file parallel write 1 7.14
11:28:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:30:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:32:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:34:00 (2.0 min) 3 CPU + Wait for CPU 2 14.29
os thread startup 1 7.14
11:36:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:38:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:40:00 (2.0 min) 1 CPU + Wait for CPU 1 7.14
11:42:00 (1.2 min) 1 log file parallel write 1 7.14
-------------------------------------------------------------
End of Report
Report written to ashrpt_1_0313_1143.txt
如果实在RAC环境中，我们需要指定其中的某一个实例，我们就可以使用ashrpti.sql脚本来调取ASH报告。
欢迎大家批评指正：
QQ交流群：300392987
论 坛：http://www.oraclefreebase.com
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29210156/viewspace-1129570/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29210156/viewspace-1129570/
1917
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
