Oracle optimizer and SQL sharing

1. oracle optimizer
======================================
Oracle has three Optimizer
RULE
COST
CHOOSE
The OPTIMIZER_MODE parameter is used to set the optimizer type. It is a selective optimizer by default and is both a CHOOSE
SQL> show parameter OPTIMIZER_MODE
NAME TYPE VALUE
-----------------------------------------------------------
Optimizer_mode string ALL_ROWS
The values of this parameter may contain
ALL_ROWS
FIRST_ROWS
RULE
COST
CHOOSE
ALL_ROWS: a cost-based optimizer that selects an execution plan that returns all data in the shortest time.
FIRST_ROWS: Unlike ALL_ROWS, FIRST_ROWS selects an execution plan that returns the first record of the result set as soon as possible, whether cost-based or rule-based.

<Note>:
When using CBO, you must always use analyze to perform statistical analysis on database objects to increase the accuracy of object information in the database.
If the CHOOSE optimizer is used, the optimizer actually used is related to whether the analyze command has been used.
If analyze is used, the CBO mode is used. Otherwise, the RULE mode is used.
By default, the database uses the CHOOSE optimizer. To avoid too many full table scans, it is best to avoid using the optimizer of the selected mode.

==================================
2. Access Table

Full table scan:

Sequential access is used to access each record. In actual process, oracle reads multiple data blocks at a time to accelerate full table scan.
The actual database uses the db_file_multiblock_read_count parameter to control the number of blocks read at a time. This parameter can be properly configured to optimize I/O
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
--------------------------------------------------------------
Db_file_multiblock_read_count integer 16

---------------------------------
Access through ROWID

When an index is used, the index uses the key value to differentiate data. Each key value corresponds to the ROWID of the stored data. Finally, the data is found through the corresponding ROWID of the index to optimize the query.

======================================
 
Shared SQL

To avoid repeated parsing of the same SQL statement, after the first parsing, oracle places the SQL statement in the library cache,
When the same SQL statement is executed again, the previous execution plan is directly referenced.
The parsed SQL statements stored in the library cache are cleared according to the lru rules.

To share SQL statements, the following three conditions must be met:
--------------------
1. Character-level comparison
Including statement
Uppercase/lowercase letters
Space
Tab key
Must be completely consistent
Example:
SELECT * from emp;
And
SELECT * from EMP;
Will not be shared
--------------------
2. The objects referred to by the two statements must be identical.
Example:
User1 has table t1
User2 has table t1
The two users establish their own connections to access the database, both of which are executed.
Select * from t1;
 
The statement cannot be shared because the owner is different.
---------------------
3. the Helper variables with the same name must be used in the two statements.
Select pin, name from people where pin =: blk1.pin;
Select pin, name from people where pin =: blk1.pin;
Shared
Select pin, name from people where pin =: blk1.ot _ ind;
Select pin, name from people where pin =: blk1.ov _ ind;
Cannot be shared

 

This article is from "one leaf in the wind"