Oracle SQL performance optimization series 1

The ORACLE tutorial you are looking at is :Oracle SQL performance optimization series learning 1.

1. Select the appropriate ORACLE optimizer

There are three types of optimizers for ORACLE:

a.RULE (based on rules) b.COST (based on cost) c.CHOOSE (optional)

Set the default optimizer, can through to the init. ora file OPTIMIZER_MODE parameters of all kinds of statements, such as RULE COST, CHOOSE, ALL_ROWS, FIRST_ROWS. Of course, you also at levels SQL sentence or conversation (session) on the cover.

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 in the database (object statistics).

If the database optimizer mode is set to selective (CHOOSE), then the actual optimizer mode will depend on whether or not the analyze command has been run. If table has already been run by analyze, the optimizer mode will automatically become CBO, whereas the database will adopt the optimizer in the form of RULE.

By default,ORACLE USES the CHOOSE optimizer. To avoid unnecessary full table scans (full table scan), you must avoid using the CHOOSE optimizer and go directly to a rules-based or cost-based optimizer.

2. Access to Table

ORACLE USES two ways to access the records in the table:

a. Full table scan

A full table scan is a sequential access to each record in a table. ORACLE optimizes a full table scan by reading multiple data blocks at once (database block).

b. Access the tables through ROWID

You can improve the efficiency of accessing the table by using the access mode based on ROWID. ROWID contains the physical location information recorded in the table. ORACLE USES an index (INDEX) to link data to the physical location where the data is stored (ROWID). In general, indexes provide fast access to ROWID, so queries based on indexed columns can get performance improvements.

3. Share SQL statements

In order not to repeat the same SQL statement parsing, after the first resolution ORACLE will SQL statements stored in memory. This is located in the system global area SGA (system global area) of the Shared pool (shared buffer pool) in memory can be Shared among all the database users. Therefore, when you execute a SQL statement (sometimes referred to as a cursor), if it and before the execution of the statement is the same, ORACLE quickly gets the parsed statements and the best execution paths. ORACLE greatly improves the performance of SQL and saves memory.

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

The database administrator must set the appropriate parameters for this region in init.ora, and the larger the memory region, the more statements can be kept and the more likely it is to be Shared.

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

It should be noted here that ORACLE is a strict match between the two. To share,SQL statements must be identical (including Spaces, line feeds, etc.).

The Shared statement must satisfy three conditions:

A. Character level comparison:

The statement currently being executed must be exactly the same as the statement in the Shared pool.

Such as:

SELECT * FROM EMP;

It's different from each of the following

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

B. The objects referred to in both statements must be identical:

Such as:

How is the user object name 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 the two users.

Can SQL be Shared? Why

select max(sal_cap) from sal_limit;

Can't. Each user has one private synonym-sal_limit, which are 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 , plant_detail b where a.city_id = b.city_id

Can't. User jack accesses plant_detail through private synonym and jill is the owner of the table with different objects.

C. Binding variable that must use the same name in two SQL statements (bind variables)

Such as:

The two SQL statements in group 1 are the same (they can be Shared), while the two statements in group 2 are different (even if, at run time, they are assigned the same value 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;

On 1 page