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

This article introduces the knowledge of "common methods to view the implementation plan in oracle". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This article introduces common methods for viewing execution plans in oracle.

1. EXPLAIN PLAN command

2. AUTOTRACE switch

3 、 DBMS_XPLAN

4. 10046 event

1. EXPLAIN PLAN command

SQL > var a number

SQL > var b number

SQL > exec: a: = 0

PL/SQL procedure successfully completed.

SQL > exec: B: = 70000

PL/SQL procedure successfully completed.

SQL > explain plan for select count (*) from t where object_id between: an and: B

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 2213771543

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

| | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 5 |

| | * 2 | FILTER | | |

| | * 3 | INDEX RANGE SCAN | T_IDX | 180 | 900 | 2 (0) | 00:00:01 |

. Omit part

SQL > set autot traceonly

SQL > select count (*) from t where object_id between: an and: B

Execution Plan

Plan hash value: 2213771543

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

| | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 5 |

| | * 2 | FILTER | | |

| | * 3 | INDEX RANGE SCAN | T_IDX | 180 | 900 | 2 (0) | 00:00:01 |

. . . Omit part

SQL > select count (*) from t where object_id between: an and: B

COUNT (*)

-

136544

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

PLAN_TABLE_OUTPUT

SQL_ID 9cgwqzzvtw8wc, child number 0

-

Select count (*) from t where object_id between: an and: B

Plan hash value: 853742775

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

| | 0 | SELECT STATEMENT | 45 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 5 |

PLAN_TABLE_OUTPUT

| | * 2 | FILTER | | |

| | * 3 | INDEX FAST FULL SCAN | T_IDX | 50561 | 246k | 45 (0) | 00:00:01 |

. . . Omit part

The real execution plan should be INDEX FAST FULL SCAN

3 、 DBMS_XPLAN

The common subroutines of the DBMS_XPLANB package are:

DISPLAY: for use with explain plan for

DISPLAY_CURSOR: applies to the sql execution plan that sqlplus has just executed, or to the execution plan stored in shared pool.

After the execution plan of DISPLAY_AWR:sql is aga out from shared pool, if the execution plan is collected into the awr report, then you can use this method to view the execution plan.

Example:

SQL > select status from t where owner=user

VALID

VALID

VALID

. . . Omit part

31206 rows selected.

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

PLAN_TABLE_OUTPUT

-

SQL_ID 7m7b6un3xtss3, child number 0

-

Select status from t where owner=user

Plan hash value: 47527108

-

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

-

| | 0 | SELECT STATEMENT | 13 (100) | | |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 2255 | 27060 | 13 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1 / T@SEL$1

2-SEL$1 / T@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

DB_VERSION ('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"T" @ "SEL$1"DESC_T_INX")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

2-access ("T". "SYS_NC00016 $" = SYS_OP_DESCEND (user!))

Filter (SYS_OP_UNDESCEND ("T". "SYS_NC00016 $") = user!)

Column Projection Information (identified by operation id):

1-"STATUS" [VARCHAR2,7]

2-"T" .ROWID [ROWID,10], "T". "SYS_NC00016 $" [RAW,46]

46 rows selected.

= > 1. You don't see the relevant statistics compared to the AUTOTRACE switch, and you have to wait until the statement execution is complete. But the execution plan obtained is real.

two。 Where the parameter format is advanced, compared with the parameter all, there is more information output from Outline Data.

3. The Rows column here is estimated. To see the real value, you can set the format parameter to 'ALLSTATS LAST''.

The format parameter is set to 'ALLSTATS LAST' example:

SQL > alter session set statistics_level = all

Session altered.

SQL > select status from t where owner=user

VALID

VALID

VALID

. Omit part

31206 rows selected.

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

PLAN_TABLE_OUTPUT

-

SQL_ID 7m7b6un3xtss3, child number 1

-

Select status from t where owner=user

Plan hash value: 47527108

-

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

-

| | 0 | SELECT STATEMENT | | 1 | | 31206 | 00.05 | 5555 | 829 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 2255 | 31206 | 00.05 | 5555 | 829 |

| | * 2 | INDEX RANGE SCAN | DESC_T_INX | 1 | 70 | 31206 | 00ride00.02 | 2190 | 114C |

-

Predicate Information (identified by operation id):

2-access ("T". "SYS_NC00016 $" = SYS_OP_DESCEND (user!))

Filter (SYS_OP_UNDESCEND ("T". "SYS_NC00016 $") = user!)

Compared with the advanced parameter, there is less output, but you can see the actual number of records taken at each step.

Enter sqlid to view an example of the execution plan:

SQL > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user'

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

Select status from t where owner=user 7m7b6un3xtss3 2 3

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

PLAN_TABLE_OUTPUT

SQL_ID 7m7b6un3xtss3, child number 0

-

Select status from t where owner=user

Plan hash value: 47527108

-

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

-

| | 0 | SELECT STATEMENT | 13 (100) | | |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 2255 | 27060 | 13 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1 / T@SEL$1

2-SEL$1 / T@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

DB_VERSION ('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"T" @ "SEL$1"DESC_T_INX")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

2-access ("T". "SYS_NC00016 $" = SYS_OP_DESCEND (user!))

Filter (SYS_OP_UNDESCEND ("T". "SYS_NC00016 $") = user!)

Column Projection Information (identified by operation id):

1-"STATUS" [VARCHAR2,7]

2-"T" .ROWID [ROWID,10], "T". "SYS_NC00016 $" [RAW,46]

Display_awr example:

SQL > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user'

No rows selected

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

PLAN_TABLE_OUTPUT

SQL_ID: 7m7b6un3xtss3, child number: 0 cannot be found

SQL > select * from table (dbms_xplan.display_awr ('7m7b6un3xtss3'))

PLAN_TABLE_OUTPUT

SQL_ID 7m7b6un3xtss3

-

Select status from t where owner=user

Plan hash value: 47527108

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

| | 0 | SELECT STATEMENT | 13 (100) | | |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 2255 | 27060 | 13 (0) | 00:00:01 |

| | 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0) | 00:00:01 |

= = > 1. The same execution plan can also be obtained through the @? / rdbms/admin/awrsqrpt command.

4. 10046 mode

SQL > alter session set tracefile_identifier=plan_10046

Session altered.

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

Session altered.

SQL > select status from t where owner=user

VALID

VALID

VALID

. . . Omit part

31206 rows selected.

SQL >

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

Session altered.

You can find the file of plan_10046 based on the diagnostic_dest parameter.

Use tkprof for output:

[ora11@ora12c ~] $tkprof / ora11_10/ora11/diag/rdbms/ora11/ora11/trace/ora11_ora_26758_PLAN_10046.trc PLAN_10046.trc

TKPROF: Release 11.2.0.1.0-Development on Tue Aug 15 21:14:49 2017

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

SQL ID: 57fcnar0x2buq

Plan Hash: 47527108

Select status

From

T where owner=user

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 11 57 00

Execute 1 0.00 0.00 00 00

Fetch 2082 0.13 0.13 1007 5555 0 31206

--

Total 2084 0.14 0.13 1018 5612 0 31206

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

31206 TABLE ACCESS BY INDEX ROWID T (cr=5555 pr=1007 pw=0 time=76792 us cost=13 size=27060 card=2255)

31206 INDEX RANGE SCAN DESC_T_INX (cr=2190 pr=114 pw=0 time=29158 us cost=10 size=0 card=70) (object id 99885)

This is the end of the content of "Common ways to View the execution Plan in oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.