Oracle-based SQL optimization

[ Oracle-based SQL Optimization ]

Oracle-based SQL optimization

"Bo Master" Gao Ruilin

"Blog Address" http://www.cnblogs.com/grl214

A Written Intent description

In the early stage of the system development, due to less database data, the SQL statement of various writing can not reflect the performance of SQL, with the increasing data, the emergence of massive data, poor SQL and high-quality SQL in the execution efficiency even there are hundreds of times, the importance of SQL optimization can be seen

Two SQL Statement Performance optimization

2.1 Understanding Oracle's execution Process

2.2 Oracle Optimization Rules---funnel rules

2.3 Oracle Execution Plan 2.3.1 What is Oracle execution plan

An execution plan is a description of a query statement that executes a procedure or access path in Oracle.

2.3.2 View Oracle Execution plan

1. Interpretation of column fields commonly used in execution plans

Cardinality: Number of result set rows returned

Bytes: Number of bytes returned after performing this step

Consumption (cust), CPU consumption: Oracle estimates the execution cost of this step, which is used to illustrate the cost of SQL execution, and the less theoretically the better.

2.3.3 Understand Oracle Execution plan

2.3.3.1 Execution Order

According to indentation, the indentation is the most first execution (the indentation is the same, the top of the first execution)

2.4 How tables are accessed

• Table ACCESS Full (All-table scan)
• Table access by ROWID (accessed through ROWID tables)
• TABLE ACCESS by index Scan (index scans)

2.4.1 ABLE ACCESS Full (All-table scan)

Oracle reads all rows in the table and checks to see if the conditions in the where statement are satisfied;

Usage recommendation: Tables with too large data size are not recommended for full table scans

2.4.2 Table access by ROWID (accessed via ROWID table)

ROWID's explanation: Oracle automatically adds a pseudo-column to the last column of each row in the table, and the table does not physically store the ROWID value, and once a row of data is inserted, its corresponding ROWID is unique for the life of the row, and the ROWID value of the row is the same even if a row migration occurs.

2.4.3 TABLE ACCESS by index Scan (index scanning)

The key value for each index is stored in the index block, and the rowid of the pair that has the key value is stored.

The scan of an index is divided into two steps: The first is to find the rowid of the index, followed by the ROWID to read the row data

There are five more types of index scans:

• Index unique Scan (indexed only)
• Index range Scan
• Index full scan
• Index fast full Scan (indexed quick Scan)
• Index skip Scan (indexed hop scanning)

(a). Index uniquescan (indexed uniquely scanned):

Scan for uniqueness Index (unique index), return at most one record at a time, mainly for the field primary key or unique;

(b). IndexRangeScan (index range scanning)

Use an index to access multiple rows of data;

Three scenarios in which an index range scan occurs:

• A range operator is used on a unique index column (such as:> < <> >= <= between)
• On a composite index, queries are made using only a subset of columns (the query must contain a leading column or a full table scan)
• Any query that is made on a non-unique indexed column

(c).indexfull scan

• When a full index scan is performed, the data that is queried must be directly available from the index

(d).index Fast full scan(Index quick Scan)

• Scanning all data blocks in an index is similar to index full SCAN, but one notable difference is that it does not sort the queried data (that is, the data is not returned in sort order)

(E). Index skipScan(Index hop scanning):

Oracle 9i is provided, sometimes the leading column of the composite Index (the first column in the index) does not appear in the query statement, ORALCE will also use the composite index, this time the use of the index SKIP SCAN;

When Oracle discovers that the number of unique values for a leading column is small, each unique value is used as the entry for a regular scan, on top of which a lookup is made and the query is finally merged;

For example:

Suppose the table EMP has a ename (employee name), Job (job name), sex (gender) three fields, and a composite index such as CREATE INDEX idx_emp on emp (sex, ename, job) is established;

Because the gender is only ' male ' and ' female ' two values, so in order to improve the index utilization, Oracle can be the composite index (' Male ', ename, Job), (' Female ', ename, job) These two composite indexes;

When querying the SELECT * from emp where job = ' Programmer ', the query is issued:

Oracle first enters sex as a ' male ' entry, when it uses the compound index (' Male ', ename, job) to find the entry for job = ' Programmer ';

Then enter the sex as ' female ' entrance, this time used to (' female ', ename, job) This compound index, find job = ' Programmer ' entry;

The result set from two portals that were finally merged into the query.

2.5 Processing of SQL statements

1. Finding SQL statements in a shared pool

2. Check grammar

3. Check semantics and related permissions

4. Merging (merge) view definitions and subqueries

5. Determine the execution plan

binding (BIND) :

1. Find the binding variable in the statement

2. Assigning (or re-assigning) values

Execution (EXECUTE) :

1. Application Execution Plan

2. Perform the necessary I/O and sort operations

Extract (FETCH) :

1. Returning records from query results

2. Sort as necessary

3. Using the array fetch mechanism

Shared Cursors: Benefits

1. Reduced parsing

2. Dynamic memory Adjustment

3. Increase Memory utilization

2.5.1 SQL Sharing principle

Oracle puts the SQL statements in the execution process into a shared pool of memory that can be shared by all database users, and when an SQL statement is executed, Oracle quickly gets the parsed statement and the best execution path if it is identical to the previous SQL execution statement.

This system is a global area, but Oracle provides caching only for simple tables, and if it is a multi-table connection query, the database administrator must set the appropriate parameters for the zone in the startup parameters file to increase the likelihood of sharing.

2.5.2 Conditions for SQL sharing (considerations)

1. The execution statement must be identical to the shared pool statement, including (case, space, line wrapping, etc.).

2. The two statement must have exactly the same object.

3. Two SQL statement binding variables must have the same name.

Example: Character-level comparisons

SELECT * from Ur_user_info

Select * from Ur_user_info

Example: the same binding variable name

Select Pay_fee,pay_method from Bal_payment_info where pay_sn=: PAY_SN;

Select Pay_fee,pay_method from Bal_payment_info where pay_sn=: pay_no;

Binding variables are different and cannot be shared.

2.5.3 Shared SQL Zone

2.5.4 SQL parsing and shared SQL statements

When an Oracle instance receives a SQL

1, create a cursor

2. Parse the Statement analysis statement

3. Describe Results of a query describes the result set

4. Define output of a query defines the input data of the query

5. Bind any Variables bound variable

6. Parallelize The Statement parallel execution statement

7. Run the Statement statement

8, fetch rows of a query to fetch the row

9. Close the cursor off cursors

2.6 Binding Variable 2.6.1 recompilation problem

For example:

Select *from ur_user_info where contract_no = 32013484095139

Each time the following statement is executed, a share POOL is required to parse a

Times, 1 million users is 1 million times, consumes CPU and memory, if the business

A large amount, which is likely to lead to downtime ...

If you bind a variable, you only need to parse it hard once, and repeat the call to

2.6.2 binding variables to resolve recompilation problems

For example:

Select *from ur_user_info where contract_no = 32013484095139

Select *from ur_user_info where contract_no = 12013481213149

Using bound variables

Select *from ur_user_info where Contract_no =:contract_no

2.6.3 Binding Variable Considerations

A, do not use the database-level variable binding parameter cursor_sharing to strong

Whether the value is force or similar

b, some with > < statements bound variables may cause the optimizer not to correctly

Working with Indexes

2.5 Principles and Considerations for SQL optimization

• Goal:

(1). general principles of SQL optimization design aspects:

• Design aspects:

(1). Rely on the Oracle optimizer and provide the conditions for it;

(2). The appropriate index, the double effect of the index, the selectivity of the column;

• Coding aspects:

(1). Use the index to avoid large full table SCAN;

(2). Reasonable use of temporary tables;

(3). Avoid writing overly complex SQL, not necessarily a SQL to solve the problem;

(4). Reduce the granularity of the transaction without impacting the business;

2.5.1 is null and is not NULL

Any SQL statement that has been added after the where statement is null or is not NULL, the Oracl optimizer will no longer use the index.

2.5.2 using a statement with a wildcard character (%)

List two examples to illustrate the problem:

Query the service number for Phone_no with 10 in the Ur_user_info table

Example 1:select *from ur_user_info where phone_no like '%10% ';

Example 2:select *from ur_user_info where phone_no like ' 10% ';

Because the wildcard character (%) In Example 1 appears at the beginning of the search term, the Oracle system does not use the index of the Phone_no, and the wildcard character reduces the efficiency of the query, but when the wildcard does not appear first, the index can be used, as shown in Example 2.

Three Oracle Statement Optimization Rules

3.1 Select the most efficient table name order

Example: TAB1 1000 Records, TAB2 1 records

Select the minimum record as the base table

Select Count (*) from TAB1,TAB2;

If you have 3 or more than 3 tables, select the crosstab as the base table

3.2 The connection order in the WHERE clause

The parsing of Oracle is parsed from top to bottom, so the connection between tables must be written before the Where condition:

For example:

Low efficiency:

Select: From

EMP E

Where Sal > 50000 and job = ' manager '

< (select count (*) from EMP where mgr=e.empno);

High efficiency:

Select: From

EMP E

where < (select count (*) from EMP where mgr=e.empno)

and Sal > 50000

and job = ' manager ';

3.3 Use of the wildcard ' * '

When SQL executes a statement with wildcard characters, if '% ' is in the first place, then the primary key or index established on the field will be invalidated!

The appearance of similar statements should be avoided

Select name from User_info where name= '%A ';

3.4 Using truncate instead of delete

When deleting a table, use the delete operation, the rollback side is used to hold the recoverable information, when the transaction is not committed, the rollback of the transaction, the data will revert to the execution of the delete operation, and when used truncate is, the rollback side will not hold the recoverable information, reduce the resource calls.

3.5 replacing the HAVING clause with a WHERE clause

Avoid having a HAVING clause that filters the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead.

3.6 Reducing the query on a table

Low efficiency:

Select Tab_name from tables where tab_name = (select

Tab_name from tab_columns where Version = 604) and db_ver=

(select Db_ver from tab_columns where Version = 604)

Efficient:

Select Tab_name from tables where (tab_name,db_ver) =

(select Tab_name,db_ver) from Tab_columns where version =604)

3.7 Replace or with in

Low Efficiency :

Select: From location where loc_id = ten or loc_id = or loc_id = 30

Efficient :

Select: From location where loc_in in (10,20,30);

3.8 Deleting duplicate data

The most efficient way to delete duplicate records

Delete from Ur_user_info A

Where a.rowid> (select min (b.rowid)

From Ur_user_info b

Where B. uid=a. UID);

3.9 Avoid using resource-intensive operations

SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform the resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting.

For example, a union query, where each query has a GROUP BY clause, and GROUP by triggers an embedded sort (NESTED sort); In this way, each query needs to be sorted once, and then when the Union is executed, another unique sort (sort unique) operation is performed and it can only begin execution after the previous embedded sort has ended. The depth of the embedded sort can greatly affect the efficiency of the query.

3.10 Auto-Select index

If there are more than two (including two) indexes in the table, there is a unique index, and the others are non-unique. In this case, the non-uniqueness index is completely ignored by Oracle using a unique index.

Example:

Select ename from emp where empno = 2326 and deptno = 20; Here, only the indexes on the empno are unique, so the empno index will be used to retrieve the records.

Table access by ROWID on EMP Index unique scan on EMP_NO_IDX;

3.11 to include at least the first column of the combined index

If the index is built on more than one column, the optimizer chooses to use the index only if its first column (leading column) is referenced by a WHERE clause. When referencing only the second column of an index, the optimizer uses a full table scan and ignores the index.

3.12 Avoiding the use of functions on indexed columns

Low efficiency:

Select:

From Dept

where Sal * > 25000;

Efficient :

Select:

From Dept

where Sal > 25000/12;

3.13 Avoid automatic conversion of indexed columns

Oracle automatically makes simple type conversions to columns when comparing data of different data types.

Suppose Emp_type is an indexed column of a character type.

Select User_no,user_name,address

From User_files

where user_no = 109204421

This statement is translated by Oracle to:

Select User_no,user_name,address

From User_files

where To_number (user_no) = 109204421 This index will not be used because of the type conversions that occur internally!

3.14 Avoid automatic conversion of indexed columns

For example:

where a.order_no = B.order_no

No:

where To_number (substr (A.order_no, InStr (B.order_no, '. ')-1)

= To_number (substr (A.order_no, InStr (B.order_no, '. ')-1)

3.15 using decode to reduce processing time

For example:

Select COUNT (*) sum (SAL)

From EMP

where dept_no = 0020

and ename like ' smith% ';

Select COUNT (*) sum (SAL)

From EMP

where dept_no = 0030

and ename like ' smith% ';

You can use the Decode function to get the same results efficiently.

Select COUNT (Decode (dept_no, 0020, ' x ', null)) D0020_count,

Count (Decode (dept_no, 0030, ' x ', null)) D0030_count,

SUM (Decode (dept_no, 0020, Sal, null)) D0020_sal,

SUM (Decode (dept_no, 0030, Sal, null)) D0030_sal

From EMP

where ename like ' smith% ';

3.16 Reducing the query on a table

Low efficiency

Select Tab_name

From tables

where Tab_name = (select Tab_name

From Tab_columns

where Version = 604)

and db_ver= (select Db_ver

From Tab_columns

where Version = 604)

Efficient

Select Tab_name

From tables

Where (Tab_name,db_ver)

= (select Tab_name,db_ver)

From Tab_columns

where Version = 604)

3.17 Order by statement

(a). The order BY statement determines how Oracle will sort the returned query results. The ORDER BY statement has no special restrictions on the columns to be sorted, or it can be added to a column (like joins or additions). Any non-indexed item in the ORDER BY statement, or a computed expression, will slow down the query.

(b). The ORDER BY statement to find non-indexed items or expressions that degrade performance. The solution to this problem is to rewrite the order BY statement to use the index, or you can establish another index for the column you are using, and you should absolutely avoid using an expression in the ORDER BY clause.

3.18 Improve efficiency with indexes

An index is a conceptual part of a table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure. In general, querying data through an index is faster than a full table scan. When Oracle finds the best path to execute queries and UPDATE statements, the Oracle Optimizer uses the index. Also, using indexes when joining multiple tables can improve efficiency. Another advantage of using an index is that it provides uniqueness validation of the primary key (primary key). In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index.

3.19 Avoid using calculations on indexed columns

Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index.

Low efficiency:

SELECT ... From DEPT WHERE SAL * > 25000;

Efficient:

SELECT ... From DEPT WHERE SAL > 25000/12;

3.20 Replace > with >=

If there is an index on the deptno.

Efficient:

SELECT *

From EMP

WHERE DEPTNO >=4

Low efficiency:

SELECT *

From EMP

WHERE DEPTNO >3

3.21 Avoid using the not command by using >=, <=, etc.

Example:

SELECT * FROM employee where salary <> 3000;

For this query, it can be rewritten to not use not:

SELECT * FROM employee where salary<3000 or salary>3000;

Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second query allows Oracle to use indexes on salary columns, while the first query cannot use indexes.

3.22 character-Type field quotation marks

For example, some table phone_no fields are char, and they are created with indexes.

However, if you forget to quote in the Where condition, the index is not used.

WHERE phone_no= ' 13920202022 '

WHERE phone_no=13920202022

Four Optimization Summary

A. When creating a table. Should try to establish the primary key, as far as possible according to the actual need to adjust the data table pctfree and pctused parameters; Big Data table Delete, use TRUNCATE table instead of delete.

B. Proper use of indexes does not have too many indexes on one table in an OLTP application. Columns with large data repetition do not create a two-tree index, you can use a bitmap index, and the column order of the combined index is consistent with the order of the query criteria columns, and for tables with frequent data operations, indexes need to be rebuilt periodically to reduce failed indexes and fragmentation.

C. Query as far as possible with the identified column name, less use the * number.

Select COUNT (key) from tab where key> 0 performance is better than select COUNT (*) from Tab;

D. As few nested subqueries as possible, this query consumes a lot of CPU resources; for queries with more or operations, it is recommended that you divide the query into multiple queries, join together with union ALL, and select the most efficient table name order in the query statement for the Multi-table query. The Oracle parser parses the table from right to left, so the table with fewer records is placed on the right.

E. Commit the transaction as much as possible, can release resources in time, unlock, free log space, reduce management costs, in frequent, high performance requirements of data operations, to avoid remote access, such as database chain, access to frequent tables can be resident memory: ALTER TABLE ... Cache

F. Execute SQL dynamically in Oracle, as far as possible, using Execute mode without Dbms_sql package.

Reference documents

Oracle SQL Statement Optimization (black_snail)

Oracle-based typical case analysis of SQL optimization 2013dbsnake @dbsnake

Oracle-based SQL optimization