1. Choose the appropriate Oracle optimizer
There are 3 Oracle optimizer types:
1.RULE (rule-based) 2.COST (based on cost) 3.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:
1. 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.
2. 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.).
The shared statement must meet three criteria:
A. Character-level comparisons:
The statement that is currently executed and the statements in the shared pool must be identical. For example:
SELECT * from EMP;
is different from each of the following
SELECT * from EMP;
Select * from EMP;
SELECT * from EMP;
B. Two statements refer to objects that must be identical, for example:
User |
Object Name |
How to access |
Jack |
Sal_limit |
Private synonym |
|
Work_city |
public synonym |
|
Plant_detail |
public synonym |
Jill |
Sal_limit |
Private synonym |
|
Work_city |
public synonym |
|
Plant_detail |
Table owner |
Consider whether the following SQL statements can be shared among these two users.
Sql |
Can share |
Reason |
Select Max (sal_cap) from Sal_limit; |
No |
Each user has a private synonym-sal_limit, they are different objects |
Select count (*0 from work_city where Sdesc like ' new% '; |
Yes |
Two users accessing the same object public synonym-work_city |
Select A.sdesc,b.location from Work_city A, plant_detail b where a.city_id = b.city_id |
No |
User Jack accesses plant_detail through private synonym and Jill is the owner of the table, with different objects |
C. Two SQL statements must use the same name of the binding variable (bind variables), for example:
The first group of two SQL statements is the same (can be shared), and the two statements in the second group are different (even at run time, with the same values as different binding variables)
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;