All Products
Document Center

SQL tuning methods

Last Updated: Jan 10, 2020

Statement-specific SQL tuning

Statement-specific SQL tuning is for reducing the response time or resources (such as memory and disk I/O) that are consumed when you execute a single SQL statement or a type of SQL statements. Statement-specific SQL tuning performance is related to SQL execution plans. Therefore, analyzing SQL execution plans is the most important method for tuning. Based on the static analysis of SQL execution plans, you can find the causes of performance problems.

If performance problems are not generated by optimizer bugs, you need to perform the following operations on databases to reduce the response time or consumed resources:

  • Change system settings and system variables.
  • Change database schemas, such as creating partitions and secondary indexes.
  • Change SQL statements, such as using equivalent syntax to rewrite SQL statements and adding optimizer hints.
  • Change the degree of parallelism (DOP) for parallel queries.

An SQL statement goes through the following processes during the query:

  1. The request is sent to the server.
  2. The request queues to be processed.
  3. The plan cache is queried.
  4. The SQL execution plan is optimized if the plan cache misses.
  5. The query is processed based on the execution plan.
  6. The result is returned.

If the response time of an SQL statement increases, you must identify the process that takes more time than before. You can use the SQL Trace feature, check the SQL audit table, or view slow query logs. After identifying the process, you can analyze the root cause of the performance problem.

You can use an SQL statement to query a single table or multiple tables. SQL tuning methods vary based on whether an SQL statement is used to query a single table or multiple tables. For an SQL statement that is used to query a single table, SQL tuning focuses on the following issues:

1. Access path

Access paths are one of the most important factors that affect the performance of single-table queries. If full table scans are used for queries, the response time increases with the amount of data that needs to be scanned. You can use the explain extended command to view the range of a full table scan. Using indexes can significantly reduce the amount of data that are searched during queries. Therefore, if full table scans are used during queries, you need to determine the reason why index scans are not used. The reasons include no available index and a wide scan range.

2. Sorting and aggregation operations

Sorting or aggregation operations are time-consuming. To reduce the response time, the optimizer uses the index order when an index is available to avoid additional sorting operations. You can also create indexes for the columns that are frequently queried to avoid unnecessary sorting operations.

3. Partition pruning

Partition pruning is an important method for optimization. If you have created partitions based on appropriate conditions, the optimizer automatically skips the partitions that do not need to be accessed.

4. DOP

By increasing the DOP for queries, you can schedule more resources to process queries and improve SQL performance. If the amount of data and partitions to be searched is large, you can reduce the response time by increasing the DOP.

For an SQL statement that is used to query multiple tables, you need to consider the JOIN relations between tables in addition to the preceding issues. You need to analyze the following factors:

  • JOIN orders
  • JOIN algorithms
  • Data redistribution for cross-node and parallel JOINs
  • Query statement rewriting

System-level SQL tuning

System-level SQL tuning focuses on the system throughput. The main purpose is to maximize the capacity of the database system to process requests under a certain limit of resources such as CPU, I/O, and networks. The system throughput is assessed and optimized to meet the needs when new businesses are launched and big promotion activities are organized.

To optimize the throughput, you can perform the following operations:

1. Find and optimize slow SQL queries

Slow SQL queries consumes a large amount of resources and reduce the system throughput. You can follow these steps to solve the problem:

  1. Use the TOP SQL function of OceanBase Intelligent Diagnosis or the plan cache view to identify the TOP N time-consuming SQL statements.

  2. After identifying the specific slow SQL queries, you can perform statement-specific SQL tuning by following the preceding instructions.

2. Check whether SQL requests are evenly distributed across different servers.

In a multi-node environment, you need to make full use of resources of all nodes. You can view the SQL audit table to check whether workloads are balanced. The following factors affect the balance of workloads:

  • ob_read_consistency variable
  • Primary zone
  • Routing policy of the proxy or Java client
  • Whether workloads for frequently accessed partitions are balanced

3. Check whether Remote Process Call (RPC) requests of subplans are evenly distributed across different servers.

Typically, a large amount of distributed execution plans are configured with the weak read consistency. In this case, the RPC requests of subplans consume a large amount of resources. If SQL requests are evenly distributed across different servers, you can view the SQL audit table to check whether RPC requests of subplans are balanced. The following factors affect the even distribution of RPC requests:

  • The internal routing policy of the observer
  • Whether workloads for frequently accessed partitions are balanced