Oracle optimization techniques

Posted under Article Database on Monday, 13 July 2009 by rueben

1. The types of ORACLE optimizer

ORACLE optimizer of a total of three kinds:

1.RULE (rule-based) 2.COST (based on cost) 3.CHOOSE (optional)

Optimization of the default settings, you can file through the init.ora parameters OPTIMIZER_MODE various statements, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. You, of course, also SQL sentence or conversation class (session) level of its coverage.

In order to use cost-based optimizer (CBO, Cost-Based Optimizer), you must always run the analyze command to increase the database of statistical information of the object (object statistics) accuracy.

If the database optimizer mode is set to selective (CHOOSE), the actual model optimizer will analyze whether the run-off orders. If the table has been over analyze, and optimize the mode will automatically become the CBO, the contrary, the database will be used RULE forms of optimizer.

By default, ORACLE optimizer CHOOSE used, in order to avoid unnecessary to scan the entire table (full table scan), you must try to avoid using CHOOSE optimizer, and the direct use of rule-based or cost-based optimizer.

2. The way to visit Table

ORACLE uses two types of visit record form:

1. Full table scan

Full table scan is the order of access to records of each table. ORACLE read more than the use of a data block (database block) to optimize the way of scanning the entire table.

2. ROWID access table through

You can visit ROWID-based approach, the table to improve the efficiency of the visit,, ROWID table record contains the physical location information .. ORACLE using the index (INDEX) to achieve the data and the physical location of stored data (ROWID) between Contact. usually the index provides a quick access to the ROWID method listed in the index-based query performance can be improved.

3. Shared SQL statement

In order not to repeat the same analysis of the SQL statement, after the first analysis, ORACLE to SQL statement stored in the memory. This region is located in the system overall SGA (system global area) of the shared pool (shared buffer pool) the memory can be users to share all of the database. Therefore, when you implement a SQL statement (sometimes called a cursor), if it had been prior to the implementation of the same statement, ORACLE has been able to quickly resolve the statement, as well as the most good execution path. ORACLE’s this feature greatly enhance the performance of the SQL and save the use of memory.

It is a pity that only a simple ORACLE table provides high-speed buffer (cache buffering), this feature does not apply to multi-table join query.

Init.ora database administrator must set up the region for suitable parameters, the greater when the memory region, you can retain more of the statement, of course, was the greater the possibility of sharing the.

When you submit a SQL to ORACLE statement, ORACLE will first memory in this statement to find the same.

It should be noted that, ORACLE is taken for both a strict match, it is necessary to reach a share, SQL statements must be exactly the same (including spaces, line, etc.).

Sharing the statement must satisfy three conditions:

A. Comparison of character class:

The current statement being executed and shared pool must be identical to the statement. For example:

SELECT * FROM EMP;

And following each of the different

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

B. referred to the object of two statements must be identical to, for example:

Users	Object name	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 the following SQL statement can be shared among the two users.

SQL	Shared	Reasons
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 access the same object of public synonym – work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id	No	Users through private synonym access jack and jill is plant_detail table owner, the object of different

C. two SQL statement must use the same name of the bind variables (bind variables), such as:

The first group of two SQL statements are the same (can be shared), while the second group the two statements are different (even in the run-time, giving different values of the same bind variables)

select pin, name from people where pin =: blk1.pin;

select pin, name from people where pin =: blk1.ot_ind;

select pin, name from people where pin =: blk1.ov_ind;