How to use dbms_xplan to get execution Plan in oracle 01/16 Update SLTechnology News&Howtos

How to use dbms_xplan to get execution Plan in oracle

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

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

In this issue, the editor will bring you about how to use dbms_xplan to obtain the implementation plan in oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Dbms_ xplan package

Display: returns the execution plan stored in plan_table

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

Table_name: defaults to plan_table

Statement_id: the default null will display the execution plan recently inserted into the plan_table. Specify the name of the sql statement (plan_table.STATEMENT_ID). This parameter is optional for explain plan.

Format: provide those outputs, basic

Basic: display the least information (actions + operands)

Typical: displays most of the information (except aliases, outline, and field projection), defualt typical

Serial: same as typical, parallel operations are not displayed

All: display everything except outline

Advanced: all show

Modifier +, -, bytes,cost,alias (query block and alias), note (partial note display), outline display, parallel (whether parallel information is displayed)

Partition (control partition), peeked_binds (snooping bind,explain plan for does not peep, but plan_table is usually obtained through explain for, and can also be insert, so this modifier does not make much sense to display), predicate (predicate display access,filter part), projection (projection information), rows,remote (remote execution sql statement display)

Filter_preds: a where condition based on plan_table. The default null indicates the output of the most recent plan_table,10gR2 display.

Basic use

SQL > select count (*) from T1

COUNT (*)

3000

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Error: cannot fetch last explain plan from PLAN_TABLE

Need explain plan to store plan_table (can also be created under another name)

SQL > explain plan for select count (*) from T1

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 3724264953

| | 0 | SELECT STATEMENT | | 1 | 10 | 6 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS FULL | T1 | 3000 | 30000 | 6 (0) | 00:00:01 |

Note

PLAN_TABLE_OUTPUT

-SQL plan baseline "SYS_SQL_PLAN_b98b6a04616acf47" used for this statement

SQL > explain plan set statement_id='xh_test' for select * from T1

Explained.

SQL > select * from table (dbms_xplan.display (null,'xh_test'))

PLAN_TABLE_OUTPUT

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

8 rows selected.

SQL >

SQL > select id,operation, object_name from plan_table where statement_id='xh_test'

ID OPERATION OBJECT_NAME

0 SELECT STATEMENT

1 TABLE ACCESS T1

SQL > explain plan for select * from T1

Explained.

SQL > select * from table (dbms_xplan.display (null,null,'basic'))

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS FULL | T1 | only the most basic information is displayed |

8 rows selected.

SQL > select * from table (dbms_xplan.display (null,null,'TYPICAl'))

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111K | 6 (0) | 00:00:01 | usual information cost (% cpu) 6 (2) indicates that cpu accounts for a percentage of cost |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

8 rows selected.

SQL > select * from table (dbms_xplan.display (null,null,'all'))

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

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

PLAN_TABLE_OUTPUT

- -

1-SEL$1 / T1@SEL$1

Column Projection Information (identified by operation id):

1-"COUNTRY" [VARCHAR2,16], "T1". "CONTINENT" [VARCHAR2,9], ~ shows the field (you can see the field definition)

"T1". "PLANET" [CHARACTER,5], "T1". "ONE" [NUMBER,22]

19 rows selected.

SQL > select * from table (dbms_xplan.display (null,null,'advanced')); ~ ~ shows all the information

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

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

PLAN_TABLE_OUTPUT

- -

1-SEL$1 / T1@SEL$1

Outline Data

/ * +

BEGIN_OUTLINE_DATA ~ ~ shows outline information, which is actually a bunch of hints, which does not mean that outline is used, but only shows which hints is included if outline is made.

FULL (@ "SEL$1"T1" @ "SEL$1") can be understood to mean that these hint can fix the plan (use outline xx appears in the note if outline is used)

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

PLAN_TABLE_OUTPUT

- -

DB_VERSION ('11.1.0.6')

OPTIMIZER_FEATURES_ENABLE ('11.1.0.6')

IGNORE_OPTIM_ × × DED_HINTS

END_OUTLINE_DATA

, /

Column Projection Information (identified by operation id):

1-"COUNTRY" [VARCHAR2,16], "T1". "CONTINENT" [VARCHAR2,9]

"T1". "PLANET" [CHARACTER,5], "T1". "ONE" [NUMBER,22]

33 rows selected.

Combine some modifiers to use

SQL > select * from table (dbms_xplan.display (null,null,'typical-rows'))

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 111k | 6 (0) | 00:00:01 |

8 rows selected.

SQL > select * from table (dbms_xplan.display (null,null,'typical + outline'))

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

Outline Data

PLAN_TABLE_OUTPUT

- -

/ * +

BEGIN_OUTLINE_DATA

FULL (@ "SEL$1"T1" @ "SEL$1")

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

DB_VERSION ('11.1.0.6')

OPTIMIZER_FEATURES_ENABLE ('11.1.0.6')

IGNORE_OPTIM_ × × DED_HINTS

END_OUTLINE_DATA

, /

22 rows selected.

Use the filter_preds parameter

SQL > explain plan for select count (*) from T1; a plan has been stored in plan_table just now, and now it is regenerated into a

Explained.

SQL > select * from table (dbms_xplan.display); actually query plan_table

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3724264953

| | 0 | SELECT STATEMENT | | 1 | 10 | 6 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 10 |

| | 2 | TABLE ACCESS FULL | T1 | 3000 | 30000 | 6 (0) | 00:00:01 |

Note

PLAN_TABLE_OUTPUT

- -

-SQL plan baseline "SYS_SQL_PLAN_b98b6a04616acf47" used for this statement

13 rows selected.

SQL > select count (*) from plan_table

COUNT (*)

five

SQL > select distinct plan_id from plan_table

PLAN_ID

two hundred and thirty three

two hundred and thirty four

SQL > select * from table (dbms_xplan.display (null,null,null,'plan_id=233')); actually query plan_table, and now add a where plan_id=233

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | | 3000 | 111k | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 3000 | 111k | 6 (0) | 00:00:01 |

8 rows selected.

Display_cursor: get the execution plan from libary cache (10g is available)

Sql_id; returns the parent cursor of the sql statement, the default null, and the execution plan of the last sql statement of the current session

Cursor_chilid_no: child cursor number. Default is null, which means that all child cursors under the parent cursor will be returned.

Format: contains everything in display (with modifiers), plus run-time statistics (parameter statistics_level=all,or use hint gahter_plan_statistics), default typical

To use display_cursor, you need the select permission of vaccounsession.vaccounsql.rep., or select any dictionary system permission.

Format modifiers for runtime statistics

Allstats=iostats+memstats

Iostats=i/o information display

Last= displays all calculated statistics by default. If last is specified, only the statistics of the last execution are displayed.

Memstats:pga information

Runstats_last=iostats last 10gr1

Runstats_tot=iostats 10gr1

Run time system

Starts: the number of times the operation was executed, a-rows: the number of real records returned by the operation, and a-time: the real time when the operation was executed (HH:MM:SS.FF)

ICompo statistics:

Buffers: number of logical reads

Reads: number of physical reads

Writes: number of physical writes

Here is some useful information, a-rows, and buffers,a-rows/buffers to indicate the number of logical reads per row returned.

A-rows/buffers15or20 logical reads, which indicates that the path is not good and can be optimized

In addition, a-rows and e-rows can simply identify erroneous estimates (statistics) e-rows evaluation cardinality (card), a-rows true cardinality (card), but sometimes related types of operations should be considered (execution plan uses a child to control other child, execution time, but non-related type executes only once) such as nl

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

COUNT (*)

one

SQL > select sql_id,child_number from v$sql where sql_text='select / * + gather_plan_statistics*/count (*) from T1'

SQL_ID CHILD_NUMBER

87da3j7f1yd4a 0

SQL > select * from table (dbms_xplan.display_cursor ('87da3j7f1yd4a)); add this is the last statistical information executed

PLAN_TABLE_OUTPUT

- -

SQL_ID 87da3j7f1yd4a, child number 0

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

Plan hash value: 3724264953

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

| | 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 | 00lv 00.01 | 7 |

13 rows selected.

SQL > select * from table (dbms_xplan.display_cursor ('87da3j7f1yd4)); this is the total output of this sql executed several times (and shared this subcursor), which contains the information of these times.

PLAN_TABLE_OUTPUT

- -

SQL_ID 87da3j7f1yd4a, child number 0

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

Plan hash value: 3724264953

| | 1 | SORT AGGREGATE | | 2 | 1 | 2 | 00 00.01 | 14 | it can be seen that the sql has been executed twice. |

| | 2 | TABLE ACCESS FULL | T1 | 2 | 1 | 2 | 00.01 | 00.01 | 14 |

13 rows selected.

SQL > select / * + gather_plan_statistics*/* from T1 order by 1

one

SQL > select sql_id,child_number from v$sql where sql_text='select / * + gather_plan_statistics*/* from T1 order by 1'

SQL_ID CHILD_NUMBER

1x49dbnz86s5f 0

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

PLAN_TABLE_OUTPUT

- -

SQL_ID 1x49dbnz86s5f, child number 0

Select / * + gather_plan_statistics*/* from T1 order by 1

Plan hash value: 2148421099

| | 1 | SORT ORDER BY | | 1 | 1 | 1 | 2048 | 2048 | 2048 (0) |

| | 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 | 00lv 00.01 |

13 rows selected.

SQL >

You can see that iostats,memesats requires runtime statistics

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

PLAN_TABLE_OUTPUT

- -

SQL_ID 4hrkxzxp0uj5q, child number 0

Select * from T1 order by 1

Plan hash value: 2148421099

| | 1 | SORT ORDER BY | | 1 |

| | 2 | TABLE ACCESS FULL | T1 | 1 | |

Note

-Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to' ALL', at session or system l

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

PLAN_TABLE_OUTPUT

- -

SQL_ID 4hrkxzxp0uj5q, child number 0

Select * from T1 order by 1

Plan hash value: 2148421099

| | 1 | SORT ORDER BY | | 1 | 2048 | 2048 | 2048 (0) | |

| | 2 | TABLE ACCESS FULL | T1 | 1 | |

Note

-Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to' ALL', at session or system level

19 rows selected.

Since explain plan for does not peeking (5% selection rate), and display_cursor is extracted from library cache, the modifier peeked_binds is useful, we can see what the value of query opitimzer peeking is (11g will peeking multiple times)

SQL > variable a number

SQL > execute: aVERV 1

PL/SQL procedure successfully completed.

SQL > select * from T1 where astata

one

SQL > select sql_id,child_number from v$sql where sql_text='select * from T1 where aura'

SQL_ID CHILD_NUMBER

B980nwgpprj6w 0

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

PLAN_TABLE_OUTPUT

- -

SQL_ID b980nwgpprj6w, child number 0

Select * from T1 where astata

Plan hash value: 3617692013

| | 0 | SELECT STATEMENT | 3 (100) | |

| | * 1 | TABLE ACCESS FULL | T1 | 1 | 3 | 3 (0) | 00:00:01 |

Peeked Binds (identified by position):

1 -: a (NUMBER): 1

Predicate Information (identified by operation id):

1-filter ("A" =: a)

Display_awr: you can extract information from awr to execute the plan

Sql_id: parent cursor id

Plan_hash_value: execution plan hash value. Default null (means all execution plans under sql_id will be output)

Db_id: returns the db where the sql statement is executed. The default null (returns the exact db)

Format: full-time with display. Default typical

Unlike display_cursor, display_cursor comes from shared pool libirary cache, while display_awr comes from awr feature, which is finally saved in the view of dba_hist class (ASH feature), so after flush shared pool

Display cursor can't get the execution plan, and display_awr can (exist in fixed view)

SQL > @ F:\ oracle\ product\ 10.2.0\ db_1\ RDBMS\ ADMIN\ awrrpt.sql generate awr report

6aq34nj2zb2n7 select col#, grantee#, privilege#, max (mod (nvl (option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#

Grantee# order by col#, grantee#

Use SQL_ID 6aq34nj2zb2n7

SQL > select * from table (dbms_xplan.display_awr ('6aq34nj2zb2n7'))

PLAN_TABLE_OUTPUT

SQL_ID 6aq34nj2zb2n7

Select col#, grantee#, privilege#,max (mod (nvl (option$,0), 2) from

Objauth$ where obj#=:1 and col# is not null group by privilege#, col#

Grantee# order by col#, grantee#

Plan hash value: 2874733959

| | 0 | SELECT STATEMENT |

| | 1 | SORT GROUP BY |

Note

-rule based optimizer used (consider using cbo)

21 rows have been selected.

SQL > alter system flush shared_pool;~~~ refresh shared pool is also available

The system has changed.

SQL > select * from table (dbms_xplan.display_awr ('6aq34nj2zb2n7'))

PLAN_TABLE_OUTPUT

SQL_ID 6aq34nj2zb2n7

Select col#, grantee#, privilege#,max (mod (nvl (option$,0), 2) from

Objauth$ where obj#=:1 and col# is not null group by privilege#, col#

Grantee# order by col#, grantee#

Plan hash value: 2874733959

| | 0 | SELECT STATEMENT |

| | 1 | SORT GROUP BY |

Note

-rule based optimizer used (consider using cbo)

21 rows have been selected.

9i gets plan, from library cache, 9i does not have display_cursor, but it is not very accurate to use explain plan for, especially when there is bind, use the following method

[oracle@smxdmk1 ~] $cd $ORACLE_HOME/rdbms/admin

[oracle@smxdmk1 admin] $sqlplus'/ as sysdba'

SQL > @ utlxplan.sql

Table created.

SQL > create table t (an int)

Table created.

SQL > insert into t values (1)

1 row created.

SQL > commit

Commit complete.

SQL > select count (*) from t

COUNT (*)

one

SQL > select hash_value,child_number,address from v$sql where sql_text='select count (*) from t'

HASH_VALUE CHILD_NUMBER ADDRESS

2816538551 0 00000001311BC490

SQL > INSERT INTO plan_table (operation, options

2 object_node, object_owner, object_name, optimizer

3 search_columns, id, parent_id, position, cost

4 cardinality, bytes, other_tag, partition_start

5 partition_stop, partition_id, other, distribution

6 cpu_cost, io_cost, temp_space, access_predicates

7 filter_predicates)

8 SELECT

9 operation, options, object_node, object_owner, object_name

10 optimizer, search_columns, id, parent_id, position, cost

11 cardinality, bytes, other_tag, partition_start, partition_stop

12 partition_id, other, distribution, cpu_cost, io_cost, temp_space

13 access_predicates, filter_predicates

14 FROM v$sql_plan

15 WHERE address = '00000001311BC490'

16 AND hash_value = 2816538551

17 AND child_number = 0

3 rows created.

SQL > commit

Commit complete.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Error: cannot fetch last explain plan

SQL > delete plan_table

3 rows deleted.

SQL > commit

Commit complete.

SQL > INSERT INTO plan_table (timestamp,operation, options

2 object_node, object_owner, object_name, optimizer

3 search_columns, id, parent_id, position, cost

4 cardinality, bytes, other_tag, partition_start

5 partition_stop, partition_id, other, distribution

6 cpu_cost, io_cost, temp_space, access_predicates

7 filter_predicates)

8 SELECT

9 to_timestamp (sysdate), operation, options, object_node, object_owner, object_name

10 optimizer, search_columns, id, parent_id, position, cost

11 cardinality, bytes, other_tag, partition_start, partition_stop

12 partition_id, other, distribution, cpu_cost, io_cost, temp_space

13 access_predicates, filter_predicates

14 FROM v$sql_plan

15 WHERE address = '00000001311BC490'

AND hash_value = 2816538551

AND child_number = 0

16 17

3 rows created.

SQL > SQL > commit

Commit complete.

SQL > select * from table (dbms_xplan.display (null,'xh'))

PLAN_TABLE_OUTPUT

Error: cannot fetch plan for statement_id 'xh'SQL > alter session set events' 10046 trace name context forever, level 12'

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

Session altered.

SQL > select * from table (dbms_xplan.display (null,'xh'))

PLAN_TABLE_OUTPUT

Error: cannot fetch plan for statement_id 'xh'

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

Session altered.

PARSING IN CURSOR # 2 len=2152 dep=1 uid=0 ct=3 lid=0 tim=1240937193104553 hv=1438696113 ad='33f697b0'

SELECT / * EXEC_FROM_DBMS_XPLAN * / id, position, level, operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost, decode (partition_sta

Rt, 'ROW LOCATION'

'ROWID', decode (partition_start, 'KEY',' KEY')

Decode (partition_start, 'KEY (INLIST)', 'KEY (I)'

Decode (substr (partition_start, 1,6)

'NUMBER', substr (substr (partition_start, 8, 10), 1

Length (substr (partition_start, 8,10)-1)

Decode (partition_start,null,'', partition_start)

Decode (partition_stop, 'ROW LOCATION',' ROW L'

Decode (partition_stop, 'KEY',' KEY', decode (partition_stop)

'KEY (INLIST)', 'KEY (I)', decode (substr (partition_stop, 1,6)

'NUMBER', substr (substr (partition_stop, 8, 10), 1

Length (substr (partition_stop, 8,10)-1)

Decode (partition_stop,null,'', partition_stop), object_node, other_tag, decode (distribution, null,'')

Decode (distribution, 'PARTITION (ROWID)', 'PART (RID)'

Decode (distribution, 'PARTITION (KEY)', 'PART (KEY)'

Decode (distribution, 'ROUND-ROBIN',' RND-ROBIN'

Decode (distribution, 'BROADCAST',' BROADCAST'

Substr (distribution, 1,12), access_predicates, filter_predicates, null from PLAN_TABLE start with id = 0

And timestamp > = (select max (timestamp))

From PLAN_TABLE where id=0 and statement_id = 'xh' and nvl (statement_id,') not like 'SYS_LE%')

And nvl (statement_id,'') not like 'SYS_LE%' and statement_id =' xh' connect by (prior id = parent_id)

And prior nvl (statement_id,'') =

Nvl (statement_id,'')

And prior timestamp select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS FULL | T | |

Note: rule based optimization

9 rows selected.

SQL > select timestamp from plan_table

TIMESTAMP

08-APR-10

SQL > select to_char (timestamp,'YYYY-MM-DD HH24:mi:ss') from plan_table

TO_CHAR (TIMESTAMP,'

2010-04-08 17:44:31

SQL > SQL > INSERT INTO plan_table (timestamp,operation, options

2 object_node, object_owner, object_name, optimizer

3 search_columns, id, parent_id, position, cost

4 cardinality, bytes, other_tag, partition_start

Partition_stop, partition_id, other, distribution

5 6 cpu_cost, io_cost, temp_space, access_predicates

7 filter_predicates)

8 SELECT

9 to_timestamp (sysdate), operation, options, object_node, object_owner, object_name

10 optimizer, search_columns, id, parent_id, position, cost

11 cardinality, bytes, other_tag, partition_start, partition_stop

12 partition_id, other, distribution, cpu_cost, io_cost, temp_space

13 access_predicates, filter_predicates

14 FROM v$sql_plan

15 WHERE address = '00000001311BC490'

16 AND hash_value = 2816538551

17 AND child_number = 0

3 rows created.

SQL >

SQL > commit

Commit complete.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT |

| | 1 | SORT AGGREGATE |

| | 2 | TABLE ACCESS FULL | T | |

Note: rule based optimization

10 rows selected.

SQL > select * from t

one

SQL > select hash_value,child_number,address from v$sql where sql_text='select * from t'

HASH_VALUE CHILD_NUMBER ADDRESS

520543201 0 0000000133F1E708

INSERT INTO plan_table (timestamp,statement_id,operation, options

Object_node, object_owner, object_name, optimizer

Search_columns, id, parent_id, position, cost

Cardinality, bytes, other_tag, partition_start

Partition_stop, partition_id, other, distribution

Cpu_cost, io_cost, temp_space, access_predicates

Filter_predicates)

SELECT

To_timestamp (sysdate), 'xh',operation, options, object_node, object_owner, object_name

Optimizer, search_columns, id, parent_id, position, cost

Cardinality, bytes, other_tag, partition_start, partition_stop

Partition_id, other, distribution, cpu_cost, io_cost, temp_space

Access_predicates, filter_predicates

FROM v$sql_plan

WHERE address = '0000000133F1E708'

AND hash_value = 520543201

AND child_number = 0

SQL >

SQL > INSERT INTO plan_table (timestamp,statement_id,operation, options

2 object_node, object_owner, object_name, optimizer

3 search_columns, id, parent_id, position, cost

4 cardinality, bytes, other_tag, partition_start

5 partition_stop, partition_id, other, distribution

6 cpu_cost, io_cost, temp_space, access_predicates

7 filter_predicates)

8 SELECT

9 to_timestamp (sysdate), 'xh',operation, options, object_node, object_owner, object_name

10 optimizer, search_columns, id, parent_id, position, cost

11 cardinality, bytes, other_tag, partition_start, partition_stop

12 partition_id, other, distribution, cpu_cost, io_cost, temp_space

13 access_predicates, filter_predicates

14 FROM v$sql_plan

15 WHERE address = '0000000133F1E708'

16 AND hash_value = 520543201

17 AND child_number = 0

2 rows created.

SQL >

SQL > commit

Commit complete.

SQL > select * from table (dbms_xplan.display (null,'xh'))

PLAN_TABLE_OUTPUT

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS FULL | T | |

Note: rule based optimization

9 rows selected.

SQL >

10g, 11g to insert the execution plan from libary cache to plan_table, and add plan_id

INSERT INTO plan_table (plan_id,statement_id,operation, options

Object_node, object_owner, object_name, optimizer

Search_columns, id, parent_id, position, cost

Cardinality, bytes, other_tag, partition_start

Partition_stop, partition_id, other, distribution

Cpu_cost, io_cost, temp_space, access_predicates

Filter_predicates)

SELECT

To_timestamp (sysdate), 'xh',operation, options, object_node, object_owner, object_name

Optimizer, search_columns, id, parent_id, position, cost

Cardinality, bytes, other_tag, partition_start, partition_stop

Partition_id, other, distribution, cpu_cost, io_cost, temp_space

Access_predicates, filter_predicates

FROM v$sql_plan

WHERE address = '0000000133F1E708'

AND hash_value = 520543201

AND child_number = 0

Because trace select * from table (dbms_xplan.display)

SQL > SELECT / * + opt_param ('parallel_execution_enabled','false') * /

2 / * EXEC_FROM_DBMS_XPLAN * / id, position, depth, operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost, time, partition_start, partition_stop, object_node, other_tag, distribution, projection, access_predicates, filter_predicates, other, qblock_name, object_alias, nvl (other_xml, remarks) other_xml, null sql_profile, null sql_plan_baseline, null, null

3 null, null

4 null, null from PLAN_TABLE where plan_id = (select max (plan_id))

5 from PLAN_TABLE where id=0 and statement_id = 'xh')

6 order by id

7 /

This is how to use dbms_xplan to get the execution plan in the oracle shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.