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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 10.27 V$SESSION_WAIT_HISTORYSessionAI
- 【DBA】DBA_HIST_ACTIVE_SESS_HISTORY檢視用途
- ASH可以生成指定的session或sql_id的報告，ASH和AWR的區別SessionSQL
- [20211223]tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [Active Learning] Multi-Criteria-based Active Learning
- Javascript history物件的 history.pushState() 和 history.replaceState() 方法JavaScript物件
- statspack、awr、addm,ash影片分享
- spring.profiles.active=@profiles.active@的含義Spring
- Active Record Associations
- AP History
- history物件物件
- history.go()Go
- history.forward()Forward
- history.back()
- history.length
- JavaScript history 物件JavaScript物件
- History- Linux下定製個性化history記錄Linux
- history.back(-1)和history.go(-1)區別Go
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- mysqldump: Error: Binlogging on server not activeMySqlErrorServer
- MySQL History List LengthMySql
- 常見命令-history
- HMAC: Introduction, History, and ApplicationsMacAPP
- linux-history命令Linux
- vue History模式 laravelVue模式Laravel
- [擴充套件] hieu-le active 判斷導航元素的 active 狀態套件
- [ABC163E] Active Infants
- WebKit Insie: Active 樣式表WebKit
- CSS E:active 選擇器CSS
- Linux history 命令詳解Linux
- nginx 配置 vue History模式NginxVue模式
- Dialogue: New Chapter in the History of ComputingAPT
- Linux之history使用技巧Linux
- Vue路由History模式分析Vue路由模式
- history 模式反向代理配置模式