1. Use the appropriate Oracle optimizer
Oracle's optimizer has three types:
a. Rule (based on rules) b. COST (Based on cost) c. choose (selective)
Set the default optimizer, you can pass the various declarations of the Optimizer_Mode parameter in the init.ora file, such as Rule, Cost, Choose, All_Rows, First_Rows. Of course, it is also available on the SQL sentence class or a session (session) level. cover.
To use a cost-based optimizer (CBO, COST-BASED OPTIMIZER), you must regularly run the Analyze command to increase the accuracy of object statistics in the database.
If the database's optimizer mode is set to selectability, the actual optimizer mode will be related to whether the analysis command is running. If table has been over, the optimizer mode will automatically become CBO, but in turn, the database will use Rule Form optimizer.
By default, Oracle uses a Choose optimizer, in order to avoid Full Table Scan, you must try to avoid using the Choose optimizer without using a rule or cost-based optimizer.
2. Way to access Table
Oracle uses two ways to record in the table:
a full table scan
The full table scan is sequentially accessing each record in the table. Oracle uses a multi-data block (Database Block) to optimize full mete scan.
b. Access to the RowID
You can use the ROWID-based access method, improve the efficiency of the access table, and the RowID contains the physical location information recorded in the table .. Oracle adopts index (INDEX) to implement the physical location of the data and storage data (RowID) Contact. Usually the index provides a way to quickly access the RowID, so those inquiry based on the index column can be improved.
3. Share SQL statement
In order not to repeat the same SQL statement, after the first resolution, Oracle stores the SQL statement in memory. This block in the shared buffer pool of the system global area can be All database users share. So, when you perform a SQL statement (sometimes called a cursor), if it
And the previously performed statements are exactly the same, Oracle can quickly get the statements that have been parsed and the best
Perform a path. Oracle's functionality greatly increases SQL execution performance and saves memory usage.
Unfortunately, Oracle only provides cache buffering for simple tables, which does not apply to multi-table connection queries.
The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the more statements can be retained, and the possibility of being shared is, the larger.
When you submit an SQL statement to Oracle, Oracle will first find the same statement in this memory.
What needs to be indicated here is that oracle takes a strict match to reach a sharing, the SQL statement must
Example (including space, wrap, etc.).
The shared statement must meet three conditions:
A. Comparison of characters:
The statement currently executed and the statement in the shared pool must be exactly the same.
E.g:
SELECT *.
And each of the following
SELECT *.
SELECT *.
SELECT *.
B. The object referred to in the two statements must be exactly the same:
E.g:
How to access the user object name
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 share between these two users. SQL
Can you share?
the reason
SELECT MAX (SAL_CAP) from Sal_Limit;
Cannot
Each user has a private synonym - sal_limit, which is different objects
SELECT Count (* 0 from work_city where sdesc like 'new% ";
can
Two users access the same object Public Synonym - Work_city
Select a.sdesc, b.location from work_city a, plan_detail b where a.city_id = b.city_id
Cannot
User Jack Access Plant_Detail via Private Synonym and jill is the owner of the table, the object is different.
C. Bind variation of the same name must be used in two SQL statements (Bind Variables)
E.g:
The two SQL statements of the first group are the same (can be shared), and the two statements in the second group are different (even if they are running, the same value of 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;
(Excerpt from 9cbs)
