All Products
Search
Document Center

Overview of SQL tuning

Last Updated: Jun 18, 2021

SQL tuning is the process of adjusting SQL execution policies to maximize resource utilization by comprehensively analyzing factors such as SQL execution plans, execution monitoring information, system configuration, and system load.

It includes basic SQL tuning and systematic SQL tuning.

Basic SQL tuning

In this mode where the main task is tuning the execution of a SQL query, the target generally includes optimization of the SQL execution time and resource consumption during the execution. General tuning measures used for basic SQL tuning include the change of the access path, the adjustment of the execution order, and the rewriting of the execution logic.

Tuning of a single SQL query involves two scenarios: single-table access, and multi-table access.

Scenario

Checklist

Single-table access

  • Whether index scanning is enabled for access path: Index scanning reduces the amount of data to read.

  • Whether a proper index is created: Index sorting reduces time-consuming operations such as sorting and aggregation.

  • Whether partition pruning conditions are correctly set: Appropriate partitioning conditions reduce unnecessary partition access.

  • Whether the degree of parallelism is improved: When a great number of partitions need to be accessed, a higher degree of parallelism improves the performance of an SQL query at the cost of higher resource consumption.

Multi-table access

Apart from the SQL tuning of a single table, you need to pay attention to multi-table joining:

  • Join order

  • Join algorithms

  • Mode of data redistribution in cross-zone or parallel joins

  • Query rewrite

Systematic SQL tuning

Systematic SQL tuning aims to improve the overall throughput or availability of the system. During systematic SQL tuning, multiple SQL execution plans are leveraged to analyze load characteristics and global tuning points of the current system, such as hotspot row competition and buffer cache hit rate.

Throughput performance tuning aims at maximizing the request processing capacity of the database system based on a specified amount of resources (such as CPU, I/O, and network). The following table describes some typical measures of systematic SQL tuning.

Typical measure

Description

Optimize slow SQL queries

Find a specific slow SQL query and optimize its performance. For more information, see Find the TOP N queries with the longest execution time within a specified period.

Balance the traffic of SQL queries

For more information, see Check whether SQL queries are balanced across servers in a cluster.

Main factors affecting the balance:

  • Settings of the ob_read_consistency parameter

  • Settings of the primary zone

  • Settings of the proxy or Java client routing policy

  • Partitioning for frequently executed queries

Balance the resources for RPCs of subplans

For more information, see Check whether the RPC execution count of a distributed subplan is balanced across servers in a cluster.

Main factors affecting the balance:

  • Settings of the internal routing policy of OBServer

  • Partitioning for frequently executed queries