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

one。 There are 6 ways to obtain oracle execution plan. The advantages and disadvantages of each method are as follows. Choose to use them according to the actual situation:

Explain plan for mode

Step 1:explain plan for is followed by a SQL statement

Step 2:select * from table (dbms_xplan.display ())

Advantages: 1. You don't really need to execute the statement, it's quick and convenient.

Disadvantages: 1. Although fast, but because there is no real run, so there is no output of runtime-related statistics (logical reads, recursive calls, physical reads)

two。 Do not know the number of rows being processed

3. Do not know the number of times the table has been accessed

Set autotrace on mode

Step 1:set autotrace on

Step 2:sql statement

There are four different modes of set autotrace

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 run result and statistics section, only show the execution plan part, and do not execute the statement if it is a select query)

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

Advantages: 1. You can output relevant statistics of the runtime (how many logical reads, how many recursive calls, how many physical reads), which method 1 does not have.

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.

Defects: 1. You must wait until the statement has been executed before you can produce a result.

two。 Cannot see the number of times the table has been accessed.

Statistics level=all mode

Step 1:alter session set statistics_level=all (if there is a previous set autotrace on, you need to set autotrace off first)

Step 2: execute the SQL statement (if you add hint / + gather_plan_statistics / to the sql statement, you don't have to set step 1)

Step 3:select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Starts is the actual number of execution of the statement, E-Rows is the estimated number of rows in the execution plan, A-Rows is the actual number of rows returned, A-Time is the time of actual execution of each step, and Buffers is the logical read of the actual execution of each step.

Advantages: 1. You can clearly get the actual number of execution from the STARTS and how much the table is accessed.

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

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

two。 The record must be typed out on the output screen, unlike autotrace, which has a traceonly that can control not to output the result on screen.

3. Can not see the number of recursive calls, can not see how much physical reading

Bring it into sql_id mode

Step 1: select from table (dbms_xplan.display_cursor ('& sql_id')); (this method is obtained from the shared pool, and this statement must be run once)

Another method: select from table (dbms_xplan.display_awr ('& sq_id')); (this is obtained from the awr performance view)

If you have multiple execution plans, you can find out in a similar way

Select from table (dbms_xplan.display_cursor ('sql_id',0))

Select from table (dbms_xplan.display_cursor ('sql_id',1))

Advantages: 1. Knowing that sql_id can get an execution plan immediately, like explain plan for, there is no need to execute.

two。 You can get a real execution plan. Methods 1 and 2 are predictions.

Defect 1. No run-time related statistics are output (logical reads, recursive calls, physical reads)

two。 Unable to determine the number of rows processed

3. Unable to determine the number of times the table was accessed.

10046TRACE mode

Step 1:alter session set events' 10046 trace name context forever,level 12'

Step 2: execute the sql statement

Step 3:alter session set events' 10046 trace name context off'

Step 4: find the file generated by the trace

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 OR t.thread#=to_number (v.value) I

(select value

From v$parameter

Where name='user_dump_dest') d

Exit

Step 5:tkprof trc file path destination txt file sys=no sort=prsela,exeela,fchela

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.

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

Defects: 1. The steps are tedious and troublesome.

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

3. The conditional statements in the execution plan cannot be clearly displayed.

Awrsqrpt.sql mode

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

Step 2: select the breakpoint values you want (begin snap and end snap)

Step 3: enter the sql_id for the statement

Advantages: 1. You can also get multiple execution plans and output them in the report.

two。 Knowing that sql_id can get an execution plan immediately, like explain plan for, there is no need to execute.

3. You can get a real execution plan. Methods 1 and 2 are predictions.

Defects: 1. The steps are tedious and troublesome, and you need to check the time of snap.

two。 No run-time related statistics are output (logical reads, recursive calls, physical reads)

3. Unable to determine the number of rows processed

4. Unable to determine the number of times the table was accessed.

Summary of application:

1. If a SQL takes a very long time to produce a result, or even too slow to return a result, use explain plan for or set autotrace traceonly statistics (if select) to see the execution plan.

two。 The easiest way to track a SQL is explain plan for, followed by set autotrace on (traceonly)

3. If you want to observe that a SQL has multiple execution plans, you can only obtain and view the awrsqrpt.sql directly by entering the sql_id parameter of dbms_xplan.display_cursor.

4. If there are multiple functions in SQL, and there are multi-layer recursive calls such as SQL in the functions, if you want to analyze them accurately, you can only trace them with 10046 trace.

5. To make sure you see a real execution plan, you can't use explain plan for and set autotrace on

6. To get the number of times the table is accessed, you can only use the statistics_level=all method

two。 How to identify inefficient SQL:

There are seven things to pay attention to.

1. The ratio of the true return value to the logical read generated

Generally speaking, logical reads that cost less than 5 for each row fetched are ok.

If you use statitics_level=all to get the execution plan, check the ratio of BUFFERS (logical reads) / A-ROWS (get rows), and if you use autotrace, check the ratio of consistent gets (logical reads) / rows processed (get rows)

two。 The importance of accurate evaluation in the implementation plan

This is important, because misassessment often means inefficient implementation of the plan, which must be checked with statistics_level=all.

Therefore, to see the ratio of the estimated number of rows of execution E-Rows to the actual number of rows of execution A-Rows, when the deviation is large, it is likely to be caused by inaccurate collection of histograms, so you need to collect the histogram again.

3. Type conversion occurs and needs to be watched.

Watch for type conversions in the Predicate Information (identified by operation id) section of the execution plan.

Because it is often impossible to call the index after type conversion, resulting in inefficiency, this problem is easy to occur when the field type is not clear.

4. View of the number of recursive calls

Of the six methods, only autotrace can check the number of recursive calls. If a statement has a very large number of recursive calls, such as tens of thousands of recursive calls to a table with tens of thousands of rows, there must be a problem.

Detailed problems should be further tracked through 10046 trace to see the specific cause. According to personal experience, it is generally caused by table join problems.

5. View of the number of visits to the table

Among the six methods, only statisitcs_level=all can see the number of table visits (STARTS).

If a table is accessed many times, there is likely to be a problem. It depends on whether there is a join in the table and the type of join. If the table is accessed so many times, it is usually a hash or sort join, and if it is a NL connection, then there must be a problem.

6. Note the number of rows actually accessed by the table

This is often caused by the failure to optimize the query conditions. When the number of real visits is too large, you can carefully check whether the sql statement can have room for optimization.

There are similarities and differences between this and the first point. Take a look at the rows processed. If the planned A-Rows is too high at the beginning, there is probably room for optimization.

The following example is very good:

Select

From (select T1, rownum as rn from T1, T2 where t1.object_id = t2.id1) a

Where a.rn > = 1

And a.rn

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.