Common methods for viewing SQL execution plans

Whether you are developing a DBA or maintaining a DBA, you are always more or less encountering SQL execution efficiencies or SQL tuning issues, and it is necessary to view the execution plan. Generally we can view in 3 ways:

First, explain plan for

Examples are enough to illustrate their use.

sys@ORCL> explain plan for
2 select sysdate from dual;
Explained.
sys@ORCL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------
Note: rule based optimization
9 rows selected.

Ii. Use of tkprof tools

Tkprof is an executable tool for parsing Oracle trace files and generating a clearer and more reasonable output. If a system performs less efficiently, a better approach is to track the user's session and format the output using the Tkprof tool's sorting function to find the problematic SQL statement.

The options behind the tkprof command and the meaning of each column in the output file are not described here in detail. Google will have a lot of information.

The following is a brief description of the use of the Tkprof tool:

1. Set Sql_trace=true at session level

sys@ORCL> alter session set sql_trace=true;
Session altered.

If you want to set the session level to true in Pl/sql, you can use the Dbms_system package:

sys@ORCL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

2, specify the generated trace file name, easy to find:

sys@ORCL> alter session set tracefile_identifier='yourname';

3, execute the SQL statement.

4, using the TKPROF tool format the output of the trace file:

[oracle@q1test01 ~] $ tkprof /oracle/admin/orcl/udump/orcl_ora_10266_yourname.trc /oracle/yourname.txt explain=user/pwd aggregate=yes sys=no waits=yes sort=fchela

5, view the generated files and set Sql_trace=false:

sys@ORCL> alter session set sql_trace=false;