In a stress testing environment, the execution efficiency of SQL statements determines the system performance to some extent. Therefore, SQL diagnostics is the most important part of the performance tuning process. This topic describes how to locate and optimize slow SQL queries.
Check statistics in views
Identify the SQL types with high execution frequency based on plan cache statistics.
obclient> select plan_id, sql_id, hit_count, avg_exe_usec, substr(statement, 1, 100) from gv$ob_plan_cache_plan_stat where tenant_id=1002 order by hit_count desc limit 10;
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
| plan_id | sql_id | hit_count | avg_exe_usec | substr(statement, 1, 100) |
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
| 7020372274597948570 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 14767 | 562 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 7020372274597948744 | 9CA2F8D24467EB1A28CA50EE09743A86 | 14707 | 468 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? |
| 7020372274597948743 | C1E19F19B0677FD5875F8C7C4FF30436 | 14707 | 492 | SELECT * FROM __all_freeze_info |
| 7020372274597948834 | 624F9288016A7704D6201261C0F494FF | 11823 | 110 | select * from __all_tenant_scheduler_job where tenant_id = ? and job = ? |
| 7020372274597948569 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7424 | 158 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 7020372274597948812 | B6E4D946D9527AB02AFBEC16F74F2E25 | 7405 | 589 | SELECT * FROM __all_freeze_info ORDER BY frozen_scn DESC LIMIT ? |
| 7020372274597948741 | C1E19F19B0677FD5875F8C7C4FF30436 | 7389 | 80 | SELECT * FROM __all_freeze_info |
| 7020372274597948742 | 9CA2F8D24467EB1A28CA50EE09743A86 | 7389 | 68 | SELECT * FROM __all_acquired_snapshot WHERE tenant_id = ? |
| 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7382 | 1012 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
| 49 | 17605A1DA6B6A2150E9FBCA5D4C7653A | 7381 | 1009 | SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, |
+---------------------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
10 rows in set
Collect the execution statistics of a period based on the SQL ID.
obclient> select svr_ip, plan_type, elapsed_time, AFFECTED_ROWS, RETURN_ROWS, tx_id, usec_to_time(REQUEST_TIME), substr(query_sql, 1, 30) from gv$ob_sql_audit where sql_id='F96CE9DFB959E383828A9D91575EE97F' and request_time > time_to_usec('2021-08-25 22:00:00') and request_time < time_to_usec('2021-08-25 22:50:00') order by elapsed_time desc limit 10;
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| svr_ip | plan_type | elapsed_time | AFFECTED_ROWS | RETURN_ROWS | transaction_hash | usec_to_time(REQUEST_TIME) | substr(query_sql, 1, 30) |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| 10.10.10.1 | 1 | 465114 | 0 | 0 | 10023348016566894972 | 2021-08-25 22:44:08.533070 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 375107 | 0 | 0 | 13001988804803062059 | 2021-08-25 22:44:08.573525 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 226940 | 0 | 0 | 0 | 2021-08-25 22:44:08.722480 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 224519 | 0 | 0 | 0 | 2021-08-25 22:44:08.730139 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 220272 | 0 | 0 | 6454906702768493748 | 2021-08-25 22:44:08.745529 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 78577 | 0 | 0 | 0 | 2021-08-25 22:44:08.884916 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 49034 | 0 | 0 | 0 | 2021-08-25 22:44:08.905322 | SELECT * FROM __all_root_table |
| 10.10.10.2 | 2 | 48885 | 0 | 0 | 0 | 2021-08-25 22:44:08.905610 | SELECT * FROM __all_root_table |
| 10.10.10.1 | 1 | 45239 | 0 | 0 | 11958340144270554107 | 2021-08-25 22:44:08.906159 | SELECT * FROM __all_root_table |
| 10.10.10.3 | 2 | 33454 | 0 | 0 | 0 | 2021-08-25 22:44:08.920650 | SELECT * FROM __all_root_table |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
10 rows in set
You can adjust the syntax of SQL statements based on your business requirements.
Monitor performance in OCP
OceanBase Cloud Platform (OCP) allows you to analyze slow SQL statements in terms of execution plans, execution frequency, and time consumption. You can use OCP to improve efficiency and identify information about slow SQL statements.
Optimize SQL statements
After you identify slow SQL statements, you can troubleshoot for causes from the following aspects:
Are tenant resources sufficient?
Is the index or plan optimal?
Are the numbers of partitions and rows involved in the write or query too large according to the affected rows and return rows?
Is cross-city or cross-server access involved?
Is the dumping of intermediate results to disks as expected for a complex SQL statement?
Are there any exceptions related to minor compactions, disk I/O usage, and other system metrics?
For more information about SQL tuning, see SQL Tuning Guide.