Oracle SQL Performance Optimization Series learning a _oracle

The Oracle tutorial being looked at is: Oracle SQL Performance Tuning series learning one.

1. Choose the appropriate Oracle Optimizer

There are 3 Oracle optimizer types:

A. Rule (rule-based) b. Cost (based on costs) c. 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:

A. 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.

B. 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;

The objects referred to in B. Two statements 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 among these two users.

Can SQL be shared, why

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, and the objects are different.

C. Two binding variables that must use the same name in SQL statements (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)

[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;

Previous page