The ASH_REPORT procedure displays the Active Session History (ASH) report in text.
Syntax
PROCEDURE ASH_REPORT(BTIME IN DATE,
ETIME IN DATE,
SQL_ID IN VARCHAR2 DEFAULT NULL,
TRACE_ID IN VARCHAR2 DEFAULT NULL,
WAIT_CLASS IN VARCHAR2 DEFAULT NULL,
REPORT_TYPE IN VARCHAR2 DEFAULT 'text'
);
Parameters
Parameter |
Description |
|---|---|
| BTIME | The sampling start time. |
| ETIME | The sampling end time. |
| SQL_ID | The SQL ID of the SQL statement to be sampled. If you do not specify this parameter or set the value to NULL, the SQL ID of the statement to be sampled is not limited. |
| TRACE_ID | The trace ID of the SQL statement to be sampled. If you do not specify this parameter or set the value to NULL, the trace ID of the SQL statement to be sampled is not limited. |
| WAIT_CLASS | The type of the wait event to be sampled. The valid values of WAIT_CLASS are the same as those of the GV$ACTIVE_SESSION_HISTORY.WAIT_CLASS field. If you do not specify this parameter or set the value to NULL, the type of wait events is not limited. |
| REPORT_TYPE | The type of the report. At present, only the TEXT type is supported. This parameter is optional. |
Considerations
You can query the GV$ACTIVE_SESSION_HISTORY view for related ASH information.
Examples
Call the ASH_REPORT procedure and specify the start time and end time for diagnostics. The ASH report information is displayed.
obclient> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-12-25 10:15:00', '2023-12-25 10:19:02' );
A sample output is as follows:
REPORT:
# ASH Report
----
Sample Begin: 2023-12-25 10:15:00
Sample End: 2023-12-25 10:19:02
----------
Analysis Begin Time: 2023-12-25 10:15:00
Analysis End Time: 2023-12-25 10:19:02
Elapsed Time: 242
Num of Sample: 611
Num of Events: 611
Average Active Sessions: 2.52
----
## Top User Events:
+----------------------------------------------------------------+--------------------+---------+
| Event| WAIT_CLASS| % Event|
+----------------------------------------------------------------+--------------------+---------+
| sleep wait| IDLE| 40.26%|
| ha service condition wait| CONCURRENCY| 39.61%|
| CPU + Wait for CPU| OTHER| 18.49%|
| exec inner sql wait| OTHER| 0.82%|
| palf write| SYSTEM_IO| 0.82%|
+----------------------------------------------------------------+--------------------+---------+
## Top Events P1/P2/P3 Value:
+----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
| Event| % Event| % Activity| Max P1/P2/P3| Parameter 1| Parameter 2| Parameter 3|
+----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
| sleep wait| 40.26%| 40.262%| 5000000, 0, 0| sleep_interval| | |
| ha service condition wait| 39.61%| 39.607%| 140638547515008, 0, 0| address| | |
| CPU + Wait for CPU| 18.49%| 18.494%| 0, 0, 0| | | |
| exec inner sql wait| 0.82%| 0.818%| 40004, 3221699850, 0|wait inner sql class| inner session id| |
| palf write| 0.82%| 0.818%| 296, 39702528, 4096| fd| offset| size|
+----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
## Top Phase of Execution:
+----------------------------------------+------------+--------------+--------------------+
| Phase of Execution| % Activity| Sample Count| Avg Active Sessions|
+----------------------------------------+------------+--------------+--------------------+
| IN_SQL_EXECUTION| 0.655%| 4| 0.017|
| IN_COMMITTING| 0.327%| 2| 0.008|
| IN_STORAGE_READ| 0.327%| 2| 0.008|
| IN_STORAGE_WRITE| 0.164%| 1| 0.004|
| IN_PARSE| 0.000%| 0| 0.000|
| IN_PL_PARSE| 0.000%| 0| 0.000|
| IN_PLAN_CACHE| 0.000%| 0| 0.000|
| IN_SQL_OPTIMIZE| 0.000%| 0| 0.000|
| IN_PX_EXECUTION| 0.000%| 0| 0.000|
| IN_SEQUENCE_LOAD| 0.000%| 0| 0.000|
| IN_REMOTE_DAS_EXECUTION| 0.000%| 0| 0.000|
+----------------------------------------+------------+--------------+--------------------+
## Top SQL with Top Events
- All events included.
- Empty ''SQL Text'' if it is PL/SQL query
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
| SQL ID| PLAN ID| Sampled # of Executions| Event| % Event| SQL Text|
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
| | 0| 246| sleep wait| 40.26%| |
| | 0| 242| ha service condition wait| 39.61%| |
| | 0| 106| CPU + Wait for CPU| 17.35%| |
| | 0| 5| palf write| 0.82%| |
| | 0| 3| exec inner sql wait| 0.49%| |
| EAE53285C197E30A688A3C03842EA4D7| 7295| 1| CPU + Wait for CPU| 0.16%| insert into t1 values (?, ?)|
| 015E90DA7A2EB4B2B3DC22F2F4D75385| 7307| 1| CPU + Wait for CPU| 0.16%| insert into t5 values (?, ?)|
| 20BC5A371C0DCAAE023418FFE73A91B5| 7359| 1| CPU + Wait for CPU| 0.16%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
| 14BBBC93DF2A7B3CB19BEDF7804CF846| 7368| 1| CPU + Wait for CPU| 0.16%| SELECT OBJ_OWNER, OBJ_NAME, PLSQL_ENTRY_SUBPROGRAM_NAME|
| 24D486F20861EBD9E0217692DB0E9C61| 7266| 1| CPU + Wait for CPU| 0.16%| update __all_weak_read_service set min_version=17034106|
| 24D486F20861EBD9E0217692DB0E9C61| 7266| 1| exec inner sql wait| 0.16%| update __all_weak_read_service set min_version=17034106|
| 24D486F20861EBD9E0217692DB0E9C61| 18664| 1| exec inner sql wait| 0.16%| update __all_weak_read_service set min_version=17034106|
| 24D486F20861EBD9E0217692DB0E9C61| 18664| 1| CPU + Wait for CPU| 0.16%| update __all_weak_read_service set min_version=17034106|
| 613AF46E802331AA465098F2A9533AEB| 7316| 1| CPU + Wait for CPU| 0.16%| |
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## Top SQL with Top Blocking Events
- Empty result if no event other than On CPU sampled
- Empty ''SQL Text'' if it is PL/SQL query
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
| SQL ID| PLAN ID| Sampled # of Executions| Event| % Event| SQL Text|
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
| | 0| 246| sleep wait| 40.26%| |
| | 0| 242| ha service condition wait| 39.61%| |
| | 0| 5| palf write| 0.82%| |
+----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## Complete List of SQL Text:
SQL ID: EAE53285C197E30A688A3C03842EA4D7
PLAN ID: 7295
SQL Text: insert into t1 values (?, ?)
SQL ID: 015E90DA7A2EB4B2B3DC22F2F4D75385
PLAN ID: 7307
SQL Text: insert into t5 values (?, ?)
SQL ID: 20BC5A371C0DCAAE023418FFE73A91B5
PLAN ID: 7359
SQL Text: SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,:2,:3,:4) AS REPORT
SQL ID: 14BBBC93DF2A7B3CB19BEDF7804CF846
PLAN ID: 7368
SQL Text: SELECT OBJ_OWNER, OBJ_NAME, PLSQL_ENTRY_SUBPROGRAM_NAME, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, ENTRY_CNT FROM (SELECT ash.*, obj.OWNER OBJ_OWNER, obj.OBJECT_NAME OBJ_NAME FROM (SELECT PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_ENTRY_SUBPROGRAM_NAME, CAST(SUM(1)AS SIGNED INTEGER) ENTRY_CNT FROM (SELECT * FROM (SELECT * FROM ( SELECT a.sample_id, a.sample_time, a.svr_ip, a.svr_port, a.con_id, a.user_id, a.session_id, a.session_type, a.session_state, a.sql_id, a.top_level_sql_id, a.plan_id, a.trace_id, IF(a.event='' OR a.event is NULL, 'CPU + Wait for CPU', a.event) as event, nvl(a.event_no, 1) as event_no, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text, IF(a.wait_class = '' OR a.wait_class is NULL, 'CPU', a.wait_class) as wait_class, nvl(a.wait_class_id, 9999) as wait_class_id, a.time_waited, a.sql_plan_line_id, a.in_parse, a.in_pl_parse, a.in_plan_cache, a.in_sql_optimize, a.in_sql_execution, a.in_px_execution, a.in_sequence_load, a.in_committing, a.in_storage_read, a.in_storage_write, a.in_remote_das_execution, a.in_plsql_compilation, a.in_plsql_execution, a.module, a.action, a.client_id, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_entry_subprogram_name, a.plsql_object_id, a.plsql_subprogram_id, a.plsql_subprogram_name FROM GV$ACTIVE_SESSION_HISTORY a WHERE 1=1 and a.sample_time between '2023-12-25 10:15:00' and '2023-12-25 10:17:45' ) unified_ash WHERE sample_time between '2023-12-25 10:15:00' and '2023-12-25 10:17:45' AND ('' = '' OR sql_id like '') AND ('' = '' OR trace_id like '') AND ('' = '' OR wait_class like '') AND ('' = '' OR module like '') AND ('' = '' OR action like '') AND ('' = '' OR client_id like '')) WHERE PLSQL_ENTRY_OBJECT_ID > 0 ) top_event GROUP BY PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_ENTRY_SUBPROGRAM_NAME) ash LEFT JOIN (select database_name owner, routine_name object_name, routine_id object_id from oceanbase.__all_database db, oceanbase.__all_routine r where r.database_id = db.database_id union select database_name owner, package_name object_name, package_id object_id from oceanbase.__all_database db, oceanbase.__all_package p where p.database_id = db.database_id) obj ON ash.plsql_entry_object_id = obj.object_id ORDER BY ENTRY_CNT DESC) v1 LIMIT 100
SQL ID: 24D486F20861EBD9E0217692DB0E9C61
PLAN ID: 7266
SQL Text: update __all_weak_read_service set min_version=1703410607282621000, max_version=1703410607282621000 where tenant_id = 1001 and level_id = 0 and level_value = '' and min_version = 1703410607083370000 and max_version = 1703410607083370000
SQL ID: 24D486F20861EBD9E0217692DB0E9C61
PLAN ID: 7266
SQL Text: update __all_weak_read_service set min_version=1703410607282621000, max_version=1703410607282621000 where tenant_id = 1001 and level_id = 0 and level_value = '' and min_version = 1703410607083370000 and max_version = 1703410607083370000
SQL ID: 24D486F20861EBD9E0217692DB0E9C61
PLAN ID: 18664
SQL Text: update __all_weak_read_service set min_version=1703410607336421000, max_version=1703410607336421000 where tenant_id = 1003 and level_id = 0 and level_value = '' and min_version = 1703410607136126000 and max_version = 1703410607136126000
SQL ID: 24D486F20861EBD9E0217692DB0E9C61
PLAN ID: 18664
SQL Text: update __all_weak_read_service set min_version=1703410607336421000, max_version=1703410607336421000 where tenant_id = 1003 and level_id = 0 and level_value = '' and min_version = 1703410607136126000 and max_version = 1703410607136126000
## Top PL/SQL Procedures
-> ''PL/SQL entry subprogram'' represents the application''s top-level
entry-point(procedure, function, trigger, package initialization)
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
-> ''PL/SQL entry subprogram'' represents the application''s top-level
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| PLSQL Entry Subprogram| PLSQL Current Subprogram| % Activity|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| test.d1| -| 0.65%|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
## Top Sessions:
- ''# Samples Active'' shows the number of ASH samples in which the blocking session was found active.
+--------------------+----------------------+----------------------------------------------------------------+------------+------------------------------+--------------------+------------------------------+
| Sid| % Activity| Event| Event Count| % Event| User| # Samples Active|
+--------------------+----------------------+----------------------------------------------------------------+------------+------------------------------+--------------------+------------------------------+
| 2770253905920| 39.61%| ha service condition wait| 242| 39.61%| | 242/242[100.00%]|
| 579820584960| 39.44%| sleep wait| 241| 39.44%| | 241/242[99.59%]|
| 141733920768| 3.27%| CPU + Wait for CPU| 20| 3.27%| | 20/242[8.26%]|
| 8589934592| 2.78%| CPU + Wait for CPU| 17| 2.78%| | 17/242[7.02%]|
| 5909874999296| 1.31%| CPU + Wait for CPU| 8| 1.31%| | 8/242[3.31%]|
| 34359738368| 0.82%| CPU + Wait for CPU| 5| 0.82%| | 5/242[2.07%]|
| 2074469203968| 0.82%| sleep wait| 5| 0.82%| | 5/242[2.07%]|
| 2095944040448| 0.65%| CPU + Wait for CPU| 4| 0.65%| | 4/242[1.65%]|
| 3221667406| 0.65%| CPU + Wait for CPU| 4| 0.65%| root| 4/242[1.65%]|
| 4290672328704| 0.65%| CPU + Wait for CPU| 4| 0.65%| | 4/242[1.65%]|
| 5136780886016| 0.65%| CPU + Wait for CPU| 4| 0.65%| | 4/242[1.65%]|
| 3517578215424| 0.49%| CPU + Wait for CPU| 3| 0.49%| | 3/242[1.24%]|
| 38654705664| 0.49%| CPU + Wait for CPU| 3| 0.49%| | 3/242[1.24%]|
| 2057289334784| 0.33%| CPU + Wait for CPU| 2| 0.33%| | 2/242[0.83%]|
| 146028888064| 0.33%| CPU + Wait for CPU| 2| 0.33%| | 2/242[0.83%]|
| 2942052597760| 0.33%| palf write| 2| 0.33%| | 2/242[0.83%]|
| 558345748480| 0.33%| CPU + Wait for CPU| 2| 0.33%| | 2/242[0.83%]|
| 5871220293632| 0.33%| exec inner sql wait| 2| 0.33%| | 2/242[0.83%]|
| 2946347565056| 0.33%| palf write| 2| 0.33%| | 2/242[0.83%]|
| 1434519076864| 0.33%| CPU + Wait for CPU| 2| 0.33%| | 2/242[0.83%]|
| 5836860555264| 0.33%| CPU + Wait for CPU| 2| 0.33%| | 2/242[0.83%]|
| 3221693274| 0.16%| CPU + Wait for CPU| 1| 0.16%| root| 1/242[0.41%]|
| 3221690158| 0.16%| exec inner sql wait| 1| 0.16%| root| 1/242[0.41%]|
| 3221690147| 0.16%| CPU + Wait for CPU| 1| 0.16%| root| 1/242[0.41%]|
| 3865470566400| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 2031519531008| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 575525617664| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 4531190497280| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 30064771072| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3221687623| 0.16%| CPU + Wait for CPU| 1| 0.16%| root| 1/242[0.41%]|
| 5931349835776| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 5484673236992| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 5385888989184| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 4419521347584| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 5871220293632| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 365072220160| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3221693280| 0.16%| CPU + Wait for CPU| 1| 0.16%| root| 1/242[0.41%]|
| 682899800064| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 5768141078528| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 579820584960| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 5098126180352| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3221689656| 0.16%| exec inner sql wait| 1| 0.16%| root| 1/242[0.41%]|
| 4608499908608| 0.16%| exec inner sql wait| 1| 0.16%| | 1/242[0.41%]|
| 184683593728| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 6047313952768| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3448858738688| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 4677219385344| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 1619202670592| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 2838973382656| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3775276253184| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3221699850| 0.16%| CPU + Wait for CPU| 1| 0.16%| root| 1/242[0.41%]|
| 4312147165184| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 6043018985472| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3427383902208| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
| 3770981285888| 0.16%| palf write| 1| 0.16%| | 1/242[0.41%]|
| 4423816314880| 0.16%| CPU + Wait for CPU| 1| 0.16%| | 1/242[0.41%]|
+--------------------+----------------------+----------------------------------------------------------------+------------+------------------------------+--------------------+------------------------------+
## Top Blocking Sessions:
- Blocking session activity percentages are calculated with respect to waits on latches and locks only.
- ''# 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 time.
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
| Blocking Sid| % Activity| Event Caused| Event Count| % Event| User| # Samples Active|
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
| 2770253905920| 39.61%| ha service condition wait| 242| 39.61%| | 242/242[100.00%]|
| 579820584960| 39.44%| sleep wait| 241| 39.44%| | 241/242[99.59%]|
| 2074469203968| 0.82%| sleep wait| 5| 0.82%| | 5/242[2.07%]|
| 2946347565056| 0.33%| palf write| 2| 0.33%| | 2/242[0.83%]|
| 2942052597760| 0.33%| palf write| 2| 0.33%| | 2/242[0.83%]|
| 3770981285888| 0.16%| palf write| 1| 0.16%| | 1/242[0.41%]|
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
## Top latches:
+----------------------------------------------------------------+--------------------+--------------------+
| Latch| Sampled Count| % Activity|
+----------------------------------------------------------------+--------------------+--------------------+
+----------------------------------------------------------------+--------------------+--------------------+
## Node Load:
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
| SVR IP| SVR PORT| Sampled Count| Idle Wait Count| Load|
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
| 172.xx.xx.xx| 2882| 611| 246| 0.47|
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+