Oracle SQL Performance Optimization series

1. Select the appropriate Oracle Optimizer

There are 3 types of Oracle Optimizer:

A. Rule (rule-based) B. Cost (based on costs) c. CHOOSE (selectivity)

The default optimizer can be set by various declarations of the Optimizer_mode parameter in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You can certainly 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 the object statistics (objects statistics) in the database.

If the optimizer mode of the database is set to selective (CHOOSE), then the actual optimizer mode will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer mode will automatically become the CBO, whereas the database will use the rule-form optimizer.

By default, Oracle uses the Choose Optimizer, and to avoid unnecessary full table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.

2. How to Access table

ORACLE uses two ways to access records in a table:

A. Full table scan

A full table scan is the sequential access to each record in the table. Oracle optimizes full-table scanning in a way that reads multiple data blocks (database block).

B. Accessing the table through ROWID

You can use ROWID-based access to improve the efficiency of your Access tables, ROWID contains the physical location information that is recorded in the table. Oracle employs an index to achieve the connection between data and the physical location (ROWID) where the data resides. Usually the index provides a quick way to access rowid, so those queries based on indexed columns can get a performance boost.

3. Shared SQL statements

In order not to parse the same SQL statements repeatedly, after the first resolution, Oracle stores the SQL statements in memory. This is a shared pool located in the system global area of the SGA (total buffer pool) Memory can be shared by all database users. So, 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 parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage.

Unfortunately, Oracle only provides caching (cache buffering) for simple tables, which does not apply to multi-table connection queries.

The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this block of memory.

It is important to note that Oracle has a strict match for both, and the SQL statements must be identical (including spaces, line breaks, and so on) to achieve sharing.

A shared statement must meet three conditions:

A. Character-level comparisons:

The statements that are currently executed and those in the shared pool must be identical.

For example:

SELECT * from EMP;

It's different from every one of the following.

SELECT * from EMP;

Select * from EMP;

SELECT * from EMP;

B. The object that the two statement refers to must be exactly the same:

For example:

How user object names are accessed

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 between these two users.

Can SQL be shared, why

Select Max (sal_cap) from Sal_limit;

No. Each user has a private synonym-sal_limit, which are different objects

Select count (*0 from work_city where Sdesc like ' new% ';

Yes. Two users access 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. Binding variables in two SQL statements that must use the same name (bind variables)

For example:

The first group of two SQL statements are the same (can be shared), while two statements in the second group are different (even at run time, the same values are assigned to different binding variables)

[NextPage]

 

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;