Oracle SQL Performance Optimization Series (1)

zhaozj2021-02-16  75

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 is available in the SHARED BUFFER POOL in the System Global Area. 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;

(to be continued)

转载请注明原文地址:https://www.9cbs.com/read-15131.html

New Post(0)