Several methods for obtaining an oracle execution plan

Several methods for obtaining an oracle execution plan: 1.set autotrace on
I believe this method is the simplest and most commonly used method. This method is often used to analyze an SQL statement. Here the syntax is pasted, which is simple: SQL> set autotrace
Usage: set autot [RACE] {OFF | ON | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS]
Example:

SQL> set autot on
SQL> select count (*) from plan_table;

COUNT (*)
----------
68
Execution Plan
----------------------------------------------------------
Plan hash value: 1751138260

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | table access full | PLAN_TABLE $ | 68 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement


Statistics
----------------------------------------------------------
27 recursive cballs
0 db block gets
15 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL * Net to client
487 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
It mainly depends on consistent gets and physical reads. consistent gets consumes memory, physical reads consumes disk, and unit is data block. Supplemented by other indicators. If you do not need to list the statement results, set autotrace traceonly.
2. explain plan

Example:
SQL> explain plan for select * from book_info;
SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3200443156

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------------------------------------
| 0 | select statement | 1363 | 177K | 9 (0) | 00:00:01 |
| 1 | table access full | BOOK_INFO | 1363 | 177K | 9 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Of course, the above is the simplest use, and the explain statement is a DML statement, not an implicit commit.
Statment_id specifies the identity for the SQL statements to provide the execution plan
Id: Specifies a name to differentiate multiple execution plans in the execution plan.
Table specifies the table name of the schedule. The default value is plan_table.
Note that in 9i, plan_table is a common table and 10 Gb is a synonym. You can view it in the data dictionary.
@? /Rdbms/admin/utlxplan can be used to create a schedule
@? /Rdbms/admin/utlxpls can also read the execution schedule, which is the same as calling dbms_xplan.display.
@? /Rdbms/admin/utlxplp to view information about parallel processing.
However, consistency reading and physical reading are not displayed here, and IO statistics are not displayed. However, after 10 Gb, display_cursor can be used to view the statistics.
Pay attention to the replacement of variables here:
For example, in a PL/SQL statement: select * from tab1 where name = p_value;
If you want to view the execution plan, do not replace it with constants. Use select * from tab1 where name =: p_value.
Use statsment_id to view the execution plan:
SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT
Certificate quota ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1409354130

Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 133 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | BOOK_INFO | 1 | 133 | 2 (0) | 00:00:01 |
| * 2 | index unique scan | PK_BOOK_BOOK_INFO_ID | 1 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):

PLAN_TABLE_OUTPUT
2-access ("ID" = 10270)

SQL> explain plan set statement_id = 't1' for select * from book_info where id = 20360;
SQL> select * from table (dbms_xplan.display (null, 't1 '));

PLAN_TABLE_OUTPUT
Plan hash value: 1409354130

Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 133 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | BOOK_INFO | 1 | 133 | 2 (0) | 00:00:01 |
| * 2 | index unique scan | PK_BOOK_BOOK_INFO_ID | 1 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
PLAN_TABLE_OUTPUT
2-access ("ID" = 20360)
About the format parameter:
Select * from table (dbms_xplan.display (null, null, 'Basic '));


3. AWR
Since AWR is automatically installed after 10 Gb to collect information, you can use the display_awr function provided by the dbms_xplan package to view the awr SQL extraction plan.
Here we can view the information passed in through the SQL _id parameter.
Example:
Select * from table (dbms_xplan.display_awr ('xxxxxxxxxx '));
Here, we will not go deep into the parameters.

4. Event tracking, such as 10046,10053
SQL _trace
Session trace alter session set SQL _trace = true; -- trace the current session; false: end trace
Of course, you can also track other sessions
Select sid, serial #, username from v $ session ...... locate sid and serial # based on your needs and perform the next tracking
Exec dbms_system.set_ SQL _trace_in_session (sid, serial #, true); -- start
Exec dbms_system.set_ SQL _trace_in_session (sid, serial #, false); -- end
This method does not display binding information and wait events. To complete the information, you need to set the events level, that is, the 10046 event.
Alter session set events '10046 trace name context forever, level 12'; level 1: trace SQL statements, including parsing, execution, extraction, submission, and rollback.
Level 4: including detailed information about variables
Level 8: including waiting events
Level 12: includes binding variables and waiting for the event alter session set evts '2017 trace name context off'; end tracking
The set_ev process of dbms_system also works. In addition, dbms_monitor can be used for tracking in 10 Gb.
Find the trace file:
Alter session set tracefile_identifier = 'xxxxxx ';
Set the identifier for search. The default path is under udump, and some changes to the 11G path are, of course, under $ ORACLE_BASE \ diag \ rdbms \ orcl \ trace.
You can also use SQL to directly locate
SELECT d. VALUE
| '/'
| LOWER (RTRIM (I. INSTANCE, CHR (0 )))
| '_ Ora _'
| P. spid
| '. Trc'
AS "trace_file_name"
FROM (SELECT p. spid
FROM v $ mystat m, v $ session s, v $ process p
WHERE m. statistic # = 1 AND s. SID = m. sid and p. addr = s. paddr) p,
(SELECT t. INSTANCE
FROM v $ thread t, v $ parameter v
WHERE v. NAME = 'thread'
AND (v. VALUE = 0 OR t. thread # = TO_NUMBER (v. VALUE) I,
(SELECT VALUE
FROM v $ parameter
Where name = 'user _ dump_dest ') d;
Or
Set linesize 100
COLUMN trace_file FORMAT A60

SELECT s. sid,
S. serial #,
Pa. value | '/' | LOWER (SYS_CONTEXT ('userenv', 'instance _ name') |
'_ Ora _' | p. spid | '. trc' AS trace_file
FROM v $ session s,
V $ process p,
V $ parameter pa
WHERE pa. name = 'user _ dump_dest'
AND s. paddr = p. addr
AND s. audsid = SYS_CONTEXT ('userenv', 'sessionid ');
Find the file, you can use TKPROF to view it. TKPROF will not be explained here

5. Use a third-party tool toad PL/SQL Developer
I believe this is basically used. It is more convenient for PL/SQL Developer F5 and toad database-> monitor-> session browser options to view the plan for executing SQL.

6. oradebug10046SQL> oradebug event 10046 trace name context forever, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;