Oracle SQL Performance Optimization

With the continuous development of software technology, system performance becomes more and more important.

System performance is measured by system response time and concurrency.

There are two possible causes for poor SQL statement performance:
1. Developers only pay attention to the correctness of the query results, ignoring the efficiency of the query statements.
2. Developers only pay attention to the efficiency of SQL statements. They are not clear about the principle of SQL statement execution and the main factors that affect SQL statement execution efficiency.

* The former can be solved through in-depth study of SQL syntax and various SQL tuning skills.
SQL optimization is a system engineering, familiar with SQL syntax, master various embedded functions, points
The usage of function analysis is only necessary for writing efficient SQL statements.

* Starting from the analysis of SQL statement execution principles, the latter points out that SQL optimization should be performed in the optimization of SQL statements.
Analyze and optimize the CBO.

Optimization field:

1. application-level optimization:
* SQL statement Optimization
* Management change Optimization

2. instance-level optimization
* Memory
* Data Structure
* Instance Configuration

3. Operating System Interaction
* I/O
* SWAP
* Parameters

Adjustments at different stages generate corresponding performance benefits:




SQL statement optimization process:

1. Locate the problematic statement
2. Check the execution plan
3. Check the optimizer statistics during execution.
4. Analyze the number of records and indexes of related tables
5. Rewrite SQL statements, use HINT, adjust indexes, and analyze tables.
6. Some SQL statements may not be optimized and the processing method needs to be optimized.
7. achieve the best execution plan

What is a good SQL statement?

1. Simple and Modular
2. Easy to read and maintain
3. Save resources
* Memory
* CPU
* Fewer data blocks are scanned.
* Less sort
4. No Deadlock