The best idea of Oracle DBA for SQL Optimization

How to Write high-quality SQL statements in Oracle databases and diagnose and adjust SQL statements with performance problems in Oracle databases is an inevitable challenge for DBAs in ORACLE Database practice. Next let's analyze how we can start our optimization ideas after getting a problematic SQL statement.

The specific steps for SQL Optimization for problematic SQL statements are as follows:

1. view the execution plan of the SQL statement, and analyze whether the execution plan is reasonable based on its resource consumption, related statistics, and Trace files;

2. Adjust the SQL statement to shorten the execution time by modifying measures (such as adjusting the SQL Execution Plan, the ultimate goal of SQL Optimization in Oracle databases is to shorten the execution time of target SQL statements. To achieve the above purpose, we usually have the following three methods to choose from:

1. Reduce the resource consumption of the target SQL statement;

2. Execute the target SQL statement in parallel;

3. Balance System resource consumption.

Method 1: reduce the resource consumption of the target SQL statement to shorten the execution time. This is the most common SQL optimization method. The core of this method is to rewrite the SQL statement without changing the business logic to reduce the resource consumption of the target SQL statement, you can reduce the resource consumption of the target SQL statement by adjusting the execution plan or related table data without changing the SQL statement.

Method 2: Execute the target SQL statement in parallel. In fact, the execution time is shortened by the consumption of additional resources. In many cases, parallel execution is the only Optimization Method for some SQL statements.

Method 3: balance system resource consumption "can avoid unnecessary resource contention resulting in an increase in the execution time of the target SQL statement. For some SQL statements, the increasing or unstable execution time (fast and slow) is not due to the execution plan, but to the high load of the system during the execution of the target SQL statement, the hardware resources (CPU, memory, IO, etc.) required for executing the target SQL statement are not guaranteed and there is a race for use. In this case, if we can balance the resource consumption of the system, perform operations that are not important but consume system resources (such as reports and batch processing) at the same time as the target SQL statement) when the system is not busy, for example, if they are moved to the evening for execution, some system hardware resources can be released to ensure that the hardware resources required for the target SQL Execution do not compete for use, this avoids the increase or instability of the execution time.