Understanding of SQL Optimization in Oracle

The ultimate goal of SQL optimization in Oracle database is to shorten the execution time of the target SQL statement. To achieve this, we usually have the following three ways to choose:
1, reduce the resource consumption of the target SQL statement.
2. Execute the target SQL statement in parallel.
3. Balance the resource consumption of the system.
The 1:a method reduces the resource consumption of the target SQL statement by overwriting SQL without changing the business logic.
b, do not overwrite SQL but reduce the resource consumption of the target SQL statement by adjusting the execution plan or the data of the related table.
Note: Walking an index is not necessarily better than a full table scan, for example, in many cases the execution efficiency of a nested loop connection that is indexed is often more efficient than a hash connection that does not take a full table scan.
Method 2: In fact, with additional resource consumption in exchange for the shortening of execution time, in many cases using parallelism is the only optimization method for some SQL.
Method 3: You can avoid an increase in the execution time of the target SQL statement caused by unnecessary resource contention. Because the system is in a high-load state while the target SQL is executing, the required
Hardware Resources (CPU, memory, I/O, etc.) are not guaranteed and there is contention. In this case, we can put some of the same execution with the target SQL is not important but consumes system resources
Operations (such as reports, batches, etc.) to be performed when the system is not busy.

The specific steps for SQL optimization in the Oracle database:
1. Find the top SQL statement that has the longest execution time and consumes the most system resources.
2, review the above top SQL statement execution plan, and the combination of resource consumption and related statistics, trace files to analyze its execution plan is reasonable.
3, through the correction measures (such as: adjust the above top SQL execution plan, etc.) to adjust the above top SQL to shorten their execution time, here the guiding principle of adjustment is described earlier
Three different methods.

Common tuning methods for top sql:
1, if the statistical information is not allowed or because the CBO calculates some of the SQL cost of the formula used to cause SQL performance problems, we can by re-collect statistics, or manually
Modify the statistics, or use hint to resolve them.
Comment: How to collect statistics on a table exec dbms_stats.gather_table_stats (ownname=> ' database user name ',tabname=> ' table name ',method_opt=> ' for all
Columns size 1 ', cascade=>true,estimate_percent=100);
2. In the case of SQL statements, we can resolve the problem by rewriting SQL without changing the business logic.
3. If the performance problem of the target SQL is caused by unnecessary full table scan/Sorting, we can solve it by establishing appropriate indexes (including function index, bitmap index, etc.).
4. If the execution plan for the target SQL is unstable for various reasons, we can solve the problem by using hint, SQL Profile or SPM.
5, if the poor design of the table or index results in the performance of the target SQL, we can re-design the table/index to reorganize the tables of data to solve the problem.
6, if the above adjustment measures are invalid, we can consider using parallelism to shorten the execution time of the target SQL.
7, if the above adjustment measures (including parallel) are not valid, we can also be based on the practice of business to change the execution logic of the target SQL, or even do not execute the target SQL, this is the most thorough
Optimized.

Understanding of SQL Optimization in Oracle