Oracle SQL Performance Optimization Lecture series

1. Choose the appropriate Oracle Optimizer

There are 3 Oracle optimizer types:

A. Rule (rule-based) b. Cost (based on costs) c. CHOOSE (optional)

Set the default optimizer to pass various declarations of optimizer_mode parameters in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You will of course overwrite it at the SQL sentence level or at the session level.

In order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of object statistics in the database (objects statistics).

If the optimizer mode of the database is set to selectivity (CHOOSE), the actual optimizer pattern will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer pattern will automatically become a CBO, whereas the database will use the rule-form optimizer.

By default, Oracle employs the Choose Optimizer, and in order to avoid unnecessary full table scans (scan), you must avoid using the Choose Optimizer as much as possible, using a rule-based or cost-based optimizer.

2. How to Access table

ORACLE takes two ways to access records in a table:

A. Full table scan

A full table scan is a sequential access to each record in the table. Oracle optimizes full table scans in a way that reads multiple data blocks (database block) at a time.

B. Access to tables via ROWID

You can use ROWID access mode to improve the efficiency of the Access table, ROWID contains the physical location information recorded in the table. Oracle uses indexes (index) to achieve the link between the data and the physical location (ROWID) where the data resides. Often indexes provide a quick way to access rowid, so queries based on indexed columns can improve performance.

3. Sharing SQL statements

In order not to parse the same SQL statement repeatedly, Oracle holds the SQL statement in memory after the first resolution. This is a shared pool (sharing buffer pool) in the system global Zone SGA (System global Area) The memory in can be shared by all database users. Therefore, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the statements that have been parsed and the best execution path. This feature of Oracle greatly improves the execution performance of SQL and saves memory usage.

Unfortunately, Oracle only provides high-speed buffering (cache buffering) for simple tables, and this feature does not apply to multiple table join queries.

The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared.

When you submit an SQL statement to Oracle, Oracle first looks for the same statement in this block of memory.

It should be noted here that Oracle is a strict match between the two, to achieve sharing, the SQL statement must be exactly the same (including spaces, line wraps, etc.).