Summary of oracle performance Optimization recommendations

  • 2020-08-22 22:59:12
  • OfStack

Principle 1: Note the join order in WHERE clauses:
ORACLE parses the WHERE clause in a bottom-up order. According to this principle, the joins between tables must be written before the other WHERE conditions, and those conditions that filter out the maximum number of records must be written at the end of the WHERE clause.
In particular, "Primary key ID=?" Conditions like this.

Principle 2: Avoid the use of '*' in SELECT clauses:
ORACLE converts '*' to all column names in turn during parsing, which is done by querying the data dictionary, which means more time.

In short, the shorter the statement execution time, the better (especially for the end user of the system). For query statements, due to the large amount of data read by full table scan, especially for large tables, the query speed is not only slow, but also causes great pressure on disk IO, which should be generally avoided, and the way to avoid is to use index Index.

The advantages and costs of using indexes.
Advantage:
1) index is a concept part of the table, used to improve the efficiency of the data is retrieved, ORACLE USES a complex self balancing B - tree structure. Usually, by index query data faster than a full table scan. When execute query and Update statement ORACLE find out the optimum path, ORACLE optimizer will use index. When multiple tables are in the same connection using the index can also improve efficiency.
2) Another advantage of using an index is that it provides one-of-a-kind verification of the primary key (primary key). For those LONG or LONG RAW data types, you can index almost all the columns. In general, using indexes is particularly effective in large tables. Of course, you will find that using indexes can also be more efficient when scanning small tables.
Price: while using the index can get query efficiency, but the price we also must pay attention to it. The index need space to store, also need regular maintenance, whenever there is a record in a table or index columns is modified, the increase or decrease the index itself will be modified. This means that each record INSERT, DELETE, UPDATE will pay more for it 4, 5 times of disk I/O. Because the index require additional storage and handling, the unnecessary index can make the query response time is slow. Instead. And the bigger the table, the more severe the impact.

Points to note when using indexes:

1. Avoid using NOT on index columns.
We want to avoid using NOT on index columns, NOT has the same effect as using functions on index columns. When ORACLE "encounters" NOT, it stops using the index and performs a full table scan.

2. Avoid using calculations on index columns.
WHERE clause, if the index column is part 1 of the function. The optimizer will use a full table scan instead of an index. For example:


 Inefficient. :SELECT  ...  FROM DEPT WHERE SAL * 12 > 25000; 
 efficient :SELECT  ...  FROM DEPT WHERE SAL > 25000/12;

3. Avoid using IS NULL and IS NOT NULL on index columns
Avoid using any column that can be empty in the index, which will not be used by ORACLE performance. For a single-column index, if the column contains a null value, the record does not exist in the index. For a composite index, if every column is null, the record does not exist in the index. If at least one column is not null, the record exists in the index. Example: if only one sex index build B A columns and columns in a table, and exists in the table 1 records A, B value of (123, null), ORACLE will not accept the one with the same A, B values (123, null) record (insert). However, if all the indexed column is empty, ORACLE will think the key value is empty and empty is not equal to empty. So you can be inserted into article 1000 records with the same key value, of course they are empty! Because a null value does not exist in the index column, a null comparison of the index column in the WHERE clause will cause ORACLE to deactivate the index.

 
 Inefficient. :( Index of the failure ) SELECT  ...  FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 
 efficient :( Index effectively ) SELECT  ...  FROM DEPARTMENT WHERE DEPT_CODE >=0; 


4. Note the effect of wildcard %
Oracle may deactivate the index if wildcards are used. Such as:
 
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE like  ' %123456%' (Invalid).  
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE =  ' 123456' (effective)  

5. Avoid changing the type of index column.
When comparing data of different data types, ORACLE automatically performs simple type conversions on the columns.
Suppose EMPNO is an indexed column of a numeric type. SELECT... FROM WHERE EMPNO = '123' in fact, after ORACLE conversion, the statement is converted to: SELECT... FROM WHERE EMPNO = TO_NUMBER(' 123') Fortunately, the cast did not take place on the index column and the purpose of the index did not change. Now assume that EMP_TYPE is an index column of type 1 characters. FROM EMP WHERE EMP_TYPE = 123 This statement is converted by ORACLE to: SELECT... FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 Because of an internal cast, this index will not be used! To avoid ORACLE casting your SQL implicitly, it is best to cast it explicitly. Note that when comparing characters to values, ORACLE converts numeric values to character types first

6. Some "tempers" in the Index
a. If the number of records in a table is retrieved from more than 30% of the data volume, there will be no significant efficiency gains using indexes.
In certain circumstances, using an index may be slower than a full table scan, but this is an order of magnitude different. In general, indexes are blocks or even thousands of times slower than a full table scan!

In addition to using indexes, we have other ways to reduce resource consumption:

1. Replace DISTINCT with EXISTS:
When submitting a query that contains 1 pair of multi-table information (such as department table and employee table), EXIST can be considered instead of DISTINCT1 in the SELECT clause. EXISTS makes the query faster because the RDBMS core module will return the results as soon as condition 1 of the subquery is met.
Example:
 
( Inefficient. ): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E 
WHERE D.DEPT_NO = E.DEPT_NO 
And E.sex =man 
( efficient ): SELECT DEPT_NO,DEPT_NAME FROM DEPT D 
WHERE EXISTS 
( SELECT  ' X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO 
And E.sex =man 
); 

2. Replace OR (for indexed columns) with (UNION)UNION ALL
In general, replacing OR in the WHERE clause with UNION will work better. Using OR for index columns will result in a full table scan.
Note that the above rule only applies to multiple indexed columns. If column is not indexed, the query efficiency may be reduced because you did not select OR. In the following example, both LOC_ID and REGION are indexed.
If you insist on using OR, you will need to return the index column with the fewest records written at the top.
 
 efficient : SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL 
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION =  " MELBOURNE "  
 Inefficient. : SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION =  " MELBOURNE "  

3. Replace UNION with UNION-ES173en (if possible) :
When the SQL statement requires two query result sets from UNION, the two result sets are combined as ES178en-ES179en and sorted before the final result is printed out. If UNION ALL is used instead of UNION, sorting is not necessary. The efficiency is thus improved. UNION ALL will repeatedly print the same record in both result sets. Therefore, you will need to consider the feasibility of using UNION ALL for business requirements analysis. UNION will sort the result set and this operation will use the memory of SORT_AREA_SIZE. Optimization of this memory is also very important.
4. Order By statement is added to the index column, preferably on the primary key PK.
 
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE (low)  
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE  (efficient)  

5. Avoid resource-consuming operations:
With DISTINCT UNION MINUS, INTERSECT SQL statements will start SQL engine perform the function of expensive sort (SORT). DISTINCT need one sort operations, and need to be performed at least twice of the other sort. Usually, with UNION, MINUS, INTERSECT's SQL statements can be rewritten in other ways. If your database's SORT_AREA_SIZE is well formulated, use UNION, MINUS, INTERSECT as they are very readable

6. Use Where instead of Having (if possible)
Optimize GROUP BY:
Improve 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.
 
 Inefficient. : 
SELECT JOB , AVG(SAL) 
FROM EMP GROUP JOB HAVING JOB =  ' PRESIDENT'AND AVG(SAL)>XXX 
 efficient : 
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB =  ' PRESIDENT' 
OR JOB =  ' MANAGER' GROUP JOB Having AND AVG(SAL)>XXX 

7. In general, if the statement can avoid the use of subqueries, try not to use subqueries. Because the overhead of subqueries is quite expensive. Specific examples are shown in the following case "1 SQL optimization process".
If you have any other tips on improving the performance of Oracle, you can share them in the forums on the site.


Related articles: