ORACLE Performance Optimization-SQL statement Optimization

Operating Environment: AIX + 11g + PLSQL

Includes the following content:

1. SQL statement execution process

2. optimizer and execution plan

3. Properly apply Hints

4. Indexes and Application Instances

5. Other optimization technologies and applications

1. SQL statement execution process 1.1 SQL statement execution steps

1) analyze the syntax and analyze whether the syntax of the statement complies with the specifications, to measure the meaning of each expression in the statement.

2) perform Semantic Analysis to check whether all database objects involved in the statement exist and the user has the corresponding permissions.

3) view conversion: converts query statements related to views into corresponding query statements for base tables.

4) expression conversion: converts complex SQL expressions into simple equivalent join expressions.

5) Select the optimizer. Different optimizers generally generate different "execution plans"

6) Select the connection mode. There are three connection modes in ORACLE. The appropriate connection mode is selected for multi-Table ORACLE connection.

7) Select the connection sequence, select which table to connect to ORACLE for multi-Table connection, and select which table in the two tables as the basic data table.

8) Select the data search path and select the appropriate data search path based on the preceding conditions. For example, select full table search, index or other methods.

9) run the "Execution Plan"

You can use the following statement to query the execution plan in the cache:

SELECT t1. *, 't2 --> ', t2. * FROM v $ SQL _plan t1 JOIN v $ SQL t2 ON t1.address = t2.address AND t1.hash _ value = t2.hash _ value AND t1.child _ number = t2.child _ number; -- the execution plan in the cache.

1.2 complete execution sequence of typical SELECT statements

1) The from clause assembles data from different data sources;

2) The where clause filters record rows based on specified conditions;

3) The group by clause divides data into multiple groups;

4) use aggregate functions for computation;

5) use the having clause to filter groups;

6) Calculate all expressions;

7) Calculate the select field;

8) order by is used to sort the result set.

1.3 SQL statement execution process

As shown in:

Note:

* This is an SQL statement execution process diagram.

* Execution plan is necessary for SQL statement execution.

* Execution plan is the product of Optimizer.

* Two Different Methods: CBO and RBO

View optimizer settings:

Method 1:

SELECT VALUE FROM v$parameter t WHERE t.name = 'optimizer_mode';

Method 2 (executed under SQLPLUS ):

showparameter optimizer_mode

* CBO uses Statistics in the dictionary, while RBO does not

Analyze statistics related SQL:

analyze table tablename compute statistics;

analyze table tablename compute statistics for all indexes

analyze table tablename delete statistics 

2. optimizer and execution plan 2.1 SQL optimization methodology * Versions later than ORACLE10g. The essence of SQL optimization is based on a deep understanding of CBO and execution plan. In the CBO era, we must understand the execution plan.
* There are many ways to view the execution plan. For example, if you use the PL/SQL Developer tool and select a select statement, press F5 to display the execution plan, but the display is incomplete.

* It is best to use the official Oracle sqlplus tool, which has the best performance and is convenient and intuitive. The following describes two ways to view execution plans (and the simplest two ways)

Some knowledge about the execution plan:

* Full Table Scans Full Table scan * Rowid Scans rowid scan * Index Scans Index scan * Index Unique Scans * Index Range Scans Descending * Index Skip Scans * Full Scans * Fast Full index Scans (CBO) * Index Joins * Bitmap Joins * Cluster Scans Cluster scan * Hash Scans Hash scan * Sample Table Scans Table sampling Scan

2 In the RBO era, access path is very simple and can be used with indexes. For the join method, programmers generally get better running results by adjusting the order between joined tables. What are the disadvantages? 2. With CBO, the word "CBO" is simply two words-"CBO takes care of an arranged marriage:" Let me handle your affairs. By default, statistics are automatically collected from to the next day from Monday to Friday and during the entire weekend.

You can view the parameters:

show parameter STATISTICS_LEVEL

2 problem: the CBO execution plan depends on an inaccurate statistic (missing or too old), which leads to deviations in computing execution costs and may lead to incorrect execution plans. What should I do?
Step 1: collect statistics again!
Part 2: when the first part cannot solve the problem, use Hints3. reasonably apply Hints3.1Hints

Using hint with caution may cause serious consequences. For example, append may generate a lock block, resulting in concurrent resource waiting.

Hints classification: * Hints forOptimization Approaches and Goals (4)
/* + ALL_ROWS * // * + FIRST_ROWS (n) * // * + CHOOSE * // * + RULE */

* Hints for AccessPaths (12)
/* + FULL (table) * // * + INDEX (tableindex) * // * + INDEX_ASC (tableindex) * // * + INDEX_COMBINE (table index) * // * + INDEX_JOIN (table index) * // * + INDEX_DESC (table index) * // * + INDEX_FFS (tableindex) * // * + NO_INDEX (tableindex) * // * + AND_EQUAL (tableindex index )*/
* Hints for QueryTransformations (10) * Hints for JoinOrders (2) * Hints for JoinOperations (11)
/* + USE_NL (table) * // * + USE_MERGE (table) * // * + USE_HASH (table) * // * + LEADING (table )*/
* Hints for ParallelExecution (5) * Additional Hints (13)

The following example shows how to use Hints.

create table t_1(owner varchar2(30),table_name varchar2(30));create table t_2(owner varchar2(30),table_name varchar2(30));insert into t_1 SELECT owner,table_name FROM dba_tables;insert into t_2 SELECT owner,view_name  FROM dba_views t;create index idx_t_1 on t_1(table_name);create index idx_t_2 on t_2(table_name);analyze table t_1  compute statistics; analyze table t_2  compute statistics; SELECT *  FROM (SELECT * FROM t_1        UNION ALL        SELECT * FROM t_2) aa WHERE aa.table_name LIKE 'Z%';                 ---- Full Table Scans SELECT /*+ index(AA.t_1 idx_t_1) index(AA.t_2 idx_t_2)*/ *  FROM (SELECT * FROM t_1        UNION ALL        SELECT * FROM t_2) AA WHERE AA.table_name LIKE 'Z%';               ---- Index Scans

Paste the execution graph:

4. Indexes and Application Instances

4.1 what is an index * Oracle index is a self-balancing B * Tree storage structure. Its basic storage unit is data blocks, which are called nodes. There are three types of nodes in total: root Node, Branch node, and leaf node. * Branch node storage {index value, key value corresponds to the block address of the next level node, lmc pointer} * leaf node storage {index value and rowid, data block address of the current node}
The two pointers on all leaf nodes form a two-way linked list. All index values on this two-way linked list are arranged from small to large. For reverse desc indexes, they are arranged from large to small.

B * TREE index chart:

4.2 Index classification

 

Logically:
Single column Index
Concatenated multi-column Index
Unique index
Non-Unique index
Function-based Function Index
Domain index

Physically:
Partitioned partition Index
Non-Partitioned Non-partition Index
B * tree:
Normal B-type tree
ReverseKey reverse B-tree
Bitmap index 4.3 When to use the Index * if you want to retrieve the entire table, you do not need to create an index because the index will bring additional IO operations. * If the number of retrieved records accounts for less than 10% of all table records, you can create an index (large table ). * You can create an index for the joined fields between tables, especially for a large table and a small table. * B * Tree indexes are suitable for a large number of OLTP values );
Queries that contain the OR operator are not applicable. NULL judgment is not applicable;
Suitable High base* Bitmap indexes are suitable for decision-making support system OLAP;
The cost of UPDATE is relatively high; blocks will be locked;
It is very suitable for OR operator queries;
Suitable Low base* The Reverse index reverses the byte in the B * tree index code. It is used in parallel server environments for more even allocation of index entries, used to reduce the competition for index leaves. Index is a double-edged sword, seeking a balance between query and DML 4.4 rewrite SQL Using Index

* If the common index column a is not null, it is logically changed to a> 0 or a>''

* Like Operation Rewriting

* You can use union all to never use union unless you want to duplicate it.

* Although the in operation is easy to understand, oracle converts the data into Table connection queries. Using in, the data is converted in one step, therefore, we recommend that you use table join query ** not in. We strongly recommend that you use not exists or (Outer Join + null) * <> (not equal to) operations without indexing, we recommend that you change a <> 0 to (a> 0 ora <0) a <> ''To a> ''' * to avoid implicit type conversion, oracle's internal processing of a = 0 is completely different from a = '0', and even does not go through the index 4.5 index application.

Example 1. Use an appropriate index to avoid unnecessary full table Scan

If you want to query the is not null condition in the index column, we recommend that you add the is not null constraint to the column. The default value is the constraint,

However, for some reason, the index column is designed to be null and you still want to use the is null condition for indexing. How can this problem be solved? See

Drop table t_tab1; create table t_tab1 as SELECT t. owner, t. object_name, t. object_type, t. created, t. last_ddl_time FROM dba_objects t; analyze table t_tab1 compute statistics; create index idx01_t_tab1 on t_tab1 (last_ddl_time); -- general index set autotrace trace; SELECT * FROM t_tab1 t where t. last_ddl_time is null;

The execution plan is as follows:

Changed to composite index

Drop index idx01_t_tab1; create index idx01_t_tab1 on t_tab1 (last_ddl_time, 1); -- added a constant set autotrace trace; SELECT * FROM t_tab1 t where t. last_ddl_time is null;

The execution plan is as follows:

Example 2: Use appropriate function indexes to avoid full table scans that seem unavoidable

drop table t_tab1 purge;create table t_tab1 as     SELECT t.owner,      t.object_name,       t.object_type,       t.OBJECT_ID,       t.created,       t.last_ddl_time  FROM dba_objects t;CREATE INDEX IDX01_T_TAB1 ON T_TAB1(object_name);analyze table t_tab1  compute statistics; set autot traceSELECT * FROM t_tab1 t where t.object_name like '%20121231';

The execution plan is as follows:

Improve the index. Here, reverse function indexes are used. In addition, the frequently used function indexes include instr () and substr ().

drop index IDX01_T_TAB1;CREATE INDEX IDX02_T_TAB1 ON T_TAB1(reverse(object_name));analyze table t_tab1  compute statistics; SELECT * FROM t_tab1 t where reverse(t.object_name) like reverse('%20121231');

The execution plan is as follows:

 

5. Other optimization technologies and applications 5.1 Other optimization technologies and ideas

In parallel technology, the target SQL statement is executed in parallel. In fact, the execution time is shortened by the consumption of additional resources. In many cases, parallel execution is the only Optimization Method for some SQL statements.

Use shell scheduling or other scheduling tools.

SQL statement-Level Parallelism:/* + parallel */

/* + Parallel (table_name 4 )*/

Table compression technology

Compress

NOLOGGING

Reduce logs

Partition Technology

Divide and conquer

Intermediate table/temporary table transaction decomposition ideas

'Big things make thing'

Balance

CPU and Memory are powerful, and IO bottlenecks exist (most common)

New Features

Insertall uses listparts () and other analysis functions, which are about 50 times faster than wm_concat () and row_number ().

Reasonable combination of software and hardware resources

Is hardware required? Boss will tell you, look for an accountant and pay you in advance ......

5.2 SQL optimization Summary

SQL optimization methods include but are not limited to the following measures:

* If the statistical information is inaccurate or the SQL Performance problem is caused by the inherent insufficiency of the formula used by CBO to calculate the cost of some SQL Execution paths (Access paths,
We can re-collect statistics, manually modify statistics, or use Hint to solve the problem. * if it is an SQL statement, we can rewrite the SQL statement without changing the business logic. * if it is unnecessary to scan or sort the entire table, the performance of the target SQL statement may be affected, we can solve this problem by establishing appropriate indexes (including function indexes and bitmap indexes). * If the performance of the target SQL statement is caused by poor design of tables or indexes, we can re-design the table/index and re-organize the data in the table to solve the problem. * If the above adjustment measures fail, we can consider using parallelism to shorten the execution time of the target SQL statement; * If the above adjustment measures, including parallel operations, fail, we can also modify the execution logic of the target SQL statement based on actual services, or even not execute the target SQL statement. This is the most thorough optimization :)