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