Oracle查看SQL执行计划的方式

获取Oracle sql执行计划并查看执行计划，是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看sql执行计划的方式： 基本有以下几种方式：1、通过sql_trace初始化参数2、通过Autotrace3、通过explain plan4、通过dbms_xplan.display_cursor5、通过dbms_xplan.display_awr6、通过10046事件 1、通过explain plan 工具12:24:00 SCOTT@ prod>explain plan for12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788;Explained.Elapsed: 00:00:00.2212:24:16 SCOTT@ prod>select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 46 | 2 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 （0）| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 （0）| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information （identified by operation id）:---------------------------------------------------2 - access（"EMPNO"=7788）14 rows selected.Elapsed: 00:00:01.14 2、通过DBMS_XPLAN.display_cursor查看12:52:37 SCOTT@ prod>desc dbms_xplanFUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLEArgument Name Type In/Out Default？------------------------------ ----------------------- ------ --------SQL_ID VARCHAR2 IN DEFAULTCURSOR_CHILD_NO NUMBER（38） IN DEFAULTFORMAT VARCHAR2 IN DEFAULT如果以scott用户访问需要进行授权：12:31:44 SYS@ prod>select * from dict where upper（table_name）="V$SESSION";TABLE_NAME COMMENTS------------------------------ ----------------------------------------V$SESSION Synonym for V_$SESSIONElapsed: 00:00:00.0912:31:09 SYS@ prod>grant select on V_$SESSION to scott;Grant succeeded.Elapsed: 00:00:00.10 12:43:15 SCOTT@ prod>select * from table（dbms_xplan.display_cursor（null,null,"advanced"））;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------User has no SELECT privilege on V$SQL_PLAN 解决权限不足：12:42:33 SYS@ prod>grant select any table to scott;Grant succeeded.12:43:46 SYS@ prod>show parameter o7NAME TYPE VALUE------------------------------------ ----------- ------------------------------O7_DICTIONARY_ACCESSIBILITY boolean TRUE12:44:54 SYS@ prod>案例：dbms_xplan.display_cursor12:42:45 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ----------7788 SCOTT 3000 20 Elapsed: 00:00:00.08 12:43:15 SCOTT@ prod>select * from table（dbms_xplan.display_cursor（null,null,"all"））;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------SQL_ID bqz9ujgnn4jzu, child number 0-------------------------------------select empno,ename,sal,deptno from emp where empno=7788Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 （100）| || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 （0）| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 （0）| 00:00:01 |--------------------------------------------------------------------------------------Query Block Name / Object Alias （identified by operation id）:-------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------1 - SEL$1 / EMP@SEL$12 - SEL$1 / EMP@SEL$1Predicate Information （identified by operation id）:---------------------------------------------------2 - access（"EMPNO"=7788）Column Projection Information （identified by operation id）:-----------------------------------------------------------1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],"DEPTNO"[NUMBER,22]2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]32 rows selected.Elapsed: 00:00:00.05案例：12:49:10 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ----------7788 SCOTT 3000 20 Elapsed: 00:00:00.0012:50:06 SCOTT@ prod>select * from table（dbms_xplan.display_cursor（null,null,"advanced"））;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------SQL_ID bqz9ujgnn4jzu, child number 0-------------------------------------select empno,ename,sal,deptno from emp where empno=7788Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 （100）| || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 （0）| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 （0）| 00:00:01 |--------------------------------------------------------------------------------------Query Block Name / Object Alias （identified by operation id）:-------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------1 - SEL$1 / EMP@SEL$12 - SEL$1 / EMP@SEL$1Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE（"11.2.0.1"）DB_VERSION（"11.2.0.1"）ALL_ROWSOUTLINE_LEAF（@"SEL$1"）INDEX_RS_ASC（@"SEL$1" "EMP"@"SEL$1" （"EMP"."EMPNO"））END_OUTLINE_DATA*/PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:---------------------------------------------------2 - access（"EMPNO"=7788）Column Projection Information （identified by operation id）:-----------------------------------------------------------1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],"DEPTNO"[NUMBER,22]2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]46 rows selected.Elapsed: 00:00:00.0612:50:21 SCOTT@ prod>这种方法在 SQLPLUS中查看刚执行过的 SQLSQLSQL的执行计划。 -- dbms_xplan.display_cursor传入的前两个参数值均为 null,null第三个参数是 "advanced"第三个参数也可以是 "all"得到的显示结果，少了 "Outline data"部分的内容。 sql>select sql_text,sql_id,hash_value,child_number from v$sql2* where sql_text like "select empno,ename,sal%"SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER-------------------------------------------------- ------------- ---------- ------------select empno,ename,sal,deptno from emp where empno bqz9ujgnn4jzu 3913435130 0=7788Elapsed: 00:00:00.0413:00:25 SCOTT@ prod>select * from table（dbms_xplan.display_cursor（"bqz9ujgnn4jzu",0,"advanced"））; PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------SQL_ID bqz9ujgnn4jzu, child number 0-------------------------------------select empno,ename,sal,deptno from emp where empno=7788Plan hash value: 2949544139--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 （100）| || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 46 | 2 （0）| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 （0）| 00:00:01 |--------------------------------------------------------------------------------------Query Block Name / Object Alias （identified by operation id）:-------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------1 - SEL$1 / EMP@SEL$12 - SEL$1 / EMP@SEL$1Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE（"11.2.0.1"）DB_VERSION（"11.2.0.1"）ALL_ROWSOUTLINE_LEAF（@"SEL$1"）INDEX_RS_ASC（@"SEL$1" "EMP"@"SEL$1" （"EMP"."EMPNO"））END_OUTLINE_DATA*/PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:---------------------------------------------------2 - access（"EMPNO"=7788）Column Projection Information （identified by operation id）:-----------------------------------------------------------1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],"DEPTNO"[NUMBER,22]2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]46 rows selected.Elapsed: 00:00:00.14更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/102085p2.htmVMware+Linux+Oracle 10G RAC全程详细图解 http://www.linuxidc.com/Linux/2011-02/31976.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12

Oracle 归档错误案例Oracle 10g AND Oracle 11g手工建库案例--Oracle 11g相关资讯 Oracle执行sql Oracle查看SQL执行计划

Oracle提高sql执行效率的建议（08/09/2013 09:10:44）

本文评论查看全部评论（0）