After generating an active session history (ASH) report, you can view its content to identify the possible causes of transient performance issues.
Report content
An ASH report records the sampling start and end time, analysis start and end time, diagnosis period, sample size, event quantity, and average number of active sessions. It also outputs information of sections including Top Active Tenants, Top Node Load, Top Foreground DB Time, Top Execution Phase, Top Background DB Time, Top Sessions, Top Groups, Top Latches, Activity Over Time, Top SQL Statement Types, Top SQL with Top DB Time, Top SQL with Top Events, Top SQL with Top Operator, Top PL/SQL Procedures, and Complete List of SQL Text. You can analyze the performance in the target scope for the target object in different dimensions, such as the time, session, module, operation, or SQL statement.
ASH Report
The ASH Report section shows the sampling time, analysis time, and general statistics. The specific parameters are described as follows:
- Cluster Name: the name of the OceanBase cluster.
- Observer Version: the version number and build version of OceanBase Database.
- Operation System Info: the kernel version and architecture of the operating system.
- User Input Begin Time: the start time of sampling, which indicates the point in time when session activity recording starts.
- User Input End Time: the end time of sampling, which indicates the point in time when session activity recording stops.
- Analysis Begin Time: the start time of analysis, which indicates the point in time when sampling data analysis starts.
- Analysis End Time: the end time of analysis, which indicates the point in time when sampling data analysis stops.
- Elapsed Time: the total duration for all samplings, in seconds.
- Num of Samples: the number of samples, which indicates the number of recorded session activities.
- Average Active Sessions: the average number of active sessions during the sampling period.
The preceding statistics in the ASH report encompass the version of OceanBase Database, operating system, sampling period, analysis period, sample size, event quantity, and average number of active sessions. You can use these statistics to learn about the activity status, the number of sessions and events, and activeness of OceanBase Database during the sampling period.
ASH Report
Cluster Name: test424
Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c3132574xxxxxx)
Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
User Input Begin Time: 2023-09-22 10:26:47
User Input End Time: 2024-06-22 20:27:07
Analysis Begin Time: 2024-06-13 18:24:34
Analysis End Time: 2024-06-14 17:42:35
Elapsed Time: 83880
Num of Sample: 17386
Average Active Sessions: 0.21
Top Active Tenants
The Top Active Tenants section lists the tenants with the highest activeness. The related parameters are described as follows:
- Tenant ID: the ID of the tenant.
- Session Type: the type of the session. Valid values are
BACKGROUND, which indicates a background process, andFOREGROUND, which indicates a foreground process. - Total Count: the total number of sampled sessions recorded during the analysis period of the ASH report.
- Wait Event Count: the number of sampled sessions that are waiting for an event.
- On CPU Count: the number of sampled sessions that occupy CPU cores.
- Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
- % Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section provides detailed activities of the specified tenant to show OceanBase Database performance in different dimensions and the resource usage of the tenant.
Top Active Tenants:
- this section lists top active tenant information
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
|Tenant ID|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| 1002| BACKGROUND| 17350| 2060| 15290| 0.21| 99.79%|
| 1002| FOREGROUND| 36| 22| 14| 0.00| 0.21%|
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top Node Load
The Top Node Load section shows the DB time metrics used to measure the performance of top nodes. The related parameters are described as follows:
IP: the IP address of the OBServer node.
Port: The port number of the OBServer node.
Session Type: the type of the session. Valid values are
BACKGROUND, which indicates a background process, andFOREGROUND, which indicates a foreground process.Total Count: the total number of sampled sessions recorded during the analysis period of the ASH report.
Wait Event Count: the number of sampled sessions that are waiting for an event.
On CPU Count: the number of sampled sessions that occupy CPU cores.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section provides the metrics that measure the load performance of OBServer nodes in the OceanBase Database cluster.
Top Node Load:
- this section lists top node measured by DB time
- IP: OceanBase instance svr_ip
- Port: OceanBase instance svr_port
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| IP| Port|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| xx.xx.xx.xx | 2882| BACKGROUND| 7244| 972| 6272| 0.09| 41.67%|
| xx.xx.xx.xx | 2882| BACKGROUND| 5070| 555| 4515| 0.06| 29.16%|
| xx.xx.xx.xx | 2882| BACKGROUND| 5036| 533| 4503| 0.06| 28.97%|
| xx.xx.xx.xx | 2882| FOREGROUND| 35| 22| 13| 0.00| 0.20%|
| xx.xx.xx.xx | 2882| FOREGROUND| 1| 0| 1| 0.00| 0.01%|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top Foreground DB Time
The Top Foreground DB Time section shows the foreground sessions that consume the most DB time by event type. The related parameters are described as follows:
Event Name: the event that consumes the most DB time, which can be a wait event or an event that occupies CPU resources.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section displays the foreground sessions that consume the most DB time within the specified analysis period.
Top Foreground DB Time:
- this section lists top foreground db time categorized by event
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
| Event Name| Wait Class| Event Count| Avg Active Sessions| % Activity|
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
| sync rpc| NETWORK| 19| 0.00| 0.11%|
| ON CPU| NULL| 14| 0.00| 0.08%|
| sleep wait| IDLE| 3| 0.00| 0.02%|
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
Top Execution Phase
The Top Execution Phase section shows the activities that occupy high proportions during execution by session type, including the SQL statement execution, PL/SQL statement processing, and storage read/write. The related parameters are described as follows:
Session Type: the type of the session. Valid values are
BACKGROUND, which indicates a background process, andFOREGROUND, which indicates a foreground process.Phase of Execution: the name of the execution phase, which can be
IN_SQL_EXECUTION(SQL execution),IN_COMMITTING(commiting), orIN_PLSQL_EXECUTION(PL/SQL execution).Count: the number of occurrences or records in each execution phase.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section displays the activity frequency, activity percentage, and average number of concurrent sessions for key execution phases.
Top Execution Phase:
- this section lists top phases of execution, such as SQL, PL/SQL, STORAGE, etc.
+------------+----------------------------------------+----------+-----------+--------------------+
|Session Type| Phase of Execution| Count| % Activity| Avg Active Sessions|
+------------+----------------------------------------+----------+-----------+--------------------+
| BACKGROUND| IN_SQL_EXECUTION| 48| 0.28%| 0.00|
| BACKGROUND| IN_COMMITTING| 15| 0.09%| 0.00|
| BACKGROUND| IN_PLSQL_EXECUTION| 14| 0.08%| 0.00|
| BACKGROUND| IN_STORAGE_READ| 7| 0.04%| 0.00|
| BACKGROUND| IN_SQL_OPTIMIZE| 2| 0.01%| 0.00|
| BACKGROUND| IN_PLSQL_COMPILATION| 2| 0.01%| 0.00|
| BACKGROUND| IN_PARSE| 1| 0.01%| 0.00|
| FOREGROUND| IN_SQL_EXECUTION| 22| 0.13%| 0.00|
| FOREGROUND| IN_PLSQL_EXECUTION| 19| 0.11%| 0.00|
| FOREGROUND| IN_PLSQL_COMPILATION| 17| 0.10%| 0.00|
+------------+----------------------------------------+----------+-----------+--------------------+
Top Background DB Time
The Top Background DB Time section shows the background sessions that consume the most DB time. The related parameters are described as follows:
Program: the name of the process for the background session.
Event Name: the event that consumes the most DB time, which can be a wait event or an event that occupies CPU resources.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section displays the information about the processes in the background that occupy most resources, including event type, activity frequency, percentage of consumed DB time, and average number of concurrent active sessions.
Top Background DB Time:
- this section lists top DB Time for background sessions
- Program: process name for background sessions
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Program| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| T1002_ArcSender| ON CPU| NULL| 5959| 34.27%| 0.07|
| T1002_TenantWea| ON CPU| NULL| 1305| 7.51%| 0.02|
| T1002_LockWaitM| ON CPU| NULL| 1236| 7.11%| 0.01|
| T1002_IOWorker| palf write| SYSTEM_IO| 840| 4.83%| 0.01|
| RPC PROCESS| wait for network request in queue| NETWORK| 637| 3.66%| 0.01|
| T1002_Occam| ON CPU| NULL| 547| 3.15%| 0.01|
| T1002_TenantInf| ON CPU| NULL| 450| 2.59%| 0.01|
| T1002_PLSSer| ON CPU| NULL| 425| 2.44%| 0.01|
| RPC PROCESS (T1002_L5_G0)_1308| ON CPU| NULL| 364| 2.09%| 0.00|
| T1002_TntShared| ON CPU| NULL| 361| 2.08%| 0.00|
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Sessions
The Top Sessions section shows the information about the top sessions. The related parameters are described as follows:
Session ID: The ID of the sampled session.
Event Name: the event that consumes the most DB time, which can be a wait event or an event that occupies CPU resources.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section sorts sessions based on the sampled session ID, percentage of active sessions, event type, number of events, percentage of events, current user, and sampled activity status, facilitating the discovery of sessions with potential issues
Top Sessions:
- this section lists top Sessions
- Session ID: user session id
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Session ID| Program| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 3221678132| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 9| 0.05%| 0.00|
| 3221655565| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 7| 0.04%| 0.00|
| 3221655565| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 5| 0.03%| 0.00|
| 3221648823| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 3| 0.02%| 0.00|
| 3221648823| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 3| 0.02%| 0.00|
| 3221741043| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 2| 0.01%| 0.00|
| 3221678132| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sleep wait| IDLE| 2| 0.01%| 0.00|
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Groups
The Top Groups section shows the resource consumer groups that consume the most resources. The related parameters are described as follows:
Group ID: the unique ID of the resource consumer group.
Event Name: the event that consumes the most DB time, which can be a wait event or an event that occupies CPU resources.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section displays the top resource consumer groups and their key performance metrics, including the types of performance events that they frequently encounter, performance event frequency, activity percentage, and average number of concurrent active sessions.
Top Groups:
- this section lists top resource consumer groups
- Group ID: resource consumer group id
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Group ID| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 0| ON CPU| NULL| 14644| 84.23%| 0.17|
| 0| palf write| SYSTEM_IO| 840| 4.83%| 0.01|
| 1| wait for network request in queue| NETWORK| 483| 2.78%| 0.01|
| 1| ON CPU| NULL| 445| 2.56%| 0.01|
| 0| slog flush condition wait| CONCURRENCY| 182| 1.05%| 0.00|
| 2| ON CPU| NULL| 174| 1.00%| 0.00|
| 0| db file compact write| SYSTEM_IO| 166| 0.95%| 0.00|
| 0| async rpc proxy condition wait| NETWORK| 89| 0.51%| 0.00|
| 0| wait for network request in queue| NETWORK| 71| 0.41%| 0.00|
| 2| wait for network request in queue| NETWORK| 61| 0.35%| 0.00|
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Latches
The Top Latches section shows the latch that most processes in OceanBase Database contend. The related parameters are described as follows:
Latch Wait Event: the name of the latch wait event.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section identifies the bottlenecks of concurrency control in OceanBase Database by providing the following metrics: the latch resulting in the most intense contention, the number of corresponding wait events, the activity percentage, and the average number of active sessions. These metrics are essential for diagnosing and resolving latch contention issues, reducing waiting duration, and improving the response speed and overall performance of OceanBase Database.
Top Latchs:
- this section lists top latches
- Latch Wait Event: event that waiting for latch
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------------------------------------------+-------------+-----------+--------------------+
| Latch Wait Event| Event Count| % Activity| Avg Active Sessions|
+----------------------------------------------------------------+-------------+-----------+--------------------+
+----------------------------------------------------------------+-------------+-----------+--------------------+
Activity Over Time
The Activity Over Time section shows the dynamic changes at intervals of the activities in OceanBase Database during the analysis period. The related parameters are described as follows:
Slot Begin Time: the start time of the current time slice, which ends when the next time slice starts.
Event Name: the event that consumes the most DB time, which can be a wait event or an event that occupies CPU resources.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
This section displays the following metrics on dynamic changes of the activities in OceanBase Database during the analysis period, including the distribution of key events, intensity of activities, and trends in resource contention over time. With these metrics, you can identify the trend of performance issues, manage peak hours, and develop time-based performance optimization strategies.
Activity Over Time:
- this section lists time slot information during the analysis period.
- Slot Begin Time: current slot's begin time. current slot end with next slot begin time.
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Slot Begin Time| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 2024-06-13 18:20:00.000000| ON CPU| NULL| 9| 0.05%| 0.03|
| | slog flush condition wait| CONCURRENCY| 4| 0.02%| 0.01|
| 2024-06-13 18:25:00.000000| ON CPU| NULL| 57| 0.33%| 0.19|
| | slog flush condition wait| CONCURRENCY| 2| 0.01%| 0.01|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| 2024-06-13 18:30:00.000000| ON CPU| NULL| 64| 0.37%| 0.21|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:35:00.000000| ON CPU| NULL| 62| 0.36%| 0.21|
| | wait for network request in queue| NETWORK| 5| 0.03%| 0.02|
| | sync rpc| NETWORK| 2| 0.01%| 0.01|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| 2024-06-13 18:40:00.000000| ON CPU| NULL| 55| 0.32%| 0.18|
| | wait for network request in queue| NETWORK| 3| 0.02%| 0.01|
| | async rpc proxy condition wait| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:45:00.000000| ON CPU| NULL| 62| 0.36%| 0.21|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| | db file compact write| SYSTEM_IO| 2| 0.01%| 0.01|
| | slog flush condition wait| CONCURRENCY| 2| 0.01%| 0.01|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:50:00.000000| ON CPU| NULL| 60| 0.34%| 0.20|
| | palf write| SYSTEM_IO| 4| 0.02%| 0.01|
| | async rpc proxy condition wait| NETWORK| 3| 0.02%| 0.01|
| | db file compact write| SYSTEM_IO| 2| 0.01%| 0.01|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:55:00.000000| ON CPU| NULL| 58| 0.33%| 0.19|
| | palf write| SYSTEM_IO| 9| 0.05%| 0.03|
| | wait for network request in queue| NETWORK| 3| 0.02%| 0.01|
| 2024-06-13 19:00:00.000000| ON CPU| NULL| 72| 0.41%| 0.24|
| | wait for network request in queue| NETWORK| 9| 0.05%| 0.03|
| | async rpc proxy condition wait| NETWORK| 7| 0.04%| 0.02|
| | palf write| SYSTEM_IO| 5| 0.03%| 0.02|
| 2024-06-13 19:05:00.000000| ON CPU| NULL| 72| 0.41%| 0.24|
| | wait for network request in queue| NETWORK| 5| 0.03%| 0.02|
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top SQL Statement Types
The Top SQL Statement Types section shows the types of the SQL statements that are most frequently executed during the analysis period of the ASH report. The related parameters are described as follows:
SQL Statement Type: the type of the executed SQL statement, which can be
SELECT,UPDATE,INSERT, orDELETE.Total Count: the total number of times that the specified SQL statement has been executed during the analysis period of the ASH report.
Wait Event Count: the number of the specified SQL statements when the session is waiting on a wait event.
On CPU Count: the number of the specified SQL statements when the session occupies CPU resources for execution.
Avg Active Sessions: the average number of active sessions (including those waiting for an event or occupying CPU resources) in OceanBase Database during the sampling period.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section classifies the executed SQL statements, highlighting the impact of different operation types, such as SELECT and UPDATE, on database resource consumption and system activity. These statistics are critical for identifying SQL statements with high workloads, optimizing query strategies, balancing resource allocation, and implementing targeted performance tuning.
Top SQL Statement Types:
- this section lists top sql statement type.
- SQL Statement Type: SQL statement types such as SELECT or UPDATE
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
| SQL Statement Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
| T_CREATE_TABLE| 19| 18| 1| 0.00| 0.11%|
| T_ALTER_TABLE| 8| 0| 8| 0.00| 0.05%|
| T_SELECT| 5| 0| 5| 0.00| 0.03%|
| T_DROP_DATABASE| 3| 3| 0| 0.00| 0.02%|
| T_CALL_PROCEDURE| 1| 1| 0| 0.00| 0.01%|
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top SQL with Top DB Time
The Top SQL with Top DB Time section shows the SQL statements that account for the highest percentages of the sampled sessions during the analysis period of the ASH report. The related parameters are described as follows:
Plan Hash: the hash value of the current SQL execution plan. Execution plans of the same type share one hash value.
Total Count: the total number of times that the specified SQL statement has been executed during the analysis period of the ASH report.
Wait Event Count: the number of the specified SQL statements when the session is waiting on a wait event.
On CPU Count: the number of the specified SQL statements when the session occupies CPU resources for execution.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section displays the SQL statements that consume the most DB Time and their execution characteristics. These statistics help the database administrator (DBA) identify underperforming SQL statements and trace changes in execution plans using the hash value, to maximize the execution efficiency of SQL statements.
Top SQL with Top DB Time:
- This Section lists the SQL statements that accounted for the highest percentages of sampled session activity.
- Plan Hash: Numeric representation of the current SQL plan.
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Total Count| Wait Event Count| On CPU Count| % Activity| SQL Text|
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| 4| 0| 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| 4| 0| 4| 0.02%| CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 |
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
Top SQL with Top Events
The Top SQL with Top Events section shows the SQL statements that are most closely associated with the specified top events. The related parameters are described as follows:
Plan Hash: the hash value of the current SQL execution plan, which is used to uniquely identify each execution plan.
Event: the name of the event triggered by the user.
Event Count: the number of sampled session activities recorded for each event.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section lists the SQL statements and events that account for the highest activity percentages of the sampled sessions. By pinpointing these bottleneck events and related SQL statements, the DBA can optimize underperforming SQL statements, adjust their execution plans, and improve system configurations, to reduce waiting duration and enhance resource utilization.
Top SQL with Top Events:
- This Section lists the SQL statements that accounted for the highest percentages event.
- Plan Hash: Numeric representation of the current SQL plan
- Event: top event for current SQL plan
- Event Count: num of samples for top event
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Event| Event Count| % Activity| SQL Text|
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| ON CPU| 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
Top SQL with Top Operator
The Top SQL with Top Operator section shows the SQL statements that use specified operators and account for high activity percentages of the sampled sessions. The related parameters are described as follows:
Plan Hash: the hash value of the current SQL execution plan, which is used to uniquely identify each execution plan.
Operator: the frequently used operator or the operator that consumes the most resources in the current SQL execution plan.
Count: the number of occurrences or records in each execution phase.
% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section lists the operators used in SQL execution plans that lead to high session activeness. The provided information helps the DBA and developers efficiently improve the execution paths of SQL statements.
Top SQL with Top Operator:
- This Section lists the SQL statements that accounted for the highest percentages of sampled session activity with sql operator
- Plan Hash: Numeric representation of the current SQL plan
- Operator: top operator name for current SQL plan
- Count: num of samples for top current SQL operator
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Operator| Count| % Activity| SQL Text|
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| | 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| | 4| 0.02%| CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 |
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
Top PL/SQL Procedures
The Top PL/SQL Procedures section shows the information about the top PL/SQL stored procedures. The related parameters are described as follows:
PL/SQL Entry Subprogram: the top PL/SQL entry subprogram in the application, which can be a procedure, function, trigger, or package initialization.
PL/SQL Current Subprogram: the current PL/SQL subprogram being executed at the sampling point. The value
SQLindicates the percentage of time that a specified PL/SQL entry subprogram spends executing SQL statements. The value--indicates that no subprogram is specified for execution.% Activity: the percentage of activities (including wait events and events occupying CPU resources) to all events that occurred in the given tenant within the specified period of time.
This section displays the execution of top PL/SQL stored procedures, including the top entry subprogram, current subprogram, and activity percentage.
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 subprogram name
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| PLSQL Entry Subprogram| PLSQL Current Subprogram| % Activity|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| .| -| 0.09%|
| -| .| 0.08%|
| -| .| 0.01%|
| -| SQL| 0.09%|
| .| -| 0.04%|
| -| .| 0.04%|
| -| SQL| 0.04%|
| .| -| 0.03%|
| -| .| 0.03%|
| -| SQL| 0.03%|
| .| -| 0.02%|
| -| .| 0.02%|
| -| .| 0.01%|
| -| SQL| 0.02%|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
Complete List of SQL Text
The Complete List of SQL Text section shows a list of SQL text, including the queried ID and text of each SQL statement. The related parameters are described as follows:
SQL ID: the unique ID of the SQL statement.
SQL Text: the text of the SQL statement.
This section provides a SQL query list, which can be used to identify, analyze, and optimize query performance.
Complete List of SQL Text:
SQL ID: E828C9647CCA3FC0E94A9117C8A28A46
SQL Text: create table dup_part_zero(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: 1DCD1A13FF867BD1C03DE1582C274EE6
SQL Text: CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2024-06-22 20:27:07' )
SQL ID: 1DCD1A13FF867BD1C03DE1582C274EE6
SQL Text: SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,:2,:3,:4, :6, :7, :8, LOWER(:5)) AS REPORT
SQL ID: E040EE778B7D6089C672F8A39E378AE9
SQL Text: INSERT INTO __all_column_usage(tenant_id,table_id,column_id,equality_preds,equijoin_preds,nonequijion_preds,range_preds,like_preds,null_preds,distinct_member,groupby_member,flags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?
SQL ID: 8D67BCF7B67A397C590C4173B8AA7160
SQL Text: drop database mwx
SQL ID: 63094A5D530AA167DC53930A30C383ED
SQL Text: create table dup_part_five(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: 948C3E3C7B0A265A3FFB574BC37D8558
SQL Text: create table dup_part_three(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: D3711121823630566148389A5434F5CC
SQL Text: create table dup_part_two(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: CF05F891D761DB9505D1EA76FDB29165
SQL Text: INSERT INTO __all_column_usage(tenant_id,table_id,column_id,equality_preds,equijoin_preds,nonequijion_preds,range_preds,like_preds,null_preds,distinct_member,groupby_member,flags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)ON DUPLICATE KEY UPDATE equality_preds = equality_preds + if (values(flags) & ?, ?, ?),equijoin_preds = equijoin_preds + if (values(flags) & ?, ?, ?),nonequijion_preds = nonequijion_preds + if (values(flags) &
SQL ID: 175EFE9FDD277AA4CBB1EC93AAA622F1
SQL Text: drop database info
Complete report
Here is a complete ASH report:
ASH Report
Cluster Name: test424
Observer Version: OceanBase 4.2.4.0 (200000342024061210-c4c0c18741e45a1d40889b6147c3132574xxxxxx)
Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
User Input Begin Time: 2023-09-22 10:26:47
User Input End Time: 2024-06-22 20:27:07
Analysis Begin Time: 2024-06-13 18:24:34
Analysis End Time: 2024-06-14 17:42:35
Elapsed Time: 83880
Num of Sample: 17386
Average Active Sessions: 0.21
Top Active Tenants:
- this section lists top active tenant information
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
|Tenant ID|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| 1002| BACKGROUND| 17350| 2060| 15290| 0.21| 99.79%|
| 1002| FOREGROUND| 36| 22| 14| 0.00| 0.21%|
+---------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top Node Load:
- this section lists top node measured by DB time
- IP: OceanBase instance svr_ip
- Port: OceanBase instance svr_port
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| IP| Port|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
| xx.xx.xx.xx | 2882| BACKGROUND| 7244| 972| 6272| 0.09| 41.67%|
| xx.xx.xx.xx | 2882| BACKGROUND| 5070| 555| 4515| 0.06| 29.16%|
| xx.xx.xx.xx | 2882| BACKGROUND| 5036| 533| 4503| 0.06| 28.97%|
| xx.xx.xx.xx | 2882| FOREGROUND| 35| 22| 13| 0.00| 0.20%|
| xx.xx.xx.xx | 2882| FOREGROUND| 1| 0| 1| 0.00| 0.01%|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top Foreground DB Time:
- this section lists top foreground db time categorized by event
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
| Event Name| Wait Class| Event Count| Avg Active Sessions| % Activity|
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
| sync rpc| NETWORK| 19| 0.00| 0.11%|
| ON CPU| NULL| 14| 0.00| 0.08%|
| sleep wait| IDLE| 3| 0.00| 0.02%|
+----------------------------------------------------------------+--------------------+-------------+--------------------+-----------+
Top Execution Phase:
- this section lists top phases of execution, such as SQL, PL/SQL, STORAGE, etc.
+------------+----------------------------------------+----------+-----------+--------------------+
|Session Type| Phase of Execution| Count| % Activity| Avg Active Sessions|
+------------+----------------------------------------+----------+-----------+--------------------+
| BACKGROUND| IN_SQL_EXECUTION| 48| 0.28%| 0.00|
| BACKGROUND| IN_COMMITTING| 15| 0.09%| 0.00|
| BACKGROUND| IN_PLSQL_EXECUTION| 14| 0.08%| 0.00|
| BACKGROUND| IN_STORAGE_READ| 7| 0.04%| 0.00|
| BACKGROUND| IN_SQL_OPTIMIZE| 2| 0.01%| 0.00|
| BACKGROUND| IN_PLSQL_COMPILATION| 2| 0.01%| 0.00|
| BACKGROUND| IN_PARSE| 1| 0.01%| 0.00|
| FOREGROUND| IN_SQL_EXECUTION| 22| 0.13%| 0.00|
| FOREGROUND| IN_PLSQL_EXECUTION| 19| 0.11%| 0.00|
| FOREGROUND| IN_PLSQL_COMPILATION| 17| 0.10%| 0.00|
+------------+----------------------------------------+----------+-----------+--------------------+
Top Background DB Time:
- this section lists top DB Time for background sessions
- Program: process name for background sessions
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Program| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| T1002_ArcSender| ON CPU| NULL| 5959| 34.27%| 0.07|
| T1002_TenantWea| ON CPU| NULL| 1305| 7.51%| 0.02|
| T1002_LockWaitM| ON CPU| NULL| 1236| 7.11%| 0.01|
| T1002_IOWorker| palf write| SYSTEM_IO| 840| 4.83%| 0.01|
| RPC PROCESS| wait for network request in queue| NETWORK| 637| 3.66%| 0.01|
| T1002_Occam| ON CPU| NULL| 547| 3.15%| 0.01|
| T1002_TenantInf| ON CPU| NULL| 450| 2.59%| 0.01|
| T1002_PLSSer| ON CPU| NULL| 425| 2.44%| 0.01|
| RPC PROCESS (T1002_L5_G0)_1308| ON CPU| NULL| 364| 2.09%| 0.00|
| T1002_TntShared| ON CPU| NULL| 361| 2.08%| 0.00|
+-----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Sessions:
- this section lists top Sessions
- Session ID: user session id
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Session ID| Program| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 3221678132| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 9| 0.05%| 0.00|
| 3221655565| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 7| 0.04%| 0.00|
| 3221655565| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 5| 0.03%| 0.00|
| 3221648823| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 3| 0.02%| 0.00|
| 3221648823| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sync rpc| NETWORK| 3| 0.02%| 0.00|
| 3221741043| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| ON CPU| NULL| 2| 0.01%| 0.00|
| 3221678132| SQL CMD Query EXEC root@xx.xx.xx.xx (T1002_L0_G0)| sleep wait| IDLE| 2| 0.01%| 0.00|
+--------------------+----------------------------------------------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Groups:
- this section lists top resource consumer groups
- Group ID: resource consumer group id
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Group ID| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 0| ON CPU| NULL| 14644| 84.23%| 0.17|
| 0| palf write| SYSTEM_IO| 840| 4.83%| 0.01|
| 1| wait for network request in queue| NETWORK| 483| 2.78%| 0.01|
| 1| ON CPU| NULL| 445| 2.56%| 0.01|
| 0| slog flush condition wait| CONCURRENCY| 182| 1.05%| 0.00|
| 2| ON CPU| NULL| 174| 1.00%| 0.00|
| 0| db file compact write| SYSTEM_IO| 166| 0.95%| 0.00|
| 0| async rpc proxy condition wait| NETWORK| 89| 0.51%| 0.00|
| 0| wait for network request in queue| NETWORK| 71| 0.41%| 0.00|
| 2| wait for network request in queue| NETWORK| 61| 0.35%| 0.00|
+----------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top Latchs:
- this section lists top latches
- Latch Wait Event: event that waiting for latch
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------------------------------------------+-------------+-----------+--------------------+
| Latch Wait Event| Event Count| % Activity| Avg Active Sessions|
+----------------------------------------------------------------+-------------+-----------+--------------------+
+----------------------------------------------------------------+-------------+-----------+--------------------+
Activity Over Time:
- this section lists time slot information during the analysis period.
- Slot Begin Time: current slot's begin time. current slot end with next slot begin time.
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| Slot Begin Time| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
| 2024-06-13 18:20:00.000000| ON CPU| NULL| 9| 0.05%| 0.03|
| | slog flush condition wait| CONCURRENCY| 4| 0.02%| 0.01|
| 2024-06-13 18:25:00.000000| ON CPU| NULL| 57| 0.33%| 0.19|
| | slog flush condition wait| CONCURRENCY| 2| 0.01%| 0.01|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| 2024-06-13 18:30:00.000000| ON CPU| NULL| 64| 0.37%| 0.21|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:35:00.000000| ON CPU| NULL| 62| 0.36%| 0.21|
| | wait for network request in queue| NETWORK| 5| 0.03%| 0.02|
| | sync rpc| NETWORK| 2| 0.01%| 0.01|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| 2024-06-13 18:40:00.000000| ON CPU| NULL| 55| 0.32%| 0.18|
| | wait for network request in queue| NETWORK| 3| 0.02%| 0.01|
| | async rpc proxy condition wait| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:45:00.000000| ON CPU| NULL| 62| 0.36%| 0.21|
| | palf write| SYSTEM_IO| 2| 0.01%| 0.01|
| | db file compact write| SYSTEM_IO| 2| 0.01%| 0.01|
| | slog flush condition wait| CONCURRENCY| 2| 0.01%| 0.01|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:50:00.000000| ON CPU| NULL| 60| 0.34%| 0.20|
| | palf write| SYSTEM_IO| 4| 0.02%| 0.01|
| | async rpc proxy condition wait| NETWORK| 3| 0.02%| 0.01|
| | db file compact write| SYSTEM_IO| 2| 0.01%| 0.01|
| | wait for network request in queue| NETWORK| 2| 0.01%| 0.01|
| 2024-06-13 18:55:00.000000| ON CPU| NULL| 58| 0.33%| 0.19|
| | palf write| SYSTEM_IO| 9| 0.05%| 0.03|
| | wait for network request in queue| NETWORK| 3| 0.02%| 0.01|
| 2024-06-13 19:00:00.000000| ON CPU| NULL| 72| 0.41%| 0.24|
| | wait for network request in queue| NETWORK| 9| 0.05%| 0.03|
| | async rpc proxy condition wait| NETWORK| 7| 0.04%| 0.02|
| | palf write| SYSTEM_IO| 5| 0.03%| 0.02|
| 2024-06-13 19:05:00.000000| ON CPU| NULL| 72| 0.41%| 0.24|
| | wait for network request in queue| NETWORK| 5| 0.03%| 0.02|
+----------------------------+----------------------------------------------------------------+--------------------+-------------+-----------+--------------------+
Top SQL Statement Types:
- this section lists top sql statement type.
- SQL Statement Type: SQL statement types such as SELECT or UPDATE
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- Avg Active Sessions: average active sessions during ash report analysis time period
- % Activity: activity(cpu + wait) percentage for given tenant
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
| SQL Statement Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
| T_CREATE_TABLE| 19| 18| 1| 0.00| 0.11%|
| T_ALTER_TABLE| 8| 0| 8| 0.00| 0.05%|
| T_SELECT| 5| 0| 5| 0.00| 0.03%|
| T_DROP_DATABASE| 3| 3| 0| 0.00| 0.02%|
| T_CALL_PROCEDURE| 1| 1| 0| 0.00| 0.01%|
+---------------------------------------------+------------------+-----------------------+-------------------+--------------------+-----------+
Top SQL with Top DB Time:
- This Section lists the SQL statements that accounted for the highest percentages of sampled session activity.
- Plan Hash: Numeric representation of the current SQL plan.
- Total Count: num of records during ash report analysis time period
- Wait Event Count: num of records when session is on wait event
- On CPU Count: num of records when session is on cpu
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Total Count| Wait Event Count| On CPU Count| % Activity| SQL Text|
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| 4| 0| 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| 4| 0| 4| 0.02%| CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 |
+----------------------------------------+--------------------+------------------+-----------------------+-------------------+------------+----------------------------------------------------------------+
Top SQL with Top Events:
- This Section lists the SQL statements that accounted for the highest percentages event.
- Plan Hash: Numeric representation of the current SQL plan
- Event: top event for current SQL plan
- Event Count: num of samples for top event
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Event| Event Count| % Activity| SQL Text|
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| ON CPU| 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
+----------------------------------------+--------------------+----------------------------------------------------------------+--------------+------------+----------------------------------------------------------------+
Top SQL with Top Operator:
- This Section lists the SQL statements that accounted for the highest percentages of sampled session activity with sql operator
- Plan Hash: Numeric representation of the current SQL plan
- Operator: top operator name for current SQL plan
- Count: num of samples for top current SQL operator
- % Activity: activity percentage for given event
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Operator| Count| % Activity| SQL Text|
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| | 4| 0.02%| SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,|
| 1DCD1A13FF867BD1C03DE1582C274EE6| 6388442502389984409| | 4| 0.02%| CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 |
+----------------------------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------------------------------------------------------+
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 subprogram name
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| PLSQL Entry Subprogram| PLSQL Current Subprogram| % Activity|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
| .| -| 0.09%|
| -| .| 0.08%|
| -| .| 0.01%|
| -| SQL| 0.09%|
| .| -| 0.04%|
| -| .| 0.04%|
| -| SQL| 0.04%|
| .| -| 0.03%|
| -| .| 0.03%|
| -| SQL| 0.03%|
| .| -| 0.02%|
| -| .| 0.02%|
| -| .| 0.01%|
| -| SQL| 0.02%|
+------------------------------------------------------------+------------------------------------------------------------+--------------------+
Complete List of SQL Text:
SQL ID: E828C9647CCA3FC0E94A9117C8A28A46
SQL Text: create table dup_part_zero(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: 1DCD1A13FF867BD1C03DE1582C274EE6
SQL Text: CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT( '2023-09-22 10:26:47', '2024-06-22 20:27:07' )
SQL ID: 1DCD1A13FF867BD1C03DE1582C274EE6
SQL Text: SELECT DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:0, :1,:2,:3,:4, :6, :7, :8, LOWER(:5)) AS REPORT
SQL ID: E040EE778B7D6089C672F8A39E378AE9
SQL Text: INSERT INTO __all_column_usage(tenant_id,table_id,column_id,equality_preds,equijoin_preds,nonequijion_preds,range_preds,like_preds,null_preds,distinct_member,groupby_member,flags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?
SQL ID: 8D67BCF7B67A397C590C4173B8AA7160
SQL Text: drop database mwx
SQL ID: 63094A5D530AA167DC53930A30C383ED
SQL Text: create table dup_part_five(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: 948C3E3C7B0A265A3FFB574BC37D8558
SQL Text: create table dup_part_three(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: D3711121823630566148389A5434F5CC
SQL Text: create table dup_part_two(c1 int, c2 int, c3 longtext, index(c1) local, index(c2) global) duplicate_scope = 'cluster'
SQL ID: CF05F891D761DB9505D1EA76FDB29165
SQL Text: INSERT INTO __all_column_usage(tenant_id,table_id,column_id,equality_preds,equijoin_preds,nonequijion_preds,range_preds,like_preds,null_preds,distinct_member,groupby_member,flags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)ON DUPLICATE KEY UPDATE equality_preds = equality_preds + if (values(flags) & ?, ?, ?),equijoin_preds = equijoin_preds + if (values(flags) & ?, ?, ?),nonequijion_preds = nonequijion_preds + if (values(flags) &
SQL ID: 175EFE9FDD277AA4CBB1EC93AAA622F1
SQL Text: drop database info
Related wait events
For the meanings of related wait events, see Common wait events, which can help you quickly locate problems during ASH report analysis.