oracle中如何查看SQL的执行计划方法

这篇文章主要介绍了oracle中如何查看SQL的执行计划方法，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。Oracle查看SQL执行计划的方式 获取oraclesql执行计划并查看执行计划，是掌握和判断数据库性能的基本技巧。下面案例介绍了多种查看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.142、通过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.1012: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 20Elapsed: 00:00:00.0812: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$1 2 – 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 20Elapsed: 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$1 2 – SEL$1 / EMP@SEL$1Outline Data————-/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1’) DB_VERSION(‘11.2.0.1’) ALL_ROWS OUTLINE_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 empnobqz9ujgnn4jzu 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$1 2 – SEL$1 / EMP@SEL$1Outline Data————-/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(‘11.2.0.1’) DB_VERSION(‘11.2.0.1’) ALL_ROWS OUTLINE_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.143、通过DBMS_XPLAN.display_awr使用方法dbms_xplan.display_cursor能够得到sql执行计划的前提条件是该SQL还在共享池中，而如果执行计划的前提条件是该SQL还在共享池中，而如果执行计划的前提条件是该 还在共享池中，而如果SQLSQLSQL的执行计划已经被刷出共享池，那么只要该SQL的执行计划被ORACLE采集到 AWR Repository中， 就可以用该方法来查看 。12:24:00 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;13:10:56 SYS@ prod>exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.13:11:37 SYS@ prod>alter system flush shared_pool;System altered.Elapsed: 00:00:00.3113:16:28 SYS@ prod>select * from table(dbms_xplan.display_cursor(‘bqz9ujgnn4jzu’,0,’advanced’));PLAN_TABLE_OUTPUT——————————————————————————————————SQL_ID: bqz9ujgnn4jzu, child number: 0 cannot be found13:21:53 SYS@ prod>desc dbms_xplanFUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLEArgument Name Type In/Out Default?—————————— ———————– —— ——–SQL_ID VARCHAR2 INPLAN_HASH_VALUE NUMBER(38) IN DEFAULTDB_ID NUMBER(38) IN DEFAULTFORMAT VARCHAR2 IN DEFAULT13:30:15 SCOTT@ prod>select * from table(dbms_xplan.display_awr(‘bqz9ujgnn4jzu’));PLAN_TABLE_OUTPUT——————————————————————————————————SQL_ID bqz9ujgnn4jzu——————–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 |————————————————————————————–14 rows selected.Elapsed: 00:00:00.304、通过10046 事件查看1）查看当前session：13:29:52 SYS@ prod>grant alter session to scott;Grant succeeded.13:44:31 SCOTT@ prod>alter session set events ‘10046 trace name context forever,level 12’;Session altered.13:44:53 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO———- ———- ———- ———- 7788 SCOTT 3000 20Elapsed: 00:00:00.1013:45:51 SCOTT@ prod>alter session set events ‘10046 trace name context off’;Session altered.Elapsed: 00:00:00.03[oracle@rh7 ~]$ ls -lt /u01/app/oracle/diag/rdbms/prod/prod/trace/|moretotal 1256-rw-r—– 1 oracle oinstall 27801 May 16 13:46 prod_ora_4995.trc-rw-r—– 1 oracle oinstall 177 May 16 13:46 prod_ora_4995.trm-rw-r—– 1 oracle oinstall 1122 May 16 13:34 prod_j000_5188.trc-rw-r—– 1 oracle oinstall 59 May 16 13:34 prod_j000_5188.trm……[oracle@rh7 ~]$ tkprof /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_4995.trc /home/oracle/emp_0416.txt sys=noTKPROF: Release 11.2.0.1.0 – Development on Fri May 16 13:47:41 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.[oracle@rh7 ~]$ cat emp_0416.txtSQL ID: bqz9ujgnn4jzuPlan Hash: 2949544139select empno,ename,sal,deptnofromemp where empno=7788call count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 1 0.07 0.09 1 66 0 0Execute 1 0.00 0.00 0 0 0 0Fetc免费主机域名h 2 0.00 0.00 0 2 0 1——- —— ——– ———- ———- ———- ———- ———-total 4 0.07 0.09 1 68 0 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 84 Rows Row Source Operation——- ————————————————— 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1) 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited—————————————- Waited ———- ————db file sequential read 1 0.00 0.00SQL*Net message to client 2 0.00 0.00SQL*Net message from client 2 19.13 19.13********************************************************************************2）查看其它session：14:12:23 SYS@ prod>select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME———- ———- —————————— 1 5 SYS42 9SCOTT6 rows selected.Elapsed: 00:00:00.1014:12:47 SYS@ prod>desc dbms_monitorPROCEDURE SESSION_TRACE_DISABLEArgument Name Type In/Out Default?—————————— ———————– —— ——–SESSION_ID BINARY_INTEGER IN DEFAULTSERIAL_NUM BINARY_INTEGER IN DEFAULTPROCEDURE SESSION_TRACE_ENABLEArgument Name Type In/Out Default?—————————— ———————– —— ——–SESSION_ID BINARY_INTEGER IN DEFAULTSERIAL_NUM BINARY_INTEGER IN DEFAULTWAITS BOOLEAN IN DEFAULTBINDS BOOLEAN IN DEFAULTPLAN_STAT VARCHAR2 IN DEFAULT14:13:11 SCOTT@ prod>select sid from v$mystat where rownum=1; SID———- 4214:13:41 SYS@ prod>exec dbms_monitor.SESSION_TRACE_ENABLE(42,9,waits=>true,binds=>true);PL/SQL procedure successfully completed.Elapsed: 00:00:00.1014:13:25 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7369; EMPNO ENAME SAL DEPTNO———- ———- ———- ———- 7369 SMITH 800 20Elapsed: 00:00:00.0314:14:29 SYS@ prod>exec dbms_monitor.SESSION_TRACE_disable(42,9);PL/SQL procedure successfully completed.Elapsed: 00:00:00.02[oracle@rh7 ~]$ cat emp_0416.txtSQL ID: fyydvbdw2uq6qPlan Hash: 2949544139select empno,ename,sal,deptnofromemp where empno=7369call count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 0 2 0 1——- —— ——– ———- ———- ———- ———- ———-total 4 0.00 0.00 0 2 0 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 84 Rows Row Source Operation——- ————————————————— 1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1) 1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73202)Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited—————————————- Waited ———- ————SQL*Net message to client 2 0.00 0.00SQL*Net message from client 1 0.00 0.005、通过autotrace查看SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
SET AUTOTRACE TRACEONLY EXPLAIN
–使用SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY时，目标SQL已经被执行过，所以在SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY 的情况下能看到目标SQL
的实际消耗情况。
–使用SET AUTOTRACE TRACEONLY EXPLAIN时，如果执行的是SELECT语句，则该SQL并没有被执行，但如果执行的是DML语句，情况就不一样了，此时的DML语句是会
被ORACLE执行的。
–需要特别说明的是，虽然使用SET AUTOTRACE命令所得到的执行计划可能是不准确的，因为SET AUTOTRACE命令所显示的执行计划都是源自于explain plan 命令。案例：15:32:11 SYS@ prod>conn scott/tigerConnected.15:32:17 SCOTT@ prod>set autotrace on15:32:21 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO———- ———- ———- ———- 7788 SCOTT 3000 20Elapsed: 00:00:00.03Execution Plan———————————————————-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)Statistics———————————————————- 168 recursive calls 0 db block gets 38 consistent gets 0 physical reads 0 redo size 736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed15:32:26 SCOTT@ prod>set autotrace trace15:32:49 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788;Elapsed: 00:00:00.02Execution Plan———————————————————-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)Statistics———————————————————- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed15:34:59 SCOTT@ prod>set autotrace on exp15:35:04 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO———- ———- ———- ———- 7788 SCOTT 3000 20Elapsed: 00:00:00.02Execution Plan———————————————————-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)15:35:12 SCOTT@ prod>set autotrace on statis15:35:20 SCOTT@ prod>select empno,ename,sal,deptno from emp where empno=7788; EMPNO ENAME SAL DEPTNO———- ———- ———- ———- 7788 SCOTT 3000 20Elapsed: 00:00:00.03Statistics———————————————————- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed15:35:26 SCOTT@ prod>感谢你能够认真阅读完这篇文章，希望小编分享的“oracle中如何查看SQL的执行计划方法”这篇文章对大家有帮助，同时也希望大家多多支持云技术，关注云技术行业资讯频道，更多相关知识等着你来学习!

相关推荐: sql loader中control.ctl文件配置

这期内容当中小编将会给大家带来有关sql loader中control.ctl文件配置，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 load datainfile ‘D:sqlldrj免费主机域名y_mx_az.txt’a…