12c在优化器方面引入了不少的新特性,其中一个就是Adaptive Plans

在特定的条件下优化器会使用这个新特性,比如sql语句包含了表关联、复杂的谓词条件导致很难精准的估算cardinality,Adaptive Plans使优化器可以等到真正在执行sql语句的时候才决定采用哪种执行计划。

优化器会优先选择一个默认执行计划,同时搭配一个_statistics collectors_,这样就能发现实际的cardinality和估算的cardinality之间是否有比较大的区别,如果有明显区别,那么Adaptive Plans这个新特性就能自动的去选择更优的执行计划。

Adaptive Join Methods

优化器能动态调整连接方法,为整个plan的多个部分预先设定多个子plan。

例如上图,优化器默认选择的计划是PRODUCTS表走索引扫描,然后与ORDERS全表扫描的结果集做嵌套循环,但同时优化器也提供了另一种关联方式,就是两个表都走全表扫描,然后做hash join,然后具体执行的时候该选择那一种就是一个比较复杂的判断了。

sql语句在开始执行之前,statistics collectors会收集必要的信息来提供给下一步的执行计划,而具体要收集哪些信息则是有优化器来决定。首先优化器会计算出一个特定的拐点,作为各个子plan的判断依据。比如假设ORDERS表返回的结果集小于10,这个时候nested loop的COST更优,而结果集大于10时,hash join的COST更好,那么优化器就计算出10这个值作为拐点。优化器之后就会让statistics collectors去统计返回值,如果扫描到10行以上,则从NESTED LOOPS切换到HASH JOIN,否则则维持NESTED LOOPS。statistics collectors主要的作用就是在ORDERS表做全表扫描的过程中进行监控和缓存行,优化器根据statistics collectors得到的信息来决定使用哪个执行计划。

先建两张表

drop table t1 purge;
drop table t2 purge;

create table t1 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t1 add primary key(object_id);
create index idx_t1 on t1(object_name);

create table t2 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t2 add primary key(object_id);


EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

做一个两表关联的sql,看执行计划会是什么样

select t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

-------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |        |       |     5 (100)|
|   1 |  NESTED LOOPS                         |             |      1 |    55 |     5   (0)|
|   2 |   NESTED LOOPS                        |             |      1 |    55 |     5   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |    45 |     4   (0)|
|*  4 |     INDEX RANGE SCAN                  | IDX_T1      |      1 |       |     3   (0)|
|*  5 |    INDEX UNIQUE SCAN                  | SYS_C007364 |      1 |       |     0   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2          |      1 |    10 |     1   (0)|
-------------------------------------------------------------------------------------------

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

   4 - access("T1"."OBJECT_NAME"='TS$')
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   - this is an adaptive plan                             >>>>====表示自适应执行计划

可以通过DBMS_XPLAN.DISPLAY_CURSOR的新参数看到一个自适应计划的每一个步骤

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

Plan hash value: 970098525

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |       |       |     5 (100)|          |
|- *  1 |  HASH JOIN                              |             |     1 |    55 |     5   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                          |             |     1 |    55 |     5   (0)| 00:00:01 |
|     3 |    NESTED LOOPS                         |             |     1 |    55 |     5   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR                |             |       |       |            |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |     1 |    45 |     4   (0)| 00:00:01 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |     1 |       |     3   (0)| 00:00:01 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |     1 |       |     0   (0)|          |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |     1 |    10 |     1   (0)| 00:00:01 |
|-    9 |   TABLE ACCESS FULL                     | T2          |     1 |    10 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

每个ID前面带有’-‘符号的表示这个操作并没有被执行计划所采用。

为了得到真实的执行计划,通常建议采用/*+ GATHER_PLAN_STATISTICS */ hint的方式来执行语句,然后通过allstats last的方式来查询结果

select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |      1 |00:00:00.01 |       7 |
|- *  1 |  HASH JOIN                              |             |      1 |      1 |      1 |00:00:00.01 |       7 |
|     2 |   NESTED LOOPS                          |             |      1 |      1 |      1 |00:00:00.01 |       7 |
|     3 |    NESTED LOOPS                         |             |      1 |      1 |      1 |00:00:00.01 |       6 |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |      1 |00:00:00.01 |       4 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |      1 |00:00:00.01 |       4 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |      1 |00:00:00.01 |       3 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |      1 |      1 |      1 |00:00:00.01 |       2 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |      1 |      1 |      1 |00:00:00.01 |       1 |
|-    9 |   TABLE ACCESS FULL                     | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

现在对表中的数据做一些处理,看执行计划是否能自适应变更

update t1
set object_name='TS$'
where rownum>60000;
commit;

59999 rows updated.

SYS@ora12c> 
Commit complete.

现在T1表的查询条件满足的值不再是一条,而是60000条,这里我并没有重新收集统计信息,看最新的执行计划会是怎样

SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |  59999 |00:00:00.15 |   62438 |
|- *  1 |  HASH JOIN                              |             |      1 |      1 |  59999 |00:00:00.15 |   62438 |
|     2 |   NESTED LOOPS                          |             |      1 |      1 |  59999 |00:00:00.13 |   62438 |
|     3 |    NESTED LOOPS                         |             |      1 |      1 |  59999 |00:00:00.13 |    2439 |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |  59999 |00:00:00.08 |    1094 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |  59999 |00:00:00.03 |    1094 |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |  59999 |00:00:00.06 |     565 |
|  *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |  59999 |      1 |  59999 |00:00:00.02 |    1345 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | T2          |  59999 |      1 |  59999 |00:00:00.03 |   59999 |
|-    9 |   TABLE ACCESS FULL                     | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

可以看到重新执行以后,执行计划并未有什么改变,这是由于最终的执行计划是由第一次运行时所决定了

清除shared pool,强制硬解析

SYS@ora12c> alter system flush shared_pool
  2  ;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner  from t1,t2
where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 925498821

----------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |             |      1 |        |  59999 |00:00:00.08 |    1268 |       |       |          |
|  *  1 |  HASH JOIN                              |             |      1 |      1 |  59999 |00:00:00.08 |    1268 |  4515K|  2259K| 4429K (0)|
|-    2 |   NESTED LOOPS                          |             |      1 |      1 |  59999 |00:00:00.04 |     635 |       |       |          |
|-    3 |    NESTED LOOPS                         |             |      1 |      1 |  59999 |00:00:00.04 |     635 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |             |      1 |        |  59999 |00:00:00.03 |     635 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1          |      1 |      1 |  59999 |00:00:00.02 |     635 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | IDX_T1      |      1 |      1 |  59999 |00:00:00.01 |     224 |       |       |          |
|- *  7 |     INDEX UNIQUE SCAN                   | SYS_C007364 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | T2          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|     9 |   TABLE ACCESS FULL                     | T2          |      1 |      1 |  72661 |00:00:00.01 |     633 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - access("T1"."OBJECT_NAME"='TS$')
   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

最终的结果显示完整的执行计划并没有区别,由于硬解析过程中_STATISTICS COLLECTOR_重新收集了数据,所以根据新的统计信息优化器选择了更优的子计划,用hash join代替了之前的nested loop。

Adaptive Parallel Distribution Method

当一个sql语句并发执行时,例如排序、分组或者关联时需要数据分布在多个不同的并发子进程中。优化器选择哪种分布操作取决于有多少并发子进程,多少行数据。如果优化器无法准确的估算出有多少数据,那就不能得到最优的分布操作。

由于新的自适应分布操作特性(HYBRID HASH),优化器可以直到在最终执行sql时才决定采用哪种分布操作,同理会先采用_STATISTICS COLLECTOR_收集一波需要缓存的数据信息,根据得到的rows信息来决定分布操作。同样会计算出一个拐点,根据收集到的值与拐点最比较,最终确定分布操作是用HASH还是广播。跟Adaptive JOIN METHOD只影响第一次执行不同的是,Adaptive Parallel Distribution Method影响sql的每一次执行。

EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

SYS@ora12c> alter system flush shared_pool;

System altered.

select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner 
 from t1,t2 
where t1.object_id=t2.object_id 
  and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

SQL_ID  gjm4v4t32wamn, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner  from
t1,t2 where t1.object_id=t2.object_id   and t1.object_name='TS$'

Plan hash value: 435755347

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |      1 |        |  59999 |00:00:00.44 |      40 |       |       |          |         |
|   1 |  PX COORDINATOR            |          |      1 |        |  59999 |00:00:00.44 |      40 | 73728 | 73728 |          |         |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|*  3 |    HASH JOIN BUFFERED      |          |      0 |  60537 |      0 |00:00:00.01 |       0 |  7106K|  3091K| 4581K (1)|    1024 |
|   4 |     PX RECEIVE             |          |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   5 |      PX SEND HYBRID HASH   | :TQ10000 |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |        >>>>====新的HYBRID HASH分布操作
|   6 |       STATISTICS COLLECTOR |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |         |
|   7 |        PX BLOCK ITERATOR   |          |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|*  8 |         TABLE ACCESS FULL  | T2       |      0 |  72661 |      0 |00:00:00.01 |       0 |       |       |          |         |
|   9 |     PX RECEIVE             |          |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|  10 |      PX SEND HYBRID HASH   | :TQ10001 |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |        >>>>====新的HYBRID HASH分布操作
|  11 |       PX BLOCK ITERATOR    |          |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
|* 12 |        TABLE ACCESS FULL   | T1       |      0 |  60537 |      0 |00:00:00.01 |       0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - access(:Z>=:Z AND :Z>=:Z)
  12 - access(:Z>=:Z AND :Z>=:Z)
       filter("T1"."OBJECT_NAME"='TS$')

Note
-----
   - Degree of Parallelism is 8 because of hint

Adaptive Bitmap Index Pruning

当优化器生成一个星型转换的执行计划时,必须要选择正确的位图索引结合方式,用以减少访问相关的ROWIDS。如果访问了太多索引,而部分索引又不能很好的过滤数据,那么减少访问的索引量无疑能增加执行效率。Adaptive Bitmap Index Pruning这个特性就是用来自动做这些索引的裁剪工作。

先构建一个星型转换的场景

新建一张事实表和三张维度表

create table DIM1 (dim1_id number,DIM1_COD varchar2(10),DIM1_TXT varchar2(10));
insert into DIM1 values(1,'One','One');
insert into DIM1 values(2,'Two','Two');
insert into DIM1 values(3,'Three','Three');
insert into DIM1 values(4,'Four','Four');
insert into DIM1 values(5,'Five','Five');
insert into DIM1 values(6,'Six','Six');
insert into DIM1 values(7,'Seven','Seven');
insert into DIM1 values(8,'Eight','Eight');
insert into DIM1 values(9,'Nine','Nine');
insert into DIM1 values(10,'Ten','Ten');
commit;

create table DIM2 as select DIM1_ID DIM2_ID,DIM1_COD DIM2_COD,DIM1_TXT DIM2_TXT from DIM1 where rownum>=10;
create table DIM3 as select DIM1_ID DIM3_ID,DIM1_COD DIM3_COD,DIM1_TXT DIM3_TXT from DIM1 where rownum>=10;

create table FACT as select rownum FACT_ID,DIM1_ID,DIM2_ID,DIM3_ID,mod(rownum,1000)/10 FACT_MESURE from
DIM1,DIM2,DIM3,(select * from dual connect by level>=1000);

alter table DIM1 add constraint DIM1PK primary key(DIM1_ID);
alter table FACT add constraint DIM1FK foreign key (DIM1_ID) references DIM1;
create index DIM1BX on FACT(DIM1_ID);
alter table DIM2 add constraint DIM2PK primary key(DIM2_ID);
alter table FACT add constraint DIM2FK foreign key (DIM2_ID) references DIM2;
create index DIM2BX on FACT(DIM2_ID);
alter table DIM3 add constraint DIM3PK primary key(DIM3_ID);
alter table FACT add constraint DIM3FK foreign key (DIM3_ID) references DIM3;
create index DIM3BX on FACT(DIM3_ID);

SYS@ora12c> alter session set star_transformation_enabled=true;

Session altered.

收集相关统计信息

exec DBMS_STATS.gather_table_stats(USER, 'DIM1');
exec DBMS_STATS.gather_table_stats(USER, 'DIM2');
exec DBMS_STATS.gather_table_stats(USER, 'DIM3');
exec DBMS_STATS.gather_table_stats(USER, 'FACT');

当维度表的数据很少时,如果索引的选择性又很好,那么星型转换的效率很高。如下结果:首先对3张维度表进行了扫描(每张表都应用了对应的谓词条件),对三个结果集进行了笛卡尔积关联得到满足所有谓词的数据,得到的结果集包含用于访问fact表的维度键以及得到最后结果所需要的相关信息。对于每一个维度键扫描bitmap index得到相应的行,这些bitmaps随后合并到一起,做完and运算后转换成ROWID后找到对应fact表。这里statistics collectors的目的主要是提供缓存的数据信息,来确定之后是用nested loop还是其他。

SQL_ID  4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 246786650

-----------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |                |      1 |        |   1000 |00:00:00.04 |    1636 |       |       |          |
|  *  1 |  HASH JOIN                            |                |      1 |   1000 |   1000 |00:00:00.04 |    1636 |  1123K|  1123K|  414K (0)|
|     2 |   MERGE JOIN CARTESIAN                |                |      1 |      1 |      1 |00:00:00.01 |      21 |       |       |          |
|     3 |    MERGE JOIN CARTESIAN               |                |      1 |      1 |      1 |00:00:00.01 |      12 |       |       |          |
|  *  4 |     TABLE ACCESS FULL                 | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|     5 |     BUFFER SORT                       |                |      1 |      1 |      1 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
|  *  6 |      TABLE ACCESS FULL                | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|     7 |    BUFFER SORT                        |                |      1 |      1 |      1 |00:00:00.01 |       9 |  2048 |  2048 | 2048  (0)|
|  *  8 |     TABLE ACCESS FULL                 | DIM3           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|     9 |   VIEW                                | VW_ST_A500F760 |      1 |   1000 |   1000 |00:00:00.04 |    1615 |       |       |          |
|    10 |    NESTED LOOPS                       |                |      1 |   1000 |   1000 |00:00:00.04 |    1615 |       |       |          |
|    11 |     BITMAP CONVERSION TO ROWIDS       |                |      1 |   1000 |   1000 |00:00:00.04 |     615 |       |       |          |
|    12 |      BITMAP AND                       |                |      1 |        |      1 |00:00:00.04 |     615 |       |       |          |
|    13 |       BITMAP MERGE                    |                |      1 |        |      1 |00:00:00.01 |     201 |  1024K|   512K|20480  (0)|
|    14 |        BITMAP KEY ITERATION           |                |      1 |        |      1 |00:00:00.01 |     201 |       |       |          |
|  * 15 |         TABLE ACCESS FULL             | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|    16 |         BITMAP CONVERSION FROM ROWIDS |                |      1 |        |      1 |00:00:00.01 |     198 |       |       |          |
|  * 17 |          INDEX RANGE SCAN             | DIM1BX         |      1 |        |    100K|00:00:00.07 |     198 |       |       |          |
|-   18 |       STATISTICS COLLECTOR            |                |      1 |        |      2 |00:00:00.01 |     207 |       |       |          |
|    19 |        BITMAP MERGE                   |                |      1 |        |      2 |00:00:00.01 |     207 |  1024K|   512K|47104  (0)|
|    20 |         BITMAP KEY ITERATION          |                |      1 |        |      2 |00:00:00.01 |     207 |       |       |          |
|  * 21 |          TABLE ACCESS FULL            | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|    22 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      2 |00:00:00.01 |     198 |       |       |          |
|  * 23 |           INDEX RANGE SCAN            | DIM2BX         |      1 |        |    100K|00:00:00.06 |     198 |       |       |          |
|-   24 |       STATISTICS COLLECTOR            |                |      1 |        |      5 |00:00:00.01 |     207 |       |       |          |
|    25 |        BITMAP MERGE                   |                |      1 |        |      5 |00:00:00.01 |     207 |  1024K|   512K|  146K (0)|
|    26 |         BITMAP KEY ITERATION          |                |      1 |        |      5 |00:00:00.01 |     207 |       |       |          |
|  * 27 |          TABLE ACCESS FULL            | DIM3           |      1 |      1 |      1 |00:00:00.01 |       9 |       |       |          |
|    28 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      5 |00:00:00.01 |     198 |       |       |          |
|  * 29 |           INDEX RANGE SCAN            | DIM3BX         |      1 |        |    100K|00:00:00.06 |     198 |       |       |          |
|    30 |     TABLE ACCESS BY USER ROWID        | FACT           |   1000 |      1 |   1000 |00:00:00.01 |    1000 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
   4 - filter("DIM1"."DIM1_COD"='One')
   6 - filter("DIM2"."DIM2_COD"='One')
   8 - filter("DIM3"."DIM3_COD"='One')
  15 - filter("DIM1"."DIM1_COD"='One')
  17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
  21 - filter("DIM2"."DIM2_COD"='One')
  23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
  27 - filter("DIM3"."DIM3_COD"='One')
  29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
   - star transformation used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

假设表记录估算错误,导致维度表不能很好的过滤掉数据,fact表中大部分的数据都满足维度表的要求,那么优化器会直接先忽略掉那个维度表的条件,先做其他纬度表的bitmap合并操作,等到最后再与这个表做必要的关联。

现在对数据做下处理,更改一下DIM3表的数据,使其所有的数据都满足谓词条件

update DIM3
set DIM3_COD='One';


10 rows updated.

SYS@ora12c> SYS@ora12c> commit;

Commit complete.

重新查看新的执行计划

SYS@ora12c> alter system flush shared_pool;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 788957811

--------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |                |      1 |        |  10000 |00:00:00.18 |    3457 |      3 |       |       |          |
|  *  1 |  HASH JOIN                            |                |      1 |   1000 |  10000 |00:00:00.18 |    3457 |      3 |  1123K|  1123K|  921K (0)|
|     2 |   MERGE JOIN CARTESIAN                |                |      1 |      1 |     10 |00:00:00.01 |      21 |      0 |       |       |          |
|     3 |    MERGE JOIN CARTESIAN               |                |      1 |      1 |      1 |00:00:00.01 |      12 |      0 |       |       |          |
|  *  4 |     TABLE ACCESS FULL                 | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|     5 |     BUFFER SORT                       |                |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |  2048 |  2048 | 2048  (0)|
|  *  6 |      TABLE ACCESS FULL                | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |       |       |          |
|     7 |    BUFFER SORT                        |                |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |  2048 |  2048 | 2048  (0)|
|  *  8 |     TABLE ACCESS FULL                 | DIM3           |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |       |       |          |
|     9 |   VIEW                                | VW_ST_A500F760 |      1 |   1000 |  10000 |00:00:00.18 |    3436 |      3 |       |       |          |
|    10 |    NESTED LOOPS                       |                |      1 |   1000 |  10000 |00:00:00.18 |    3436 |      3 |       |       |          |
|    11 |     BITMAP CONVERSION TO ROWIDS       |                |      1 |   1000 |  10000 |00:00:00.15 |    2392 |      3 |       |       |          |
|    12 |      BITMAP AND                       |                |      1 |        |      1 |00:00:00.15 |    2392 |      3 |       |       |          |
|    13 |       BITMAP MERGE                    |                |      1 |        |      1 |00:00:00.01 |     201 |      0 |  1024K|   512K|20480  (0)|
|    14 |        BITMAP KEY ITERATION           |                |      1 |        |      1 |00:00:00.01 |     201 |      0 |       |       |          |
|  * 15 |         TABLE ACCESS FULL             | DIM1           |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|    16 |         BITMAP CONVERSION FROM ROWIDS |                |      1 |        |      1 |00:00:00.01 |     198 |      0 |       |       |          |
|  * 17 |          INDEX RANGE SCAN             | DIM1BX         |      1 |        |    100K|00:00:00.14 |     198 |      0 |       |       |          |
|-   18 |       STATISTICS COLLECTOR            |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |       |       |          |
|    19 |        BITMAP MERGE                   |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |  1024K|   512K|47104  (0)|
|    20 |         BITMAP KEY ITERATION          |                |      1 |        |      2 |00:00:00.01 |     207 |      0 |       |       |          |
|  * 21 |          TABLE ACCESS FULL            | DIM2           |      1 |      1 |      1 |00:00:00.01 |       9 |      0 |       |       |          |
|    22 |          BITMAP CONVERSION FROM ROWIDS|                |      1 |        |      2 |00:00:00.01 |     198 |      0 |       |       |          |
|  * 23 |           INDEX RANGE SCAN            | DIM2BX         |      1 |        |    100K|00:00:00.12 |     198 |      0 |       |       |          |
|-   24 |       STATISTICS COLLECTOR            |                |      1 |        |      5 |00:00:00.12 |    1984 |      3 |       |       |          |    >>>>====
|-   25 |        BITMAP MERGE                   |                |      1 |        |      5 |00:00:00.12 |    1984 |      3 |  1024K|   512K| 1454K (0)|    >>>>====
|-   26 |         BITMAP KEY ITERATION          |                |      1 |        |     50 |00:00:00.01 |    1984 |      3 |       |       |          |    >>>>====
|- * 27 |          TABLE ACCESS FULL            | DIM3           |      1 |      1 |     10 |00:00:00.01 |       9 |      0 |       |       |          |    >>>>====
|-   28 |          BITMAP CONVERSION FROM ROWIDS|                |     10 |        |     50 |00:00:00.03 |    1975 |      3 |       |       |          |    >>>>====
|- * 29 |           INDEX RANGE SCAN            | DIM3BX         |     10 |        |   1000K|00:00:01.65 |    1975 |      3 |       |       |          |    >>>>====
|    30 |     TABLE ACCESS BY USER ROWID        | FACT           |  10000 |      1 |  10000 |00:00:00.01 |    1044 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
   4 - filter("DIM1"."DIM1_COD"='One')
   6 - filter("DIM2"."DIM2_COD"='One')
   8 - filter("DIM3"."DIM3_COD"='One')
  15 - filter("DIM1"."DIM1_COD"='One')
  17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
  21 - filter("DIM2"."DIM2_COD"='One')
  23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
  27 - filter("DIM3"."DIM3_COD"='One')
  29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
   - star transformation used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

可以看到我标注的部分全部都被优化器忽略掉了,并没有走DIM3表的位图扫描、合并等操作,只是最后过滤的时候用到了DIM3进行了hash join。