After you generate an ASH report, analyze the report to identify the possible causes of the performance issues.
ASH report content
The ASH Report section of the ASH report records statistical data about the ASH report, including the system version, operating system environment, information sampling period, analysis period, ASH and WR data sources, sampling count, event count, and average active session count. It generates information about 18 modules, such as Top Active Tenants, Top Node Load, Top Groups, Top Foreground DB Time, Top Background DB Time, Top Sessions, Top IO Bandwidth, Top Blocking Sessions, Top Latchs, Top DB Objects, Activity Over Time, Top Execution Phase, Top IO Events, Top SQL Statement Types, Top SQL with Top Events, Top SQL with Top Operator, Top PL/SQL Procedures, and Complete List of SQL Text And Status.
These modules mainly display the performance data collected by ObServer from two aspects: resource overhead and execution path overhead. Therefore, when analyzing specific performance issues, you can check the relevant modules to determine whether the issue is caused by resource bottlenecks or inefficient execution methods.
ASH report
This section displays some information about sampling time, analysis time, and general statistics in the ASH (Active Session History) report.
- Cluster Name: the name of the database cluster.
- Observer Version: the version of OceanBase Database, which contains the version number and the detailed build version.
- Operation System Info: the operating system information, including the kernel version and architecture.
- User Input Begin Time: the sampling start time, which indicates the time when session activities start to be recorded.
- User Input End Time: the sampling end time, which indicates the time when session activities stop being recorded.
- Analysis Begin Time: the analysis start time, which indicates the start time when the sampled data is analyzed.
- Analysis End Time: the analysis end time, which indicates the end time when the sampled data is analyzed.
- Ash Data Source: the ASH data source. This column does not exist if ASH is not used.
- Wr Data Source: the WR data source. This column does not exist if WR is not used.
- Elapsed Time: the total number of seconds of the sampling time.
- Ash Num of Sample: the number of ASH samples, which indicates the number of recorded session activities.
- Wr Num of Sample: the number of WR samples, which indicates the number of performance snapshots generated in the analysis time range.
- Average Active Sessions: the average number of active sessions, which indicates the average number of active sessions during the sampling period.
These data can help you understand the activity status, the number of sessions and events, and the system activity level during the sampling period.
ASH Report
Cluster Name: test425
Observer Version: OceanBase 4.2.5.3 (203000012025022717-866087xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
Operation System Info: Linux(3.10.0-327.ali2019.alios7.x86_64)_x86_64
User Input Begin Time: 2024-09-22 10:26:47
User Input End Time: 2025-06-22 20:27:07
Ash Analysis Begin Time: 2025-03-05 12:59:50
Ash Analysis End Time: 2025-03-05 15:47:12
Wr Analysis Begin Time: 2025-02-28 09:43:43
Wr Analysis End Time: 2025-03-05 12:59:35
Ash Data Source: oceanbase.GV$ACTIVE_SESSION_HISTORY
Wr Data Source: oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY
Elapsed Time: 453793
Ash Num of Sample: 11019
Wr Num of Sample: 475940
Average Active Sessions: 1.07
Top Active Tenants
This section divides the resource overhead into foreground user requests and background tasks, and displays the overall resource overhead of the tenant.
- Tenant Name: the name of the tenant, which identifies the information of a specific tenant.
- Session Type: the session type. The value can be BACKGROUND or FOREGROUND, which respectively indicate a background process and a foreground process.
- Total Samples: the total number of records, which indicates the total number of samples in the ASH report analysis time range.
- Wait Event Count: the number of wait events, which indicates the number of samples of sessions waiting for events.
- On CPU Samples: the number of samples on the CPU, which indicates the number of samples of sessions executed on the CPU.
- Avg Active Sessions: the average number of active sessions, which indicates the average number of active database sessions at the same time during the sampling period, regardless of whether the sessions are on the CPU or waiting for events.
- % Activity: the activity percentage of events, which indicates the percentage of activities (CPU + wait) of a specified tenant in a specified time range.
- Equivalent Client Load: the number of active connections between the client and the database in the ASH report analysis time range.
The information in this section provides details about tenant activities and reveals different aspects of database performance and the resource usage of tenants.
In the Top Active Tenants section, performance issues may originate from within the database or from the interaction between the database and the business application. First, you need to confirm the performance metrics Equivalent Client Load and Avg Active Sessions. These two metrics are used to evaluate the impact of the database and the link on performance. The higher the ratio (Avg Active Sessions/Equivalent Client Load), the greater the impact of internal database overhead on performance; the lower the ratio, the greater the impact of the network link or client on performance.
For example, the report shows that the ratio is 88%, indicating that the performance issue is primarily caused by database load. After confirming that the database load is problematic, you should further analyze the overhead of internal database resources. In the Top Active Tenants section, foreground user requests consume 69.65% of the database resources, while background tasks account for about 30%. Therefore, the performance bottleneck mainly occurs in the execution chain of user requests. Additionally, foreground tasks consume a total of 1140 seconds of database time in waiting events, while CPU execution consumes 72860 seconds. This suggests that the business model does not suffer from significant waiting due to resource limitations such as disk I/O or network bandwidth. The main overhead is concentrated on CPU execution. Since the business is running normally in the current version, it can be inferred that the CPU resources of the cluster are sufficient. The performance slowdown may be related to a decline in the efficiency of a certain link in the SQL execution chain after the upgrade.
Top Node Load
This section displays the performance metrics of the top nodes in the DB time metric.
IP: the IP address of the server.
Port: the port number of the server.
Session Type: the session type. The value can be BACKGROUND or FOREGROUND, which respectively indicate a background process and a foreground process.
Total Samples: the total number of records, which indicates the total number of samples in the ASH report analysis time range.
Wait Event Samples: the number of wait events, which indicates the number of samples of sessions waiting for events.
On CPU Samples: the number of samples on the CPU, which indicates the number of samples of sessions executed on the CPU.
Avg Active Sessions: the average number of active sessions, which indicates the average number of active database sessions at the same time during the sampling period, regardless of whether the sessions are on the CPU or waiting for events.
% Activity: the activity percentage of events, which indicates the percentage of activities (CPU + wait) of a specified tenant in a specified time range.
Equivalent Client Load: the number of active connections between the client and the database. The higher the ratio (Avg Active Sessions/Equivalent Client Load), the greater the impact of internal database overhead on performance; the lower the ratio, the greater the impact of the network link or client on performance.
The information in this section provides the performance metrics related to the load of each node in the database cluster.
This section displays the performance metrics of the top nodes in the DB time metric.
Top Groups
This section displays the top resource-consuming user resource management groups (Resource Consumer Groups).
Node: the node address.
Group Name: the name of the resource group.
Group Samples: the number of session activity records sampled in the current resource group.
% Activity: the percentage of activity for the event, indicating the percentage of activity (CPU + waiting) for the specified tenant during the specified period.
Program: the name of the process corresponding to the background session.
% Program: the percentage of the background session process in the resource group (such as CPU or thread).
Module: the name of the functional module in the program.
% Module: the percentage of the module in active sessions, used to locate hot operations within the program.
Action: the specific operation in the module.
% Action: the percentage of the operation in active sessions, further refining the granularity of performance analysis.
Avg Active Sessions: the average number of active sessions, indicating the average number of active database sessions during the sampling period (regardless of whether they are on CPU or waiting for events).
Slot Begin Time: the start time of the session activity period, in the format
YYYY-MM-DD HH:MM:SS(+ offset in seconds), indicating the start of the statistical period.Slot Count: the total number of samples in the current period. ASH samples active session states once per second by default, so the Slot Count value equals the length of the period in seconds.
Action Key: identifies the type of operation that triggered the active session, used to locate resource usage for specific services or components. It typically contains the following information:
- Source node: such as
xx.xx.xx.xx:2882(IP and port). - Tenant and resource group: such as
tenant:1002/group:0. - Service type: such as
T1002_LogService-LogRestoreService-RemoteLogWriter(log restore service).
- Source node: such as
Action Samples: the number of active sessions sampled for the operation (Action Key) in the current period. A higher value indicates more database resources are consumed by the operation.
This information provides details about the top resource-consuming groups and their key performance metrics, including the main performance events they encounter, the frequency of these events, their activity percentage, and the average number of concurrent sessions.
Top Foreground DB Time
This section lists the main consumers of database foreground activity time by event type.
Node: the node address.
Event Name: the event that consumes the most DB time, including both wait events and CPU events.
Wait Class: the type of the wait event.
Event Samples: the number of session activity records sampled for each event.
Avg Active Sessions: the average number of active sessions, indicating the average number of active database sessions during the sampling period (regardless of whether they are on CPU or waiting for events).
% Activity: the percentage of activity for the event, indicating the percentage of activity (CPU + waiting) for the specified tenant during the specified period.
This information provides details about the top consumers of DB time in foreground sessions during the specified analysis period.
For example, in the Top Foreground DB Time data, it can be seen that lock conflicts caused by accessing transaction contexts do occur during business task execution. However, this overhead accounts for less than 1% of the total, and no wait event corresponding to transaction commit (tx committing wait) was found. Therefore, the two assumptions about performance issues in Top Active Tenants can be ruled out.
In conclusion, the performance degradation is not caused by resource bottlenecks. Therefore, it is likely that the execution chain in the new version has regressed in performance.
Top Background DB Time
This section displays the DB time consumed by background sessions.
Node: the node address.
Program: the name of the process corresponding to the background session.
Module: the name of the functional module in the program.
Action: the specific operation in the module.
Event Name: the event that consumes the most DB time, including both wait events and CPU events.
Wait Class: the type of the wait event.
Event Samples: the number of session activity records sampled for each event.
% Activity: the percentage of activity for the event, indicating the percentage of activity (CPU + waiting) for the specified tenant during the specified period.
Avg Active Sessions: the average number of active sessions, indicating the average number of active database sessions during the sampling period (regardless of whether they are on CPU or waiting for events).
This information provides details about the most resource-consuming background processes, the main types of events they experience, their activity frequency, their contribution to overall DB time, and the average number of concurrent active sessions.
Top Sessions
This section displays information about the top sessions (Top Sessions).
Session ID: the ID of the sampled session.
Program: the name of the process corresponding to the background session.
% Activity: the percentage of activity for the event, indicating the percentage of activity (CPU + waiting) for the specified tenant during the specified period.
Avg Active Sessions: the average number of active sessions, indicating the average number of active database sessions during the sampling period (regardless of whether they are on CPU or waiting for events).
Event Name: the event that consumes the most DB time, including both wait events and CPU events.
Wait Class: the type of the wait event.
% Event: the percentage of the event in total database activity. A high percentage may indicate that the event is a major bottleneck in system performance.
SQL ID: the unique identifier of the SQL query.
Plan Hash: the numerical value of the current SQL execution plan. The same plan has the same hash value.
% SQL ID: the percentage of the SQL statement associated with the event in total database activity. A high percentage indicates that the SQL statement is a major source of resource consumption.
Sql Executions: the number of times the SQL statement is executed during the analysis period. High-frequency execution may lead to resource contention or lock conflicts.
This information provides statistics on active sessions sorted by session. By examining the sampled session ID, activity percentage, events, number of events, event percentage, and comparing with the current user and sampled activity status, potential issues can be identified.
Top IO Bandwidth
This section lists the top I/O bandwidths from the perspectives of SQL and background tasks.
Node: the node address.
Program Module Action/SQL ID: the type of background task (such as log aggregation or transaction log write) or the unique identifier of an SQL query, which tracks the resource consumption of specific SQL statements.
Plan Hash: the numerical value of the current SQL execution plan. The same plan has the same hash value.
Type: the type of I/O operation, which distinguishes the source of read and write loads.
IOPS: the number of I/O requests per second, reflecting the load pressure on the storage device. A high IOPS may indicate frequent small data operations.
IO Size(MB): the total read and write data volume (MB) of the task or SQL during the sampling period. Background tasks typically involve larger data volumes (such as log batch writes).
IO Bandwidth(MB/s): the I/O throughput per second, measuring the actual utilization of the storage bandwidth. Low bandwidth may be caused by data dispersion or hardware limitations.
Object ID: the main data shard involved in the I/O operation. If no data is involved, it indicates no data dispersion or no data was collected during the sampling period.
% Object ID: the I/O ratio of the dominant shard.
These details provide information about parameters such as Node, SQL ID, and IOPS, enabling the analysis of I/O throughput for SQL and background tasks. By considering read/write types, data volume, and distribution characteristics, performance bottlenecks can be identified.
Top Blocking Sessions
This section displays blocking session issues in the database caused by locks (enqueues), latches, and buffer busy waits.
Blocking Session ID: the session identifier that blocks other sessions, used to locate the source of the issue.
% Activity: the percentage of events triggered by the blocking session, with a high percentage indicating it is a primary contention source.
Avg Active Sessions: the average number of active sessions for the blocking session during the sampling period, indicating how many database sessions were active on average during the sampling period (whether on the CPU or waiting for an event).
Holder User: the user holding the lock or resource.
Holder TX ID: the transaction ID of the resource holder.
Holder SQL ID: the SQL statement identifier holding the resource, used to associate with specific operations.
Event Caused: the type of waiting event triggered by the blocking session.
% Event: the percentage of this event among similar blocking events, helping to identify frequently contended event types.
XIDs: the transaction ID chain associated with the event, used to trace transaction dependencies.
Top Waiting SQL ID: the SQL statement identifier most affected by the blocking session.
% SQL ID: the percentage of this SQL statement in total blocking waits, used to prioritize optimization of high-impact queries.
These details provide fields such as Blocking Session ID and Holder TX ID to precisely locate the source of blocking. By combining the quantified contention impact from % Activity and % Event, the SQL-level root cause can be associated using Holder SQL ID and Top Waiting SQL ID. Transaction chain tracing is also enabled through XIDs, offering end-to-end diagnostic capabilities from sessions to transactions to SQL statements for database performance bottlenecks.
Top Latchs
This section displays the latches with the most contention in the database.
Latch Wait Event: the name of the latch wait event.
Event Samples: the number of session activity records sampled for each event.
% Activity: the activity percentage of the event, indicating the percentage of activities (CPU + wait) for the specified tenant during the specified period.
Avg Active Sessions: the average number of active sessions, indicating how many database sessions were active on average during the sampling period (whether on the CPU or waiting for an event).
These details provide information about the latches causing the highest wait, their wait event counts, activity percentages, and average active sessions, directly pointing to the key bottlenecks in the database's concurrency control. This information is crucial for diagnosing and resolving latch contention issues, reducing wait times, and improving system response speed and overall performance.
Top DB Objects
This section quantifies the access load and associated SQL statements for database objects (such as tables and partitions) by analyzing four types of critical wait events: Application, Cluster, User I/O, and buffer busy waits, to identify high-contention hotspots.
Node Address: the node address (in the format IP:Port), used to identify the physical node location in a distributed environment.
Tenant ID: the tenant ID, the unique identifier of the tenant.
Object ID: the tablet_id of the database table, used for metadata association.
Avg Active Sessions: the average number of active sessions, indicating how many database sessions were active on average during the sampling period (whether on the CPU or waiting for an event).
% Activity: the activity percentage of the event, indicating the percentage of activities (CPU + wait) for the specified tenant during the specified period.
Execution Count: the total number of SQL executions for the object. Frequent access may lead to resource contention.
Event: the corresponding top wait event (up to 5 events are printed).
% Event: the percentage of the corresponding top wait event (up to 5 events are printed).
SQL ID: the SQL ID with high load in the wait event.
% SQL ID/Module: the percentage of the SQL ID or module with high load in the wait event, used to prioritize optimization of high-impact queries.
Object Name (Type/Partition Name): the name, type, and partition information of the database object being accessed.
These details quantify the access load, associated SQL statements, and wait events for database objects (such as tables and partitions), identifying the hotspots causing high resource contention. This provides direct evidence for optimizing table structures, index designs, and SQL performance.
Activity Over Time
This section displays the dynamic changes in database activity during the analysis period based on time intervals.
Slot Begin Time: the start time of the current time slot, which ends until the next time slot begins.
Slot Count: the total number of samples in the current time period. ASH samples the active session status once per second by default, so the Slot Count value equals the length of the time period in seconds.
Event Name: the event that consumes the most DB time, including both wait events and events on the CPU.
Wait Class: the type of the wait event.
Event Samples: the number of session activity records sampled for each event.
% Activity: the activity percentage of the event, indicating the percentage of activities (CPU + wait) for the specified tenant during the specified period.
Avg Active Sessions: the average number of active sessions, indicating how many database sessions were active on average during the sampling period (whether on the CPU or waiting for an event).
These details provide a time-series view, detailing the dynamic changes in database activity during the analysis period, including the distribution of key events, activity intensity, and the evolution of resource contention over time. This is valuable for identifying performance trends, managing peak periods, and developing performance optimization strategies based on time dimensions.
Top Execution Phase
This section displays the execution phases with the highest activity, categorized by session type, covering SQL execution, PL/SQL processing, storage read/write, and more.
Session Type: The session type, which can be BACKGROUND or FOREGROUND, indicating background processes and foreground processes, respectively.
Phase of Execution: The name of the execution phase, such as IN_SQL_EXECUTION (SQL execution), IN_COMMITTING (commit process), IN_PLSQL_EXECUTION (PL/SQL execution), etc.
Active Samples: The number of occurrences or records for each execution phase.
% Activity: The activity percentage of the event, representing the percentage of activities (CPU + wait) for the specified tenant during the specified period.
SQL ID: The SQL ID with high load in the wait event.
% SQL ID/Module: The percentage of the SQL ID or module with high load in the wait event, used to prioritize optimization of high-impact queries.
Since performance issues in the base path usually affect all requests in the database, the Top Execution Phase can intuitively summarize the impact of performance on the base path.
For example, the content of the Top Execution Phase module provides the following information:
IN_SQL_EXECUTION accounts for 69.65% of the overhead, representing the total overhead after SQL plans are obtained and execution begins. In OceanBase Database, IN_SQL_EXECUTION includes IN_STORAGE_WRITE, IN_STORAGE_READ, and IN_RPC_ENCODE. From this metric, we can see that the main overhead of SQL is in the execution (EXECUTION) phase, not in the parsing (PARSER) or plan cache (PLAN CACHE) phase.
IN_STORAGE_WRITE is a subphase of IN_SQL_EXECUTION, accounting for 68.19% of the overhead. This indicates that the main performance overhead occurs during data writing to the memtable.
IN_DEADLOCK_ROW_REGISTER is a subphase of IN_STORAGE_WRITE, accounting for 65.10% of the overhead. This indicates that most of the overhead occurs in this phase.
In benchmark tests for import tasks, these three metrics typically do not exceed 30%. This is because, in import tasks, background tasks such as memtable dumping, clog synchronization, and disk writing consume a portion of CPU resources, and stages such as SQL parsing and expression calculation also consume CPU resources. In this task, the overhead of the IN_STORAGE_WRITE phase is close to 70%, far exceeding the benchmark reference value. This indicates that the performance issues in this business scenario mainly occur in this phase. Additionally, the IN_DEADLOCK_ROW_REGISTER phase is primarily responsible for registering the row lock information held in the written data to the Deadlock Manager, facilitating the detection of active transactions for deadlocks by background tasks. Typically, this step involves updating and maintaining a hashmap value, which is a very fast operation. In benchmark tests, the overhead of this phase typically does not exceed 3%, which is significantly higher than the benchmark reference value in this system.
Based on the above analysis, we can determine that the performance issues are primarily in the IN_DEADLOCK_ROW_REGISTER phase. This is a common path for memtable writing. Performance issues on this common path can affect either a specific SQL statement or all SQL statements accessing this path. Therefore, how do we determine the scope of affected SQL statements in this case?
In addition to listing the overhead percentages of the predefined phases in OceanBase Database, the Top Execution Phase section also displays the SQL statements with the highest overhead in each phase and their specific percentages. From the report data, we can see that the SQL statement with the highest overhead in the IN_DEADLOCK_ROW_REGISTER phase is D5B2150EA75EB405C25592A65708DD21. According to the text information provided in the report, this SQL statement is an INSERT statement:
INSERT INTO TRADE_BASE_000_LOAD_42
SELECT TRADE_NO, TRADE_NO
FROM TRADE_BASE_000_LOAD PARTITION(p42)
WHERE ROWNUM <= 50000000;
The overhead of this SQL statement in the IN_DEADLOCK_ROW_REGISTER phase is 0.67%, which is significantly different from the total overhead of the phase. This indicates that the total overhead of the IN_DEADLOCK_ROW_REGISTER phase is not concentrated on a single SQL statement but is distributed across many SQL statements.
Top IO Events
This section statistically analyzes the I/O wait events of SQL and background tasks, identifies the sources of disk I/O bottlenecks, including high-load SQL, background task types, and specific device performance metrics.
Node: The node address.
Program Module Action/SQL ID: The type of background task (such as log aggregation or transaction log writing) or the unique identifier of the SQL query, used to track the resource consumption of specific SQL statements.
Plan Hash: The numerical value of the current SQL execution plan. The same plan will have the same hash value.
IO Event Samples: The total number of I/O wait event samples during task execution.
%IO Event Samples: The proportion of I/O wait event samples among all sampled points, reflecting the frequency of I/O requests.
Top Event: The name of the main I/O wait event.
IO Type: The type of I/O wait event (read/write/asynchronous), used to distinguish operation modes.
% EVENT: The activity percentage of the I/O wait event.
Enqueue Time(S): The total time spent waiting for I/O requests to be queued, in seconds, reflecting the congestion level of the device.
Device Time(S): The total time spent executing I/O requests on the device, in seconds, measuring disk performance.
Callback Time(S): The total time spent on I/O request callbacks, in seconds.
By quantifying the I/O wait events of SQL and background tasks and combining them with the enqueue time (Enqueue Time) and execution time (Device Time), this information helps identify high-load SQL statements (through SQL ID and Plan Hash) and disk performance bottlenecks, providing a basis for optimizing I/O-intensive operations.
Top SQL Statement Types
This section summarizes the execution of various statement types during the ASH report analysis period, helping to analyze where business performance issues occur.
SQL Statement Type: Lists the most frequently executed SQL statement types, such as SELECT, UPDATE, INSERT, or DELETE.
Total Samples: The total number of executions of the specified SQL statement type during the entire ASH report analysis period.
% Activity: The activity percentage of the event, representing the percentage of activities (CPU + wait) for the specified tenant during the specified period.
Sampled Executions: The number of sampled executions of this type of SQL (multiple executions may be independently counted due to different parameters).
Node: The node address.
% Node: The activity percentage of this type of SQL on the node, used to evaluate node load balancing.
% On CPU: The percentage of time this type of SQL spends running on the CPU. A high value indicates a CPU-intensive operation, such as complex aggregation.
% Event: The percentage of this event in the total database activity. A high percentage may indicate that this event is a major bottleneck in system performance.
This information provides a categorized statistical analysis of SQL statement executions, revealing the impact of different operations (such as queries and updates) on database resource consumption and system activity. These data are valuable for identifying high-load SQL types, optimizing query strategies, balancing resource allocation, and performing targeted performance tuning.
Top SQL with Top Events
This section displays the SQL statements most closely associated with specific top events.
SQL ID: The SQL ID with the highest load in the wait event.
Plan Hash: The hash value of the current SQL execution plan. The same plan has the same hash value.
Active Samples: The number of occurrences or records for each execution phase.
% Activity: The activity percentage of the event, indicating the percentage of activity (CPU + wait) for the specified tenant during the specified period.
Sampled Executions: The number of sampled executions for this type of SQL (executions with different parameters may be independently counted).
Top Event: The name of the main I/O wait event.
% Event: The percentage of this event in the total database activity. A high percentage may indicate that this event is the main performance bottleneck.
Top Operator/ExecPhase: The operation with the longest duration in the current SQL execution plan.
% Operator/ExecPhase: The percentage of the operation/phase in the total SQL duration, used to locate execution plan bottlenecks.
SQL Text: The text of the SQL query.
This information provides the specific events that cause the highest activity percentage in SQL statement execution, directly pointing to the core of the performance bottleneck. By identifying these "bottleneck" events and related SQL statements, database administrators can optimize SQL statements, adjust execution plans, or improve system configurations to reduce wait times and improve resource utilization.
The Top SQL with Top Events module displays information about SQL statements that take more than 1% of the total time. If this section is empty, it indicates that the performance issue is not specific to certain SQL statements but rather a general slowdown in SQL execution. Based on the report, we can conclude that the performance issue is related to deadlock detection (IN_DEADLOCK_ROW_REGISTER) and is a common performance issue.
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
- Active Samples: num of samples for top current SQL
- % Activity: activity percentage for given SQL ID
- Sampled Executions: represents the number of times the current SQL execution has been sampled
- Top Event: top event name for current SQL plan
- % Event: activity percentage for current SQL plan
- Top Operator/ExecPhase: top operator name or execution phase for current event
- % Operator/ExecPhase: activity percentage for given operator
+----------------------------------------+--------------------+--------------------+--------------+--------------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+------------------------+----------------------------------------------------------------+
| SQL ID| Plan Hash| Active Samples| % Activity| Sampled Executions| Top Event| % Event| Top Operator/ExecPhase| % Operator/ExecPhase| SQL Text|
+----------------------------------------+--------------------+--------------------+--------------+--------------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+------------------------+----------------------------------------------------------------+
+----------------------------------------+--------------------+--------------------+--------------+--------------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+------------------------+----------------------------------------------------------------+
Top SQL with Top Operator
This section displays the SQL statements that use specific operators and account for a high proportion of sampled session activity in the execution plan.
SQL ID: The SQL ID with the highest load in the wait event.
Plan Hash: The hash value of the current SQL execution plan, used to uniquely identify the SQL execution plan.
Active Samples: The number of occurrences or records for each execution phase.
% Activity: The activity percentage of the event, indicating the percentage of activity (CPU + wait) for the specified tenant during the specified period.
Sampled Executions: The number of sampled executions for this type of SQL (executions with different parameters may be independently counted).
Top Operator: The operation with the longest duration in the current SQL execution plan.
% Operator: The percentage of the phase in the total SQL duration, used to locate execution plan bottlenecks.
Top Event: The name of the main I/O wait event.
% Event: The percentage of this event in the total database activity. A high percentage may indicate that this event is the main performance bottleneck.
SQL Text: The text of the SQL query.
This information provides the SQL operators associated with the highest activity percentage, offering precise guidance for optimizing SQL execution paths for database administrators and developers.
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
- Active Samples: num of samples for top current SQL
- % Activity: activity percentage for given SQL ID
- Sampled Executions: represents the number of times the current SQL execution has been sampled
- Top Operator: top operator name for current SQL plan
- % Operator: activity percentage for given operator
- Top Event: top event name for current operator
- % Event: activity percentage for given event
+----------------------------------------+--------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
| SQL ID| Plan Hash|Active Samples| % Activity| Sampled Executions| Top Operator| % Operator| Top Event| % Event| SQL Text|
+----------------------------------------+--------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
+----------------------------------------+--------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
Top PL/SQL Procedures
This section displays information about the top PL/SQL stored procedures.
PLSQL Entry Subprogram: Displays the top-level PL/SQL entry subprogram of the application, which can be a procedure, function, trigger, or package initialization.
% Activity: The activity percentage of the event, indicating the percentage of activity (CPU + wait) for the specified tenant during the specified period.
PLSQL Current Subprogram: Displays the PL/SQL subprogram currently being executed at the sampling point. If the value is
SQL, it indicates the percentage of time spent executing SQL within a specific PL/SQL entry subprogram; if the value is--, it indicates that no specific subprogram is being executed.% Current: The percentage of time spent in the current subprogram (PL/SQL Current Subprogram) relative to the entry subprogram. If the current subprogram is SQL, it reflects the percentage of time spent executing SQL.
This information provides details about the execution of top PL/SQL stored procedures, including the top-level entry subprogram, the current executing subprogram, and the 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| % Activity| PLSQL Current Subprogram| % Current|
+------------------------------------------------------------+--------------------+------------------------------------------------------------+--------------------+
| oceanbase.dbms_workload_repository.ASH_REPORT| 0.00%| oceanbase.dbms_workload_repository.ASH_REPORT_TEXT| 0.00%|
+------------------------------------------------------------+--------------------+------------------------------------------------------------+--------------------+
Complete List of SQL Text (Versions earlier than V4.3.5 BP5)
This section displays a list of SQL texts (SQL Text), including the SQL ID and text of each SQL query.
SQL ID: The unique identifier of the SQL query.
SQL Text: The text of the SQL query.
This information provides a list of SQL queries, which can be used to identify, analyze, and optimize query performance.
Complete List of SQL Text And Status (V4.3.5 BP5 and later)
This section displays a list of SQL statements and their execution status, including key metrics for performance analysis. Note that some of the most efficient SQL statements may not be displayed in this list due to lower resource consumption.
First Load Time: The time when the execution plan was first loaded into the cache, i.e., the time when the plan was generated.
Plan Cache Hit Rate: The percentage of times the execution plan was successfully retrieved from the cache instead of being regenerated.
AVG RPC: The average number of remote procedure calls (RPCs) initiated each time the SQL statement is executed.
AVG Partition: The average number of partitions accessed during each execution.
Route Miss Rate: The percentage of requests that were incorrectly routed to a data node that does not contain the target data.
AVG Disk Reads: The average number of bytes read from the disk during each execution.
Muti Query Rate: The percentage of multi-statement queries (requests containing multiple SQL statements).
Muti Query Batch Rate: The percentage of requests executed in batch mode.
AVG Error: The average number of errors occurring per request.
Full Table Scan Rate: The percentage of execution plans that include full table scans.
AVG Retry: The average number of retries during each execution due to various reasons (e.g., timeouts, conflicts).
Executions: The total number of executions of the SQL statement during the statistical period.
AVG Elapsed Time: The average execution time for each execution of the SQL statement.
This information provides a comprehensive view of SQL query execution efficiency, resource consumption, and stability, enabling quick identification of performance bottlenecks, optimization of query statements, and adjustment of database configurations.
Complete List of SQL Text And Status (V4.3.5 BP5 and later)
This section displays a list of SQL statements and their execution status, which includes key metrics for performance analysis. Note that some of the most efficient SQL statements may not be displayed due to their low resource consumption.
- First Load Time: The time when the execution plan is first loaded into the cache, which is the time when the plan is generated.
- Plan Cache Hit Rate: The percentage of times the execution plan is successfully retrieved from the cache instead of being regenerated.
- AVG RPC: The average number of remote procedure calls (RPCs) initiated each time the SQL statement is executed.
- AVG Partition: The average number of partitions accessed during each execution.
- Route Miss Rate: The percentage of requests that are incorrectly routed to a data node that does not contain the target data.
- AVG Disk Reads: The average number of bytes read from the disk during each execution.
- Multi Query Rate: The percentage of requests that contain multiple SQL statements.
- Multi Query Batch Rate: The percentage of requests executed in batch mode.
- AVG Error: The average number of errors occurring per request.
- Full Table Scan Rate: The percentage of execution plans that include full table scans.
- AVG Retry: The average number of retries during each execution due to reasons such as timeouts or conflicts.
- Executions: The total number of executions of the SQL statement within the statistical period.
- AVG Elapsed Time: The average execution time for each execution of the SQL statement.
This information provides a comprehensive view of the execution efficiency, resource consumption, and stability of SQL queries, which can be used to quickly identify performance bottlenecks, optimize query statements, and adjust database configurations.
Related wait events
You can query the Common wait events topic to understand the meanings of wait events, which helps you quickly locate issues during analysis.