oracle执行计划解释
|
设置执行计划方法: set autotrace off 默认值,关闭执行计划 set autotrace on explain 只显示执行计划 set autotrace on statistics 只显示执行计划统计信息 set autotrace on 显示执行计划和统计信息 set autotrace traceonly 与on相似,不显示语句的执行结果 1.oracle数据库访问数据的方法 (1).全表扫描(Full Table Scans,FTS) (2).通过隐藏rowid字段扫描(Table Access by ROWID) (3).索引范围扫描(Index range Scan) (4).索引唯一扫描(Index unique scan) (5).索引全扫描(Index full scan) (6).索引快速扫描(Index fast full scan) 1).全表扫描 SQL>set autotrace on SQL> select * from emp where comm=1400; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("COMM"=1400) Access:表示条件的值将会影响数据的访问路径(表和索引) Filter:表示条件的值不会影响数据的访问路径,只起过滤作用 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets --读了多少个数据块 7 consistent gets --逻辑读(从buffer cache中读取的block数量) 0 physical reads --物理读(从磁盘中读取的block数量) 0 redo size --产生多少redo日志 1028 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 processed --影响多少行 SQL> 2).rowid字段扫描 SQL> select * from emp where rowid='AAAVREAAEAAAACXAAN'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 Execution Plan ---------------------------------------------------------- Plan hash value: 1116584662 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 1022 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 processed SQL> 3).索引范围扫描
SQL> create index in_sal on emp(sal); Index created. SQL> select * from emp where sal < 1000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 Execution Plan ---------------------------------------------------------- Plan hash value: 3065173639 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IN_SAL | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAL"<1000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1115 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) 2 rows processed SQL> (4).索引唯一扫描 SQL> select * from emp where empno=7566; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 Execution Plan ---------------------------------------------------------- Plan hash value: 2949544139 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7566) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 892 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> (5).索引全扫描 SQL> select * from emp where sal >2000 order by empno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4170700152 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 380 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 10 | 380 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SAL">2000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1263 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) 6 rows processed SQL> 6)索引快速扫描 2.使用explain plan for查看执行计划 SQL> explain plan for select * from emp where sal > 3000; Explained. SQL> seletc * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 3065173639 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 266 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 7 | 266 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IN_SAL | 7 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- 2 - access("SAL">3000) 14 rows selected. SQL> （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |