Shulou(Shulou.com)06/01 Report--

6 ways to get the execution plan

1. Explain plan for acquisition

2. Set autotrace on

3. Statistics_level=all

4. Directly obtain the input sql_id parameters through dbms_xplan.display_cursor

5. 10046 trace tracking

6. Awrsqrpt.sql

Applicable situation analysis

1. If a SQL takes a very long time to get a result, or even too slow to return a result, you can only use method 1 to see the execution plan.

two。 The easiest way to track a SQL is method 1, followed by method 2, which is executed

3. If you want to observe that a SQL has multiple execution plans, you can only use methods 4 and 6.

4. If there are multiple functions in SQL, and there are multi-layer recursive calls such as SQL in the functions, you can only use method 5 to analyze them accurately.

5. To ensure that you see the real execution plan, you cannot use methods 1 and 2.

6. To get the number of times the table has been accessed, you can only use method 3

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 (18pm 19)

Select * from table (dbms_xplan.display ())

Explained.

Advantages

No need to really execute, fast and convenient

Shortcoming

1. There is no output of runtime-related statistics (how many logical reads, how many recursive calls, how many physical reads)

two。 It is impossible to tell how many lines have been processed

3. It is impossible to tell how many times the table has been accessed.

Set autotarce on

Step 1:set autotrace on

Step 2: execute your SQL here, and the result will be output later.

SQL > set autotrace on

SQL > SELECT *

2 FROM t1, t2

3 WHERE t1.id = t2.t1_id

4 AND t1.n in (18recovery19)

Set autotrace on (get the execution plan and output the running result)

Set autotrace traceonly (get the execution plan and do not output the running result)

Set autotrace traceonly explain (get the execution plan, do not output the running result and statistics part, only show the execution plan part)

Set autotrace traceonl statistics (do not output the run result and execution plan, only show the statistics part)

Advantages

1. You can output statistics about the runtime (how many logical reads, how many recursive calls, how many physical reads)

two。 Although you have to wait for the statement to be executed before you can output the execution plan, you can have a traceonly switch to control the output of the returned result without hitting the screen.

Shortcoming

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

two。 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 the / * + gather_plan_statistics * / method, you can omit step 1 and go straight to step 2, step 3.

two。 Keyword interpretation:

The number of times Starts was executed for this sql.

The number of rows expected by E-Rows for the execution plan.

A-Rows is the actual number of rows returned. By comparing A-Rows with E-Rows, you can determine which step of the execution plan went wrong.

A-Time is the time it takes to actually execute each step (HH:MM:SS.FF), and from this line you can know where the sql was spent.

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

Reads is a physical read.

OMem: the total size of the workspace in private memory (PGA) used by the current operation to complete all memory Work Aera operations, estimated from optimizer statistics and performance data from the previous execution

1Mem: when the size of the workspace cannot meet the size required for the operation, part of the data needs to be written to the temporary disk space (if you only need to write once to complete the operation, it is said to pass once, One-Pass; otherwise means multiple passes, Multi_Pass). This column of data is the amount of memory required for a single write to disk during the last execution of the statement, which is estimated by optimizer statistics and performance data from the previous execution

User-Mem: during the last execution of the statement, the size of the memory workspace used for the current operation is shown in parentheses (the number of times of disk swapping occurs: One-Pass, Multi_Pass if more than 1, OPTIMAL if no disk is used)

OMem and 1Mem are the evaluation values of memory required for execution, 0Mem is the evaluation value of memory required for the optimal execution mode, and 1Mem is the evaluation value of memory required for one-pass mode.

0Universe M is the number of times of optimal / one-pass/multipass execution. Memory consumed by Used-Mem

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 (18recovery19)

SQL > select * fromtable (dbms_xplan.display_cursor (null,null,'allstats last'))

Advantages

1. You can clearly tell how much the table is accessed from STARTS.

two。 The predicted number of rows and the real number of rows can be clearly obtained from E-ROWS and A-ROWS, so that we can accurately judge whether the Oracle evaluation is accurate.

3. Although there is no specific output runtime related statistics, but the BUFFERS in the execution plan is the real logical read how much

Shortcoming

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

two。 It is impossible to control the output of the recording screen, unlike autotrace, which has traceonly to control not to output the result on the screen.

3. Can not see the number of recursive calls, can not see how much physical reading (but logical reading is the key)

Dbms_xplan.display_cursor

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

There are many ways to obtain sql_id, such as in relevant performance views, AWR and other reports.

Note: if there are multiple implementation 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. Knowing that sql_id can get an execution plan immediately, like explainplan for, there is no need to execute.

two。 You can get a real execution plan.

Shortcoming

1. There is no output of runtime-related statistics (how many logical reads, how many recursive calls, how many physical reads)

two。 It is impossible to tell how many lines have been processed

3. It is impossible to tell how many times the table has been accessed.

10046trace

Step 1:alter session set events' 10046 trace name context forever,level 12 minutes; (enable tracking)

Step 2: execute your statement

Step 3:alter session set events' 10046 trace name context off'; (turn off tracing)

Step 4: find the file generated by the trace

Step 5:tkprof trc file target 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 (18recovery19)

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 mpen vain 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 tje vain parameter v

WHERE v. nameplate 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

Just view the 10046test.txt text.

Advantages

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

two。 If there are function calls in the SQL statement and SQL in the SQL, they will all be listed with nowhere to hide.

3. You can easily see the number of rows processed and the resulting physical and logical reads.

4. It is convenient to see the parsing time and execution time.

5. You can track the entire package

Shortcoming

1. The steps are tedious and troublesome.

two。 It is impossible to tell how many times the table has been accessed.

3. The predicate part of the execution plan cannot be clearly shown.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.