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 oceanbase.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 [SYS]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2023-09-22 20:27:07' );
A sample output is as follows:
REPORT:
# ASH Report
----
Sample Begin: 2023-09-22 10:26:47
Sample End: 2023-09-22 20:27:07
----------
Analysis Begin Time: 2023-09-22 16:44:05
Analysis End Time: 2023-09-22 17:26:57
Elapsed Time: 2572
Num of Sample: 2
Num of Events: 2
Average Active Sessions: 0.00
----
## Top User Events:
+----------------------------------------+--------------------+----------+---------+
| Event| WAIT_CLASS| EVENT_CNT| % Event|
+----------------------------------------+--------------------+----------+---------+
| CPU + Wait for CPU| OTHER| 2| 100.00%|
+----------------------------------------+--------------------+----------+---------+
## Top Events P1/P2/P3 Value:
+----------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
| Event| % Event| % Activity| Max P1/P2/P3| Parameter 1| Parameter 2| Parameter 3|
+----------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
| CPU + Wait for CPU| 100.00%| 100.000%| 0, 0, 0| | | |
+----------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
## Top Phase of Execution:
+----------------------------------------+------------+--------------+----------------------------------------+
| Phase of Execution| % Activity| Sample Count| Avg Active Sessions|
+----------------------------------------+------------+--------------+----------------------------------------+
| IN_SQL_OPTIMIZE| 50.000%| 1| 0.039%|
| IN_SQL_EXECUTION| 50.000%| 1| 0.039%|
| IN_PARSE| 0.000%| 0| 0.000%|
| IN_PL_PARSE| 0.000%| 0| 0.000%|
| IN_PLAN_CACHE| 0.000%| 0| 0.000%|
| IN_PX_EXECUTION| 0.000%| 0| 0.000%|
| IN_SEQUENCE_LOAD| 0.000%| 0| 0.000%|
| IN_COMMITTING| 0.000%| 0| 0.000%|
| IN_STORAGE_READ| 0.000%| 0| 0.000%|
| IN_STORAGE_WRITE| 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|
+----------------------------------------+------------+-------------------------+----------------------------------------+------------+------------------------------------------------------------+
| 359E40D6A4AB75171925325700759325| 257| 1| CPU + Wait for CPU| 50.00%| SELECT DATABASE()|
| 48E5A16C19C400FC1A2F38CE198F9EAC| 257| 1| CPU + Wait for CPU| 50.00%| |
+----------------------------------------+------------+-------------------------+----------------------------------------+------------+------------------------------------------------------------+
## 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|
+----------------------------------------+------------+-------------------------+----------------------------------------+------------+------------------------------------------------------------+
+----------------------------------------+------------+-------------------------+----------------------------------------+------------+------------------------------------------------------------+
## Complete List of SQL Text:
SQL ID: 359E40D6A4AB75171925325700759325
PLAN ID: 257
SQL Text: SELECT DATABASE()
## 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|
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
| 3221487619| 50.00%| CPU + Wait for CPU| 1| 50.00%| root| 1/2572[0.04%]|
| 3221487618| 50.00%| CPU + Wait for CPU| 1| 50.00%| root| 1/2572[0.04%]|
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
## 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|
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
+--------------------+----------------------+----------------------------------------+------------+------------------------------+--------------------+------------------------------+
## Top latches:
+----------------------------------------+--------------------+--------------------+
| Latch| Sampled Count| % Activity|
+----------------------------------------+--------------------+--------------------+
+----------------------------------------+--------------------+--------------------+
## Node Load:
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
| SVR IP| SVR PORT| Sampled Count| Idle Wait Count| Load|
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
| 172.xx.xx.xx| 2882| 2| 0| 0.00|
+----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
1 row in set
Query OK, 0 rows affected
