oracle执行计划解释

前端之家收集整理的这篇文章主要介绍了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>

原文链接:https://www.f2er.com/oracle/212338.html

猜你在找的Oracle相关文章