oracle执行计划解释

(1).explain plan命令(不准)

explain plan for select语句

select * from table(dbms_xplan.display);

(2).DBMS_XPLAN包

select * from table(dbms_xplan.display);      --必须在缓存，结果不准

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));    --必须在缓存，准确

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));     --必须在缓存，查找sql_id，准确(select sql_text,sql_id,hash_value,child_cursor from v$sql where sql_text like 'select * from abc' );

select * from table(dbms_xplan.display_awr('sql_id'));     --在AWR报告中查找sql_id，准确

(3).autotrace开关(sqlplus使用)

set autotrace on

(4).10046事件，执行计划最准确

SQL> oradebug event 10046 trace name context forever,level 12      --开启10046事件

SQL> select * from abc where a >100;          --执行sql语句

SQL> oradebug tracefile_name              --查看生成的文件

SQL> oradebug event 10046 trace name context off          --关闭10046事件

[root@localhost ~]$ tkprof 10000.trc   20000.trc                 --将trc文件转换格式，可读性很高 

设置执行计划方法:

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>