The ASH_REPORT procedure displays the Active Session History (ASH) report in text.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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'
SVR_IP IN VARCHAR2 DEFAULT NULL,
SVR_PORT IN NUMBER DEFAULT NULL,
TENANT_ID IN NUMBER DEFAULT NULL
);
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. Specify WAIT_CLASS in accordance with the value of the WAIT_CLASS column in the GV$ACTIVE_SESSION_HISTORY view. If you do not specify this parameter or set it to NULL, the wait event class is not limited. |
| REPORT_TYPE | The type of the report. At present, the HTML and TEXT types are supported. If you do not specify this parameter, the TEXT type is used by default. |
| SVR_IP | Specifies the IP address of the OBServer node where the ASH report is generated. If you do not specify this parameter or set the value to NULL, the IP address of the OBServer node where the ASH report is generated is not limited. |
| SVR_PORT | Specifies the port number of the OBServer node where the ASH report is generated. If you do not specify this parameter or set the value to NULL, the port number of the node where the ASH report is generated is not limited. |
| TENANT_ID | Specifies the ID of the tenant for which the ASH report is generated. If you do not specify this parameter or set the value to NULL, the ID of the tenant for which the ASH report is generated is not limited. |
Considerations
You can query the oceanbase.GV$ACTIVE_SESSION_HISTORY view for related ASH information.
Examples
Enable output on the server.
obclient> SET SERVEROUTPUT ON;
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(to_date('2024-02-22 19:26:47', 'yyyy-MM-dd HH24:mi:ss'),to_date('2024-05-22 19:27:07', 'yyyy-MM-dd HH24:mi:ss'), NULL, NULL, NULL, 'text', '172.30.xxx.xxx', 28xx);
A sample output is as follows:
# ASH Report
----
Sample Begin: 2024-02-22 19:26:47
Sample End: 2024-05-22 19:27:07
----------
Analysis Begin Time: 2024-04-09 18:11:18
Analysis End Time: 2024-04-10 10:45:53
Elapsed Time: 59675(secs)
Num of Sample: 3585
Num of Events: 3585
Average Active Sessions: 0.060
----
## Top User Events:
----------------------------------------------------------------+--------------------+---------+
Event| WAIT_CLASS| % Event|
----------------------------------------------------------------+--------------------+---------+
CPU + Wait for CPU| OTHER| 92.50%|
palf write| SYSTEM_IO| 4.04%|
slog flush condition wait| CONCURRENCY| 1.09%|
db file compact write| SYSTEM_IO| 0.86%|
async rpc proxy condition wait| NETWORK| 0.36%|
exec inner sql wait| OTHER| 0.31%|
wait for network request in queue| NETWORK| 0.22%|
sleep wait| IDLE| 0.17%|
db file compact read| SYSTEM_IO| 0.11%|
palf read| SYSTEM_IO| 0.11%|
sync rpc| NETWORK| 0.08%|
default condition wait| CONCURRENCY| 0.08%|
latch: ls meta lock wait| CONCURRENCY| 0.06%|
----------------------------------------------------------------+--------------------+---------+
## Top Events P1/P2/P3 Value:
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
Event| % Event| % Activity| Max P1/P2/P3| Parameter 1| Parameter 2| Parameter 3|
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
CPU + Wait for CPU| 92.50%| 92.497%| "0","0","0"| | | |
palf write| 4.04%| 4.045%| "406","66957312","8192"| fd| offset| size|
slog flush condition wait| 1.09%| 1.088%| "140458332257256","0","0"| address| | |
db file compact write| 0.86%| 0.865%| "8192","0","0"| fd| offset| size|
async rpc proxy condition wait| 0.36%| 0.363%| "140454242609688","0","0"| address| | |
exec inner sql wait| 0.31%| 0.307%| "50004","3221742313","0"|wait inner sql class| inner session id| |
wait for network request in queue| 0.22%| 0.223%| "5387","0","2"| pcode| level| priority|
sleep wait| 0.17%| 0.167%| "5000000","0","0"| sleep_interval| | |
db file compact read| 0.11%| 0.112%| "387","0","0"| fd| offset| size|
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
## Top Phase of Execution:
----------------------------------------+------------+--------------+----------------------------------------+
Phase of Execution| % Activity| Sample Count| Avg Active Sessions|
----------------------------------------+------------+--------------+----------------------------------------+
IN_STORAGE_READ| 0.167%| 6| 0.00|
IN_COMMITTING| 0.139%| 5| 0.00|
IN_SQL_OPTIMIZE| 0.056%| 2| 0.00|
IN_SQL_EXECUTION| 0.056%| 2| 0.00|
IN_STORAGE_WRITE| 0.028%| 1| 0.00|
IN_PARSE| 0.000%| 0| 0.00|
IN_PL_PARSE| 0.000%| 0| 0.00|
IN_PLAN_CACHE| 0.000%| 0| 0.00|
IN_PX_EXECUTION| 0.000%| 0| 0.00|
IN_SEQUENCE_LOAD| 0.000%| 0| 0.00|
IN_REMOTE_DAS_EXECUTION| 0.000%| 0| 0.00|
----------------------------------------+------------+--------------+----------------------------------------+
## 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| 3309| CPU + Wait for CPU| 92.30%| |
| 0| 145| palf write| 4.04%| |
| 0| 39| slog flush condition wait| 1.09%| |
| 0| 31| db file compact write| 0.86%| |
| 0| 13| async rpc proxy condition wait| 0.36%| |
| 0| 11| exec inner sql wait| 0.31%| |
| 0| 8| wait for network request in queue| 0.22%| |
| 0| 6| sleep wait| 0.17%| |
945096BAD4B3B7C7DA87D4F93AC22C4D| 394| 5| CPU + Wait for CPU| 0.14%| |
| 0| 4| palf read| 0.11%| |
| 0| 4| db file compact read| 0.11%| |
| 0| 3| default condition wait| 0.08%| |
| 0| 3| sync rpc| 0.08%| |
| 0| 2| latch: ls meta lock wait| 0.06%| |
BF3CC7901C9D3AA1C2F3920B3BEFD839| 1564| 1| CPU + Wait for CPU| 0.03%| select "ANONYMOUS_VIEW1"."EVENT" AS "EVENT","ANONYMOUS_|
8482C8137DA40CE77C9387299138C1E7| 1565| 1| CPU + Wait for CPU| 0.03%| select "ANONYMOUS_VIEW2"."EXECUTION_PHASE" AS "EXECUTIO|
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## 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|
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
NULL| 0| 145| palf write| 4.04%| |
NULL| 0| 39| slog flush condition wait| 1.09%| |
NULL| 0| 31| db file compact write| 0.86%| |
NULL| 0| 13| async rpc proxy condition wait| 0.36%| |
NULL| 0| 8| wait for network request in queue| 0.22%| |
NULL| 0| 6| sleep wait| 0.17%| |
NULL| 0| 4| palf read| 0.11%| |
NULL| 0| 4| db file compact read| 0.11%| |
NULL| 0| 3| sync rpc| 0.08%| |
NULL| 0| 3| default condition wait| 0.08%| |
NULL| 0| 2| latch: ls meta lock wait| 0.06%| |
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## Complete List of SQL Text
SQL ID: BF3CC7901C9D3AA1C2F3920B3BEFD839
PLAN ID: 1564
SQL Text: select "ANONYMOUS_VIEW1"."EVENT" AS "EVENT","ANONYMOUS_VIEW1"."EVENT_CNT" AS "EVENT_CNT","ANONYMOUS_VIEW1"."SAMPLE_CNT" AS "SAMPLE_CNT","ANONYMOUS_VIEW1"."P1" AS "P1","ANONYMOUS_VIEW1"."P2" AS "P2","ANONYMOUS_VIEW1"."P3" AS "P3","ANONYMOUS_VIEW1"."P1TEXT" AS "P1TEXT","ANONYMOUS_VIEW1"."P2TEXT" AS "P2TEXT","ANONYMOUS_VIEW1"."P3TEXT" AS "P3TEXT" from (select "TOP_EVENT"."EVENT" AS "EVENT",sum(1) AS "EVENT_CNT",count(1) AS "SAMPLE_CNT",max("TOP_EVENT"."P1") AS "P1",max("TOP_EVENT"."P2") AS "P2",max("TOP_EVENT"."P3") AS "P3",max("TOP_EVENT"."P1TEXT") AS "P1TEXT",max("TOP_EVENT"."P2TEXT") AS "P2TEXT",max("TOP_EVENT"."P3TEXT") AS "P3TEXT" from (select "UNIFIED_ASH"."SAMPLE_ID" AS "SAMPLE_ID","UNIFIED_ASH"."SAMPLE_TIME" AS "SAMPLE_TIME","UNIFIED_ASH"."SVR_IP" AS "SVR_IP","UNIFIED_ASH"."SVR_PORT" AS "SVR_PORT","UNIFIED_ASH"."CON_ID" AS "CON_ID","UNIFIED_ASH"."USER_ID" AS "USER_ID","UNIFIED_ASH"."SESSION_ID" AS "SESSION_ID","UNIFIED_ASH"."SESSION_TYPE" AS "SESSION_TYPE","UNIFIED_ASH"."SESSION_STATE" AS "SESSION_STATE","UNIFIED_ASH"."TOP_LEVEL_SQL_ID" AS "TOP_LEVEL_SQL_ID","UNIFIED_ASH"."SQL_ID" AS "SQL_ID","UNIFIED_ASH"."PLAN_ID" AS "PLAN_ID","UNIFIED_ASH"."TRACE_ID" AS "TRACE_ID","UNIFIED_ASH"."EVENT" AS "EVENT","UNIFIED_ASH"."EVENT_NO" AS "EVENT_NO","UNIFIED_ASH"."P1" AS "P1","UNIFIED_ASH"."P1TEXT" AS "P1TEXT","UNIFIED_ASH"."P2" AS "P2","UNIFIED_ASH"."P2TEXT" AS "P2TEXT","UNIFIED_ASH"."P3" AS "P3","UNIFIED_ASH"."P3TEXT" AS "P3TEXT","UNIFIED_ASH"."WAIT_CLASS" AS "WAIT_CLASS","UNIFIED_ASH"."WAIT_CLASS_ID" AS "WAIT_CLASS_ID","UNIFIED_ASH"."TIME_WAITED" AS "TIME_WAITED","UNIFIED_ASH"."SQL_PLAN_LINE_ID" AS "SQL_PLAN_LINE_ID","UNIFIED_ASH"."IN_PARSE" AS "IN_PARSE","UNIFIED_ASH"."IN_PL_PARSE" AS "IN_PL_PARSE","UNIFIED_ASH"."IN_PLAN_CACHE" AS "IN_PLAN_CACHE","UNIFIED_ASH"."IN_SQL_OPTIMIZE" AS "IN_SQL_OPTIMIZE","UNIFIED_ASH"."IN_SQL_EXECUTION" AS "IN_SQL_EXECUTION","UNIFIED_ASH"."IN_PX_EXECUTION" AS "IN_PX_EXECUTION","UNIFIED_ASH"."IN_SEQUENCE_LOAD" AS "IN_SEQUENCE_LOAD","UNIFIED_ASH"."IN_COMMITTING" AS "IN_COMMITTING","UNIFIED_ASH"."IN_STORAGE_READ" AS "IN_STORAGE_READ","UNIFIED_ASH"."IN_STORAGE_WRITE" AS "IN_STORAGE_WRITE","UNIFIED_ASH"."IN_REMOTE_DAS_EXECUTION" AS "IN_REMOTE_DAS_EXECUTION","UNIFIED_ASH"."IN_PLSQL_EXECUTION" AS "IN_PLSQL_EXECUTION","UNIFIED_ASH"."IN_PLSQL_COMPILATION" AS "IN_PLSQL_COMPILATION","UNIFIED_ASH"."PLSQL_ENTRY_OBJECT_ID" AS "PLSQL_ENTRY_OBJECT_ID","UNIFIED_ASH"."PLSQL_ENTRY_SUBPROGRAM_ID" AS "PLSQL_ENTRY_SUBPROGRAM_ID","UNIFIED_ASH"."PLSQL_ENTRY_SUBPROGRAM_NAME" AS "PLSQL_ENTRY_SUBPROGRAM_NAME","UNIFIED_ASH"."PLSQL_OBJECT_ID" AS "PLSQL_OBJECT_ID","UNIFIED_ASH"."PLSQL_SUBPROGRAM_ID" AS "PLSQL_SUBPROGRAM_ID","UNIFIED_ASH"."PLSQL_SUBPROGRAM_NAME" AS "PLSQL_SUBPROGRAM_NAME","UNIFIED_ASH"."MODULE" AS "MODULE","UNIFIED_ASH"."ACTION" AS "ACTION","UNIFIED_ASH"."CLIENT_ID" AS "CLIENT_ID" from (select "SYS"."A"."SAMPLE_ID" AS "SAMPLE_ID","SYS"."A"."SAMPLE_TIME" AS "SAMPLE_TIME","SYS"."A"."SVR_IP" AS "SVR_IP","SYS"."A"."SVR_PORT" AS "SVR_PORT","SYS"."A"."CON_ID" AS "CON_ID","SYS"."A"."USER_ID" AS "USER_ID","SYS"."A"."SESSION_ID" AS "SESSION_ID","SYS"."A"."SESSION_TYPE" AS "SESSION_TYPE","SYS"."A"."SESSION_STATE" AS "SESSION_STATE","SYS"."A"."TOP_LEVEL_SQL_ID" AS "TOP_LEVEL_SQL_ID","SYS"."A"."SQL_ID" AS "SQL_ID","SYS"."A"."PLAN_ID" AS "PLAN_ID","SYS"."A"."TRACE_ID" AS "TRACE_ID",NVL("SYS"."A"."EVENT",'CPU + Wait for CPU') AS "EVENT",NVL("SYS"."A"."EVENT_NO",1) AS "EVENT_NO","SYS"."A"."P1" AS "P1","SYS"."A"."P1TEXT" AS "P1TEXT","SYS"."A"."P2" AS "P2","SYS"."A"."P2TEXT" AS "P2TEXT","SYS"."A"."P3" AS "P3","SYS"."A"."P3TEXT" AS "P3TEXT",NVL("SYS"."A"."WAIT_CLASS",'CPU') AS "WAIT_CLASS",NVL("SYS"."A"."WAIT_CLASS_ID",9999) AS "WAIT_CLASS_ID","SYS"."A"."TIME_WAITED" AS "TIME_WAITED","SYS"."A"."SQL_PLAN_LINE_ID" AS "SQL_PLAN_LINE_ID","SYS"."A"."IN_PARSE" AS "IN_PARSE","SYS"."A"."IN_PL_PARSE" AS "IN_PL_PARSE","SYS"."A"."IN_PLAN_CACHE" AS "IN_PLAN_CACHE","SYS"."A"."IN_SQL_OPTIMIZE" AS "IN_SQL_OPTIMIZE","SYS"."A"."IN_SQL_EXECUTION" AS "
SQL ID: 8482C8137DA40CE77C9387299138C1E7
PLAN ID: 1565
SQL Text: select "ANONYMOUS_VIEW2"."EXECUTION_PHASE" AS "EXECUTION_PHASE","ANONYMOUS_VIEW2"."SAMPLES_CNT" AS "SAMPLES_CNT" from (select /*+ NO_REWRITE */ * FROM (select sum((case "TOP_EVENT"."IN_PARSE" when 'N' then 0 else 1 end)) AS "IN_PARSE",sum((case "TOP_EVENT"."IN_PL_PARSE" when 'N' then 0 else 1 end)) AS "IN_PL_PARSE",sum((case "TOP_EVENT"."IN_PLAN_CACHE" when 'N' then 0 else 1 end)) AS "IN_PLAN_CACHE",sum((case "TOP_EVENT"."IN_SQL_OPTIMIZE" when 'N' then 0 else 1 end)) AS "IN_SQL_OPTIMIZE",sum((case "TOP_EVENT"."IN_SQL_EXECUTION" when 'N' then 0 else 1 end)) AS "IN_SQL_EXECUTION",sum((case "TOP_EVENT"."IN_PX_EXECUTION" when 'N' then 0 else 1 end)) AS "IN_PX_EXECUTION",sum((case "TOP_EVENT"."IN_SEQUENCE_LOAD" when 'N' then 0 else 1 end)) AS "IN_SEQUENCE_LOAD",sum((case "TOP_EVENT"."IN_COMMITTING" when 'N' then 0 else 1 end)) AS "IN_COMMITTING",sum((case "TOP_EVENT"."IN_STORAGE_READ" when 'N' then 0 else 1 end)) AS "IN_STORAGE_READ",sum((case "TOP_EVENT"."IN_STORAGE_WRITE" when 'N' then 0 else 1 end)) AS "IN_STORAGE_WRITE",sum((case "TOP_EVENT"."IN_REMOTE_DAS_EXECUTION" when 'N' then 0 else 1 end)) AS "IN_REMOTE_DAS_EXECUTION" from (select "UNIFIED_ASH"."SAMPLE_ID" AS "SAMPLE_ID","UNIFIED_ASH"."SAMPLE_TIME" AS "SAMPLE_TIME","UNIFIED_ASH"."SVR_IP" AS "SVR_IP","UNIFIED_ASH"."SVR_PORT" AS "SVR_PORT","UNIFIED_ASH"."CON_ID" AS "CON_ID","UNIFIED_ASH"."USER_ID" AS "USER_ID","UNIFIED_ASH"."SESSION_ID" AS "SESSION_ID","UNIFIED_ASH"."SESSION_TYPE" AS "SESSION_TYPE","UNIFIED_ASH"."SESSION_STATE" AS "SESSION_STATE","UNIFIED_ASH"."TOP_LEVEL_SQL_ID" AS "TOP_LEVEL_SQL_ID","UNIFIED_ASH"."SQL_ID" AS "SQL_ID","UNIFIED_ASH"."PLAN_ID" AS "PLAN_ID","UNIFIED_ASH"."TRACE_ID" AS "TRACE_ID","UNIFIED_ASH"."EVENT" AS "EVENT","UNIFIED_ASH"."EVENT_NO" AS "EVENT_NO","UNIFIED_ASH"."P1" AS "P1","UNIFIED_ASH"."P1TEXT" AS "P1TEXT","UNIFIED_ASH"."P2" AS "P2","UNIFIED_ASH"."P2TEXT" AS "P2TEXT","UNIFIED_ASH"."P3" AS "P3","UNIFIED_ASH"."P3TEXT" AS "P3TEXT","UNIFIED_ASH"."WAIT_CLASS" AS "WAIT_CLASS","UNIFIED_ASH"."WAIT_CLASS_ID" AS "WAIT_CLASS_ID","UNIFIED_ASH"."TIME_WAITED" AS "TIME_WAITED","UNIFIED_ASH"."SQL_PLAN_LINE_ID" AS "SQL_PLAN_LINE_ID","UNIFIED_ASH"."IN_PARSE" AS "IN_PARSE","UNIFIED_ASH"."IN_PL_PARSE" AS "IN_PL_PARSE","UNIFIED_ASH"."IN_PLAN_CACHE" AS "IN_PLAN_CACHE","UNIFIED_ASH"."IN_SQL_OPTIMIZE" AS "IN_SQL_OPTIMIZE","UNIFIED_ASH"."IN_SQL_EXECUTION" AS "IN_SQL_EXECUTION","UNIFIED_ASH"."IN_PX_EXECUTION" AS "IN_PX_EXECUTION","UNIFIED_ASH"."IN_SEQUENCE_LOAD" AS "IN_SEQUENCE_LOAD","UNIFIED_ASH"."IN_COMMITTING" AS "IN_COMMITTING","UNIFIED_ASH"."IN_STORAGE_READ" AS "IN_STORAGE_READ","UNIFIED_ASH"."IN_STORAGE_WRITE" AS "IN_STORAGE_WRITE","UNIFIED_ASH"."IN_REMOTE_DAS_EXECUTION" AS "IN_REMOTE_DAS_EXECUTION","UNIFIED_ASH"."IN_PLSQL_EXECUTION" AS "IN_PLSQL_EXECUTION","UNIFIED_ASH"."IN_PLSQL_COMPILATION" AS "IN_PLSQL_COMPILATION","UNIFIED_ASH"."PLSQL_ENTRY_OBJECT_ID" AS "PLSQL_ENTRY_OBJECT_ID","UNIFIED_ASH"."PLSQL_ENTRY_SUBPROGRAM_ID" AS "PLSQL_ENTRY_SUBPROGRAM_ID","UNIFIED_ASH"."PLSQL_ENTRY_SUBPROGRAM_NAME" AS "PLSQL_ENTRY_SUBPROGRAM_NAME","UNIFIED_ASH"."PLSQL_OBJECT_ID" AS "PLSQL_OBJECT_ID","UNIFIED_ASH"."PLSQL_SUBPROGRAM_ID" AS "PLSQL_SUBPROGRAM_ID","UNIFIED_ASH"."PLSQL_SUBPROGRAM_NAME" AS "PLSQL_SUBPROGRAM_NAME","UNIFIED_ASH"."MODULE" AS "MODULE","UNIFIED_ASH"."ACTION" AS "ACTION","UNIFIED_ASH"."CLIENT_ID" AS "CLIENT_ID" from (select "SYS"."A"."SAMPLE_ID" AS "SAMPLE_ID","SYS"."A"."SAMPLE_TIME" AS "SAMPLE_TIME","SYS"."A"."SVR_IP" AS "SVR_IP","SYS"."A"."SVR_PORT" AS "SVR_PORT","SYS"."A"."CON_ID" AS "CON_ID","SYS"."A"."USER_ID" AS "USER_ID","SYS"."A"."SESSION_ID" AS "SESSION_ID","SYS"."A"."SESSION_TYPE" AS "SESSION_TYPE","SYS"."A"."SESSION_STATE" AS "SESSION_STATE","SYS"."A"."TOP_LEVEL_SQL_ID" AS "TOP_LEVEL_SQL_ID","SYS"."A"."SQL_ID" AS "SQL_ID","SYS"."A"."PLAN_ID" AS "PLAN_ID","SYS"."A"."TRACE_ID" AS "TRACE_ID",NVL("SYS"."A"."EVENT",'CPU + Wait for CPU') AS "EVENT",NVL("SYS"."A"."EVENT_NO",1) AS "EVENT_NO","SYS"."A"."P1" AS "P1","SYS"."A"."P1TEXT" AS "P1TEXT","SYS"."
## 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|
------------------------------------------------------------+------------------------------------------------------------+--------------------+
oceanbase.__all_core_table| --| 0.28%|
------------------------------------------------------------+------------------------------------------------------------+--------------------+
## Top Sessions:
- '# 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.
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
Sid| % Activity| Event| Event Count| % Event| User| # Samples Active|
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
5334349381632| 32.94%| CPU + Wait for CPU| 1181| 32.94%| | 1181/59675[1.98%]|
5226975199232| 8.79%| CPU + Wait for CPU| 315| 8.79%| | 315/59675[0.53%]|
5295694675968| 7.09%| CPU + Wait for CPU| 254| 7.09%| | 254/59675[0.43%]|
4793183502336| 4.66%| CPU + Wait for CPU| 167| 4.66%| | 167/59675[0.28%]|
6279242186752| 3.43%| CPU + Wait for CPU| 123| 3.43%| | 123/59675[0.21%]|
6283537154048| 3.26%| CPU + Wait for CPU| 117| 3.26%| | 117/59675[0.20%]|
5368709120000| 2.26%| CPU + Wait for CPU| 81| 2.26%| | 81/59675[0.14%]|
4720169058304| 2.20%| palf write| 79| 2.20%| | 79/59675[0.13%]|
5214090297344| 2.12%| CPU + Wait for CPU| 76| 2.12%| | 76/59675[0.13%]|
4784593567744| 2.09%| CPU + Wait for CPU| 75| 2.09%| | 75/59675[0.13%]|
4724464025600| 1.84%| palf write| 66| 1.84%| | 66/59675[0.11%]|
5085241278464| 1.42%| CPU + Wait for CPU| 51| 1.42%| | 51/59675[0.09%]|
4840428142592| 1.42%| CPU + Wait for CPU| 51| 1.42%| | 51/59675[0.09%]|
4797478469632| 1.31%| CPU + Wait for CPU| 47| 1.31%| | 47/59675[0.08%]|
4690104287232| 1.20%| CPU + Wait for CPU| 43| 1.20%| | 43/59675[0.07%]|
4715874091008| 1.20%| CPU + Wait for CPU| 43| 1.20%| | 43/59675[0.07%]|
5244155068416| 1.12%| CPU + Wait for CPU| 40| 1.12%| | 40/59675[0.07%]|
5115306049536| 1.12%| CPU + Wait for CPU| 40| 1.12%| | 40/59675[0.07%]|
4771708665856| 1.12%| CPU + Wait for CPU| 40| 1.12%| | 40/59675[0.07%]|
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
## 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 blocking session was found active.
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
Blocking Sid| % Activity| Event Caused| Event Count| % Event| User| # Samples Active|
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
4720169058304| 2.20%| palf write| 79| 2.20%| | 79/59675[0.13%]|
4724464025600| 1.84%| palf write| 66| 1.84%| | 66/59675[0.11%]|
5158255722496| 0.86%| db file compact write| 31| 0.86%| | 31/59675[0.05%]|
4784593567744| 0.84%| slog flush condition wait| 30| 0.84%| | 30/59675[0.05%]|
4771708665856| 0.36%| async rpc proxy condition wait| 13| 0.36%| | 13/59675[0.02%]|
0| 0.22%| wait for network request in queue| 8| 0.22%| | 8/59675[0.01%]|
5299989643264| 0.22%| slog flush condition wait| 8| 0.22%| | 8/59675[0.01%]|
5312874545152| 0.14%| sleep wait| 5| 0.14%| | 5/59675[0.01%]|
5299989643264| 0.11%| palf read| 4| 0.11%| | 4/59675[0.01%]|
5471788335104| 0.08%| sync rpc| 3| 0.08%| | 3/59675[0.01%]|
5106716114944| 0.06%| default condition wait| 2| 0.06%| | 2/59675[0.00%]|
5308579577856| 0.06%| latch: ls meta lock wait| 2| 0.06%| | 2/59675[0.00%]|
5351529250816| 0.06%| db file compact read| 2| 0.06%| | 2/59675[0.00%]|
4986457030656| 0.03%| db file compact read| 1| 0.03%| | 1/59675[0.00%]|
176093659136| 0.03%| default condition wait| 1| 0.03%| | 1/59675[0.00%]|
4969277161472| 0.03%| slog flush condition wait| 1| 0.03%| | 1/59675[0.00%]|
5381594021888| 0.03%| sleep wait| 1| 0.03%| | 1/59675[0.00%]|
4930622455808| 0.03%| db file compact read| 1| 0.03%| | 1/59675[0.00%]|
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
## Top latches:
----------------------------------------------------------------+--------------------+--------------------+
Latch| Sampled Count| % Activity|
----------------------------------------------------------------+--------------------+--------------------+
latch: ls meta lock wait| 2| 0.06%|
----------------------------------------------------------------+--------------------+--------------------+
## Node Load:
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
SVR IP| SVR PORT| Sampled Count| Idle Wait Count| Load|
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
172.30.xxx.xxx| 28xx| 3585| 6| 0.06|
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
Execute the following statement to present the report in the HTML format:
obclient [SYS]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(TO_DATE('2024-02-22 19:26:47', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-06-22 19:27:07', 'YYYY-MM-DD HH24:MI:SS'), REPORT_TYPE=> 'TEXT');
A sample output is as follows:
<!DOCTYPE html><html lang="en"><head><title>ASH Report</title> <style type="text/css"> body.ash_html {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:rgb(246, 248, 251);} pre.ash_html {font:8pt Courier;color:black; background:rgb(246, 248, 251);} pre_sqltext.ash_html {white-space: pre-wrap;} h1.ash_html {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:rgb(246, 248, 251);border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;} h2.ash_html {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:rgb(246, 248, 251);margin-top:4pt; margin-bottom:0pt;} h3.ash_html {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:rgb(246, 248, 251);margin-top:4pt; margin-bottom:0pt;} li.ash_html {font: 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:rgb(246, 248, 251);} th.ash_htmlnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:rgb(246, 248, 251);padding-left:4px; padding-right:4px;padding-bottom:2px} th.ash_htmlbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px} td.ash_htmlnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;} td.ash_htmlc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;} td.ash_htmlnclb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-left: thin solid black;} td.ash_htmlncbb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-left: thin solid black;border-right: thin solid black;} td.ash_htmlncrb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-right: thin solid black;} td.ash_htmlcrb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-right: thin solid black;} td.ash_htmlclb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-left: thin solid black;} td.ash_htmlcbb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-left: thin solid black;border-right: thin solid black;} a.ash_html {font:bold 8pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;} td.ash_htmlnct {font:8pt Arial,Helvetica,Geneva,sans-serif;border-top: thin solid black;color:black;background:rgb(246, 248, 251);vertical-align:top;} td.ash_htmlct {font:8pt Arial,Helvetica,Geneva,sans-serif;border-top: thin solid black;color:black;background:#FFFFCC; vertical-align:top;} td.ash_htmlnclbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-top: thin solid black;border-left: thin solid black;} td.ash_htmlncbbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-left: thin solid black;border-right: thin solid black;border-top: thin solid black;} td.ash_htmlncrbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:rgb(246, 248, 251);vertical-align:top;border-top: thin solid black;border-right: thin solid black;} td.ash_htmlcrbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-right: thin solid black;} td.ash_htmlclbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-left: thin solid black;} td.ash_htmlcbbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-left: thin solid black;border-right: thin solid black;} table.tdiff { border_collapse: collapse; } table.tscl {width: 600;} table.tscl tbody, table.tscl thead { display: block; } table.tscl thead tr th {height: 12px;line-height: 12px;} table.tscl tbody { height: 100px;overflow-y: auto; overflow-x: hidden;} table.tscl tbody td, thead th {width: 200;} .hidden {position:absolute;left:-10000px;top:auto;width:1px;height:1px;overflow:hidden;} .pad {margin-left:17px;} .doublepad {margin-left:34px;} </style></head>
<script> function setSectionList() { var list = document.getElementById(110); const section_headers = document.querySelectorAll('h2'); section_headers.forEach(ele => { let str = ele.innerText.replaceAll('<br>',''); let li = document.createElement('li'); let a = document.createElement('a'); a.classList.add('ash_html'); a.setAttribute('href', str); a.innerText = str; li.innerHTML = '<a class = "ash_html" href = "#' + str + '">' + str + '</a>'; li.classList.add('ash_html'); list.appendChild(li); }); } document.addEventListener('DOMContentLoaded', setSectionList); </script> <body class="ash_html"> <h1 class="ash_html"> ASH Report </h1><pre class="ash_html"> Cluster Name: test424
Observer Version: OceanBase 4.2.4.0 (200000332024060510-08a053141e0c3e09a9166ade95b02830875dd9fa)
Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
User Input Begin Time: 2024-02-2219:26:47
User Input End Time: 2024-06-2219:27:07
Analysis Begin Time: 2024-06-1522:25:50
Analysis End Time: 2024-06-1711:21:55
Elapsed Time: 132964
Num of Sample: 8982
Num of Events: 8982
Average Active Sessions: 0.07
</pre><ul id=110></ul><a class="ash_html" name='Top Active Tenants'></a> <h2 class="ash_html">Top Active Tenants<br></h2><ul><li class='ash_html'>this section lists top active tenant information</li>
<li class='ash_html'>Total Count: num of records during ash report analysis time period</li>
<li class='ash_html'>Wait Event Count: num of records when session is on wait event</li>
<li class='ash_html'>On CPU Count: num of records when session is on cpu</li>
<li class='ash_html'>Avg Active Sessions: average active sessions during ash report analysis time period</li>
<li class='ash_html'>% Activity: activity(cpu + wait) percentage for given tenant</li>
</ul>