Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the methods of Oracle SQL performance optimization", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the methods of Oracle SQL performance optimization?"

1. SQL statement execution steps

Syntax analysis > semantic analysis > view transformation > expression transformation > select optimizer > select connection mode > select connection order > select search path of data > run execution Plan

two。 Choose a suitable Oracle optimizer

RULE (rule-based), COST (cost-based), CHOOSE (optional)

3. How to access Table

Full table scan

Full table scan is to access each record in the table sequentially. ORACLE optimizes the full table scan by reading multiple data blocks (database block) at a time.

Access tables through ROWID

ROWID contains the physical location information recorded in the table, and ORACLE uses the index to realize the relationship between the data and the physical location (ROWID) where the data is stored. Usually, the index provides a way to quickly access ROWID, so the performance of queries based on index columns can be improved.

4. Shared SQL statement

Oracle provides a mechanism for caching executed SQL statements. SQL statements that have been parsed and determined the execution path are stored in the shared pool of SGA.

Before Oracle executes a SQL statement, it looks for a buffered SQL statement from the SGA shared pool each time, and if so, executes the SQL statement directly.

The purpose of improving SGA execution performance can be achieved by appropriately resizing the Oracle shared pool.

5. Choose the most efficient order of table names

ORACLE's parser processes the table names in the FROM clause from right to left, so the table written at the end of the FROM clause (the underlying table driving table) is processed first.

When ORACLE processes multiple tables, it joins them by sorting and merging, and processes the FROM clause in right-to-left order. First, scan the first table (the last table in the FROM clause) and sort the records, then scan the second table (the penultimate table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.

Valid only in rule-based optimizers.

For example:

Table TAB1 16384 records

Table TAB2 1 record

/ * choose TAB2 as the base table (best method) * / SELECT COUNT (*) FROM TAB1,TAB2 / * execution time 0.96 seconds * / * choose TAB1 as the base table (poor method) * / SELECT COUNT (*) FROM TAB2,TAB1 / * execution time 26.09 seconds * /

If there are more than three table join queries, you need to select an intersection table as the underlying table, which refers to the table referenced by other tables.

/ * efficient SQL*/ SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

Will be more efficient than the following SQL

/ * inefficient SQL*/ SELECT * FROM EMP E, LOCATION L, CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000

6. Join order in Where clause

Oracle parses the WHERE clause in a bottom-up or right-to-left order. According to this principle, joins between tables must be written before other WHERE conditions, and conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

/ * inefficient, execution time 156.3 seconds * / SELECT Column1,Column2 FROM EMP EWHERE E.SAL > 50000 AND E.JOB = 'MANAGER' AND 25

< (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO) /*高效,执行时间10.6秒*/ SELECT Column1,Column2FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND E.SAL >

50000 AND E.JOB = 'MANAGER'

7. Avoid using "*" in the SELECT clause

When Oracle parses the SQL statement, the "*" will be converted to the corresponding column name by querying the database dictionary.

If you need to list all Column in the Select clause, it is recommended that you list all Column names instead of simply replacing them with "*", which can reduce more database query overhead.

8. Reduce the number of visits to the database

When each SQL statement is executed, ORACLE does a lot of work internally: parsing SQL statements > estimating index utilization > binding variables > reading data blocks, etc.

Thus, reducing the number of times you access the database can actually reduce the workload of ORACLE.

9. The whole simple unrelated database access

If you have a few simple database queries, you can integrate them into one query (even if there is no relationship between them) to reduce more than the database IO overhead.

Although the efficiency is improved by adopting this method, the readability of the program is greatly reduced, so it is still necessary to weigh the pros and cons.

10. Use Truncate instead of Delete

When recorded in the Delete table, Oracle saves the deletion information in the Rollback section for recovery. When Truncate deletes a record in a table, the deletion information is not saved and cannot be restored. Therefore, Truncate deletes records faster and consumes less resources than Delete.

When deleting records from a table, you should try to use Truncate instead of Delete if you do not need recovery.

Truncate can only be used to delete records from a full table.

11. Use COMMIT as much as possible

Whenever possible, use COMMIT in your program as much as possible, so that the performance of the program is improved and the requirements are reduced because of the resources released by COMMIT.

Resources released by COMMIT:

The information used to recover data on the rollback segment.

A lock acquired by a program statement

Space in redo log buffer

ORACLE is used to manage the internal expenses of the above three resources

twelve。 Calculate the number of records

It is generally believed that the second COUNT (1) method is the fastest without a primary key index. If there is only one column and no index COUNT (*) is faster, if there is an index column, of course, it is the fastest to use the index column COUNT (column).

13. Replace the Having clause with the Where clause

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

14. Reduce query operations on tables

In SQL statements that contain subqueries, you should pay attention to reducing query operations on tables.

/ * inefficient SQL*/ 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 SQL*/ SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

15. Use aliases for tables (Alias)

When joining multiple tables in a SQL statement, use the alias of the table and prefix it on each Column. In this way, you can reduce parsing time and reduce syntax errors caused by Column ambiguity.

Column ambiguity means that because different tables in SQL have the same Column name, when the Column appears in the SQL statement, the SQL parser cannot determine the attribution of the Column.

16. Replace IN with EXISTS

In many queries based on underlying tables, 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.

/ * inefficient SQL*/ SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB') / * efficient SQL*/ SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC =' MELB')

17. Replace NOT IN with NOT EXISTS

In a subquery, the NOT IN clause performs an internal sort and merge, and a full table traversal of the tables in the subquery, so it is very inefficient.

To avoid using NOT IN, you can rewrite it as an external connection (Outer Joins) or NOT EXISTS.

/ * inefficient SQL*/ SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A') / * efficient SQL*/ SELECT * FROM EMP E WHERE NOT EXISTS (SELECT 1 FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT='A')

18. Replace EXISTS with table join

Generally speaking, table join is more efficient than EXISTS.

/ * inefficient SQL*/ SELECT ENAME FROM EMP E WHERE EXISTS (SELECT 1 FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT ='A') / * efficient SQL*/ SELECT ENAME FROM DEPT D _ mai EMP E WHERE E.DEPT_NO = D.DEPT_NO AND D.DEPT_CAT ='A'

19. Replace DISTINCT with EXISTS

Avoid using DISTINCT in the SELECT clause when submitting a query that contains information about multiple tables, such as department tables and employee tables. You can generally consider replacing it with EXIST.

EXISTS makes the query faster because the RDBMS core module will return the results as soon as the conditions of the subquery are met.

/ * inefficient SQL*/ SELECT DISTINCT D.DEPTCHENTNAME FROM DEPT D, EMPE WHERE D.DEPT_NO = E.DEPTPRONAME * efficient SQL*/ SELECT D.DEPTPRONAME FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

20. Identify inefficient SQL statements

The following SQL tool can find inefficient SQL, provided that DBA permission is required, otherwise it cannot be queried.

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 另外也可以使用SQL Trace工具来收集正在执行的SQL的性能状态数据，包括解析次数，执行次数，CPU使用时间等 。 21. 用Explain Plan分析SQL语句 EXPLAIN PLAN 是一个很好的分析SQL语句的工具, 它甚至可以在不执行SQL的情况下分析语句. 通过分析, 我们就可以知道ORACLE是怎么样连接表, 使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。 22. SQL PLUS的TRACE SQL>

List SELECT * FROM dept, emp WHERE emp.deptno = dept.deptno SQL > set autotrace traceonly / * traceonly may not display the execution result * / SQL > / rows selected. Execution Plan-SELECT STATEMENT Optimizer=CHOOSE 0 NESTED LOOPS 1 TABLE ACCESS (FULL) OF 'EMP' 1 TABLE ACCESS (BY INDEX ROWID) OF' DEPT' 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

23. Use index to improve efficiency

(1) characteristics

Advantages: improve efficiency and uniqueness verification of primary keys

Cost: space storage is required for regular maintenance

Refactoring the index:

ALTER INDEX REBUILD

(2) Oracle has two access modes to the index.

Index unique scan (Index Unique Scan)

Index range scan (Index Range Scan)

(3) selection of basic table

The underlying table (Driving Table) is the table that is accessed first (usually accessed in the form of a full table scan). Depending on the optimizer, the choice of the underlying table in the SQL statement is different.

If you are using CBO (COST BASED OPTIMIZER), the optimizer checks the physical size of each table in the SQL statement, the state of the index, and then chooses the least expensive execution path.

If you use RBO (RULE BASED OPTIMIZER) and all join conditions have index correspondence, in this case, the underlying table is the last table listed in the FROM clause.

(4) multiple equal indexes

When the execution path of the SQL statement can use multiple indexes distributed on multiple tables, ORACLE uses multiple indexes simultaneously and merges their records at run time to retrieve records that are valid only for all indexes.

When ORACLE chooses the execution path, the uniqueness index is higher than the non-uniqueness index. However, this rule is valid only when index columns are compared to constants in the WHERE clause. If the index column is compared to the index class of other tables. This clause has a very low level in the optimizer.

If two indexes of the same level in different tables are referenced, the order of the tables in the FROM clause determines which will be used first. The index of the last table in the FROM clause will have the highest priority.

If two indexes of the same level in the same table will be referenced, the index that is referenced first in the WHERE clause will have the highest priority.

(5) equation comparison takes precedence over range comparison.

There is a non-unique index on DEPTNO and a non-unique index on EMP_CAT.

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

Only the EMP_CAT index is used here, and then all records are compared against the DEPTNO condition one by one. The execution path is as follows:

TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON CAT_IDX

Even if the unique index is compared with the range, its priority is lower than the equality comparison of the non-unique index.

(6) ambiguous index level

When ORACLE cannot determine the difference between the levels of an index, the optimizer will use only one index, which is listed first in the WHERE clause.

There is a non-unique index on DEPTNO and a non-unique index on EMP_CAT.

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

Here, ORACLE uses only the DEPT_NO index. The execution path is as follows:

TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX

(7) forced index invalidation

If two or more indexes have the same level, you can force the ORACLE optimizer to use one of them (through it, a small number of records are retrieved).

SELECT ENAME FROM EMP WHERE EMPNO = 7935 AND DEPTNO + 0 = 10 / * the index on DEPTNO will be invalidated * / AND EMP_TYPE | |'='A' / * the index on EMP_TYPE will fail * /

(8) avoid using calculations on index columns

The WHERE clause, if the index column is part of the function. The optimizer will use full table scans instead of indexes.

/ * inefficient SQL*/ SELECT * FROM DEPT WHERE SAL * 12 > 25000 SQL*/ SELECT * FROM DEPT WHERE SAL > 25000 Placement

(9) automatically select index

If there are more than two (including two) indexes in the table, one of them is unique and the other is non-unique. In this case, ORACLE will use the unique index and completely ignore the non-unique index.

SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20

Here, only the index on EMPNO is unique, so the EMPNO index will be used to retrieve records.

TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX

(10) avoid using NOT on index columns

In general, we want to avoid using NOT,NOT on index columns has the same effect as using functions on index columns. When ORACLE encounters NOT, it stops using indexes and performs a full table scan.

/ * inefficient SQL: (here, no index is used) * / SELECT * FROM DEPT WHERE NOT DEPT_CODE = 0 SELECT * efficient SQL: (here, index is used) * / index * FROM DEPT WHERE DEPT_CODE > 0

24. Replace > with > =

If there is an index on DEPTNO

/ * efficient SQL*/ SELECT * FROM EMP WHERE DEPTNO > = 4max * inefficient SQL*/ SELECT * FROM EMP WHERE DEPTNO > 3

The difference between the two is that the former DBMS will jump directly to the first record with DEPT equal to 4, while the latter will first locate the record with DEPTNO equal to 3 and scan forward to the first record with DEPT greater than 3.

25. Replace OR with Union (for index columns)

In general, replacing OR in the WHERE clause with UNION will have a better effect. Using OR on an index column will result in a full table scan. Note that the above rules are valid only for multiple index columns.

/ * efficient SQL*/ SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 UNIONS ELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION = 'MELBOURNE'/* inefficient SQL*/ SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION =' MELBOURNE'

twenty-six。 Replace OR with IN

/ * inefficient SQL*/ SELECT * FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 max * efficient SQL*/ SELECT * FROM LOCATION WHERE LOC_IN IN (10mai 20jue 30)

The actual implementation effect also needs to be tested, in ORACLE8i, the two execution paths seem to be the same.

twenty-seven。 Avoid using is null and is not null on index columns

Avoid using any nullable columns in the index, and ORACLE will not be able to use the index.

/ * inefficient SQL: (index invalidation) * / SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;/* efficient SQL: (index valid) * / SELECT * FROM DEPARTMENT WHERE DEPT_CODE > = 0

twenty-eight。 Always use the first column of the index

If the index is built on multiple columns, the optimizer chooses to use the index only if its first column (leading column) is referenced by the where clause.

SQL > create index multindex on multiindexusage (inda,indb); Index created. SQL > select * from multiindexusage where indb = 1; Execution Plan-SELECT STATEMENT Optimizer=CHOOSE 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'

Obviously, when referencing only the second column of the index, the optimizer uses a full table scan and ignores the index.

twenty-nine。 Use UNION ALL instead of UNION

When the SQL statement requires two query result sets to UNION, the two result sets are merged as UNION-ALL, and then sorted before the final result is output. If you replace UNION with UNION ALL, sorting is not necessary and efficiency will be improved as a result.

Since the results of UNION ALL are not sorted and duplicate records are not filtered, whether or not to replace them depends on business requirements.

thirty。 Optimization of UNION

Because UNION sorts query results and filters duplicate records, its execution efficiency is not as efficient as UNION ALL. UNION operations use SORT_AREA_SIZE memory blocks, so it is also important to optimize this memory.

You can use the following SQL to query the sorted consumption:

Select substr (name,1,25) "Sort Area Name", substr (value,1,15) "Value" from v$sysstat where name like 'sort%'

thirty-one。 Avoid changing the type of index column

ORACLE automatically performs simple type conversions on columns when comparing data of different data types.

/ * suppose EMP_TYPE is an index column of character type. * / SELECT * FROM EMP WHERE EMP_TYPE = 123 / * this statement is converted by ORACLE to: * / SELECT * FROM EMP WHERE TO_NUMBER (EMP_TYPE) = 123

This index will not be used because of the internal type conversion.

Here are a few points to note:

ORACLE automatically performs simple type conversions on columns when comparing data of different data types.

If an implicit type conversion is performed on an index column, the index will not be used when querying.

Note that when comparing characters with numeric values, ORACLE gives priority to converting numeric types to character types.

In order to prevent ORACLE from implicitly casting SQL, it is best to express the conversion explicitly.

thirty-two。 Use hints (Hints)

FULL hint tells ORACLE to access the specified table using a full table scan.

ROWID hint tells ORACLE to access the table using TABLE ACCESS BY ROWID's operation.

CACHE hint tells the optimizer to keep the query result data in the SGA.

INDEX Hint tells ORACLE to use index-based scanning.

Other Oracle Hints

ALL_ROWS

FIRST_ROWS

RULE

USE_NL

USE_MERGE

USE_HASH, wait.

This is a very skillful job. It is recommended to optimize hint only for a specific, small number of SQL.

thirty-three。 Several WHERE clauses that cannot use indexes

(1) in the following example,'! = 'will not use an index, which can only tell you what exists in the table, not what does not exist in the table.

/ * do not use index * / SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT! = 0 / SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT * use index * / index > 0

(2) in the following example,'| |'is a character concatenation function. Like other functions, the index is disabled.

/ * do not use index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME | | ACCOUNT_TYPE='AMEXA';/* uses index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = 'AMEX' AND ACCOUNT_TYPE='A'

(3) in the following example,'+'is a mathematical function. Like other mathematical functions, the index is disabled.

/ * do not use index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 > 5000 * use index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE AMOUNT > 2000

(4) in the following example, the same index columns cannot be compared to each other, which will enable full table scanning.

/ * do not use index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = NVL (: ACC_NAME, ACCOUNT_NAME) / * use index * / SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME LIKE NVL (: ACC_NAME,'%')

thirty-four。 Connect multiple scans

If you compare a column with a limited set of values, the optimizer may perform multiple scans and merge the results.

For example:

SELECT * FROM LODGING WHERE MANAGER IN ('BILL GATES','KEN MULLER')

The optimizer may convert it to the following form:

SELECT * FROM LODGING WHERE MANAGER = 'BILL GATES' OR MANAGER =' KEN MULLER'

thirty-five。 Use a more selective index under CBO

The cost-based optimizer (CBO,Cost-Based Optimizer) judges the selectivity of the index to determine whether the use of the index can improve efficiency.

If you retrieve the number of records in a table with more than 30% of the data, there will be no significant efficiency gains in using indexes.

In certain cases, using an index may be slower than a full table scan. In general, using an index is several or even thousands of times more block than a full table scan!

thirty-six。

The SQL statement with DISTINCT,UNION,MINUS,INTERSECT,ORDER BY starts the SQL engine to perform the resource-consuming sort (SORT) function. DISTINCT requires one sort operation, while others need to perform at least two sort operations.

In general, SQL statements with UNION,MINUS,INTERSECT can be rewritten in other ways.

thirty-seven。 Optimize GROUP BY

To improve the efficiency of GROUP BY statements, you can filter out unwanted records before GROUP BY.

/ * inefficient SQL*/ SELECT JOB,AVG (SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT'' OR JOB =' MANAGER'/* efficient SQL*/ SELECT JOB,AVG (SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB =' MANAGER' GROUP BY JOB

thirty-eight。 Date of use

When using a date, it is important to note that if more than 5 decimal places are added to the date, the date will go to the next day!

SELECT TO_DATE ('01-JAN-93'+.99999) FROM DUAL result:' 01-JAN-93 23 FROM DUAL 59 SELECT TO_DATE ('01-JAN-93'+.999999) FROM DUAL result:' 02-JAN-93 00VOLAR 0000'

thirty-nine。 Use display cursors (CURSORS)

Using an implicit cursor, the operation will be performed twice. The first time to retrieve the record, the second time to check the exception of TOO MANY ROWS. The explicit cursor does not perform the second operation.

forty。 Separate tables and indexes

Always build your table and index in a different tablespace (TABLESPACES).

Never store objects that are not part of the ORACLE internal system in the SYSTEM tablespace.

Make sure that the data tablespace and index tablespace are placed on different hard drives.

At this point, I believe you have a deeper understanding of "what are the methods of Oracle SQL performance optimization?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.