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:
MySQL [oceanbase]> select plan_id, sql_id, hit_count, avg_exe_usec, substr(statement, 1, 100) from gv$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) |
+---------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
| 277 | 7222544C4715703B717FAFF8E69B4480 | 539 | 0 | SELECT MAX(schema_version) as version, host_ip() as myip, rpc_port() as myport FROM __all_ddl_operat |
| 431 | CD7AC61B2434CF86FDBE9C3A7A44BB8E | 371 | 0 | INSERT INTO sbtest9(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, |
| 442 | 136C85D11DD21EFB5E1ED5FC901DEC6A | 371 | 0 | INSERT INTO sbtest10(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(? |
| 411 | C366417982B5BCEAE5C8C1EF48C1B8E2 | 371 | 0 | INSERT INTO sbtest8(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, |
| 421 | 77E9B2275B22581798AB612E9B5B96E6 | 371 | 0 | INSERT INTO sbtest13(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(? |
| 439 | B8778DBBDF6C08CB6F0E1A82986F1D0E | 371 | 0 | INSERT INTO sbtest17(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(? |
| 452 | BE6FD8B551DA05119B49D62FB165AE02 | 371 | 0 | INSERT INTO sbtest16(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(? |
| 418 | 8462CC36B2F5186B02F066CEFD214387 | 370 | 0 | INSERT INTO sbtest5(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, |
| 430 | 6753D592D51B5630EBF36F43FA5522D3 | 370 | 0 | INSERT INTO sbtest7(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, |
| 429 | 20D6D195029E9EE68B0C8B3C7DD8C846 | 370 | 0 | INSERT INTO sbtest3(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, |
+---------+----------------------------------+-----------+--------------+------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)
Collect the execution statistics of a period based on the SQL ID.
MySQL [oceanbase]> select svr_ip, plan_type, elapsed_time, AFFECTED_ROWS, RETURN_ROWS, transaction_hash, usec_to_time(REQUEST_TIME), substr(query_sql, 1, 30) from gv$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) |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
| xx.xx.xx.59 | 1 | 465114 | 0 | 0 | 10023348016566894972 | 2021-08-25 22:44:08.533070 | SELECT * FROM __all_root_table |
| xx.xx.xx.59 | 1 | 375107 | 0 | 0 | 13001988804803062059 | 2021-08-25 22:44:08.573525 | SELECT * FROM __all_root_table |
| xx.xx.xx.62 | 2 | 226940 | 0 | 0 | 0 | 2021-08-25 22:44:08.722480 | SELECT * FROM __all_root_table |
| xx.xx.xx.62 | 2 | 224519 | 0 | 0 | 0 | 2021-08-25 22:44:08.730139 | SELECT * FROM __all_root_table |
| xx.xx.xx.59 | 1 | 220272 | 0 | 0 | 6454906702768493748 | 2021-08-25 22:44:08.745529 | SELECT * FROM __all_root_table |
| xx.xx.xx.63 | 2 | 78577 | 0 | 0 | 0 | 2021-08-25 22:44:08.884916 | SELECT * FROM __all_root_table |
| xx.xx.xx.63 | 2 | 49034 | 0 | 0 | 0 | 2021-08-25 22:44:08.905322 | SELECT * FROM __all_root_table |
| xx.xx.xx.62 | 2 | 48885 | 0 | 0 | 0 | 2021-08-25 22:44:08.905610 | SELECT * FROM __all_root_table |
| xx.xx.xx.59 | 1 | 45239 | 0 | 0 | 11958340144270554107 | 2021-08-25 22:44:08.906159 | SELECT * FROM __all_root_table |
| xx.xx.xx.63 | 2 | 33454 | 0 | 0 | 0 | 2021-08-25 22:44:08.920650 | SELECT * FROM __all_root_table |
+---------------+-----------+--------------+---------------+-------------+----------------------+----------------------------+--------------------------------+
10 rows in set (0.20 sec)
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.