Optimization Scenarios in Oracle Database

From: http://woainichenxueming.iteye.com/blog/726541

I. Optimizing SQL statements in Oracle to improve operational efficiency

1. Select the most efficient table name order (valid only in the rule-based optimizer)

The Oracle parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the final table (the underlying table, driving tables) will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the fewest number of record bars as the underlying table.

such as: Table T_user has 16,384 records, table T_department has a record

Select T_department as the base table (the best method)

SQL statement: SELECT COUNT (user_id) from T_user, t_department; Execute 0.96 seconds

Choose T_user as the base table (the poor method [this place or choose T_department as the base table!])

SQL statement: SELECT COUNT (user_id) from T_department, T_user; Execute 26.09 seconds

The above is part of the Oracle SQL Performance Tuning series, but in practice it is found that a table with large data volumes is much faster than the one on the far right of the FROM clause, as opposed to the description in the article, and does not know what the cause is???

Related connections for this issue:

Http://www.oracle.com.cn/redirect.php?fid=77&tid=137705&goto=nextnewset

If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.
　　
2. Connection order in the WHERE clause

Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.
　　
3. Avoid using (*) in the SELECT clause

In the process of parsing, Oracle converts ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent
　　
4. Reduce the number of Access databases

Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading blocks, etc.
　　
5. Reset the ArraySize parameter in Sql*plus, sql*forms and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200
　　
6. Use the Decode function to reduce processing time

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.
　　
7. Integrated simple, no associated database access

If you have a few simple database query statements, you can integrate them into a single query (even if they are not related)
　　
8. Delete duplicate records

The most efficient method of deleting duplicate records (because of the use of rowID) Example:

DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from emp X where x.emp_no = E.emp_no);
　　
9. Replace Delete with truncate

When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not have a COMMIT transaction, Oracle restores the data to the state it was before it was deleted (exactly before the delete command was executed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command runs, The data cannot be restored. So very few resources are invoked and execution times are short. (Translator Press: truncate only in Delete full table applies, truncate is DDL is not DML)
　　
10. Use commit as much as possible

Whenever possible, use commit as many of the programs as possible, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit:

Resources Freed by Commit:

A. Information for recovering data on a rollback segment.
B. Locks acquired by program statements
C. Redo space in the log buffer
D. Oracle manages internal spending on 3 of these resources
　　
11. Replace 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. (Non-Oracle) on, where, have the three clauses that can be added conditionally, on is the first execution, where the second, having the last, because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to process the data, It should be said that the speed is the fastest, where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared. In the case of this single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they will be the same result, but where you can use the Rushmore technology, and have not, at the speed of the latter slow if you want to relate to the calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated after the function, so in this case, the results will be different. On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put it there
　　
12. Reduce the query on the table

In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table.

Example: Select Tab_name from TABLES where (tab_name,db_ver) = (select Tab_name,db_ver from tab_columns where VERSION = 604)
　　
13. Improve SQL efficiency with intrinsic functions

Complex SQL often sacrifices execution efficiency. The ability to master the above application function to solve the problem is very meaningful in practical work.
　　
14. Aliases for using tables (alias)

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.
　　
15. Replace in with exists instead of not exists instead of in

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.

Example:

Efficient: SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')

Inefficient: SELECT * from EMP (base table) where EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT WHERE LOC = ' Melb ')
　　
16. Identify the SQL statement for ' inefficient execution '

Although there are many graphical tools for SQL optimization, it is always a good idea to write your own SQL tools to solve the problem:

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;
　　
17. 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). Those long or long raw data types, you can index almost all the columns. 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 4-5 more disk I/O. Because the index requires additional storage space and processing, those unnecessary indexes can slow down the query response time. It is necessary to periodically refactor the index

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
　　
18. Replace distinct with exists

Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. It is generally possible to consider replacing with exist, EXISTS makes the query faster because the RDBMS core module will return the results immediately after the conditions of the subquery have been met. 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);
　　
The SQL statement is capitalized because Oracle always parses the SQL statement first, converting the lowercase letters to uppercase and then executing
　　
20. Minimize the use of the connector "+" connection string in Java code (which connection is used, is it stringbuffer?)!
　　
21. Avoid using not on indexed columns

We want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.
　　
22. 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.

Example:

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

Efficient: SELECT ... From DEPT WHERE SAL > 25000/12;
　　
23. Replacing > with >=

Efficient: SELECT * from EMP WHERE DEPTNO >=4

Inefficient: SELECT * from EMP WHERE DEPTNO >3

The difference between the two is that the former DBMS will jump directly to the first record that dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first record with a dept greater than 3.
　　
24. Replace or with union (for indexed columns)

In general, replacing or in a WHERE clause with Union will have a good effect. Using or on an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, indexes are built on both loc_id and region.
Efficient:
SELECT loc_id, Loc_desc, Region
From location
WHERE loc_id = 10
UNION
SELECT loc_id, Loc_desc, Region
From location
WHERE region = "MELBOURNE"
Low efficiency:
SELECT loc_id, Loc_desc, Region
From location
WHERE loc_id = ten OR region = "MELBOURNE"
If you persist in using or, you need to return the least logged index column to the front.
　　
25. Replace or with in

This is a simple and easy-to-remember rule, but the actual execution will have to be tested, and under Oracle8i, the execution path seems to be the same

Inefficient: 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);
　　
26. Avoid using is null and is not NULL on indexed columns

To avoid using any nullable columns in the index, Oracle will not be able to use the index. For single-column indexes, this record will not exist in the index if the column contains null values.　For composite indexes, if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index. For example, if a uniqueness index is established on column A and column B of a table, and the table has a value of a, a and a record of (123,null), Oracle will not accept the next record (insert) with the same A, B value (123,null). However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL. So you can insert 1000 records with the same key value, of course they are empty! Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index.
Inefficient: (Index invalidation)
SELECT ... From DEPARTMENT WHERE dept_code are not NULL;
Efficient: (Index valid)
SELECT ... From DEPARTMENT WHERE Dept_code >=0;
　　
27. Always use the first column of an 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. This is also a simple and important rule, when referencing only the second column of an index, the optimizer uses a full table scan and ignores the index
　　
28. Replace union with Union-all (if possible)

When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will therefore be improved. It is important to note that the UNION all will output the same record in the two result set repeatedly. So you still have to analyze the feasibility of using union all from the business requirements. The Union will sort the result set, which will use the memory of the sort_area_size. The optimization of this memory is also very important. The following SQL can be used to query the consumption of sorts

Inefficient: SELECT acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 '
UNION
SELECT Acct_num, Balance_amt from debit_transactions WHERE tran_date = ' 31-dec-95 '

Efficient:
SELECT Acct_num, Balance_amt
From Debit_transactions
WHERE tran_date = ' 31-dec-95 '
UNION All
SELECT Acct_num, Balance_amt
From Debit_transactions
WHERE tran_date = ' 31-dec-95 '
　　
29. Where to replace order by
The ORDER by clause uses the index only under two strict conditions.
All columns in an order by must be in the same index and remain in the order in which they are arranged in the index.
All columns in the ORDER by must be defined as non-empty.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.
For example:
Table Dept contains the following:
Dept_code PK not NULL
Dept_desc not NULL
Dept_type NULL
Inefficient: (index not used)
SELECT Dept_code from DEPT ORDER by Dept_type
Efficient: (using index)
SELECT Dept_code from DEPT WHERE dept_type > 0
　　　
30. Avoid changing the type of indexed columns:
Oracle automatically makes simple type conversions to columns when comparing data of different data types.
Suppose Empno is an indexed column of a numeric type.
SELECT ... From EMP WHERE EMPNO = ' 123 '
In fact, after the Oracle type conversion, the statement translates to:
SELECT ... From EMP WHERE EMPNO = to_number (' 123 ')
Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.
Now, suppose Emp_type is an indexed column of a character type.
SELECT ... From EMP WHERE Emp_type = 123
This statement is translated by Oracle to:
SELECT ... From EMP Whereto_number (emp_type) =123
This index will not be used because of the type conversions that occur internally! To avoid the implicit type conversion of your SQL by Oracle, it is best to explicitly express the type conversions. Note When comparing characters to numbers, Oracle takes precedence over numeric types to character types
　　
31. The WHERE clause to be careful:
The WHERE clause in some SELECT statements does not use an index. Here are some examples.
In the following example,
(1) '! = ' will not use the index. Remember, the index can only tell you what exists in the table, not what does not exist in the table.
(2) ' | | ' is a character join function. As with other functions, the index is deactivated.
(3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated.
(4) The same index columns cannot be compared to each other, which will enable full table scanning.
　　
32
A. If the number of records in a table that has more than 30% data is retrieved. Using indexes will have no significant efficiency gains.
B. In certain situations, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, the use of indexes than the full table scan to block several times or even thousands of times!
　　
33. Avoid using resource-intensive operations:
SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine
Performs a resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database is well-sort_area_size, using union, minus, intersect can also be considered, after all, they are very readable
　　
34. Optimize GROUP BY:
Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is significantly faster.
Low efficiency:
SELECT JOB, AVG (SAL)
From EMP
GROUP JOB
Having JOB = ' president '
OR JOB = ' MANAGER '
Efficient:
SELECT JOB, AVG (SAL)
From EMP
WHERE JOB = ' President '
OR JOB = ' MANAGER '
GROUP JOB

Two. Oracle SQL Performance Tuning tips (for reference only)

1. Select the appropriate Oracle Optimizer

There are 3 types of Oracle Optimizer: 1, rule (based on rules); 2, cost (based on costs); 3. CHOOSE (optional)

The default optimizer can be set by various declarations of the Optimizer_mode parameter in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You can certainly overwrite it at the SQL sentence level or at the session level.

In order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of the object statistics (objects statistics) in the database.

If the optimizer mode of the database is set to selective (CHOOSE), then the actual optimizer mode will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer mode will automatically become the CBO, whereas the database will use the rule-form optimizer.

By default, Oracle uses the Choose Optimizer, and to avoid unnecessary full table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.

2. How to Access table

ORACLE uses two ways to access records in a table:

A, full table scan

A full table scan is the sequential access to each record in the table. Oracle optimizes full-table scanning in a way that reads multiple data blocks (database block).

B. Access the table through ROWID

You can use ROWID-based access to improve the efficiency of your Access tables, ROWID contain the physical location information recorded in the table. Oracle employs an index to achieve the connection between data and the physical location (ROWID) where the data resides. Usually the index provides a quick way to access rowid, so those queries based on indexed columns can get a performance boost.

3. Shared SQL statements

In order not to parse the same SQL statements repeatedly, Oracle stores the SQL statements in memory after the first resolution. The memory in the shared buffer pool, which is located in the system global area of the SGA, can be shared by all database users. So, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage.

Unfortunately, Oracle only provides caching (cache buffering) for simple tables, which does not apply to multi-table connection queries.

The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this block of memory. It is important to note that Oracle has a strict match for both, and the SQL statements must be identical (including spaces, line breaks, and so on) to achieve sharing.

The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

A shared statement must meet three conditions:

A, character-level comparison: The statements that are currently executed and the statements in the shared pool must be identical.

The objects that the B, two statements refer to must be exactly the same:

A binding variable (bind variables) of the same name must be used in C, two SQL statements.

4. Select the most efficient table name order (valid only in the rule-based optimizer)

The parser for Oracle processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (base table driving tables) will be processed first. In cases where the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and order the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records from the first table.

If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

5. Connection order in the WHERE clause

Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

6. Avoid using ' * ' in the SELECT clause

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

Optimization Scenarios in Oracle Database