Active Session History (ASH)
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ASH（Active Session History）——概述（1）！Session
- Active Session History (ASH) performed an emergency flushSessionORM
- ASH（Active Session History）——根據SID生產報告（3）！Session
- WRH$_ACTIVE_SESSION_HISTORYSession
- Active Session History ReportsSession
- Clean WRH$_ACTIVE_SESSION_HISTORY in SYSAUXSessionUX
- v$active_session_history檢視Session
- v$active_session_history檢視[轉]Session
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- 11g v$active_session_history的新增列Session
- 轉載--V$ACTIVE_SESSION_HISTORY檢視的使用Session
- V$ACTIVE_SESSION_HISTORY 檢視中包含的資訊Session
- 一次WRH$_ACTIVE_SESSION_HISTORY問題處理Session
- 通過active_session_history我們能得到什麼Session
- 【ASH】如何匯出檢視DBA_HIST_ACTIVE_SESS_HISTORY的查詢結果資料
- v$active_session_history的wait_time和time_waited 列SessionAI
- 從dba_hist_active_sess_history裡尋找session間的等待關係Session
- 11G 中的V$ACTIVE_SESSION_HISTORY 檢視沒有資料Session
- ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇Oracle 10gUXSession
- v$action_session_historySession
- DBA_HIST_ACTIVE_SESS_HISTORY應用
- Oracle ASH和Session Tracing（ZT）OracleSession
- [Oracle Script] check active sessionOracleSession
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢
- active session的準確定義！Session
- 加快dba_hist_active_sess_history的查詢速度
- 11gr2 rac WRH$_ACTIVE_SESSION_HISTORY未自動清理導致SYSAUX空間過度增長SessionUX
- 10.27 V$SESSION_WAIT_HISTORYSessionAI
- [20130125]利用v$active_session_history檢視解決資料庫問題.txtSession資料庫
- ASH可以生成指定的session或sql_id的報告，ASH和AWR的區別SessionSQL
- 通過shell指令碼快速定位active session問題指令碼Session
- V$SESSION.STATUS='ACTIVE' AND WAIT_EVENT='Idle'SessionAI
- 巧用dba_hist_active_sess_history跟蹤某個時間段內SQLSQL
- oracle ASHOracle
- 如何透過dba_hist_active_sess_history分析歷史資料庫效能問題資料庫
- 【DBA】如何通過dba_hist_active_sess_history分析資料庫歷史效能問題資料庫
- oracle awr ashOracle