Two major steps to optimize and adjust Oracle SQL Performance

The following articles mainly introduce the optimization and adjustment of Oracle SQL Performance, including how to select an appropriate Oracle optimizer and how to access Table, oracle uses two methods to record data in different access tables.

1. select an appropriate Oracle optimizer

There are three optimizer types in Oracle:

A. RULE (based on Rules)

B. COST (COST-based)

C. CHOOSE (optional)

Set the default Oracle SQL Performance optimizer. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. of course, you also overwrite SQL statements or sessions.

To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.

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 CBO. Otherwise, the database will adopt the RULE form optimizer.

By default, Oracle uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer.

2. Access Table. Oracle uses two access methods to access Table records.

A. Full table Scan

Full table scan accesses each record in the table sequentially. Oracle reads multiple data blocks at a time. Oracle SQL Performance Optimization full table scan.

B. Access the table through ROWID

You can use ROWID-based access to improve the efficiency of table access. ROWID contains the physical location information recorded in the table ...... Oracle uses indexes to connect data with the physical location where data is stored (ROWID. Generally, indexes provide a quick way to access ROWID. Therefore, queries based on index columns can improve the performance.

Article by: http://www.programbbs.com/doc/3371.htm