<<Oracle数据库性能优化艺术（第五期）>> 第4周 优化器

1.请解释这条执行计划，并使用scott用户下的emp表写一条SQL语句，产生下面的执行计划。<br>
--------------------------------------------------------------------------------------<br>
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |<br>
--------------------------------------------------------------------------------------<br>
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |<br>
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |<br>
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |<br>
--------------------------------------------------------------------------------------<br>

解释:

首先扫描主键PK_EMP, 然后根据索引返回的rowid扫描表EMP,最后返回数据.

SQL> alter table emp add constraint pk_emp primary key(empno);

Table altered.

SQL> set autotrace traceonly exp;
SQL> select * from emp where empno=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     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"=1)

SQL>

--EOF--

2.请解释这条执行计划，并使用scott用户下的dept表写一条SQL语句，产生下面的执行计划。<br>

--------------------------------------------------------------------<br>
| Id  | Operation        | Name    | Rows  | Cost (%CPU)| Time     |<br>
--------------------------------------------------------------------<br>
|   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 |<br>
|   1 |  SORT AGGREGATE  |         |     1 |            |          |<br>
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |     1   (0)| 00:00:01 |<br>
--------------------------------------------------------------------<br>

解释:

首先全扫描(顺序读)索引/主键PK_DEPT,然后汇总,最后返回.

SQL> alter table dept add constraint pk_dept primary key(deptno);

Table altered.

SQL> select count(*) from dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 3051237957

--------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |         |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL>

--EOF--

3.请解释这条执行计划，尝试写一条SQL语句产生如下的执行计划。<br>

-------------------------------------------------------------------------------<br>
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |<br>
-------------------------------------------------------------------------------<br>
|   0 | SELECT STATEMENT      |       |     1 |     5 |    45   (0)| 00:00:01 |<br>
|   1 |  SORT AGGREGATE       |       |     1 |     5 |            |          |<br>
|   2 |   INDEX FAST FULL SCAN| IDX_T | 72666 |   354K|    45   (0)| 00:00:01 |<br>
-------------------------------------------------------------------------------<br>

解释:

首先多块读扫描索引IDX_T,然后汇总,最后返回.

SQL> create index idx_t on dept(loc);

Index created.

SQL> alter table dept modify loc not null;

Table altered.

SQL> select /*+ index_ffs(dept,idx_t) */ count(*) from dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_T |     4 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL>

--EOF--

4.请解释这条执行计划，并使用scott用户下的emp，dept表做关联查询，产生下面的执行计划。<br>

----------------------------------------------------------------------------------------<br>
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |<br>
----------------------------------------------------------------------------------------<br>
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |<br>
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |<br>
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |<br>
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |<br>
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |<br>
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |<br>
----------------------------------------------------------------------------------------<br>

解释:

首先索引全扫描(顺序读)PK_DEPT,然后根据rowid扫描DEPT表,接着全表扫描EMP表并排序,然后MERGE JOIN表DEPT的结果集和表EMP的结果集,最后将结果返回.

SQL> select /*+ use_merge(e,d) */ d.* from emp e, dept d where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   602 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   602 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |   120 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement

SQL>

--EOF--

5.各写一个适合用all_rows和first_rows(n) 优化器模式的SQL语句，并给出各自的执行计划。<br>

all_rows适用于最大吞吐量情况,如对全表进行各种汇总求和的情况:

SQL> select /*+ all_rows */ sum(deptno) from dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 3051237957

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |         |     1 |    13 |            |          |
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |    52 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL>

first_rows适用于最快响应时间的情况,如网站程序的分页返回:

SQL>select /*+ first_rows(10) */ sum(deptno) from dept where deptno>10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829232711

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| PK_DEPT |     3 |    39 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO">10)

Note
-----
   - dynamic sampling used for this statement

SQL>

--EOF--