执行计划实验

1、在本地创建本地命名，连接到讲师机数据。

在 e:\app\administrator\product\11.2.0\dbhome_1\netword\admin\tnsnames.ora

文件中加入以下内容

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.11.209)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

用sqlplus 测试是否可以连接

sqlplus test/test@test

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

2、用test/test 用户登录PL/SQL Developer

打开一个SQL窗口

delete from plan_table;
commit;

explain plan for

SELECT
c2.order_num,c2.customer_id, c2.create_date, c1.product_spec
FROM product c1, order_mat c2, order_detl c3
 where c3.order_id=c2.order_id and c1.product_id=c3.product_id and
 c1.product_spec='QSP3N4'and c2.create_date between
  to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-12-01','yyyy-mm-dd');

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

看相关的执行计划，发现有大量的全部扫描和hash jion
Plan hash value: 3203830265
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    18 |  1314 | 49435   (1)| 00:09:54 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |    18 |  1314 | 49435   (1)| 00:09:54 |
|*  3 |    HASH JOIN                 |             |   122 |  4026 | 49191   (1)| 00:09:51 |
|*  4 |     TABLE ACCESS FULL        | PRODUCT     |     1 |    20 |   694   (1)| 00:00:09 |
|   5 |     TABLE ACCESS FULL        | ORDER_DETL  |    21M|   261M| 48431   (1)| 00:09:42 |
|*  6 |    INDEX UNIQUE SCAN         | ORDER_UK_ID |     1 |       |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID| ORDER_MAT   |     1 |    40 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C1"."PRODUCT_ID"="C3"."PRODUCT_ID")
   4 - filter("C1"."PRODUCT_SPEC"='QSP3N4')
   6 - access("C3"."ORDER_ID"="C2"."ORDER_ID")
   7 - filter("C2"."CREATE_DATE">=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "C2"."CREATE_DATE"<=TO_DATE(' 2009-12-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

3、修改优化器模式为first_rows;

delete from plan_table;
commit;
alter session set optimizer_mode=first_rows;
explain plan for

SELECT
c2.order_num,c2.customer_id, c2.create_date, c1.product_spec
FROM product c1, order_mat c2, order_detl c3
 where c3.order_id=c2.order_id and c1.product_id=c3.product_id and
 c1.product_spec='QSP3N4'and c2.create_date between
  to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-12-01','yyyy-mm-dd');

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 3184491401
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    18 |  1314 | 49369   (1)| 00:09:53 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |    18 |  1314 | 49369   (1)| 00:09:53 |
|   3 |    NESTED LOOPS              |             |   122 |  4026 | 49125   (1)| 00:09:50 |
|*  4 |     TABLE ACCESS FULL        | PRODUCT     |     1 |    20 |   694   (1)| 00:00:09 |
|*  5 |     TABLE ACCESS FULL        | ORDER_DETL  |   122 |  1586 | 48431   (1)| 00:09:42 |
|*  6 |    INDEX UNIQUE SCAN         | ORDER_UK_ID |     1 |       |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID| ORDER_MAT   |     1 |    40 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("C1"."PRODUCT_SPEC"='QSP3N4')
   5 - filter("C1"."PRODUCT_ID"="C3"."PRODUCT_ID")
   6 - access("C3"."ORDER_ID"="C2"."ORDER_ID")
   7 - filter("C2"."CREATE_DATE">=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "C2"."CREATE_DATE"<=TO_DATE(' 2009-12-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

4、再次优化器模式为rule;

delete from plan_table;
commit;
alter session set optimizer_mode=rule;
explain plan for

SELECT
c2.order_num,c2.customer_id, c2.create_date, c1.product_spec
FROM product c1, order_mat c2, order_detl c3
 where c3.order_id=c2.order_id and c1.product_id=c3.product_id and
 c1.product_spec='QSP3N4'and c2.create_date between
  to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-12-01','yyyy-mm-dd');

SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1465182523
 
--------------------------------------------------------
| Id  | Operation                      | Name          |
--------------------------------------------------------
|   0 | SELECT STATEMENT               |               |
|   1 |  NESTED LOOPS                  |               |
|   2 |   NESTED LOOPS                 |               |
|   3 |    NESTED LOOPS                |               |
|   4 |     TABLE ACCESS FULL          | ORDER_DETL    |
|*  5 |     TABLE ACCESS BY INDEX ROWID| ORDER_MAT     |
|*  6 |      INDEX UNIQUE SCAN         | ORDER_UK_ID   |
|*  7 |    INDEX UNIQUE SCAN           | PRODUCT_PK_ID |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | PRODUCT       |
--------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("C2"."CREATE_DATE"<=TO_DATE(' 2009-12-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "C2"."CREATE_DATE">=TO_DATE(' 2009-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("C3"."ORDER_ID"="C2"."ORDER_ID")
   7 - access("C1"."PRODUCT_ID"="C3"."PRODUCT_ID")
   8 - filter("C1"."PRODUCT_SPEC"='QSP3N4')
 
Note
-----
   - rule based optimizer used (consider using cbo)

5、再次优化器模式为为默认值，在语句中中加入提示，比较和第一步的代价

delete from plan_table;
commit;
alter session set optimizer_mode=all_rows;
explain plan for

SELECT /*+ use_hash(c2) */
c2.order_num,c2.customer_id, c2.create_date, c1.product_spec
FROM product c1, order_mat c2, order_detl c3
 where c3.order_id=c2.order_id and c1.product_id=c3.product_id and
 c1.product_spec='QSP3N4'and c2.create_date between
  to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-12-01','yyyy-mm-dd');

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 1587320179
 
----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    18 |  1314 | 59730   (1)| 00:11:57 |
|*  1 |  HASH JOIN          |            |    18 |  1314 | 59730   (1)| 00:11:57 |
|*  2 |   HASH JOIN         |            |   122 |  4026 | 49191   (1)| 00:09:51 |
|*  3 |    TABLE ACCESS FULL| PRODUCT    |     1 |    20 |   694   (1)| 00:00:09 |
|   4 |    TABLE ACCESS FULL| ORDER_DETL |    21M|   261M| 48431   (1)| 00:09:42 |
|*  5 |   TABLE ACCESS FULL | ORDER_MAT  |   298K|    11M| 10538   (1)| 00:02:07 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("C3"."ORDER_ID"="C2"."ORDER_ID")
   2 - access("C1"."PRODUCT_ID"="C3"."PRODUCT_ID")
   3 - filter("C1"."PRODUCT_SPEC"='QSP3N4')
   5 - filter("C2"."CREATE_DATE">=TO_DATE(' 2009-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "C2"."CREATE_DATE"<=TO_DATE(' 2009-12-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))