Oracle执行计划和优化器

又是新的一周的作业，这周的作业比较难啊。 

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

--------------------------------------------------------------------------------------

| 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 |

--------------------------------------------------------------------------------------

答：这条执行计划是说明，在索引上进行了一次唯一扫描，通过在这个索引上的rowid号访问表上对应的记录，然后给出查询结果。 

SCOTT@orcl> select * from emp where empno=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.请解释这条执行计划，并使用scott用户下的dept表写一条SQL语句，产生下面的执行计划。

--------------------------------------------------------------------

| 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 |

--------------------------------------------------------------------

答：这条执行计划是说在索引上进行了一次全表扫描，然后做一个聚合，然后显示结果。

 

SCOTT@orcl> SCOTT@orcl> select count(*) from emp;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2937609675

 

-------------------------------------------------------------------

| 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_EMP |    14 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------

 

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

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 45 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FAST FULL SCAN| IDX_T | 72666 | 354K| 45 (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

答：这个执行计划是先对全表进行一个快速扫描，然后做一个聚合，然后显示结果。

SCOTT@orcl> select count(*) from t where owner = 'masicong';

Execution Plan

----------------------------------------------------------

Plan hash value: 2279335333

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |    17 |    67   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |          |     1 |    17 |            |          |

|*  2 |   INDEX FAST FULL SCAN| MASICONG |    11 |   187 |    67   (0)| 00:00:01 |

----------------------------------------------------------------------------------

redicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OWNER"='masicong')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

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

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

答：这个执行计划是

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

答：（1）适合all_rows的应该是适合OLAP这种需要还行返回数据量大，行数多的SQL语句。

first_row应该会死适合这种LOTP系统，返回的行数少，数据量小的情况

SCOTT@orcl> select /*+ all_rows */ count(*) from T;

  COUNT(*)

----------

     71485

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2966233522

 

-------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |   286   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| T    | 71485 |   286   (1)| 00:00:04 |

（2）适合first_rows(N)的应该是适合LOTP这种需要还行有固定行数，适合有分页，返回数据量小，行数少的SQL语句。

 

  Select /*+ first_rows(10) */b.x,b.y from

                   (

                   Select /*+ first_rows(10) */ a.*, rownum rnum from

(

         Select /*+ first_rows(20) */ * from t order by x

) a

Where rownum < 20

) b where rnum >=10;