After generating an ASH report, you can view its content to find 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 time, sample size, event quantity, and average number of active sessions. It also outputs information of modules including Top User Events, Top Events P1/P2/P3 Value, Top Phase of Execution, Top SQL with Top Events, Top SQL with Top Blocking Events, Complete List of SQL Text, Top Sessions, Top Blocking Sessions, and Top latches. 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
----
Sample Begin: 2024-01-05 21:19:00
Sample End: 2024-01-09 13:58:02
----------
Analysis Begin Time: 2024-01-06 11:17:25
Analysis End Time: 2024-01-09 13:57:49
Elapsed Time: 268824(secs)
Num of Sample: 10814
Num of Events: 10814
Average Active Sessions: 0.040
----
## Top User Events:
----------------------------------------------------------------+--------------------+---------+
Event| WAIT_CLASS| % Event|
----------------------------------------------------------------+--------------------+---------+
CPU + Wait for CPU| OTHER| 100.00%|
----------------------------------------------------------------+--------------------+---------+
## Top Events P1/P2/P3 Value:
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
Event| % Event| % Activity| Max P1/P2/P3| Parameter 1| Parameter 2| Parameter 3|
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
CPU + Wait for CPU| 100.00%| 100.000%| "0","0","0"| | | |
----------------------------------------------------------------+----------+------------+--------------------------------------------------+--------------------+--------------------+--------------------+
## Top Phase of Execution:
----------------------------------------+------------+--------------+----------------------------------------+
Phase of Execution| % Activity| Sample Count| Avg Active Sessions|
----------------------------------------+------------+--------------+----------------------------------------+
IN_SQL_EXECUTION| 0.203%| 22| 0.00|
IN_SQL_OPTIMIZE| 0.009%| 1| 0.00|
IN_PARSE| 0.000%| 0| 0.00|
IN_PL_PARSE| 0.000%| 0| 0.00|
IN_PLAN_CACHE| 0.000%| 0| 0.00|
IN_PX_EXECUTION| 0.000%| 0| 0.00|
IN_SEQUENCE_LOAD| 0.000%| 0| 0.00|
IN_COMMITTING| 0.000%| 0| 0.00|
IN_STORAGE_READ| 0.000%| 0| 0.00|
IN_STORAGE_WRITE| 0.000%| 0| 0.00|
IN_REMOTE_DAS_EXECUTION| 0.000%| 0| 0.00|
----------------------------------------+------------+--------------+----------------------------------------+
## Top SQL with Top Events
- All events included.
- Empty 'SQL Text' if it is PL/SQL query
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
SQL ID| PLAN ID| Sampled # of Executions| Event| % Event| SQL Text|
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
| 0| 10813| CPU + Wait for CPU| 99.99%| |
15B18561C66FCF844D00386749C0DA49| 1932| 1| CPU + Wait for CPU| 0.01%| |
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## Top SQL with Top Blocking Events
- Empty result if no event other than On CPU sampled
- Empty 'SQL Text' if it is PL/SQL query
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
SQL ID| PLAN ID| Sampled # of Executions| Event| % Event| SQL Text|
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
----------------------------------------+------------+-------------------------+----------------------------------------------------------------+------------+------------------------------------------------------------+
## Complete List of SQL Text
## Top PL/SQL Procedures
-> 'PL/SQL entry subprogram' represents the application's top-level
entry-point(procedure, function, trigger, package initialization) into PL/SQL.
-> 'PL/SQL current subprogram' is the pl/sql subprogram being executed
at the point of sampling . If the value is 'SQL', it represents
the percentage of time spent executing SQL for the particular
plsql entry subprogram
-> 'PL/SQL entry subprogram' represents the application's top-level
------------------------------------------------------------+------------------------------------------------------------+--------------------+
PLSQL Entry Subprogram| PLSQL Current Subprogram| % Activity|
------------------------------------------------------------+------------------------------------------------------------+--------------------+
------------------------------------------------------------+------------------------------------------------------------+--------------------+
## Top Sessions:
- '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall time.
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
Sid| % Activity| Event| Event Count| % Event| User| # Samples Active|
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
4436701216768| 48.71%| CPU + Wait for CPU| 5267| 48.71%| | 5267/268824[1.96%]|
4398046511104| 5.84%| CPU + Wait for CPU| 632| 5.84%| | 632/268824[0.24%]|
3861175599104| 5.04%| CPU + Wait for CPU| 545| 5.04%| | 545/268824[0.20%]|
5312874545152| 5.02%| CPU + Wait for CPU| 543| 5.02%| | 543/268824[0.20%]|
5317169512448| 5.02%| CPU + Wait for CPU| 543| 5.02%| | 543/268824[0.20%]|
3865470566400| 4.29%| CPU + Wait for CPU| 464| 4.29%| | 464/268824[0.17%]|
4471060955136| 2.26%| CPU + Wait for CPU| 244| 2.26%| | 244/268824[0.09%]|
3925600108544| 1.81%| CPU + Wait for CPU| 196| 1.81%| | 196/268824[0.07%]|
3981434683392| 1.54%| CPU + Wait for CPU| 167| 1.54%| | 167/268824[0.06%]|
4264902524928| 1.36%| CPU + Wait for CPU| 147| 1.36%| | 147/268824[0.05%]|
4320737099776| 1.28%| CPU + Wait for CPU| 138| 1.28%| | 138/268824[0.05%]|
4458176053248| 1.04%| CPU + Wait for CPU| 112| 1.04%| | 112/268824[0.04%]|
4402341478400| 0.97%| CPU + Wait for CPU| 105| 0.97%| | 105/268824[0.04%]|
4256312590336| 0.92%| CPU + Wait for CPU| 100| 0.92%| | 100/268824[0.04%]|
3813930958848| 0.91%| CPU + Wait for CPU| 98| 0.91%| | 98/268824[0.04%]|
3856880631808| 0.83%| CPU + Wait for CPU| 90| 0.83%| | 90/268824[0.03%]|
3934190043136| 0.80%| CPU + Wait for CPU| 87| 0.80%| | 87/268824[0.03%]|
3938485010432| 0.80%| CPU + Wait for CPU| 86| 0.80%| | 86/268824[0.03%]|
4350801870848| 0.78%| CPU + Wait for CPU| 84| 0.78%| | 84/268824[0.03%]|
--------------------+----------------------+----------------------------------------------------------------+------------+------------+--------------------+--------------------+
## Top Blocking Sessions:
- Blocking session activity percentages are calculated with respect to waits on latches and locks only.
- '# Samples Active' shows the number of ASH samples in which the blocking session was found active.
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
Blocking Sid| % Activity| Event Caused| Event Count| % Event| User| # Samples Active|
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
--------------------+----------------------+----------------------------------------+------------+------------+--------------------+--------------------+
## Top Latches:
----------------------------------------------------------------+--------------------+--------------------+
Latch| Sampled Count| % Activity|
----------------------------------------------------------------+--------------------+--------------------+
----------------------------------------------------------------+--------------------+--------------------+
## Node Load:
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
SVR IP| SVR PORT| Sampled Count| Idle Wait Count| Load|
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
xx.xx.xx.xx| 28824| 10814| 0| 0.04|
----------------------------------------+--------------------+--------------------+--------------------+------------------------------+
Top User Events
Top User Events lists the top wait events in the user process that accounted for the highest percentage of sampled session activities in multiple dimensions, such as event, event type, number of sampled events, and the percentage of wait time in the total time.
Note
- CPU + Wait for CPU: normal events.
- sync rpc: events of synchronous waiting for RPC network packets.
Top Events P1/P2/P3 Value
Top Events P1/P2/P3 Value shows the parameter values of the wait events that account for the largest percentage of the sampled session activities, in order of percentage of total wait time (%Events). For each wait event, the values of p1, p2, and p3 correspond to the wait event parameters in the Parameter 1, Parameter 2, and Parameter 3 columns.
Note
- CPU + Wait for CPU: normal events.
- sync rpc: events of synchronous waiting for RPC network packets.
Top Phase of Execution
Top Phase of Execution shows a comparison among the sampled sessions by the execution phase, the percentage of active sessions, the sample count, and the average number of active sessions. Execution phases, such as parsing and execution phases of SQL, PL/SQL, and Java, are sorted by the percentage of activities.
Top SQL with Top Events
Top SQL with Top Events shows the SQL statements that account for the highest percentage of sampled session activities and the most common wait events encountered by these SQL statements. The Sampled # of Executions column shows the different execution counts of a particular SQL statement that were sampled.
Top SQL with Top Blocking Events
Top SQL with Top Blocking Events displays the information about the blocked sessions. You can determine which part of the SQL execution is the most time-consuming based on the information in this module.
Complete List of SQL Text
Complete List of SQL Text shows the full text content of the SQL statements in Top SQL with Top Events and Top SQL with Top Blocking Events.
Top Sessions
Top Sessions sorts and counts active sessions in the session dimension. You can identify sessions with potential issues based on the sampled session ID, percentage of active sessions, event type, number of events, percentage of events, current user, and the sampled activity state.
Top Blocking Sessions
Top Blocking Sessions shows the blocked sessions with the highest percentage of sampled session activities.
Top Latches
Top Latches shows latches with the highest percentage in the sampled session activities.
Note
As a simple low-level serialization mechanism, latch is used to protect the structure of shared data in global zones.
For example, latches can protect the data structures of a list of users currently accessing the database and the blocks in the buffer cache. A server or background process gets a latch in a very short time while operating or viewing one of these structures. A latch is implemented on the operating system, which determines whether and how long the process waits for the latch in particular.
Common wait events
The following table describes some common wait events to help you quickly locate problems during ASH report analysis.
| Wait event | Description | Event class | Parameter 1 of the wait event | Parameter 2 of the wait event | Parameter 3 of the wait event |
|---|---|---|---|---|---|
| wait remove partition | The event of waiting for the deletion of partitions to complete. | ADMINISTRATIVE | tenant_id | table_id | partition_id |
| wait end stmt | The event of waiting for a statement to end. | CLUSTER | rollback | trans_hash_value | physic_plan_type |
| wait start stmt | The event of waiting for a statement to start. | CLUSTER | trans_hash_value | physic_plan_type | participant_count |
| wait end trans | The event of waiting for a transaction to commit. | COMMIT | rollback | trans_hash_value | participant_count |
| async rpc proxy condition wait | The asynchronous RPC wait event in top-level encapsulation. | CONCURRENCY | address | - | - |
| dag worker condition wait | The dag thread wait event in the background. | CONCURRENCY | address | - | - |
| hashmap lock wait | The HASH table lock wait event. | CONCURRENCY | address | number | tries |
| latch: alive server tracer lock wait | The event of waiting for the OBServer status lock in the OBAliveServerMap operation memory. |
CONCURRENCY | address | number | tries |
| latch: alloc block lock wait | The lock wait event during the management of K-level memory blocks in OceanBase Database by using the malloc() function. | CONCURRENCY | address | number | tries |
| latch: alloc object lock wait | The lock wait event during the management of small memory blocks in OceanBase Database by using the malloc() function. | CONCURRENCY | address | number | tries |
| latch: allocator chunk lock wait | The lock wait event during the management of memory blocks sized over 2 MB in OceanBase Database by using the malloc() function. | CONCURRENCY | address | number | tries |
| latch: clog cascading info lock wait | The lock wait event of the clog replica cascading module. | CONCURRENCY | address | number | tries |
| latch: clog locality lock wait | The event of waiting for the lock of locality information for internal protection of clogs. | CONCURRENCY | address | number | tries |
| latch: clog membership mgr lock wait | The event of waiting for the clog member change lock. | CONCURRENCY | address | number | tries |
| latch: clog reconfirm lock wait | The event of waiting for the lock used by the Reconfirm class in the clog module. | CONCURRENCY | address | number | tries |
| latch: clog stat mgr lock wait | The event of waiting for the clog status change lock. | CONCURRENCY | address | number | tries |
| latch: clog sw renew ms task lock wait | The event of waiting for the lock of the clog member group information change in the standby database. | CONCURRENCY | address | number | tries |
| latch: clog switch info lock wait | The event of waiting for the lock that the clog module uses to protect the primary/standby tenant switching information in the physical standby database. | CONCURRENCY | address | number | tries |
| latch: clog task lock wait | The event of waiting for the log_task lock of the clog module. | CONCURRENCY | address | number | tries |
| latch: config lock wait | The event of waiting for the parameter lock. | CONCURRENCY | address | number | tries |
| latch: default drw lock wait | The DRW lock wait event. The DRW lock is a read-write lock that adapts to scenarios with many read operations and few write operations. | CONCURRENCY | address | number | tries |
| latch: default mutex wait | The default mutex wait event. | CONCURRENCY | address | number | tries |
| latch: default recursive mutex wait | The default recursive mutex wait event. | CONCURRENCY | address | number | tries |
| latch: default spin lock wait | The default spin lock wait event. | CONCURRENCY | address | number | tries |
| latch: default spin rwlock wait | The default spin read-write lock wait event. | CONCURRENCY | address | number | tries |
| latch: id map node lock wait | The ID Map wait event. The ID Map maps integers to pointers. | CONCURRENCY | address | number | tries |
| latch: plan cache pcv set lock wait | The lock wait event when new objects are added to the plan cache. | CONCURRENCY | address | number | tries |
| latch: plan set lock wait | The plan cache lock wait event. | CONCURRENCY | address | number | tries |
| latch: ps store lock wait | The PS cache lock wait event. | CONCURRENCY | address | number | tries |
| latch: server maintaince lock wait | The lock wait event when the RootServer (RS) performs an operation on an OBServer node. | CONCURRENCY | address | number | tries |
| latch: server status lock wait | The read-write lock wait event when an operation is performed on server_statuses recorded by the RS. | CONCURRENCY | address | number | tries |
| latch: time wheel bucket lock wait | The bucket lock wait event for the transaction-layer timer. | CONCURRENCY | address | number | tries |
| latch: time wheel task lock wait | The scheduled task lock wait event of a transaction. | CONCURRENCY | address | number | tries |
| latch: trans ctx lock wait | The mutex lock wait event during concurrent operations in the transaction context. | CONCURRENCY | address | number | tries |
| latch: unit manager lock wait | The lock wait event when the Unit Manager operates on data. | CONCURRENCY | address | number | tries |
| latch: zone infos rw lock wait | The lock wait event when OBZoneManagerBase operates on data. | CONCURRENCY | address | number | tries |
| latch: zone manager maintaince lock wait | The lock wait event when the Zone Manager operates on data. | CONCURRENCY | address | number | tries |
| ob dynamic thread pool condition wait | The lock wait event of the dynamically scaled thread pool. | CONCURRENCY | address | - | - |
| partition location cache lock wait | The lock wait event that prevents concurrent location cache refreshes. | CONCURRENCY | table_id | partition_id | - |
| partition table updater condition wait | The conditional wait event when OBUniqTaskQueue processes tasks. | CONCURRENCY | address | - | - |
| rebalance task mgr condition wait | The conditional wait event when OBDRTaskMgr processes disaster recovery tasks. | CONCURRENCY | address | - | - |
| reentrant thread condition wait | The conditional wait event when OBReentrantThread processes tasks. | CONCURRENCY | address | - | - |
| rpc session handler condition wait | The streaming RPC wait event. | CONCURRENCY | address | - | - |
| th worker condition wait | The multi-tenant thread suspension wait event. | CONCURRENCY | address | - | - |
| thread idling condition wait | The RootService idle thread wait event. | CONCURRENCY | address | - | - |
| sched idle | The task execution wait event of the multi-tenant thread. | IDLE | wait start timestamp | - | - |
| mysql response wait client | The client response wait event. | NETWORK | - | - | - |
| sync rpc | The synchronization RPC wait event. | NETWORK | pcode | size | - |
| sched wait | The event of waiting for the multi-tenant thread to execute large queries. | SCHEDULER | req type | req start timestamp | wait start timestamp |
| bloomfilter build read | The event of waiting for the background BloomFilter to build data read operation. | SYSTEM_IO | fd | offset | size |
| db file compact read | The event of waiting for data read in a compaction task. | SYSTEM_IO | fd | offset | size |
| db file compact write | The event of waiting for data write in a compaction task. | SYSTEM_IO | fd | offset | size |
| db file index build read | The event of waiting for index building and reading for the database file. | SYSTEM_IO | fd | offset | size |
| db file index build write | The event of waiting for index building and writing for the database file. | SYSTEM_IO | fd | offset | size |
| db file migrate read | The migration data read wait event. | SYSTEM_IO | fd | offset | size |
| db file migrate write | The migration data write wait event. | SYSTEM_IO | fd | offset | size |
| memstore memory page alloc info | The calculated value that triggers MemStore write throttling. | SYSTEM_IO | cur_mem_hold | sleep_interval | cur_ts |
| memstore memory page alloc wait | The event of waiting for MemStore write throttling to be triggered. | SYSTEM_IO | cur_mem_hold | sleep_interval | cur_ts |
| db file data index read | The event of indexed reading of user data microblocks. | USER_IO | fd | offset | size |
| db file data read | The event of reading user data. | USER_IO | fd | offset | size |
| interm result disk read | The event of reading intermediate result data. | USER_IO | fd | offset | size |
| interm result disk write | The event of writing intermediate result data. | USER_IO | fd | offset | size |
| row store disk read | The event of reading SQL operator data from the disk. | USER_IO | fd | offset | size |
| row store disk write | The event of writing SQL operator data to the disk. | USER_IO | fd | offset | size |
| backup info wait | The wait event inside the backup information manager. | CONCURRENCY | address | number | tries |
| build index scheduler condition wait | The wait before DDL task scheduling is performed again. | CONCURRENCY | address | - | - |
| debug sync condition wait | This event is meaningful only in Errsim debug mode and is meaningless in user scenarios. | CONCURRENCY | address | - | - |
| inner connection pool condition wait | The conditional wait event of the internal connection pool. | CONCURRENCY | address | - | - |
| latch: bandwidth throttle lock wait | The bandwidth throttling lock wait event. | CONCURRENCY | address | number | tries |
| latch: default bucket lock wait | The bucket lock wait event, which is not classified. | CONCURRENCY | address | number | tries |
| latch: election group lock wait | The event of waiting for the lock that protects the election group information. | CONCURRENCY | address | number | tries |
| latch: election group trace recorder lock wait | The event of waiting for the lock that protects the election group trace information. | CONCURRENCY | address | number | tries |
| latch: election lock wait | The lock wait event during the selection of the main thread. | CONCURRENCY | address | number | tries |
| latch: election msg lock wait | The election message lock wait event. | CONCURRENCY | address | number | tries |
| latch: group migrate lock wait | The batch migration lock wait event. | CONCURRENCY | address | number | tries |
| latch: group migrate task idle wait | The task idle lock wait event in batch migration. | CONCURRENCY | address | number | tries |
| latch: group migrate task lock wait | The task lock wait event in batch migration. | CONCURRENCY | address | number | tries |
| latch: io queue lock wait | The lock wait event in I/O queue reads. | CONCURRENCY | address | number | tries |
| latch: migrate lock wait | The lock wait event in migration. | CONCURRENCY | address | number | tries |
| latch: multi tenant lock wait | The lock wait event in multi-tenant management. | CONCURRENCY | address | number | tries |
| latch: rebuild retry list lock wait | The rebuild task lock wait event. | CONCURRENCY | address | number | tries |
| latch: session trace recorder lock wait | The trace record lock wait event in a session. | CONCURRENCY | address | number | tries |
| latch: tenant lock wait | The event of waiting for the lock for tenant management. | CONCURRENCY | address | number | tries |
| latch: timezone lock wait | The Timezone structure lock wait event in a tenant. The lock is added for a tenant operation. | CONCURRENCY | address | number | tries |
| latch: trace recorder lock wait | The event of waiting for the lock of SQL trace records. | CONCURRENCY | address | number | tries |
| ob request timeinfo list wait | The lock statistics of the request count, which are related to the fail-safe capability of the plan cache. | CONCURRENCY | address | - | - |
| ob restore reader condition wait | The wait event related to backup and restore. | CONCURRENCY | address | - | - |
| oblog part mgr schema version wait | The event of waiting for the liboblog to refresh the schema version logic. | CONCURRENCY | - | - | - |
| slog flush condition wait | The event of waiting for slog disk flushes. | CONCURRENCY | address | - | - |
| ObCacheLineSegregatedArrayBase alloc mutex | The event of waiting for the lock for cache line array allocation. | USER_IO | fd | offset | size |
| server object pool arena lock wait | The event of waiting for the lock of large object cache structures. | USER_IO | fd | offset | size |