ORACLE SQL Performance Optimization series (vi)

oracle| Performance | optimization
20. Replace exists with table connection



In general, table joins are 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: In the case of Rbo, the former's execution path includes filter, the latter uses nested LOOP)



21. Replace distinct with exists

Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. You can generally consider replacing with exist



For example:

Low efficiency:

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 queries faster because the RDBMS core module returns the results immediately after the criteria for the subquery are met.



22. Identify the ' inefficient execution ' of the SQL statement



Use the following SQL tools 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: Although there are a variety of graphical tools for SQL optimization at the moment, writing your own SQL tools to solve problems is always the best way)



23. Use the Tkprof tool to query SQL performance status



The SQL Trace tool collects the performance state data of the executing SQL and logs it to a trace file. This trace file provides a number of useful information, such as the number of resolutions. Execution times, CPU usage time, etc. these data will be used to optimize your system.



Set SQL Trace at session level: valid



ALTER Session SET Sql_trace TRUE



To set up SQL Trace to emulate the entire database, you must set the Sql_trace parameter to true in Init.ora, and the User_dump_dest parameter describes the directory where the trace file is generated



(Translator: In this section, the author does not mention the use of tkprof, nor is the use of SQL Trace accurate, set SQL Trace to first set the Timed_statistics in the Init.ora, so as to get those important time state. The generated trace file is unreadable, so you want to convert it using the Tkprof tool, Tkprof has many execution parameters. You can refer to the Oracle Manual for specific configurations. )