Oracle的SQL执行计划

1、日常开发活动中，有时候需要对oracle执行计划进行监控，以此来调优程序和数据库方面的性能。常用方法有以下几种：一、通过PL/SQL Dev工具1、直接File-New-Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，

2、所以使用工具的时候推荐使用1方法。注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。二、通过sqlplus1、一般情况都是本机链接远程服务器，所以命令如下：sqlplus user/pwdserviceName此处的serviceName为tnsnames.ora中定义的命名空间。2、执行set autotrace on，然后执行sql语句，会列出以下信息：。（省略一些信息）统计信息- 1 recursive calls （归调用次数） 0 db block

3、 gets 2 consistent gets 0 physical reads （物理读执行SQL的过程中，从硬盘上读取的数据块个数） 0 redo size (重做数执行SQL的过程中，产生的重做日志的大小) 358 bytes sent via SQL*Net to client 366 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 在内存中发生的排序 0 sorts (disk) 在硬盘中发生的排序 1 rows processed省略部分信息和通过

4、PL/SQL Dev工具查看执行计划的信息一样，下面的统计信息是更详细的。判断SQL效率高低不关通过时间来衡量，还应该通过执行SQL执行状态里面的逻辑读的数量逻辑读=（db block gets+ consistent gets）1、SET AUTOTRACE ON EXPLAIN（set autot on exp)SQLPLUS的命令，在执行SQL语句的同时显示执行计划，设置EXP(LAIN)的目的是只显示执行计划而不显示统计信息.。2、SQLexplain plan for select ;SQLselect * from table(dbms_xplan.display);执行了set

5、autotrace on explain语句之后，接下来的查询、插入、更新、删除语句就会显示执行计划，直到执行“set autotrace off;”语句。如果是设置了set autotrace on，除了会显示执行计划之外，还会显示一些有用的统计信息。执行EXPLAIN PLAN FOR 可以只显示执行计划,然后执行如下查询SQL select * from table(dbms_xplan.display);如：SQL explain plan for select * from emp where deptno=20;Explained.SQL select * from table(d

6、bms_xplan.display);PLAN_TABLE_OUTPUT-Plan hash value: 3956160932-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 5 | 150 | 3 (0)| 00:00:01 |* 1 | TABLE ACCESS FULL| EMP | 5 | 150 | 3 (0)| 00:00:01 |-Predicate Information (identified by operation id):-PLAN_TABL

7、E_OUTPUT- 1 - filter(DEPTNO=20)13 rows selected.3、删除和收集表的统计信息SQLexec dbms_stats.delete_table_stats(USER,表);(删除表的统计信息）SQLexec dbms_stats.gather_table_stats(USER,表,METHOD_OPT=FOR ALL COLUMNS SIZE 100)（收集表的统计信息）4、AUTOTRACE的几个常用选项set autotrace off - 不生成autotrace 报告，这是缺省模式set autotrace on explain - autot

8、race只显示优化器执行路径报告set autotrace on statistics - 只显示执行统计信息set autotrace on - 包含执行计划和统计信息set autotrace traceonly - 同set autotrace on，但是不显示查询输(1). set autotrace on explain; -只显示执行计划SQL set autotrace on explain;SQLselect count(*) from dba_objects;COUNT(*)31820Execution Plan-0 SELECT STATEMENT Optimizer=CH

9、OOSE1 0 SORT (AGGREGATE)2 1 VIEW OF DBA_OBJECTS3 2 UNION-ALL4 3 FILTER5 4 TABLE ACCESS (BY INDEX ROWID) OF OBJ$6 5 NESTED LOOPS7 6 TABLE ACCESS (FULL) OF USER$8 6 INDEX (RANGE SCAN) OF I_OBJ2 (UNIQUE)9 4 TABLE ACCESS (BY INDEX ROWID) OF IND$10 9 INDEX (UNIQUE SCAN) OF I_IND1 (UNIQUE)11 3 NESTED LOOP

10、S12 11 TABLE ACCESS (FULL) OF USER$13 11 INDEX (RANGE SCAN) OF I_LINK1 (NON-UNIQUE)(2). set autotrace on statistics;-只显示统计信息SQL set autotrace on statistics;SQL select count(*) from dba_objects;COUNT(*)- 31820Statistics- 0recursive calls 0db block gets 25754consistent gets 0physical reads 0redo size

11、383bytes sent via SQL*Net to client 503bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed(3). set autotrace traceonly;-同set autotrace on 只是不显示查询输出SQL set autotrace traceonly;SQL select count(*) from dba_objects;Execution Plan- 0 SE

12、LECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 VIEW OF DBA_OBJECTS 3 2 UNION-ALL 4 3 FILTER 5 4 TABLE ACCESS (BY INDEX ROWID) OF OBJ$ 6 5 NESTED LOOPS 7 6 TABLE ACCESS (FULL) OF USER$8 6 INDEX (RANGE SCAN) OF I_OBJ2 (UNIQUE) 9 4 TABLE ACCESS (BY INDEX ROWID) OF IND$10 9 INDEX (UNIQUE SCAN)

13、 OF I_IND1 (UNIQUE)11 3 NESTED LOOPS12 11 TABLE ACCESS (FULL) OF USER$13 11 INDEX (RANGE SCAN) OF I_LINK1 (NON-UNIQUE)Statistics- 0recursive calls 0db block gets 25754consistent gets 0physical reads 0redo size 383bytes sent via SQL*Net to client 503bytes received via SQL*Net from client 2SQL*Net rou

14、ndtrips to/from client 0sorts (memory) 0sorts (disk) 1rows processed(4).set autotrace traceonly explain;-比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。SQL set autotrace traceonly explain;SQL select * from dba_objects;已用时间:00: 00: 00.00Execution Plan- 0 SELECT STATEME

15、NT Optimizer=CHOOSE 1 0 VIEW OF DBA_OBJECTS 2 1 UNION-ALL 3 2 FILTER 4 3 TABLE ACCESS (BY INDEX ROWID) OF OBJ$ 5 4 NESTED LOOPS 6 5 TABLE ACCESS (FULL) OF USER$ 7 5 INDEX (RANGE SCAN) OF I_OBJ2 (UNIQUE) 8 3 TABLE ACCESS (BY INDEX ROWID) OF IND$ 9 8 INDEX (UNIQUE SCAN) OF I_IND1 (UNIQUE)10 2 TABLE ACCESS (BY INDEX ROWID) OF LINK$11 10 NESTED LOOPS12 11 TABLE ACCESS (FULL) OF USER$13 11 INDEX (RANGE SCAN) OF I_LINK1 (NON-UNIQUE