Optimization of Oracle statements explain the rules of 53

Jun 16 posted in Oracle (RSS), 164 views.

1. The types of ORACLE optimizer
ORACLE optimizer of a total of three kinds:

a. RULE (rule-based)
b. COST (based on cost)
c. 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 analyze, and optimize the mode will automatically become the CBO, the contrary, the database will be used form of RULE 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 visit ORACLE table two records:

a. full-table scan is the sequence of full-table scan access to each record of the table. ORACLE used a number of data blocks read (database block) to optimize the way of scanning the entire table.

b. through the ROWID you can visit the table of ROWID based on the situation of access to improve the efficiency of access table, 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) links. Usually provide a quick access index ROWID way out so that 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 shared to all users 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 implementation of the best 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 taken for both a strict match, it is necessary to reach a share, SQL statements must be exactly the same (including spaces, line, etc.).

Shared 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:

For example:

Of how to access user object
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 can share the reasons for
select max (sal_cap) from sal_limit; not every user has a private synonym - sal_limit, they are different objects
select count (* 0 from work_city where sdesc like 'NEW%'; to 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 not the user through the private synonym access jack and jill is plant_detail table owner, a different object.

C. two SQL statement must use the same name of the bind variables (bind variables)

For example: the first two are the same SQL statement (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)

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;
SAL_RANGE = (SELECT MAX (SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
Efficient:

UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX (CATEGORY), MAX (SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

16. Through internal SQL function to improve efficiency.

SELECT H. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DESC, COUNT (*)
FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H. EMPNO = E. EMPNO
AND H. HIST_TYPE = T. HIST_TYPE
GROUP BY H. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DESC;
By calling the following function can improve efficiency.

FUNCTION LOOKUP_HIST_TYPE (TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2 (30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL (TDESC ,'?'));
END;
FUNCTION LOOKUP_EMP (EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2 (30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO = EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL (ENAME ,'?'));
END;
SELECT H. EMPNO, LOOKUP_EMP (H. EMPNO),
H. HIST_TYPE, LOOKUP_HIST_TYPE (H. HIST_TYPE), COUNT (*)
FROM EMP_HISTORY H
GROUP BY H. EMPNO, H. HIST_TYPE;
(Translator's note: see often in the forums such as' can not write a SQL .... 'Of the post, not knowing the complexity of SQL often at the expense of efficiency. Able to master the use of the above functions to solve problems in practical work is very meaningful)

17. The use of table alias (Alias)

When the SQL statement to connect more than one table, use the table alias of an alias and the prefix of each Column. In this way, we can reduce the analysis time and reduce the ambiguity caused by the Column of the syntax error.

(Translator's Note: Column ambiguity refers to as a result of the different SQL tables with the same name of the Column, when the SQL statements appear in the Column, when, SQL parser can not determine the ownership of the Column)

18. Alternative IN with EXISTS

In many forms on the basis of the query, in order to satisfy a condition, often linked to another table. In this case, the use of EXISTS (or NOT EXISTS) usually will increase the efficiency of query.

Inefficient:

SELECT *
FROM EMP (based on table)
WHERE EMPNO> 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'MELB')
Efficient:

SELECT *
FROM EMP (based on table)
WHERE EMPNO> 0
AND EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB')
(Translator's note: On the other hand, the replacement of NOT EXISTS with NOT IN will be more significant to improve efficiency, the next section will point out)

19. NOT EXISTS with NOT IN alternatives

In the sub-query, NOT IN clause will be the implementation of an internal sorting and merging. In either case, NOT IN the lowest efficiency (because of its sub-query table table-wide implementation of a traverse). In order to avoid using NOT IN, we can rewrite it into outer join (Outer Joins) or NOT EXISTS.

For example:

SELECT ...
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT = 'A');
In order to improve efficiency. Rewritten as follows:

(Method 1: High)

SELECT ....
FROM EMP A, DEPT B
WHERE A. DEPT_NO = B. DEPT (+)
AND B. DEPT_NO IS NULL
AND B. DEPT_CAT (+) = 'A'
(Method II: the most efficient)

SELECT ....
FROM EMP E
WHERE NOT EXISTS (SELECT 'X'
FROM DEPT D
WHERE D. DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A');
20. Connected with the replacement of table EXISTS

In general, the use of tables to connect more efficiently than EXISTS

SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A');
(More efficient)

SELECT ENAME
FROM DEPT D, EMP E
WHERE E. DEPT_NO = D. DEPT_NO
AND DEPT_CAT = 'A';
(Translator's note: In the case of RBO, the former path, including the implementation of FILTER, the latter using the NESTED LOOP)

21. DISTINCT replaced with EXISTS

When to submit a one-to-many table contains information (such as the department table and employee table) the query in the SELECT clause to avoid the use of DISTINCT. Generally considered to replace EXIST

For example:

Inefficient:

SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM DEPT D, EMP E
WHERE D. DEPT_NO = E. DEPT_NO
Efficient:

SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (SELECT 'X'
FROM EMP E
WHERE E. DEPT_NO = D. DEPT_NO);
EXISTS to make inquiries more quickly, because the core modules will RDBMS subquery meet the condition, once immediately after the return of the results.

22. Identification of 'inefficient implementation' of the SQL statement

SQL using the following tools to identify inefficient SQL:

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,

ROUND ((BUFFER_GETS-DISK_READS) / BUFFER_GETS, 2) Hit_radio,
ROUND (DISK_READS / EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM V $ SQLAREA
WHERE EXECUTIONS> 0
AND BUFFER_GETS> 0
AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS <0.8
ORDER BY 4 DESC;
(Translator's note: Although a variety of graphics on the SQL optimization tools and styles, but to write their SQL tools to solve the problem is always the best method)

23. TKPROF tool used to query the status of SQL performance

SQL trace tool for the collection is the performance of SQL execution state data and track record to a file. The trace file provides a lot of useful information, such as frequency analysis. The implementation of the number, CPU time used. These data will be used to optimize your system.

Set up a session-level SQL TRACE:

Effective

ALTER SESSION SET SQL_TRACE TRUE

SQL TRACE settings are to follow in the database, you SQL_TRACE parameters must be set in the init.ora of TRUE, USER_DUMP_DEST parameter describes the generation of trace file directory

(Translator's note: this section, the authors did not mention the use of TKPROF to use the SQL TRACE also not accurate enough, we must first set up in SQL TRACE in init.ora settings TIMED_STATISTICS, this can be an important time for the state of those who . generated trace file is unreadable, so use TKPROF tools of their conversion, TKPROF there are many implementation parameters. We can make reference to understand the specific ORACLE manual configuration.)

24. Using analysis of SQL statement EXPLAIN PLAN

EXPLAIN PLAN is a good tool for analysis of SQL statements, it can even be in the non-implementation analysis of the case of SQL statements. Through analysis, we can know is how to connect ORACLE table, in what way the use of scanning table (full table scan or index scan), as well as use the name of the index.

You need the outside from the inside out, from top to bottom analysis of the order of interpretation of results. Analysis of the results of EXPLAIN PLAN is indented format with the most internal operations will be the first to read, if the two operations at the same level, the operation with the smallest will be the first of its implementation.

NESTED LOOP is one of the few not in accordance with the operation of the above rules, the correct execution path is to check NESTED LOOP provide data on the operation, in which the smallest number of operations will be the first to deal with.

Translator's note: through practice, are still in use SQLPLUS easier SET TRACE function.

For example:

SQL> list
1 SELECT *
2 FROM dept, emp
3 * WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly / * traceonly can not display the results * /
SQL> /
14 rows selected.
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
-------------------------------------------------- --------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL * Net to client
503 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
From the above analysis, we can draw the actual steps are:

1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)

Note: At present, many third-party tools such as TOAD and ORACLE to provide their own tools, such as SQL Analyze the OMS provides an extremely convenient tool for EXPLAIN PLAN. Perhaps like the graphical interface can choose to use their friends.

25. To use the index to improve efficiency

Table index is part of a concept to improve the efficiency of retrieval of data. In fact, ORACLE uses a complex self-balancing B-tree structure. Typically, through the index to query the data than the full table scan faster. When the implementation of ORACLE query and Update to find the best path when statement, ORACLE optimizer will use index. The same number of table join index can also be used to improve efficiency. Another advantage of using the index is that it provides a primary key (primary key) to verify the uniqueness.

In addition to those LONG or LONG RAW data type, you can index almost all out. Typically, a large table in a particularly effective use of the index. Of course, you will find a small table in the scan, use the index can also improve efficiency.

Although the use of the index can be more efficient query, but we must also be noted that the cost of it. Need space to store the index, but also the need for regular maintenance, whenever there is a record of change in the table or index column is modified, the index itself can be modified. This means that each record of the INSERT, DELETE, UPDATE will pay 4, 5 times the disk I / O. Because the index of the need for additional storage space and processing, which would bring unnecessary index slow query response time .

Translator's note: The Reconstruction of periodic index is necessary.

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

26. Index operation

ORACLE there are two types of index access pattern.

Only scan the index (INDEX UNIQUE SCAN)

In most cases, through the WHERE clause optimizer visit INDEX.

For example:

There are two index tables LODGING: LODGING out on a unique index on the LODGING_PK and the establishment of MANAGER are listed in the non-unique index LODGING $ MANAGER.

SELECT *
FROM LODGING
WHERE LODGING = 'ROSE HILL';

In-house, the SQL implementation will be divided into two steps, first of all, LODGING_PK index scan will be the only way the index is accessed to obtain the corresponding ROWID, through the visit table ROWID implementation of the next search.

If the search return list included in the INDEX column, ORACLE will not implement the second step of processing (through the ROWID access table). Because the retrieval of data stored in the index, simply visit the index can fully satisfy the query results.

SQL only need the following INDEX UNIQUE SCAN operation.

SELECT LODGING
FROM LODGING
WHERE LODGING = 'ROSE HILL';

Range query index (INDEX RANGE SCAN)

Applies to two situations:

1. Based on a range of search 2. Based on non-uniqueness of the search index

Example 1:

SELECT LODGING FROM LODGING WHERE LODGING LIKE 'M%';

WHERE clause conditions include a range of values, ORACLE scope of inquiry by way of the index query LODGING_PK. The scope of inquiry as a result of the index will return a set of values, its efficiency will be lower than the number of index scans only.

Example 2:

SELECT LODGING
FROM LODGING
WHERE MANAGER = 'BILL GATES';

This two-step SQL implementation, LODGING $ MANAGER index of the scope of inquiry (to be recorded all eligible ROWID) and visit the next table with the ROWID had been out LODGING value. LODGING $ MANAGER as a result of a non-unique index, the database can not only scan the index to its implementation.

LODGING out as a result of SQL to return, but it does not exist in the LODGING $ MANAGER index, so the scope of inquiry in the index will be the implementation of an access table through ROWID operation.

WHERE clause, if the index column values corresponding to the first by the wildcard character (WILDCARD) started the index will not be used. In this case, ORACLE will use full table scan.

SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE '% HANMAN';

27. Based on the choice of table

The basis of table (Driving Table) means the first visit to the table (usually full-table scan access of the way). According to the different optimizer, SQL statement based on the choice of form is not the same.

If you are using the CBO (COST BASED OPTIMIZER), optimized SQL statement checks each table in the physical size of the state index, and then choose the lowest cost implementation of the path.

If you are using RBO (RULE BASED OPTIMIZER), and all the conditions have to connect the corresponding index in this case, the basis of table is listed in the FROM clause of the final table.

For example:

SELECT A. NAME, B. MANAGER
FROM WORKER A,
LODGING B
WHERE A. LODGING = B. LODING;

LODGING table as a result, there is a LODING out the index, but not WORKER comparison table of the index, WORKER table will be used as the basis of the query table.

28. A number of equal index

When the SQL statement execution path can be used on more than one table in a number of indexing, ORACLE will use multiple indexes at the same time and run-time records of their merger, only to retrieve all records indexed effective.

ORACLE choice in the implementation of the path, the only index of the level higher than the non-unique index. However this rule only if the index column in the WHERE clause and the constant comparison to be valid. If the index column and other types of tables compared to the index. This clause in the level optimizer is very low.

If you would like a different table with two levels of the index will be quoted, FROM clause table which will determine the order will be the first to use. FROM clause in the final table of the index will have the highest priority.

If the two want the same table with the index level will be quoted, WHERE clause first cited in the index will have the highest priority.

For example:

DEPTNO, there is a non-unique index, EMP_CAT also have a non-unique index.

SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = 'A';

Here, DEPTNO search index will be the first, and then retrieve the same index EMP_CAT records under one roof. Execution path is as follows:

TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX

29. Comparison and scope of the equation

When the WHERE clause in the index column, ORACLE can not merge them, ORACLE will use the range.

For example:

DEPTNO, there is a non-unique index, EMP_CAT also have a non-unique index.

SELECT ENAME
FROM EMP
WHERE DEPTNO> 20
AND EMP_CAT = 'A';

Only here EMP_CAT index was used, and then one by one all of the records will be compared with the conditions of DEPTNO. Execution path is as follows:

TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX

30. The index level is not clear

ORACLE can not determine when the level of high and low index difference between the optimizer will only use an index, it is in the WHERE clause was included in the front.

For example:

DEPTNO, there is a non-unique index, EMP_CAT also have a non-unique index.

SELECT ENAME
FROM EMP
WHERE DEPTNO> 20
AND EMP_CAT> 'A';

Here, ORACLE uses only index DEPT_NO. Execution path is as follows:

TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX

Translator's note: Let's try this case the following:

SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';

INDEX_NAME UNIQUENES

------------------------------ ---------

EMPNO UNIQUE
EMPTYPE NONUNIQUE

SQL> select * from emp where empno> = 2 and emp_type = 'A';

no rows selected

Execution Plan

-------------------------------------------------- --------

0 SELECT STATEMENT Optimizer = CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)

Although EMPNO is the only index, but because it is done by the scope of comparison, the level of the index than the non-uniqueness of the equation is relatively low!