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 得到的信息来决定使用哪个执行计划。
先建两张表
1 2 3 4 5 6 7 8 9 10 11 12 13 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,看执行计划会是什么样
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 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的新参数看到一个自适应计划的每一个步骤
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 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的方式来查询结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 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.
现在对表中的数据做一些处理，看执行计划是否能自适应变更
1 2 3 4 5 6 7 8 9 update t1 set object_name='TS$' where rownum>60000; commit; 59999 rows updated. SYS@ora12c> Commit complete.
现在T1表的查询条件满足的值不再是一条，而是60000条，这里我并没有重新收集统计信息 ，看最新的执行计划会是怎样
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 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，强制硬解析
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 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的每一次执行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 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这个特性就是用来自动做这些索引的裁剪工作。
先构建一个星型转换的场景
新建一张事实表和三张维度表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 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.
收集相关统计信息
1 2 3 4 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还是其他。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 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表的数据,使其所有的数据都满足谓词条件
1 2 3 4 5 6 7 8 9 update DIM3 set DIM3_COD='One'; 10 rows updated. SYS@ora12c> SYS@ora12c> commit; Commit complete.
重新查看新的执行计划
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 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。
