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

This article will explain in detail how to implement the plan through explain and dbms_xplan package analysis. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The tool needs to access a special table, plan _ table, which is used to store the execution plan and needs to be created with the script utlxplan.sql before Oracle 10g:

Build a table:

@?\ rdbms\ admin\ utlxplan.sql

Build synonyms:

Create public synonym plan_table for plan_table

Authorization:

Grant all on plan_table to public

Instead of creating the table plan_table after Oracle 10g, the data dictionary table plan_table$, is added and public synonyms are created based on plan_table$ for users to use.

Basic explain syntax:

Explain plan [set statement_id = 'stmt_id'] for sql_statement

The execution result of the explain instruction is stored in the table plan_table, and there are several ways to get the details of the execution plan:

1. Directly query the plan_table table

Explain the plan

Explain plan for select count (*) from scott.emp

Query result

Col id for 999

Col operation for a50

Col options for a20

Col object_name for a20

Select id

Lpad (', 2 * depth) | | operation | |''| | options | |

Decode (id, 0, substr (optimizer, 1,6) | | 'Cost =' | | to_char (cost)) operation

Options

Object_name

Position

From plan_table

Where plan_id = (select max (plan_id) from plan_table)

Order by id

ID OPERATION OPTIONS OBJECT_NAME POSITION

0 SELECT STATEMENT ALL_RO Cost = 1 1

1 SORT AGGREGATE AGGREGATE 1

2 INDEX FULL SCAN FULL SCAN PK_EMP 1

Or use the following query to include the level hierarchy of the execution plan tree

Col "Execute Plan" for A100

Select id | |'| | parent_id | |''| | lpad ('', 2 * level-1) | |

Operation | |'| options | |'| | object_name | |'(Cost=' | | cost | |')'as "Execute Plan"

From plan_table

Start with id = 0

Connect by prior id = parent_id

Execute Plan

-

0 SELECT STATEMENT (Cost=1)

1 0 SORT AGGREGATE (Cost=)

2 1 INDEX FULL SCAN PK_EMP (Cost=1)

2. Get the execution plan through the package dbms_xplan

1) get the execution plan of the last explain

Col "PLAN_TABLE_OUTPUT" for A100

Select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

-

Plan hash value: 2937609675

-

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

-

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

| | 1 | SORT AGGREGATE | | 1 |

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

-

2) query through the specified statement ID

Select * from table (dbms_xplan.display ('plan_table',' stmt_id'))

3) query through SQL_ID and sub-cursors. This function does not require explain first, and the information displayed is more detailed. In addition, there is a format parameter that can be customized in more detail.

Select * from table (dbms_xplan.display_cursor ('sql_id',' child_number', 'format'))

Displays the execution plan of the most recently executed SQL when sql_id is null, but be careful to keep the set serveroutput off, otherwise the last sentence of SQL will not be the SQL,child number you are running as null, then return the execution plan of all child cursors.

You can easily get SQL_ID and CHILD_NUMBER information by adding comments to the SQL statement, such as the following SQL. Execute it first.

Select / * 12345 / m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

From cmes.c_material_t m

Where (regexp_like ('641234HNGA080001A, m.validaterule,' c') or

Regexp_like ('641234HNGA080001A, m.validaterule2,' c'))

And m.deleted_flag ='0'

And rownum = 1

Find out its ID:

Select sql_id, child_number from v$sql where sql_text like '345%'

SQL_ID CHILD_NUMBER

--

9jk2r7a64s470 0

Cc274s1r7ab6w 0

Because the above statement containing the "12345" comment is executed, the first one executed first is the actual ID.

View the execution plan:

Col PLAN_TABLE_OUTPUT for a100

Select * from table (dbms_xplan.display_cursor ('9jk2r7a64s470, 0))

PLAN_TABLE_OUTPUT

-

SQL_ID 9jk2r7a64s470, child number 0

-

Select / * 12345 / m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

From cmes.c_material_t m

Where (regexp_like ('641234HNGA080001A, m.validaterule,' c') or

Regexp_like ('641234HNGA080001A, m.validaterule2,' c'))

And m.deleted_flag ='0'

And rownum = 1

Plan hash value: 1524529232

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

| | 0 | SELECT STATEMENT | 2 (100) | |

| | * 1 | COUNT STOPKEY | | |

| | * 2 | TABLE ACCESS FULL | C_MATERIAL_T | 2 | 116 | 2 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter (ROWNUM=1)

2-filter (("M". "DELETED_FLAG" ='0' AND (REGEXP_LIKE)

('641234HNGA 080001A, "M". "VALIDATERULE",' centering,)

To view more detailed execution plan information, you need to set the statistics level to ALL first, otherwise there is no information such as A-ROWS.

Alter session set statistics_level = all

Or use HINT at the statement level

Select / * + gather_plan_statistics*/

Select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

From cmes.c_material_t m

Where (regexp_like ('641234HNGA080001A, m.validaterule,' c') or

Regexp_like ('641234HNGA080001A, m.validaterule2,' c'))

And m.deleted_flag ='0'

And rownum = 1

Select * from table (dbms_xplan.display_cursor (null, null, 'ALLSTATS'))

PLAN_TABLE_OUTPUT

-

SQL_ID fmu73t3umxm1r, child number 0

-

Select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule

M.validaterule2 from cmes.c_material_t m where (regexp_like ('641234HNGA080001A')

M.validaterule,'c') or regexp_like ('641234HNGA080001A, m.validaterule2

'c') and m.deleted_flag ='0' and rownum = 1

Plan hash value: 1524529232

-

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

-

| | * 1 | COUNT STOPKEY | | 1 | | 0 | 00001 | 16 |

| | * 2 | TABLE ACCESS FULL | C_MATERIAL_T | 1 | 2 | 0 | 00Rank 00.01 | 16 |

-

Predicate Information (identified by operation id):

1-filter (ROWNUM=1)

2-filter ((REGEXP_LIKE ('641234HNGA080001A), "M". "VALIDATERULE",' cymbals.

Where E-Rows is the estimated number of rows returned, and A-Rows is the actual number of rows returned.

The advanced application of format can display more information, especially the specific values of bound variables, which is very useful. The following are the recommended formats

Select * from table (dbms_xplan.display_cursor (null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'))

Allstats:iostats + memstats,iostats displays io statistics (buffers, reads) of cumulative execution of the cursor, and memstats displays cumulative pga usage information (omem 1mem used-mem).

Last: displays only the statistics of the last execution

Advanced: displays information such as outline, query block name, column projection, etc

Peeked_binds: the binding variable used when printing parsing

Information such as very detailed execution plans and binding variables can be displayed by doing the following

Alter session set statistics_level = all

Var a varchar2 (20)

Exec: a: = 'EMP'

Select object_id from dba_objects where object_name =: a

Select * from table (dbms_xplan.display_cursor (null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'))

The dbms_xplan package also has a function display_awr to get the execution plan of the specified SQL_ID in the AWR report

Col PLAN_TABLE_OUTPUT for a100

Select * from table (dbms_xplan.display_awr ('9jk2r7a64s470'))

The execution plan obtained by this function is from the dba_hist_sql_plan view, and even some aging SQL execution plans can still be queried through historical data records.

3. Get the execution plan through the script utlxpls.sql or utlxplp.sql

@?\ rdbms\ admin\ utlxpls.sql

Or

@?\ rdbms\ admin\ utlxplp.sql

PLAN_TABLE_OUTPUT

-

Plan hash value: 2937609675

-

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

-

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

| | 1 | SORT AGGREGATE | | 1 |

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

-

Take a look at the contents of these two scripts. This method actually calls the package in method 2.

Get?\ rdbms\ admin\ utlxpls.sql

.

41 * select plan_table_output from table (dbms_xplan.display ('plan_table',null,'serial'))

forty-two

Get?\ rdbms\ admin\ utlxplp.sql

.

40 * select * from table (dbms_xplan.display ())

On how to analyze the implementation plan through explain and dbms_xplan package to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.