Oracle Get Execution Plan method

gets the execution plan6kind of Method

1. explain plan for acquisition;

2. set autotrace on ;

3. Statistics_level=all;

4. Direct Access via dbms_xplan.display_cursor input sql_id parameters

5. 10046 trace trace

6. Awrsqrpt.sql

Applicable occasions Analysis

1. If a SQL executes for a very long time to produce results , even if it is too slow to return the results, then the execution plan can only be used in method 1;

2. The simplest way to track a SQL is Method 1, followed by method 2, Method 2 to execute;

3. If you want to observe multiple execution plans for a single SQL , use method 4 and method 6 only ;

4. if SQL contains multiple functions, the function is nested in SQL and other multi-layer recursive calls , want to accurately analyze, can only use methods 5;

5. to ensure that real implementation plans are seen, methods 1 and 2cannot be used;

6. to get the number of times the table is accessed, use Method 3 only ;

Explain plan for

Step 1:explain plan for " your SQL"

Step 2:select * FROM table (Dbms_xplan.display ());

Sql> explain plan for

2 SELECT *

3 from T1, T2

4 WHERE t1.id = t2.t1_id

5 and T1.N in (18,19);

SELECT * FROM table (Dbms_xplan.display ());

explained.

Advantages

1. No real execution, fast and easy

Disadvantages

1. no output runtime related statistics (how many logical reads, how many times recursive calls, how many times the physical reading of the case);

2. cannot determine how many rows have been processed;

3. cannot tell how many times the table has been accessed.

Set Autotarce on

Step 1:set Autotrace on

Step 2: execute your SQL here , followed by natural results output

Sql> set Autotrace on

Sql> SELECT *

2 from T1, T2

3 WHERE t1.id = t2.t1_id

4 and T1.N in (18,19);

Set Autotrace on ( get execution plan, output run result)

Set Autotrace traceonly ( get execution plan, do not output running result)

Set Autotrace traceonly explain ( get execution plan, do not output run results and Statistics section, only show Execution plan section)

Set Autotrace traceonl statistics ( does not output run results and execution plan sections, only shows the statistics section)

Advantages

1. can output the relevant statistics of the runtime (how many logical reads, how many times recursive calls, how many times the physical reading of the case);

2. Although it is necessary to wait until the completion of the statement to output the execution plan, but there can be a traceonly switch to control the return results without hitting the screen output.

Disadvantages

1. It is necessary to wait until the statement is really completed before the results can be obtained;

2. You cannot see how many times the table has been accessed.

Statistics Level=all

Step 1:alter session set Statistics_level=all;

Step 2: execute your SQL here

Step 3:Select * Fromtable (Dbms_xplan.display_cursor (Null,null, ' allstats last '));

1. If you use /*+gather_plan_statistics */ method, you can omit step 1, direct step 2,3.

2. keyword Interpretation:

The number of times starts is executed for this SQL .

E-rows The estimated number of rows for the execution plan.

A-rows is the number of rows actually returned. a-rows Compare with e-rows , you can determine which step the implementation plan is out of the question.

A-time For each step of the actual execution time (HH:MM:ss.ff), according to this line can know where the SQL time spent.

Buffers the logical read or consistent read that is actually performed for each step.

Reads for physical reading.

Omem: The current operation completes all memory workspace (work Aera) operations using the total size of the workspace in private memory (PGA) , which is estimated by the optimizer statistics and the performance data from the previous execution

1Mem: When the size of the workspace does not meet the required size of the operation, you need to write some data to the temporary disk space ( If you need to write only once to complete the operation, call once,One-pass; Otherwise for multiple passes,multi_pass). The column data is the amount of memory required for a single write disk in the last execution of the statement, which is estimated by the optimizer statistics and the performance data from the previous execution.

User-mem: in the last execution of the statement, the size of the memory workspace used by the current operation , in parentheses (the number of disk exchanges occurred , 1 times is One-pass, more than 1 times is Multi_pass, If no disk is used, the OPTIMAL is displayed )

Omem,1Mem is the required memory evaluationvalue for execution, 0Mem is the evaluation value of the memory required for the optimal execution mode, and1Mem is The evaluation value of the memory required for the One-pass mode.

0/1/m The number of times the optimal/one-pass/multipass is executed. Used-mem consumption of memory

Sql> set Autotrace off

Sql> alter session Setstatistics_level=all;

Session altered.

Sql> SELECT *

2 from T1, T2

3 WHERE t1.id = t2.t1_id

4 and T1.N in (18,19);

Sql> SELECT * Fromtable (Dbms_xplan.display_cursor (Null,null, ' allstats last '));

Advantages

1. It is clear how much the table is accessed from starts .

2. The number of rows predicted and the true number of rows can be clearly obtained from E-rows and a-rows , thus accurately determining whether the Oracle assessment is accurate.

3. Although there is no specific statistics on the output runtime, the buffers in the execution plan is how much the actual logic reads

Disadvantages

1. You must wait until the statement is actually completed before you can produce the result.

2. unable to control the record transmission screen, unlike Autotrace have traceonly can control the results will not be played on the screen output.

3. The number of recursive calls does not see how many physical reads (but logical reading is the focus)

Dbms_xplan.display_cursor

Step 1: select * Fromtable (dbms_xplan.display_cursor (' &sq_id '));

SQL_ID are available in a variety of ways, such as in related performance views,AWR, and other reports.

Note: If there are multiple execution plans

SELECT * Fromtable (dbms_xplan.display_cursor (' cyzznbykb509s ', 0));

SELECT * FROM table (dbms_xplan.display_cursor (' cyzznbykb509s ', 1));

SELECT * FROM table (dbms_xplan.display_cursor (' cyzznbykb509s ', 2));

Advantages

1. know that the sql_id is immediately available for implementation, and that the Explainplan for No need to be executed;

2. a real implementation plan can be obtained .

Disadvantages

1. no output runtime related statistics (how many logical reads, how many times recursive calls, how many times the physical reading of the case);

2. cannot determine how many rows have been processed;

3. cannot tell how many times the table has been accessed.

10046trace

Step 1:alter session SET events ' 10046 Trace name context forever,level 12 '; (turn on tracking)

Step 2: Execute your statement

Step 3:alter session SET events ' 10046 Trace name context off '; (turn off tracking)

Step 4: Find the files generated after the trace

Step 5:tkprof trc file destination file sys=no sort=prsela,exeela,fchela (Format command)

Sql> alter session setstatistics_level=typical;

Session altered.

Sql> alter session SET Events ' 10046trace name context Forever,level 12 ';

Session altered.

Sql> set Autotrace off

Sql> SELECT *

2 from T1, T2

3 WHERE t1.id = t2.t1_id

4 and T1.N in (18,19);

Sql> alter session SET Events ' 10046trace name context off ';

Session altered.

Select D.value

|| ‘/‘

|| LOWER (RTRIM (i.instance, CHR (0)))

|| ' _ora_ '

|| P.spid

|| '. TRC ' 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 Ort.thread#=to_number (v.value))) I,

(Select value

From V$parameter

where name= ' user_dump_dest ') D;

/u01/oracle/diag/rdbms/ocp11g/ocp11g/trace/ocp11g_ora_22508.trc

TKPROF/U01/ORACLE/DIAG/RDBMS/OCP11G/OCP11G/TRACE/OCP11G_ORA_22508.TRC 10046test.txtsys=no Sort=prsela,exeela, Fchela

View 10046test.txt text to

Advantages

1. you can see the SQL statement corresponding to the wait event

2. If there is a function call inthe SQL statement, SQLwill be listed and nowhere to hide.

3. you can easily see the number of rows processed, resulting in the physical logic read.

4. the parsing time and execution time can be easily seen.

5. can track the entire package

Disadvantages

1. cumbersome steps, more trouble

2. cannot tell how many times the table has been accessed.

3. The predicate portion of the execution plan cannot be clearly demonstrated.

Awrsqrpt.sql

Step 1:@?/rdbms/admin/awrsqrpt.sql

Step 2: Select the breakpoints you want (Beginsnap and end snap)

Step 3: Enter your sql_id

This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1793451

Oracle Get Execution Plan method