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.
View statistics
Based on Plan Cache statistics, find high-frequency SQL categories:
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
Based on the specific SQL_ID, sample execution data over a period. The specific SQL is as follows:
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
The specific SQL can be adjusted according to actual business requirements.
OCP monitoring
OCP currently has comprehensive slow SQL analysis, including execution plans, execution frequency, and time consumption. Using OCP can greatly improve efficiency and quickly locate information related to slow SQL.
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.