[Oracle] Summary of methods for obtaining execution plans

Conclusion: I. Six methods for obtaining an execution plan (the detailed steps are described in the remarks section at the beginning of each example ):
1. Obtain the explain plan for statement;
2. set autotrace on;
3. statistics_level = all;
4. Use dbms_xplan.display_cursor to input the SQL _id parameter.
5. 10046 trace
6. awrsqrpt. SQL

2. Analysis of applicable scenarios

1. If the execution of an SQL statement takes a very long time to produce results, or even slow to return results, you can only use method 1 or method 4 to call the execution plan;
2. The simplest way to trace an SQL statement is method 1, followed by method 2;
3. If you want to observe that an SQL statement has multiple execution plans, you can only use methods 4 and 6;
4. If SQL contains multiple functions and multiple layers of recursive calls, such as SQL, can be used only in Method 5 for accurate analysis;
5. Methods 1 and 2 cannot be used to ensure that the actual execution plan is visible;

6. To obtain the number of times a table is accessed, use method 3;

Environment Construction -- study preparations before the number of Nested Loops Join visits

DROP TABLE t1 CASCADE CONSTRAINTS PURGE; DROP TABLE t2 CASCADE CONSTRAINTS PURGE; CREATE TABLE t1 (     id NUMBER NOT NULL,     n NUMBER,     contents VARCHAR2(4000)   )   ; CREATE TABLE t2 (     id NUMBER NOT NULL,     t1_id NUMBER NOT NULL,     n NUMBER,     contents VARCHAR2(4000)   )   ; execute dbms_random.seed(0); INSERT INTO t1     SELECT  rownum,  rownum, dbms_random.string('a', 50)       FROM dual     CONNECT BY level <= 1000      ORDER BY dbms_random.random; INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000    ORDER BY dbms_random.random; COMMIT; CREATE INDEX t1_n ON t1 (n);CREATE INDEX t2_t1_id ON t2(t1_id);

Below we will use a variety of methods to view the execution plan of the following statements
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1 _ id
AND t1.n in (); Method 1 (explain plan. Similar to F5 in plsql develope) Step 1: explain plan for "your SQL"
Step 2: select * from table (dbms_xplan.display ());

Set linesize 1000 set pagesize 2000 explain plan forSELECT * FROM t1, t2WHERE t1.id = t2.t1 _ idAND t1.n in (18, 19); select * from table (dbms_xplan.display ()); PLAN_TABLE_OUTPUT partition Plan hash value: 3532430033 bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | select statement | 2 | 8138 | 6 (0) | 00:00:01 | 1 | nested loops | 2 | nested loops | 2 | 8138 | 6 (0) | 00:00:01 | 3 | inlist iterator | 4 | table access by index rowid | T1 | 2 | 4056 | 2 (0) | 00:00:01 | * 5 | index range scan | T1_N | 1 | 1 (0) | 00:00:01 | * 6 | index range scan | T2_T1_ID | 1 | 1 (0) | 00:00:01 | 7 | table access by index rowid | T2 | 1 | 2041 | 2 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ------------------------------------------------- 5-access ("T1 ". "N" = 18 OR "T1 ". "N" = 19) 6-access ("T1 "." ID "=" T2 "." T1_ID ") Note ------dynamic sampling used for this statement (level = 2) 24 rows have been selected.

Advantages:1. quick and convenient without real execution
Defects:1. No running statistics are output (logical reads, recursive calls, physical reads );
2. Unable to determine how many rows are processed;
3. You cannot determine how many times the table is accessed.
Indeed, after all, there is no real execution and how to learn the statistical information generated by the real operation.

Method 2 (set autotrace on) Step 1: set autotrace on
Step 2: Execute your SQL statement here, and the result will be output in the future.

In addition, there are several methods:
Set autotrace on (get the execution plan and output the running result)
Set autotrace traceonly (get the execution plan without outputting the running result)
Set autotrace traceonly explain (get the execution plan, do not output the running results and statistics part, only show the execution plan part)
Set autotrace traceonl statistics (the running result and execution plan section are not output, and only the Statistical Information Section is displayed)

Set autotrace on SELECT * FROM t1, t2WHERE t1.id = t2.t1 _ idAND t1.n in (18, 19); Execution Plan ---------------------------------------------------------- Plan hash value: 3532430033 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 | 8138 | 6 (0) | 00:00:01 | 1 | nested loops | 2 | nested loops | 2 | 8138 | 6 (0) | 00:00:01 | 3 | inlist iterator | 4 | table access by index rowid | T1 | 2 | 4056 | 2 (0) | 00:00:01 | * 5 | index range scan | T1_N | 1 | 1 (0) | 00:00:01 | * 6 | index range scan | T2_T1_ID | 1 | 1 (0) | 00:00:01 | 7 | table access by index rowid | T2 | 1 | 2041 | 2 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ------------------------------------------------- 5-access ("T1 ". "N" = 18 OR "T1 ". "N" = 19) 6-access ("T1 ". "ID" = "T2 ". "T1_ID") Note ------dynamic sampling used for this statement (level = 2) statistics 0 recursive cballs 0 db block gets 12 consistent gets 0 physical reads 0 redo size 1032 bytes sent via SQL * Net to client 416 bytes received via SQL * Net from client 2 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

 

Advantages:1. Relevant statistical information during running can be output (logical reads, recursive calls, physical reads );
2. Although the execution plan can be output only after the statement is executed, the traceonly switch can be used to control the returned results without screen output.

Defects:1. The result can be returned only after the statement is actually executed;
2. You cannot see how many times the table has been accessed.

Method 3 (statistics level = all) Step 1: alter session set statistics_level = all;
Step 2: Execute your SQL statement here
Step 3: select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));

Note:

1. If you use the/* + gather_plan_statistics */method, skip step 1 and directly Step 2 and 3.
2. Keyword explanation (OMem, 1Mem, and User-Mem will be available in subsequent courses ):
Starts is the number of times the SQL statement is executed.
E-Rows is the expected number of Rows in the execution plan.
A-Rows indicates the number of Rows actually returned. When comparing A-Rows with E-Rows, you can determine which step of the execution plan has A problem.
A-Time indicates the actual execution Time of each step (HH: MM: SS. FF). You can know the Time consumed by the SQL statement based on this line.
Buffers performs logical read or consistent read for each step.
Reads is a physical read.
OMem: the total size of the workspace in the private memory (PGA) used by the current operation to complete all Work Aera operations,
This data is derived from the optimizer statistics and the performance data estimated for the previous execution.
1Mem: When the workspace size cannot meet the size required for the operation, you need to write some data into the temporary disk space (if you only need to write the data once, you can complete the operation,
It is called a Pass, One-Pass; otherwise, multiple passes, Multi_Pass). The data in this column is the memory required for a single write to the disk during the last execution of the statement.
Size, which is derived from the statistics of the optimizer and the performance data of the previous execution.
User-Mem: the size of the memory workspace used for the current operation during the last execution of the statement, which is in parentheses (the number of disk exchanges, One time is One-Pass,
If the value is greater than 1, it is Multi_Pass. If the disk is not used, OPTIMAL is displayed)
OMem and 1Mem are the memory evaluation values required for execution, 0Mem is the evaluation value for the memory required for the optimal execution mode, and 1Mem is the evaluation value for the memory required for the one-pass mode.
0/1/M is the optimal number of times/one-pass/multipass is executed. Used-Mem memory consumption

Set autotrace off alter session set statistics_level = all; SELECT * FROM t1, t2WHERE t1.id = t2.t1 _ idAND t1.n in (18, 19); select * from table (dbms_xplan.display_cursor (null, null, 'allstats last'); PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------SQL_ID 1a914ws1_gfsn, child number 0 ------------------------------------- SELECT * FROM t1, T2 WHERE t1.id = t2.t1 _ id AND t1.n in (18, 19) Plan hash value: 3532430033 Rows | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | 0 | select statement | 1 | 2 | 00:00:00. 01 | 12 | | 1 | nested loops | 1 | 2 | 00:00:00. 01 | 12 | 2 | nested loops | 1 | 2 | 2 | 00:00:00. 01 | 10 | 3 | inlist iterator | 1 | 2 | 00:00:00. 01 | 5 | 4 | table access by index rowid | T1 | 2 | 2 | 2 | 00:00:00. 01 | 5 | * 5 | index range scan | T1_N | 2 | 1 | 2 | 00:00:00. 01 | 3 | * 6 | index range scan | T2_T1_ID | 2 | 1 | 2 | 00:00:00. 01 | 5 | 7 | table access by index rowid | T2 | 2 | 1 | 2 | 00:00:00. 01 | 2 | your Predicate Information (identified by operation id): ----------------------------------------------------- 5-access ("T1 ". "N" = 18 OR "T1 ". "N" = 19) 6-access ("T1 ". "ID" = "T2 ". "T1_ID") Note ------dynamic sampling used for this statement (level = 2) 29 rows have been selected.

Advantages:1. You can clearly figure out how many tables are accessed from STARTS.
2. You can clearly predict the number of rows and the actual number of rows from the E-ROWS and A-ROWS, so as to accurately determine whether the Oracle evaluation is accurate.
3. Although there is no specific output of runtime statistics, the BUFFERS in the execution plan is the actual number of logical reads.

Defects:1. You must wait until the statement is actually executed.
2. You cannot control the output of records on the screen. Unlike autotrace's traceonly function, you cannot control the output of results on the screen.
3. no number of recursive calls can be seen, and no physical reads can be seen (but logical reads are the focus) Method 4 (after knowing SQL _id, the direct import method is simple, just step 1) step 1: select * from table (dbms_xplan.display_cursor ('& sq_id'); (this method is obtained from the Shared Pool)

Note:
1. Another method is to select * from table (dbms_xplan.display_awr ('& sq_id'); (this is obtained in the awr performance view)
2. If there are multiple execution plans, you can use a similar method to find them.
Select * from table (dbms_xplan.display_cursor ('cyzznbykb509s', 0 ));
Select * from table (dbms_xplan.display_cursor ('cyzznbykb509s', 1 ));

select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------SQL_ID  1a914ws3ggfsn, child number 0-------------------------------------SELECT  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)Plan hash value: 3532430033-------------------------------------------------------------------------------------------| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |          |       |       |     6 (100)|          ||   1 |  NESTED LOOPS                  |          |       |       |            |          ||   2 |   NESTED LOOPS                 |          |     2 |  8138 |     6   (0)| 00:00:01 ||   3 |    INLIST ITERATOR             |          |       |       |            |          ||   4 |     TABLE ACCESS BY INDEX ROWID| T1       |     2 |  4056 |     2   (0)| 00:00:01 ||*  5 |      INDEX RANGE SCAN          | T1_N     |     1 |       |     1   (0)| 00:00:01 ||*  6 |    INDEX RANGE SCAN            | T2_T1_ID |     1 |       |     1   (0)| 00:00:01 ||   7 |   TABLE ACCESS BY INDEX ROWID  | T2       |     1 |  2041 |     2   (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - access(("T1"."N"=18 OR "T1"."N"=19))   6 - access("T1"."ID"="T2"."T1_ID")Note-----   - dynamic sampling used for this statement (level=2)

Advantages:1. Know that SQL _id can get the execution plan immediately. It does not need to be executed like explain plan;
2. You can get a real execution plan. (Stop, wait. What's the truth? Are there any false execution plans in these routines ?)

Defects:1. No running statistics are output (logical reads, recursive calls, physical reads );
2. Unable to determine how many rows are processed;
3. You cannot determine how many times the table is accessed. Method 5 (10046 TRACE) Step 1: alter session set events '2017 trace name context forever, level 12'; (enable tracking)
Step 2: Execute your statement
Step 3: alter session set events '10046 trace name context off'; (disable tracking)
Step 4: Find the file generated after the trail
Step 5: The target file sys = no sort = prsela, exeela, and fchela of the tkprof trc file (format command)

set autotace offalter session set statistics_level=typical;     alter session set events '10046 trace name context  forever,level 12';SELECT  *FROM t1, t2WHERE t1.id = t2.t1_idAND t1.n in(18,19);      alter session set events '10046 trace name context off';   select d.value|| '/'|| LOWER (RTRIM(i.INSTANCE, CHR(0)))|| '_ora_'|| p.spid|| '.trc' trace_file_namefrom (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;exit       tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc    d:\10046.txt  sys=no sort=prsela,exeela,fchela       SELECT  *FROM t1, t2WHERE t1.id = t2.t1_idAND t1.n in(18,19)call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.00       0.00          0         12          0           2------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.00       0.00          0         12          0           2Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 94  Rows     Row Source Operation-------  ---------------------------------------------------      2  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)      2   NESTED LOOPS  (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)      2    INLIST ITERATOR  (cr=5 pr=0 pw=0 time=16 us)      2     TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)      2      INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)      2    INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)      2   TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  SQL*Net message from client     

Advantages:1. We can see the wait event corresponding to the SQL statement.
2. If the SQL statements contain function calls and SQL statements, they will all be listed and have nowhere to hide.
3. You can easily see the number of rows processed and the physical logic read generated.
4. You can easily see the resolution time and execution time.
5. You can track the entire package.

Defects:1. Complicated and troublesome steps
2. You cannot determine how many times the table is accessed.
3. The predicates in the execution plan cannot be clearly displayed. Method 6. awrsqrpt. SQL Step 1 :@? /Rdbms/admin/awrsqrpt. SQL
Step 2: select the expected breakpoint (begin snap and end snap)
Step 3: enter your SQL _id