Oracle SQL Performance Optimization (1)

1. select an appropriate Oracle optimizer
There are three optimizer types in Oracle:
A. Rule (Rule-based) B. Cost (cost-based) C. Choose (selectivity)
Set the default optimizer. You can declare the optimizer_mode parameter in the init. ora file, such as rule, cost, choose, all_rows, and first_rows.SQL


Overwrite a sentence or session.
To use the cost-based optimizer (CBO, cost-based Optimizer), you must run the analyze command frequently to addDatabase


Object statistics accuracy.
If the optimizer mode of the database is set to choose, the actual optimizer mode is related to whether the analyze command has been run. if the table has been analyze, the optimizer mode will automatically become a CBO. Otherwise, the database will adopt a rule optimizer.
By default, Oracle uses the choose optimizer. To avoid unnecessary full table scan, you must avoid using the choose optimizer, the optimizer based on rules or costs is directly used.
　　
2. Access Table
Oracle uses two methods to access table records:
A. Full table Scan
Full table scan refers to sequential access to each record in the table. Oracle optimizes full table scan by reading multiple data blocks at a time.
B. Access the table through rowid

You can use rowid-based access to improve table access efficiency ,,
Rowid contains the physical location information recorded in the Table. Oracle uses indexes to establish a connection between the physical location (rowid) of the data and the physical location (rowid) of the data.
Generally, indexes provide a quick way to access rowid, so those queries based on index columns can improve the performance.
3. Share SQL statements

To avoid repeated parsing of the same SQL statement, Oracle stores the SQL statement in the memory after the first parsing.
The memory in the shared buffer pool can be shared by all database users.
Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement,
Oracle can quickly obtain parsed statements and the best execution path.
This function greatly improves SQL Execution performance and saves memory usage. Unfortunately, Oracle only provides high-speed caching for simple tables (Cache
Buffering), this function does not apply to multi-table join queries.
The database administrator must set the appropriate parameters for this region in init. ora.
The larger the Statement, the more statements can be retained. Of course, the more likely it is to be shared.
When you submit an SQL statement, Oracle first searches for the same statement in the memory. It must be noted that Oracle uses a strict match between the two.
The SQL statements must be identical (including spaces and line breaks ).
The shared statement must meet three conditions:
A. Character-level comparison: the statements currently executed must be identical to those in the sharing pool.
For example:
Select * from EMP;
Different from each of the following
Select * from EMP;
Select * from EMP;
Select * from EMP;
B. The objects referred to by the two statements must be identical:
For example:
　　　
Consider whether the following SQL statements can be shared between the two users.
　　
C. bind variables must be used in the two SQL statements)
For example:
The two SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even if different bind variables have the same value at runtime)
A. Select pin, name from people where pin =: blk1.pin;
Select pin, name from people where pin =: blk1.pin;
B. Select pin, name from people where pin =: blk1.ot _ ind;
Select pin, name from people where pin =: blk1.ov _ ind;