Oracle SQL Performance Optimization Series (6)

xiaoxiao2021-03-06  74

20. Replace EXISTS with table

Typically, the way to use table connections is more efficient than EXISTS.

SELECT ENAME

From EMP E

WHERE EXISTS (SELECT 'X'

From dept

Where dept_no = E.DEPT_NO

And dept_cat = 'a');

(More efficient)

SELECT ENAME

From DEPT D, EMP E

WHERE E.DEPT_NO = D.DEPT_NO

And dept_cat = 'a';

(Translator Press: In the case of RBO, the execution path of the former includes filter, the latter uses NESTED LOOP)

21. Replace Distin with EXISTS

When submitting a query containing a pair of multi-table information (such as department tables and employee tables), avoid using Distinct in the SELECT clause. Generally consider replacing with exist

E.g:

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);

EXISTS makes the query more quickly, because the RDBMS core module will return the result immediately once it is satisfied.

22. Identify the 'inefficient execution' SQL statement

Use the following SQL tool to find inefficient SQL:

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;

(Translator Press: Although there are currently an endless graphics tools about SQL optimization, it is always the best way to solve the problem of your own SQL tools.

23. Use the TKPROF tool to query SQL performance status

The SQL Trace tool collects the performance status data that is executing and logs into a tracking file. This tracking file provides a number of useful information, such as the number of resolutions, the number of executions, CPU usage, etc. These data will be able to optimize you system.

Set SQL TRACE in the session level: valid

Alter Session Set SQL_TRACE TRUE

Setting SQL TRACE Effects throughout the database, you must set the SQL_TRACE parameter in init.ra to true, the user_dump_dest parameter illustrates the directory of the generated tracking file

(Translator Press: In this section, the author did not mention TkProf usage, the use of SQL TRACE is not accurate enough, set SQL TRACE first to set TIMED_STATISTICS in Init.ora, so that it can get those important time status The generated trace file is unreadable, so use the TKPROF tool to convert it, TKPROF has many execution parameters. You can refer to the Oracle manual to understand the specific configuration.)

转载请注明原文地址:https://www.9cbs.com/read-120572.html

New Post(0)