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

This article will explain in detail how to use utlxplan, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

These days to help a friend optimize a background JOB, found that the need to use UTLXPLAN. Test and use it yourself, and record it as follows.

Utlxplan is a tool provided by Oracle to view the execution plan of SQL statements. Compared with AUTOTRACE, it is not necessary to actually execute the SQL statement when using UTLXPLAN. For long query statements, use UTLXPLAN to get execution as soon as possible to analyze.

Using UTLXPLAN is based on the statistics collected by the database, so if you want to execute the plan more accurately, you need to be accurate about the statistics, which should be noted.

Here are the steps for using UTLXPLAN.

1. Create a PLAN_TABLE and store the execution plan.

SQL > connect / as sysdba

Connected.

SQL > @ $ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL > grant all on sys.plan_table to public

Grant succeeded.

In 11G, plan_table has been created and synonyms have been created.

SQL > select synonym_name,table_name from dba_synonyms

Where synonym_name='PLAN_TABLE'

SYNONYM_NAME TABLE_NAME

PLAN_TABLE PLAN_TABLE$

Use UTLXPLAN.

SQL > CONNECT scott/oracle

Connected.

SQL >

SQL > explain plan for

2 select *

3 from emp e,dept d

4 where e.deptno=d.deptno

5 and e.enameplate Smit

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 3625962092

-

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

-

PLAN_TABLE_OUTPUT

-

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

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 1 | 58 | 4 (0) | 00:00:01 |

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

| | * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |

| | 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |

-

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

3-filter ("E". "ENAME" = 'SMITH')

4-access ("E". "DEPTNO" = "D". "DEPTNO")

18 rows selected.

As you can see from the execution plan, a full table scan is used to access the EMP table, but there is an obvious filter condition filter ("E". "ENAME" = 'SMITH'), so you can consider creating an index in this column when optimizing the statement. (a small table may walk through the whole table.

Scan).

If multiple users execute the same SQL statement, but their execution plans are different, you can set up STATEMENT_ID to mark the statement at this time. As shown below.

SQL > explain plan set statement_id='TSH' for

2 select *

3 from emp e, dept d

4 where e.deptno=d.deptno

5 and e.enameplate Smit

Explained.

SQL > set line 120

SQL > select * from table (dbms_xplan.display ('PLAN_TABLE','TSH','BASIC'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 3625962092

| | Id | Operation | Name | |

| | 0 | SELECT STATEMENT |

| | 1 | NESTED LOOPS |

| | 2 | NESTED LOOPS |

| | 3 | TABLE ACCESS FULL | EMP |

| | 4 | INDEX UNIQUE SCAN | PK_DEPT |

| | 5 | TABLE ACCESS BY INDEX ROWID | DEPT |

PLAN_TABLE_OUTPUT

-

12 rows selected.

SQL > select * from table (dbms_xplan.display ('PLAN_TABLE','TSH','TYPICAL'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 3625962092

-

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

-

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

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 1 | 58 | 4 (0) | 00:00:01 |

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

| | * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |

| | 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

-

Predicate Information (identified by operation id):

3-filter ("E". "ENAME" = 'SMITH')

4-access ("E". "DEPTNO" = "D". "DEPTNO")

18 rows selected.

Here the table DISPLAY function takes three parameters.

TABLE_NAME:'PLAN_TABLE'

STATEMENT_ID: default is NULL, query the most recent SQL statement, or specify an ID.

FORMAT: controls the level of detail displayed, TYPICAL,BASIC,ALL,SERIAL, (advanced is not recorded in the document). The following is the query result for the ADVANCED parameter.

SQL > select * from table (dbms_xplan.display ('PLAN_TABLE','TSH','ADVANCED'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 3625962092

-

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

-

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

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 1 | 58 | 4 (0) | 00:00:01 |

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

| | * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |

| | 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

-

-

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

1-SEL$1

3-SEL$1 / E@SEL$1

4-SEL$1 / D@SEL$1

5-SEL$1 / D@SEL$1

Outline Data

PLAN_TABLE_OUTPUT

-

/ * +

BEGIN_OUTLINE_DATA

NLJ_BATCHING (@ "SEL$1"D" @ "SEL$1")

USE_NL (@ "SEL$1"D" @ "SEL$1")

LEADING (@ "SEL$1"E" @ "SEL$1"D" @ "SEL$1")

INDEX (@ "SEL$1"D" @ "SEL$1" ("DEPT". "DEPTNO"))

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

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

PLAN_TABLE_OUTPUT

-

DB_VERSION ('11.2.0.1')

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

3-filter ("E". "ENAME" = 'SMITH')

4-access ("E". "DEPTNO" = "D". "DEPTNO")

PLAN_TABLE_OUTPUT

-

Column Projection Information (identified by operation id):

1-(# keys=0) "E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

"E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7]

"E". "SAL" [NUMBER,22], "E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

"D". "DEPTNO" [NUMBER,22], "D". "DNAME" [VARCHAR2,14], "D". "LOC" [VARCHAR2,13]

2-(# keys=0) "E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

"E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7]

"E". "SAL" [NUMBER,22], "E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

PLAN_TABLE_OUTPUT

-

"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

3-"E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10], "E". "JOB" [VARCHAR2,9]

"E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7], "E". "SAL" [NUMBER,22]

"E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

4-"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

5-"D". "DNAME" [VARCHAR2,14], "D". "LOC" [VARCHAR2,13]

61 rows selected.

Let's run the query again. At this point, we create an index on the table EMP.

SQL > create index idx_emp_ename on emp (ename)

Index created.

SQL > explain plan set statement_id='TSH1' for

2 select *

3 from emp e,dept d

4 where e.deptno=d.deptno

5 and e.enameplate Smit

Explained.

SQL > select * from table (dbms_xplan.display ('PLAN_TABLE','TSH1','TYPICAL'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 2977454843

-

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

-

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

| | 1 | NESTED LOOPS | | |

| | 2 | NESTED LOOPS | | 1 | 58 | 3 (0) | 00:00:01 |

| | 3 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00:01 |

| | * 4 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0) | 00:00:01 |

| | * 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

-

| | 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-access ("E". "ENAME" = 'SMITH')

5-access ("E". "DEPTNO" = "D". "DEPTNO")

19 rows selected.

We specify the execution plan of the query STATEMENT_ID='TSH1' in PLAN_TABLE. As you can see, the index is used for access to the table EMP. COST decreased.

On how to use utlxplan to share here, I hope 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.