这篇文章将为大家详细讲解有关怎么使用utlxplan，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。这几天帮一个朋友优化一个后台JOB，发现需要使用UTLXPLAN。自己测试使用，记录如下。utlxplan是Oracle提供的查看SQL语句执行计划的工具，相对于AUTOTRACE使用UTLXPLAN不需要真实执行完该SQL语句，对于长查询的语句选择使用UTLXPLAN尽快获得执行来分析，使用UTLXPLAN是基于数据库收集的统计数据，所以此时如果想获得更准确地执行计划，就需要统计数据的精确了，这点要注意。
下面是使用UTLXPLAN的步骤。
1、创建PLAN_TABLE，存储执行计划。SQL> connect /as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sqlTable created.SQL> grant all on sys.plan_table to public;Grant succeeded.在11G中plan_table已经创建好了，同时创建了同义词同义词。
SQL> select synonym_name,table_name from dba_synonyms
where synonym_name=’PLAN_TABLE’; SYNONYM_NAME TABLE_NAME
—————————— ——————
PLAN_TABLE PLAN_TABLE$使用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.ename=’SMITH’;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.从执行计划可以看出，访问EMP表使用了全表扫描，但是有一个明显的过滤条件filter(“E”.”ENAME”=’SMITH’)，所以在优化该语句时可以考虑在该列创建索引（小表有可能走全表扫描）。
如果有多个用户执行相同的SQL语句，但是二者的执行计划不同，此时可以设置STATEMENT_ID标示该语句。如下所示。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.ename=’SMITH’;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.
这里表DISPLAY函数接受三个参数。
TABLE_NAME:’PLAN_TABLE’
STATEMENT_ID:默认是NULL，查询最近的一个SQL语句，或者指定一个ID。
FORMAT:控制显示的详细程度,TYPICAL,BASIC,ALL,SERIAL,(advanced 没有记录在文档）。以下是ADVANCED参数的查询结果。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$1Outline DataPLAN_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_ROWSPLAN_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.我们再执行一次查询。此时我们在表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.ename=’SMITH’;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.
我们指定查询STATEMENT_ID=’TSH1’在PLAN_TABLE中的执行计划。可以看出，此时表EMP的访问使用了索引。COST下降。关于怎么使用utlxplan就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
云服务器1m的带宽可以多少人用？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。云服务器1m的带宽够多少人用，计算参考公式：支持连接个人 = 服务器带宽/页面尺寸大小；评判的只是：用户从云服务器下载文…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
