Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to view the implementation plan in Oracle, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Method 1. View the SQL execution plan by using the Explain Plan Window window in the tool PLSQL Developer. Specific reference

Https://www.cnblogs.com/Dreamer-1/p/6076440.html

Method 2. Check through the autotrace command in SQL*PLUS

1. Log in to the user with dba permission and execute the

Scripts ${ORACLE_HOME} / RDBMS/ADMIN/utlxplan.sql and

Script ${ORACLE_HOME} / sqlplus/admin/plustrce.sql

Then you can view the execution plan through SQL*PLUS.

two。 There are four options for viewing the execution plan

1 > set autotrace on-(get the execution plan and output the result)

2 > set autotrace traceonly-(get the execution plan, but do not output the result)

3 > set autotrace traceonly explain-- (get the execution plan, do not output statistics and results, only show the part of the implementation plan)

4 > set autotrace traceonly statistics-(do not output execution plans and results, only show statistics)

3. Advantages and disadvantages

Advantages:

1 > you can output relevant statistics of the runtime (how many logical reads, how many recursive calls, how many physical reads)

2 > although you must wait for the statement to be executed before you can execute the plan, you can have a traceonly switch to control the output of the returned result without hitting the screen.

Disadvantages:

1 > you must wait for the statement to be executed before you can give a result

2 > cannot see how many times the table has been accessed

Method 3. Explain plan for acquisition

1. The steps are as follows:

SQL > set linesize 200

SQL > set pagesize 2000

SQL > explain plan for select * from emp

Explained

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 14 | 532 | 3 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0) | 00:00:01 |

8 rows selected

two。 Advantages and disadvantages

Advantages:

1 > No real execution, quick and convenient

Disadvantages:

1 > No relevant statistics are output (how many logical reads, how many physical reads, how many recursive calls)

2 > unable to determine how many lines have been processed

3 > unable to determine how many times the table has been accessed

Method 4. Statistics_level = all or / * + gather_plan_statistics * /

1. Execution steps

1 > through statistics_level = all

SQL > set linesize 500

SQL > set pagesize 1000

SQL > alter session set statistics_level = all

The session has changed.

SQL > select count (*) from emp

COUNT (*)

-

fourteen

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

PLAN_TABLE_OUTPUT

-

SQL_ID g59vz2u4cu404, child number 0

-

Select count (*) from emp

Plan hash value: 2937609675

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 1 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.00 SORT AGGREGATE 00.01 | 1 |

| | 2 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 14 | 00lv 00.01 | 1 |

-

14 rows have been selected.

2 > through / * + gather_plan_statistics*/

SQL > set linesize 200

SQL > set pagesize 500

SQL > select / * + gather_plan_statistics*/ count (*) from emp

COUNT (*)

-

fourteen

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

PLAN_TABLE_OUTPUT

-

SQL_ID 537ffv2mq5375, child number 0

-

Select / * + gather_plan_statistics*/ count (*) from emp

Plan hash value: 2937609675

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 1 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.00 SORT AGGREGATE 00.01 | 1 |

| | 2 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 14 | 00lv 00.01 | 1 |

-

14 rows have been selected.

two。 Keyword interpretation

1 > Starts: the number of times this SQL has been executed

2 > E-Rows: the number of rows expected in the execution plan.

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

4 > A-Time: the actual execution time of each step (HH: MM: SS.FF). According to this line, you can know where the time spent by the SQL is.

5 > Buffers: logical or consistent reads performed at each step

3. Advantages and disadvantages

Advantages

1 > you can clearly figure out how many tables are accessed from Starts

2 > 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 correct.

3 > although there is no specific output of runtime-related statistics, the buffers in the execution plan is the real logical read.

Shortcoming

1 > the result can not be obtained until the statement is actually executed.

2 > cannot control the output of the record output screen, unlike autotace, which can output the result without screen output by traceonly.

3 > you can't see the number of recursive calls or the number of physical reads (but logical reading is the key)

Method 5. Input sql_id parameters through dbms_xplan.display_cursor (& sql_id).

1. Execution steps

SQL > select sql_id from sys.v_$sql t where t.sql_text like'% select count (*) from emp'

SQL_ID

-

G59vz2u4cu404

SQL > select * from table (dbms_xplan.display_cursor ('g59vz2u4cu404'))

PLAN_TABLE_OUTPUT

-

SQL_ID g59vz2u4cu404, child number 0

-

Select count (*) from emp

Plan hash value: 2937609675

-

| | Id | Operation | Name | Rows | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0) | 00:00:01 |

-

14 rows have been selected.

two。 Advantages and disadvantages

Advantages:

1 > know that sql_id can get the execution plan immediately, just like explain plan for, there is no need to execute

2 > you can get the real execution plan.

Disadvantages:

1 > there is no output of running statistics (physical reads, logical reads, recursive calls)

2 > unable to determine how many lines have been processed

3 > unable to determine how many times the table has been accessed

Method 6. 10046 trace tracking

1. Execution steps

Step1:alter session setevents' 10046 trace name context forever,level 12 hours; (enable tracking)

Step2: execute sql

Step3:alter session setevents' 10046 trace name context off'; (turn off tracing)

Step4: step 4: find the file generated by the trace

Step5:tkprof trc file target file sys=no sort=prsela,exeela,fchela (format command)

SQL > set autot off

SQL > alter session set statistics_level=typical

Session altered.

SQL > alter session set events' 10046 trace name context forever,level 12'

Session altered.

SQL > select count (*) from test

COUNT (*)

-

seven

SQL > alter session set events' 10046 trace name context off'

Session altered.

SQL > select d.value

2 | |'/'

3 | | LOWER (RTRIM (i.INSTANCE, CHR (0)

4 | |'_ ora_'

5 | | p.spid

6 | | '.trc' trace_file_name

7 from (select p.spid

8 from v$mystat m training session s, v$process p

9 where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p

10 (select t.INSTANCE

11 FROM v$thread trecast vain parameter v

12 WHERE v. nameplate thread'

13 AND (v.VALUE=0 OR t.thread#=to_number (v.value) I

14 (select value

15 from v$parameter

16 where name='user_dump_dest') d

TRACE_FILE_NAME

-

/ u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc

SQL > host

[oracle@ora12c ~] $tkprof / u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc / home/oracle/10046.txt sys=no sort=prsela,exeela,fchela

TKPROF: Release 12.1.0.1.0-Development on Fri Jan 20 08:22:25 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

SQL ID: 7b2twsn8vgfsc Plan Hash: 784602781

Select count (*) from test

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 3 3 2 0

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 4 66 0 1

--

Total 4 0.00 0.00 7 69 2 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 103

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

1 1 1 SORT AGGREGATE (cr=66 pr=4 pw=0 time=298 us)

7 7 7 PARTITION RANGE ALL PARTITION: 1 3 (cr=66 pr=4 pw=0 time=397 us cost=39 size=0 card=11)

7 7 7 TABLE ACCESS FULL TEST PARTITION: 1 3 (cr=66 pr=4 pw=0 time=290 us cost=39 size=0 card=11)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

Db file sequential read 4 0.00 0.00

SQL*Net message to client 2 0.00 0.00

Db file scattered read 1 0.00 0.00

SQL*Net message from client 2 7.03 7.03

two。 Advantages and disadvantages

Advantages:

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

2 > if there are functions in the SQL statement and SQL in the SQL, they will all be listed with no place to hide.

3 > it is convenient to see the number of rows processed and read in logical physics.

4 > you can track the entire package

Disadvantages:

1 > the steps are tedious and troublesome

2 > unable to determine how many times the table has been accessed

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

Method 7. Awrsqlrpt.sql

1. Execution steps

Step1:@?/rdbms/admin/awrsqrpt.sql

Step2: select your breakpoints (begin snap and end snap)

Step3: enter sql_id

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 the method explain plan for to look at the execution plan.

two。 The easiest way to track a SQL is method explain plan for, followed by method autotrace

3. If you want to observe that a SQL has multiple execution plans, you can only use the method dbms_xplan.display_cursor (sql_id) and the method awrsqlrpt.sql

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 10046 trace for accurate analysis.

5. To ensure that you see the real execution plan, you cannot use methods plsql developer and method explain plan for

6. To get the number of times the table is accessed, you can only use the method statistics_level (/ * + gather_plan_statistics * /)

Thank you for reading this article carefully. I hope the article "how to View the implementation Plan in Oracle" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.