Oracle Optimization Methods and Techniques

Oracle Optimization Methods and Techniques
1. Status Quo description

Long SQL Execution time and slow Data Query

2. Problem object

SQL statement execution (especially multi-Table multi-condition association query data)

3. Theoretical knowledge

1. Oracle optimizer

Oracle optimizer: The optimizer in the Oracle database is also called QueryOptimizer ). It is an optimization tool for SQL analysis and execution. It is responsible for generating and developing SQL Execution plans.

Oracle optimizer Optimization Method

Rule-based optimization (Rule-BasedOptimization, or RBO for short)

It selects the execution plan for the specified table based on the specified rule sequence. It has a set of strict rules for use, as long as you follow it to write SQL statements, regardless of the data table

The content does not affect your "Execution Plan", that is, RB is not "sensitive" to Data ". Developers are required to understand the RBO rules. In ORACLE

10 Gb is completely replaced by CBO.

Cost-Based Optimization (CBO ).

CBO is a more reasonable and reliable optimizer than RBO. It was introduced from ORACLE 8 and completely replaced by RBO in ORACLE10g. CBO is used to calculate various possibilities of "Execution

The "COST" of the plan, that is, the COST, selects the lowest COST execution scheme as the actual running scheme. It depends on the statistical information of the database object. Whether the statistical information is accurate or not affects C

BO makes the best choice. If you find that the objects (tables, indexes, and so on) involved in an SQL statement are not analyzed or counted, ORACLE uses a dynamic sampling technique,

Dynamically collect some data information on tables and indexes.

2. Oracle Index

Oracle index is a database structure that allows the server to quickly find a row in a table. The rational use of indexes can greatly improve the database operation efficiency.

In Oracle, an index is a database structure that allows the server to quickly search for a row in a table. Creating an index in a database has the following functions.

(1) quick access to data.

(2) It can improve the database performance and ensure the uniqueness of column values.

(3) implement integrity of reference between tables

(4) when using the order by and group by clauses for data retrieval, using indexes can reduce the time for sorting and grouping.

3. Optimization Direction

A. Remove unnecessary full table scans for large tables

B. Remove unnecessary full table scans for large tables

C. cache full table scan for small tables

D. test the use of optimized indexes.

E. Test the optimized Connection Technology

F. minimize the Cost of the Execution Plan

4. Specific Optimization Methods

1. Optimization of query conditions (clauses after where)

To avoid full table scanning, you should consider creating an index on columns such as where and order by. Otherwise, full table scanning will be performed ..

Avoid null value determination on the field in the where clause; otherwise, the full table scan will be performed without using the index.

Avoid using it in the where clause! = Or <> operator. Otherwise, the full table scan will be performed without using the index.

Avoid using the or join condition. If some fields have an index and some do not have an index, the full table scan will be performed instead of using the index. We recommend that you use union all instead.

Use in and not in with caution. Otherwise, a full table scan is performed.

Replacing in with exists

The subquery result set is small, IN

Small appearance, large subquery table, with EXISTS

We recommend that you select which one to compare the execution plans of the two SQL statements.

Avoid performing expression operations on fields in the where clause whenever possible, which will cause the engine to discard the use of indexes for full table scanning. For example:

Select id from t where num/2 = 100

Optimized:

Select id from t where num = 100*2

Do not perform function functions, arithmetic operations, or other expression operations on fields in the where clause. Otherwise, the full table scan will be performed without using indexes. For example:

-- NAME: ID starting with ABC

Select id from t where substring (NAME, 1, 3) = 'abc'

-- 2005-11-30 'generated id

Select id from t where datediff (DAY, CREATEDATE, '2017-11-30') = 0

Should be changed:

Select id from t where name like 'abc %'

Select id from t where createdate> = '2014-11-30 'and createdate <'2014-12-1'

2. Optimize the results

Update statement. If only one or two fields are modified, do not Update all fields. Otherwise, frequent calls may cause significant performance consumption and a large number of logs.

For JOIN operations on tables with large data volumes (hundreds of rows are larger), you must perform the JOIN operation by page. Otherwise, the logical read operation will be high and the performance will be poor.

Select count (*) from table; this way, the count without any conditions will cause a full table scan without any business significance, so it must be eliminated.

Avoid returning large data volumes to the client. If the data volume is too large, consider whether to use paging.

3. Other Optimizations

The more indexes, the better. Although the index can improve the efficiency of the select statement, it also reduces the efficiency of insert and update.

The index may be rebuilt during update. Therefore, you need to carefully consider how to create an index, depending on the actual situation. It is recommended that the number of indexes in a table be no more than 6. If there are too many indexes, you should consider some infrequently used indexes.

Is it necessary to create an index on a column.

Avoid updating clustered index data columns whenever possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the entire table record

Adjusting the order will consume a considerable amount of resources. If the application system needs to frequently update the SET index data column, consider whether to create the index as a clustered index.

Use numeric fields whenever possible. If fields containing only numerical information are not designed as numeric fields, this will reduce query and connection performance and increase storage overhead. This is because the engine is processing queries.

And the connection will compare each character in the string one by one, and only one comparison is required for the number type.

Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index ).

Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. Temporary tables are not unavailable. Using them appropriately can make some routines more effective. For example, when you need to repeat

A dataset in a large or common table. However, it is best to use the export table for one-time events.

When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid a large number

Log to improve the speed; if the data volume is small, in order to ease the system table resources, first create table and then insert.

If a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First, truncate the table and then drop the table

In this way, the system table can be locked for a long time.

Avoid using a cursor as much as possible because the efficiency of the cursor is poor. If the cursor operation has more than 10 thousand rows of data, you should consider rewriting.

Before using the cursor-based or temporary table method, you should first find a set-based solution to solve the problem. The set-based method is generally more effective.

Like a temporary table, a cursor is not unavailable. Using a FAST_FORWARD cursor for a small dataset is usually better than other row-by-row processing methods, especially when several tables must be referenced to obtain

Required data. A routine that includes "sum" in the result set is usually faster than a cursor. If

You can try the cursor-based method and set-based method to see which method is better.

Set nocount on at the beginning of all stored procedures and triggers, and set nocount off at the end

. You do not need to send the DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger.

Avoid large transaction operations and improve system concurrency.