Oracle SQL Performance Optimization Series

SQL tuning is indeed worth a lot of in-depth work, and SQL tuning is also a very difficult task. I would like to share a good article on the Internet to thank you! [Oracle uses the choose optimizer by default. To avoid unnecessary full table scan, you must avoid using the choose optimizer whenever possible, the optimizer based on rules or costs is directly used.] it is useful in many scenarios, such as update tablea_name set column_name = (select column_value from tableb_name where tableb_name.fid = tablea_name.freferenceid) Where tablea_name.fid in (........) if tablea_name has a large amount of data, the default choose execution will be terrible and very slow. If this SQL statement is changed to update/* Rule */tablea_name set column_name = (select column_val UE from tableb_name where tableb_name.fid = tablea_name.freferenceid) Where tablea_name.fid in (........) the speed improvement will be very obvious, even several seconds of execution completed... now I will present the full text. In fact, many well-known tuning methods won't achieve very obvious tuning in practical applications, however, it is very useful to understand the internal mechanism of Oracle SQL Execution.
1. select an appropriate Oracle optimizer

There are three optimizer types in Oracle:

A. Rule (Rule-based) B. Cost (cost-based) C. Choose (selectivity)

Set the default optimizer. various declarations of the optimizer_mode parameter in the ora file, such as rule, cost, choose, all_rows, first_rows. of course, you also overwrite SQL statements or sessions.

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

If the optimizer mode of the database is set to choose, the actual optimizer mode is related to whether the analyze command has been run. if the table has been analyze, the optimizer mode will automatically become a CBO. Otherwise, the database will adopt a rule optimizer.

By default, Oracle uses the choose optimizer. To avoid unnecessary full table scan, you must avoid using the choose optimizer, the optimizer based on rules or costs is directly used.

2. Access Table

Oracle uses two methods to access table records:

A. Full table Scan

Full table scan refers to sequential access to each record in the table. Oracle optimizes full table scan by reading multiple data blocks at a time.

B. Access the table through rowid

You can use rowid-based access to improve the efficiency of accessing tables. rowid contains the physical location information recorded in the table .. oracle uses indexes to establish a connection between data and the physical location where data is stored (rowid. generally, indexes provide a quick way to access rowid, so those queries based on index columns can improve the performance.

3. Share SQL statements

To avoid repeated parsing of the same SQL statement, Oracle stores the SQL statement in memory after the first parsing. the memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, oracle can quickly obtain parsed statements and the best execution path. this function greatly improves SQL Execution performance and saves memory usage.

Unfortunately, Oracle only provides high-speed buffer (Cache buffering) for simple tables. This function is not applicable to multi-table join queries.

The database administrator must set appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.

When you submit an SQL statement to Oracle, Oracle will first find the same statement in the memory.

It should be noted that Oracle adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ).

The shared statement must meet three conditions:

A. Character-level comparison:

The statements currently executed must be the same as those in the shared pool.

For example:

　　SELECT * FROM EMP;

Different from each of the following

　　SELECT * from EMP;

　　Select * From Emp;

　　SELECT * FROM EMP;

B. The objects referred to by the two statements must be identical:

For example:

How to access user object names

　　Jack sal_limit private synonym

　　Work_city public synonym

　　Plant_detail public synonym

　　Jill sal_limit private synonym

　　Work_city public synonym

　　Plant_detail table owner

Consider whether the following SQL statements can be shared between the two users.

Can SQL be shared?

select max(sal_cap) from sal_limit;

No. Each user has a private synonym-sal_limit, which is a different object.

　　select count(*0 from work_city where sdesc like 'NEW%';

Yes. The two users access the same object public synonym-work_city.

select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id

No. User Jack accesses plant_detail through private synonym, and Jill is the table owner, with different objects.

C. bind variables must be used in the two SQL statements)

For example:

The two SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even if different bind variables have the same value at runtime)

　　a.

　　select pin , name from people where pin = :blk1.pin;
　　select pin , name from people where pin = :blk1.pin;

　　b.

　　select pin , name from people where pin = :blk1.ot_ind;
　　select pin , name from people where pin = :blk1.ov_ind;
4. Select the most efficient table name sequence (only valid in the rule-based Optimizer)

The Oracle parser processes the table names in the from clause in the order from right to left. Therefore, the table written in the from clause (basic table driving table) will be processed first. when the from clause contains multiple tables, You must select the table with the least number of records as the base 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 sort the records, and then scan the second table (the last second table in the from clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

For example:

Table tab1 16,384 records

Table tab2 1 record

Select tab2 as the base table (the best method)

Select count (*) from tab1, tab2 execution time 0.96 seconds
Select tab2 as the base table (poor method)

Select count (*) from tab2, tab1 execution time 26.09 seconds
If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

For example:

The EMP table describes the intersection between the location table and the category table.

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
It will be more efficient than the following SQL statements

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
5. The connection sequence in the WHERE clause.

Oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other where conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

Example: (inefficient, execution time: 156.3 seconds)

SELECT …

FROM EMP E

WHERE SAL >50000

AND JOB = ‘MANAGER'

AND 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO);
(Efficient, execution time: 10.6 seconds)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL >50000

AND JOB = ‘MANAGER';
6. Avoid '*' in the select clause '*'

When you want to list all columns in the select clause, using dynamic SQL column reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, Oracle converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time.

7. Reduce the number of database accesses

When each SQL statement is executed, Oracle performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. it can be seen that reducing the number of visits to the database can actually reduce the workload of oracle.

For example, there are three ways to retrieve employees whose employee number is 0342 or 0291.

Method 1 (most inefficient)

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 291;
Method 2 (low efficiency)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

…..

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1;

END;
Method 3 (efficient)

SELECT A.EMP_NAME , A.SALARY , A.GRADE,

B.EMP_NAME , B.SALARY , B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;
Note:

You can reset the arraysize parameter in SQL * Plus, SQL * forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.
8. Use the decode function to reduce processing time

You can use the decode function to avoid repeated scan of the same record or join the same table.

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 efficiently get the same result.

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%';
Similarly, the decode function can also be used in the group by and order by clauses.

9. Simple Integration and unrelated Database Access

If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)

For example:

SELECT NAME FROM EMP
WHERE EMP_NO = 1234;

SELECT NAME FROM DPT
WHERE DPT_NO = 10 ;

SELECT NAME FROM CAT
WHERE CAT_TYPE = ‘RD';
The preceding three queries can be merged into one:

SELECT E.NAME , D.NAME , C.NAME FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD';
(Translator's note: although this method is adopted, the efficiency is improved, but the readability of the program is greatly reduced, so readers still need to weigh the advantages and disadvantages)

10. Delete duplicate records

The most efficient method for deleting duplicate records (because rowid is used)

DELETE FROM EMP E
WHERE E.ROWID >(SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
11. Replace Delete with truncate

When deleting records in a table, a rollback segment is usually used to store information that can be recovered. if you do not have a commit transaction, Oracle will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When you use truncate, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (Note: truncate is applicable only when the entire table is deleted. If truncate is DDL, It is not DML)

12. Try to use commit as much as possible

As long as possible, use commit as much as possible in the program, so that the program's performance is improved, and the demand will also be reduced by the resources released by commit:

Resources released by commit:

A. Information used to restore data on the rollback segment.

B. Locks obtained by Program Statements

C. Space in redo log Buffer

D. Oracle manages the internal costs of the above three types of resources

(Note: When Using commit, you must pay attention to the integrity of the transaction. In reality, efficiency and transaction integrity are often unable to meet the needs of both the fish and the bear's paw)

If decode is null, sum (null) is null --> if all values are null, sum (null) = NULL, but as long as one value is not null, sum () <> null. Therefore, the original SQL statement should have no logic problems.

My Opinion on the eighth point: if the value of decode is null and the value of sum (null) is null, the sum will not be normal. You can change it to the following: 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, 0) d0020_sal, sum (decode (dept_no, 0030, Sal, 0 )) d0030_sal from EMP where ename like 'Smith % ';