月份：2018年10月

Oracle 12CR2查询转换之表扩展

Posted on by YongJing(敬勇)

在表扩展中，对于读取一个分区表部分数据时优化器会生成使用索引的执行计划。基于索引执行计划可以提高性能，但索引维护会增加开锁。在许多数据库中，DML只影响小部分数据。对于频繁更新的表表扩展使用基于索引的执行计划。你可以在以读取为主的数据上创建一个索引，在以频繁变化的数据上消除索引开销。通过这种方式，表扩展在避免索引维护的同时提高了性能。

表扩展工作原理
表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引，那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中，优化器将查询转换为一个union all语句，让一些子查询访问创建索引的分区，一些子查询访问没有创建索引的分区。优化器可以为每个分区选择最有效的访问路径，而不管它是否存在于查询所要访问的所有分区中。

优化器不总是会选择表扩展
.表扩展是基于成本
当数据库访问扩展表的每个分区只会跨越union all的所有分支一次，数据库所连接的任何表都是在分支中被访问。

.语义问题可能导致表扩展无效
例如，一个表出现在一个外连接的右边对于表扩展来说是无效的。

可以使用expand_table hint来控制表扩展。这个hint会覆盖基于成本的决策，但不会覆盖语义检查。

表扩展使用场景
优化器基于查询中出现的谓词条件对每个表必须被访问的分区保持跟踪。分区裁剪能让优化器使用表扩展来生成更有效的执行计划。

下面的例子假设满足以下条件:
.想要对sh.sales表执行星型查询，表sh.sales是基于time_id列进行范围分区的一个分区表。

.想要禁用特定分区上的索引来查看表扩展的优点。

操作步骤如下:
1.以sh用户登录数据库

[oracle@jytest1 ~]$ sqlplus sh/*****@jypdb

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 31 18:09:54 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 24 2018 17:00:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

2.执行以下查询

SQL> select *  from sales where time_id >= to_date('2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and prod_id = 38;
...........
        38       2470 24-DEC-01             2        999             1       31.47
        38      13440 24-DEC-01             2        999             1       31.47
        38        490 28-DEC-01             2        999             1       31.47
        38       8406 28-DEC-01             2        999             1       31.47
        38       1466 31-DEC-01             3        351             1       31.47
        38       4340 31-DEC-01             3        351             1       31.47
        38      10658 31-DEC-01             3        351             1       31.47
        38      11390 31-DEC-01             3        351             1       31.47
        38      23226 31-DEC-01             3        351             1       31.47

4224 rows selected.

3.查询执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 2342444420

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                |      1 |        |       |   224 (100)|          |       |       |   4224 |00:00:00.03 |     334 |
|   1 |  PARTITION RANGE ITERATOR                  |                |      1 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.03 |     334 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |     16 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.02 |     334 |
|   3 |    BITMAP CONVERSION TO ROWIDS             |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.01 |      24 |
|*  4 |     BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    28 |      8 |00:00:00.01 |      24 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("PROD_ID"=38)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   2 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   3 - "SALES".ROWID[ROWID,10], "PROD_ID"[NUMBER,22]
   4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "PROD_ID"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


58 rows selected.

在执行计划中的Pstart与Pstop列，显示了优化器判断只需要访问表的13到28分区。在优化器已经判断了被访问的分区之后，它将考虑所有这些分区上可以使用的索引。在上面的执行计划中，优化器选择使用sales_prod_bix位图索引

4.禁用sales表中sales_1995分区上的索引;

SQL> alter index sales_prod_bix modify partition sales_1995 unusable;

Index altered.

5.再次执行之前的查询语句，然后显示执行计划，可以看到执行计划变成了由两个子查询组成的union all语句，第一个子查询还是对13-28分区使用索引，第二个子查询步骤对应的Pstart与Pstop为invalid，id=11的过滤条件为”PROD_ID”=38,id=9的过滤条件为”SALES”.”TIME_ID”=TO_DATE(‘ 2000-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’)))这个过滤条件是为否的，所以过滤后的记录为0，从对应的A-Rows列也可以看到记录为0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 238952339

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |      1 |        |       |   224 (100)|          |       |       |   4224 |00:00:00.05 |     334 |
|   1 |  VIEW                                         | VW_TE_2        |      1 |   5079 |   431K|   224   (0)| 00:00:01 |       |       |   4224 |00:00:00.05 |     334 |
|   2 |   UNION-ALL                                   |                |      1 |        |       |            |          |       |       |   4224 |00:00:00.05 |     334 |
|   3 |    PARTITION RANGE ITERATOR                   |                |      1 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.03 |     334 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES          |     16 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.02 |     334 |
|   5 |      BITMAP CONVERSION TO ROWIDS              |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.01 |      24 |
|*  6 |       BITMAP INDEX SINGLE VALUE               | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    28 |      8 |00:00:00.01 |      24 |
|*  7 |    FILTER                                     |                |      1 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |
|   8 |     PARTITION RANGE EMPTY                     |                |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |
|  10 |       BITMAP CONVERSION TO ROWIDS             |                |      0 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |
|* 11 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      0 |        |       |            |          |INVALID|INVALID|      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$D0A14387   / VW_TE_2@SEL$0A5B0FFE
   2 - SET$D0A14387
   3 - SET$D0A14387_1
   4 - SET$D0A14387_1 / SALES@SEL$1
   7 - SET$D0A14387_2
   9 - SET$D0A14387_2 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SET$D0A14387_2")
      OUTLINE_LEAF(@"SET$D0A14387_1")
      OUTLINE_LEAF(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$0A5B0FFE")
      OUTLINE(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
      BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
      BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   6 - access("PROD_ID"=38)
   7 - filter(NULL IS NOT NULL)
   9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  11 - access("PROD_ID"=38)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
   2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
   3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
   6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
   7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
  11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


93 rows selected.

6.禁用分区28上的索引(sales_q4_2003),它是查询需要访问的一个分区:

SQL> alter index sales_prod_bix modify partition sales_q4_2003 unusable;

Index altered.

SQL> alter index sales_time_bix modify partition sales_q4_2003 unusable;

Index altered.

通过禁用查询需要访问分区上的索引，查询将不能再使用这些索引。

7.再次执行查询语句，其执行计划如下,执行计划变成了由三个子查询组成的union all语句，相比之前查询多的第三个子查询对表sales的第28个分区执行全表扫描，这里没有索引可用，因为已经禁用28分区上的索引了。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 3857158179

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |      1 |        |       |   225 (100)|          |       |       |   4224 |00:00:00.20 |     334 |     44 |
|   1 |  VIEW                                         | VW_TE_2        |      1 |   5080 |   431K|   225   (0)| 00:00:01 |       |       |   4224 |00:00:00.20 |     334 |     44 |
|   2 |   UNION-ALL                                   |                |      1 |        |       |            |          |       |       |   4224 |00:00:00.19 |     334 |     44 |
|   3 |    PARTITION RANGE ITERATOR                   |                |      1 |   5078 |   143K|   223   (0)| 00:00:01 |    13 |    27 |   4224 |00:00:00.17 |     334 |     44 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES          |     15 |   5078 |   143K|   223   (0)| 00:00:01 |    13 |    27 |   4224 |00:00:00.16 |     334 |     44 |
|   5 |      BITMAP CONVERSION TO ROWIDS              |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.03 |      24 |     16 |
|*  6 |       BITMAP INDEX SINGLE VALUE               | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    27 |      8 |00:00:00.03 |      24 |     16 |
|*  7 |    FILTER                                     |                |      1 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |      0 |
|   8 |     PARTITION RANGE EMPTY                     |                |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|  10 |       BITMAP CONVERSION TO ROWIDS             |                |      0 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |      0 |
|* 11 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      0 |        |       |            |          |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|  12 |    PARTITION RANGE SINGLE                     |                |      1 |      1 |    87 |     2   (0)| 00:00:01 |    28 |    28 |      0 |00:00:00.01 |       0 |      0 |
|* 13 |     TABLE ACCESS FULL                         | SALES          |      1 |      1 |    87 |     2   (0)| 00:00:01 |    28 |    28 |      0 |00:00:00.01 |       0 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$D0A14387   / VW_TE_2@SEL$0A5B0FFE
   2 - SET$D0A14387
   3 - SET$D0A14387_1
   4 - SET$D0A14387_1 / SALES@SEL$1
   7 - SET$D0A14387_2
   9 - SET$D0A14387_2 / SALES@SEL$1
  12 - SET$D0A14387_3
  13 - SET$D0A14387_3 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SET$D0A14387_3")
      OUTLINE_LEAF(@"SET$D0A14387_2")
      OUTLINE_LEAF(@"SET$D0A14387_1")
      OUTLINE_LEAF(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$0A5B0FFE")
      OUTLINE(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
      BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
      BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
      FULL(@"SET$D0A14387_3" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   6 - access("PROD_ID"=38)
   7 - filter(NULL IS NOT NULL)
   9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  11 - access("PROD_ID"=38)
  13 - filter("PROD_ID"=38)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
   2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
   3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
   6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
   7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
  11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
  12 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  13 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


103 rows selected.

Oracle 12CR2查询转换之cursor-duration临时表

Posted on by YongJing(敬勇)

在Oracle12C中为了物化查询的中间结果，Oracle数据库在查询编译时在内存中可能会隐式的创建一个cursor_duration临时表。

Cursor-Duration临时表的作用
复杂查询有时会处理相同查询块多次，这将会增加不必要的性能开锁。为了避免这种问题，Oracle数据库可以在游标生命周期内为查询结果创建临时表并存储在内存中。对于有with子句查询，星型转换与分组集合操作的复杂操作，这种优化增强了使用物化中间结果来优化子查询。在这种方式下，cursor-duration临时表提高了性能并且优化了I/O。

Cursor-Duration临时表工作原理
cursor-definition临时表定义内置在内存中。表定义与游标相关，并且只对执行游标的会话可见。当使用cursor-duration临时表时，数据库将执行以下操作:
1.选择使用cursor-duration临时表的执行计划
2.创建临时表时使用唯一名
3.重写查询引用临时表
4.加载数据到内存中直到没有内存可用，在这种情次品下将在磁盘上创建临时段
5.执行查询，从临时表中返回数据
6.truncate表，释放内存与任何磁盘上的临时段

注意，cursor-duration临时表的元数据只要cursor在内存中就会一直存在于内存中。元数据不会存储在数据字典中这意味着通过数据字典视图将不能查询到，不能显性地删除元数据。上面的场景依赖于可用的内存。对于特定查询，临时表使用PGA内存。

cursor-duration临时表的实现类似于排序。如果没有可用内存，那么数据库将把数据写入临时段。对于cursor-duration临时表，主要差异如下:
.在查询结束时数据库释放内存与临时段而不是当row source不现活动时释放。

.内存中的数据仍然存储在内存中，不像排序数据可能在内存与临时段之间移动。
当数据库使用cursor-duration临时表时，关键字cursor duration memory会出现在执行计划中。

cursor-duration临时表使用场景
一个with查询重复相同子查询多次可能有时使用cursor-duration临时表性能更高，下面的查询使用一个with子句来创建三个子查询块:

SQL> set long 99999
SQL> set linesize 300
SQL> with
  2  q1 as (select department_id, sum(salary) sum_sal from hr.employees group by
  3  department_id),
  4  q2 as (select * from q1),
  5  q3 as (select department_id, sum_sal from q1)
  6  select * from q1
  7  union all
  8  select * from q2
  9  union all
 10  select * from q3;

DEPARTMENT_ID    SUM_SAL
------------- ----------
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400

36 rows selected.

下面是优化转换后的执行计划

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +cost'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
with q1 as (select department_id, sum(salary) sum_sal from hr.employees
group by department_id), q2 as (select * from q1), q3 as (select
department_id, sum_sal from q1) select * from q1 union all select *
from q2 union all select * from q3

Plan hash value: 4087957524

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |     6 (100)|
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9E08D2_620789C |       |            |
|   3 |    HASH GROUP BY                         |                            |    11 |   276   (2)|
|   4 |     TABLE ACCESS FULL                    | EMPLOYEES                  |   100K|   273   (1)|
|   5 |   UNION-ALL                              |                            |       |            |
|   6 |    VIEW                                  |                            |    11 |     2   (0)|
|   7 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
|   8 |    VIEW                                  |                            |    11 |     2   (0)|
|   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
|  10 |    VIEW                                  |                            |    11 |     2   (0)|
|  11 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
----------------------------------------------------------------------------------------------------


26 rows selected.

在上面的执行计划中，在步骤1中的TEMP TABLE TRANSFORMATION指示数据库使用cursor-duration临时表来执行查询。在步骤2中的CURSOR DURATION MEMORY指示数据库使用内存，如果有可用内存，将结果作为临时表SYS_TEMP_0FD9E08D2_620789C来进行存储。如果没有可用内存，那么数据库将临时数据写入磁盘。

Linux 恢复rm -rf命令所删除的达梦数据文件

Posted on by YongJing(敬勇)

LINUX系统中被删除的文件，只要其句柄没有被关闭，可以在/proc//fd中找到其对应的文件副本。其中指打开该文件的进程id。利用该方法，结合OS命令，DM7提供失效文件的恢复方案，下面用示例来演示:
1.创建一个表空间cs，给其创建两个数据文件

SQL> create tablespace cs datafile '/dm_home/dmdba/dmdbms/data/jydm/cs1.dbf' size 128,'/dm_home/dmdba/dmdbms/data/jydm/cs.dbf' size 128;
executed successfully
used time: 00:00:21.941. Execute id is 90.

2.找出达梦服务器进程ID

[root@cs1 jydm]# ps -ef | grep dmserver
dmdba     2467     1  0 10月15 ?       00:05:53 /dm_home/dmdba/dmdbms/bin/dmserver /dm_home/dmdba/dmdbms/data/jydm/dm.ini -noconsole
root     18893 18058  0 21:45 pts/1    00:00:00 grep --color=auto dmserver

3.显示达梦服务器进程所打开的文件列表

[root@cs1 jydm]# ls /proc/2467/fd -l
总用量 0
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 0 -> /dev/null
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 1 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 10 -> /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 11 -> /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 12 -> /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 13 -> /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 14 -> pipe:[21853]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 15 -> pipe:[21853]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 16 -> pipe:[21854]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 17 -> pipe:[21854]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 18 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 19 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 2 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 20 -> pipe:[21856]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 21 -> pipe:[21856]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 22 -> socket:[297043807]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 23 -> /dm_home/dmdba/dmdbms/data/jydm/cs1.dbf
lrwx------ 1 dmdba dinstall 64 10月 25 21:42 24 -> /dm_home/dmdba/dmdbms/data/jydm/cs.dbf
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 3 -> socket:[20041]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 4 -> socket:[20042]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 5 -> /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 6 -> /dm_home/dmdba/dmdbms/data/jydm/dminst.sys
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 7 -> /dm_home/dmdba/dmdbms/data/jydm/TEMP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 8 -> /dm_home/dmdba/dmdbms/data/jydm/jydm01.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 9 -> /dm_home/dmdba/dmdbms/data/jydm/jydm02.log

4.删除表空间cs中的一个数据文件

[root@cs1 jydm]# rm -rf cs.dbf

5.通过调用系统过程SP_FILE_SYS_CHECK()来手动的对表空间失效文件进行检查。

SQL> call SP_FILE_SYS_CHECK();
DMSQL executed successfully
used time: 0.690(ms). Execute id is 93.

6.在表空间cs中创建一个测试表cs，命令执行出错，显示表空间cs中的cs.dbf文件已经被删除了。

SQL> create table cs(cs_id number) tablespace cs;
create table cs(cs_id number) tablespace cs;
[-3430]:tablespace[CS] file[/dm_home/dmdba/dmdbms/data/jydm/cs.dbf] has been deleted.
used time: 31.462(ms). Execute id is 0.

7.调用系统过程SP_TABLESPACE_PREPARE_RECOVER(tablespace_name)准备进行恢复

SQL> call SP_TABLESPACE_PREPARE_RECOVER('CS');
DMSQL executed successfully
used time: 16.121(ms). Execute id is 95.

8.显示达梦服务器进程所打开的文件列表，可以看到cs.dbf文件被标示为deleted了。

[root@cs1 jydm]# ls /proc/2467/fd -l
总用量 0
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 0 -> /dev/null
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 1 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 10 -> /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 11 -> /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 12 -> /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 13 -> /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 14 -> pipe:[21853]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 15 -> pipe:[21853]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 16 -> pipe:[21854]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 17 -> pipe:[21854]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 18 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 19 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 2 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 20 -> pipe:[21856]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 21 -> pipe:[21856]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 22 -> socket:[297043807]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 23 -> /dm_home/dmdba/dmdbms/data/jydm/cs1.dbf
lrwx------ 1 dmdba dinstall 64 10月 25 21:42 24 -> /dm_home/dmdba/dmdbms/data/jydm/cs.dbf (deleted)
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 3 -> socket:[20041]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 4 -> socket:[20042]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 5 -> /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 6 -> /dm_home/dmdba/dmdbms/data/jydm/dminst.sys
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 7 -> /dm_home/dmdba/dmdbms/data/jydm/TEMP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 8 -> /dm_home/dmdba/dmdbms/data/jydm/jydm01.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 9 -> /dm_home/dmdba/dmdbms/data/jydm/jydm02.log

9.使用操作系统的cp命令将文件(cs.dbf)复制到原位置

[root@cs1 jydm]# cp /proc/2467/fd/24 /dm_home/dmdba/dmdbms/data/jydm/cs.dbf
[root@cs1 jydm]# ls -lrt
总用量 1365112
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:12 bak
-rw-r--r-- 1 dmdba dinstall       479 7月  23 22:12 sqllog.ini
-rw-r--r-- 1 dmdba dinstall       633 7月  23 22:13 dm_service.prikey
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:14 HMAIN
-rw-r--r-- 1 dmdba dinstall       908 7月  23 22:14 dminit20180723221249.log
-rw-r--r-- 1 dmdba dinstall       890 7月  23 22:14 dmarch_example.ini
-rw-r--r-- 1 dmdba dinstall      1966 7月  23 22:14 dmdcr_cfg_example.ini
-rw-r--r-- 1 dmdba dinstall       631 7月  23 22:14 dmdcr_example.ini
-rw-r--r-- 1 dmdba dinstall      2070 7月  23 22:14 dmmal_example.ini
-rw-r--r-- 1 dmdba dinstall      1537 7月  23 22:14 dminit_example.ini
-rw-r--r-- 1 dmdba dinstall      1277 7月  23 22:14 dmmonitor_example.ini
-rw-r--r-- 1 dmdba dinstall      1679 7月  23 22:14 dmtimer_example.ini
-rw-r--r-- 1 dmdba dinstall       288 7月  23 22:14 dmmpp_example.ini
-rw-r--r-- 1 dmdba dinstall      1241 7月  23 22:14 dmwatch_example.ini
-rw-r--r-- 1 dmdba dinstall      2146 7月  23 22:14 dmwatcher_example.ini
-rw-r--r-- 1 dmdba dinstall       522 7月  23 22:14 dmwmon_example.ini
-rw-r--r-- 1 dmdba dinstall       636 7月  23 22:14 sqllog_example.ini
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:14 trace
-rw-r--r-- 1 dmdba dinstall        12 7月  23 22:14 rep_conflict.log
-rw-r--r-- 1 dmdba dinstall 157286400 7月  23 22:16 BOOKSHOP.DBF
-rw-r--r-- 1 dmdba dinstall 134217728 7月  23 22:16 DMHR.DBF
-rw-r--r-- 1 dmdba dinstall     40859 8月  20 15:20 dm.ini
-rw-r--r-- 1 dmdba dinstall  10485760 10月 15 12:21 TEMP.DBF
-rw-r--r-- 1 dmdba dinstall 268435456 10月 15 12:21 jydm02.log
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:24 MAIN.DBF
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:43 cs1.dbf
-rw-r--r-- 1 dmdba dinstall      7168 10月 25 21:43 dm.ctl
drwxr-xr-x 2 dmdba dinstall      4096 10月 25 21:43 ctl_bak
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:43 ROLL.DBF
-rw-r--r-- 1 dmdba dinstall  22020096 10月 25 21:59 SYSTEM.DBF
-rw-r--r-- 1 dmdba dinstall 268435456 10月 25 21:59 jydm01.log
-rw-r--r-- 1 root  root     134217728 10月 25 21:59 cs.dbf
-rw-r--r-- 1 dmdba dinstall       220 10月 25 21:59 dminst.sys

10.复制成功后，调用系统过程SP_TABLESPACE_RECOVER(ts_name)完成表空间失效文件的恢复。注意，要保证数据文件正确修复，需要保证在SP_TABLESPACE_PREPARE_RECOVER后进行数据文件的复制。

SQL> call SP_TABLESPACE_RECOVER('CS');
DMSQL executed successfully
used time: 46.532(ms). Execute id is 96.

11.再次在表空间cs中创建测试表cs命令成功执行

SQL> create table cs(cs_id number) tablespace cs;
executed successfully
used time: 36.913(ms). Execute id is 97.

到此，在Linux系统中恢复被删除的表空间文件恢复成功了。

Oracle Linux 7设置中文字符集

Posted on by YongJing(敬勇)

常安装Linux系统本着最简化安装，会默认使用英文字符集，不会安装中文字符集等其他字符。但是在一些必要情况下需要中文的支持，本文将演示如何在Oracle Linux7在安装中文字符集
1、首先使用locale命令看看当前系统所使用的字符集，可以看到是英文

[root@cs2 ~]# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

2、再看看系统是否有安装中文字符集的支持

[root@cs2 ~]# locale -a |grep CN
bo_CN
bo_CN.utf8
ug_CN
ug_CN.utf8
zh_CN
zh_CN.gb18030
zh_CN.gb2312
zh_CN.gbk
zh_CN.utf8

3、如果没有的话使用yum安装中文字符集

yum  install ibus-table-chinese-1.4.6-3.el7.noarch

4、按如下方法修改配置文件并重启系统测试

cat /etc/locale.conf

#LANG="en_US.UTF-8"

LANG="zh_CN.gb2312"

5、重新查看字符集，修改成功，也可以用echo命令输出中文，能正常显示了

[root@cs2 ~]# locale
LANG=zh_CN.gb2312
LC_CTYPE="zh_CN.gb2312"
LC_NUMERIC="zh_CN.gb2312"
LC_TIME="zh_CN.gb2312"
LC_COLLATE="zh_CN.gb2312"
LC_MONETARY="zh_CN.gb2312"
LC_MESSAGES="zh_CN.gb2312"
LC_PAPER="zh_CN.gb2312"
LC_NAME="zh_CN.gb2312"
LC_ADDRESS="zh_CN.gb2312"
LC_TELEPHONE="zh_CN.gb2312"
LC_MEASUREMENT="zh_CN.gb2312"
LC_IDENTIFICATION="zh_CN.gb2312"
LC_ALL=

Oracle 12CR2查询转换之临时表转换

Posted on by YongJing(敬勇)

在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:

SQL> show parameter star_transformation_enabled
star_transformation_enabled          string      FALSE
SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0 number,c1 varchar2(30))的临时表(从执行计划中的 6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]也可以看到)。这些列关联到customers表中的cust_id和cust_city列。

在下面的执行计划中的1,2,3行物化customers子查询到临时表中，在第6行，数据库扫描临时表(代替子查询)来从事实表中构建位图。第27行扫描临时表执行连接返回代替扫描customers表。数据库不用对临时表应用customer表上的过滤条件，因为在物化临时表时已经应用了过滤条件。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 2
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |       |  1177 (100)|          |       |       |     22 |00:00:00.25 |    9080 |     86 |     10 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION         |                            |      1 |        |       |            |          |       |       |     22 |00:00:00.25 |    9080 |     86 |     10 |       |       |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6893_63D6F82 |      1 |        |       |            |          |       |       |      0 |00:00:00.04 |    1535 |      0 |     10 |  1042K|  1042K|          |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |      0 |      0 |       |       |          |
|   4 |   HASH GROUP BY                    |                            |      1 |    877 | 49989 |   754   (1)| 00:00:01 |       |       |     22 |00:00:00.20 |    7538 |     85 |      0 |  1022K|  1022K| 1349K (0)|
|*  5 |    HASH JOIN                       |                            |      1 |  14534 |   809K|   753   (1)| 00:00:01 |       |       |    964 |00:00:00.20 |    7538 |     85 |      0 |  1572K|  1572K| 1696K (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6893_63D6F82 |      1 |   3341 | 50115 |     4   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |     10 |      0 |       |       |          |
|*  7 |     HASH JOIN                      |                            |      1 |  14534 |   596K|   749   (1)| 00:00:01 |       |       |    964 |00:00:00.19 |    7520 |     75 |      0 |  1538K|  1538K| 1685K (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |      1 |    181 |  2896 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|   9 |      VIEW                          | VW_ST_A3F94988             |      1 |  14534 |   369K|   731   (1)| 00:00:01 |       |       |    964 |00:00:00.18 |    7455 |     75 |      0 |       |       |          |
|  10 |       NESTED LOOPS                 |                            |      1 |  14534 |   809K|   706   (1)| 00:00:01 |       |       |    964 |00:00:00.18 |    7455 |     75 |      0 |       |       |          |
|  11 |        PARTITION RANGE SUBQUERY    |                            |      1 |  14534 |   397K|   353   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|    964 |00:00:00.17 |    7271 |     75 |      0 |       |       |          |
|  12 |         BITMAP CONVERSION TO ROWIDS|                            |      2 |  14534 |   397K|   353   (0)| 00:00:01 |       |       |    964 |00:00:00.16 |    7204 |     75 |      0 |       |       |          |
|  13 |          BITMAP AND                |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.16 |    7204 |     75 |      0 |       |       |          |
|  14 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |      15 |      5 |      0 |  1024K|   512K| 4096  (0)|
|  15 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |      15 |      5 |      0 |       |       |          |
|  16 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.01 |       9 |      0 |      0 | 73728 | 73728 |          |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |      2 |        |       |            |          |KEY(SQ)|KEY(SQ)|      2 |00:00:00.02 |       6 |      5 |      0 |       |       |          |
|  19 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |     445 |      9 |      0 |  1024K|   512K|39936  (0)|
|  20 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    181 |00:00:00.02 |     445 |      9 |      0 |       |       |          |
|  21 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |    362 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |      1 |    181 |  2896 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |    362 |        |       |            |          |KEY(SQ)|KEY(SQ)|    181 |00:00:00.02 |     380 |      9 |      0 |       |       |          |
|  24 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.13 |    6744 |     61 |      0 |  1024K|   512K|45056  (0)|
|  25 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    403 |00:00:00.12 |    6744 |     61 |      0 |       |       |          |
|  26 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |   6682 |00:00:00.01 |      18 |      0 |      0 |  5512K|   964K|  174K (0)|
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6893_63D6F82 |      1 |   3341 | 16705 |     4   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |   6682 |        |       |            |          |KEY(SQ)|KEY(SQ)|    403 |00:00:00.10 |    6726 |     61 |      0 |       |       |          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |    964 |      1 |    29 |   378   (0)| 00:00:01 | ROWID | ROWID |    964 |00:00:00.01 |     184 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$D5EF7599
   2 - SEL$F6045C7B
   3 - SEL$F6045C7B / C@SEL$F6045C7B
   6 - SEL$D5EF7599 / T1@SEL$9C741BEB
   8 - SEL$D5EF7599 / T@SEL$1
   9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
  10 - SEL$5E9A798F
  12 - SEL$5E9A798F / S@SEL$1
  17 - SEL$6EE793B7 / CH@SEL$6EE793B7
  22 - SEL$ACF30367 / T@SEL$ACF30367
  27 - SEL$E1F9C76C / T1@SEL$E1F9C76C
  29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$F6045C7B")
      OUTLINE_LEAF(@"SEL$ACF30367")
      OUTLINE_LEAF(@"SEL$6EE793B7")
      OUTLINE_LEAF(@"SEL$E1F9C76C")
      OUTLINE_LEAF(@"SEL$5E9A798F")
      TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1")
      OUTLINE_LEAF(@"SEL$D5EF7599")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$0E028FD0")
      OUTLINE(@"SEL$C3AF6D21")
      ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1")
      OUTLINE(@"SEL$5208623C")
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))
      FULL(@"SEL$D5EF7599" "T"@"SEL$1")
      NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH_AGGREGATION(@"SEL$D5EF7599")
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
      ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F")
      SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION)
      USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")
      SEMIJOIN_DRIVER(@"SEL$E1F9C76C")
      FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")
      SEMIJOIN_DRIVER(@"SEL$6EE793B7")
      FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")
      SEMIJOIN_DRIVER(@"SEL$ACF30367")
      FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")
      SEMIJOIN_DRIVER(@"SEL$F6045C7B")
      END_OUTLINE_DATA
  */

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
   3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40]
   4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22]
   6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30]
   7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22]
   8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
   9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22]
  10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]
  11 - ROWID[ROWID,10]
  12 - ROWID[ROWID,10]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22]
  16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22]
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7]
  21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7]
  24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22]
  26 - (#keys=1) "C0"[NUMBER,22]
  27 - (rowset=256) "C0"[NUMBER,22]
  28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22]
  29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - cbqt star transformation used for this statement
   - this is an adaptive plan

Oracle 12CR2查询转换之星型转换

Posted on by YongJing(敬勇)

星型转换是一种优化转换它用来避免对星型方案中的事实表进行全表扫描。一个星型方案将数据分成事实与维度表。事实是对一个事件比如销售的测量通常是数字。维度是标识事实的分类，比如日期，位置与产品。一个事实表有一个由方案中维度表主键所组成的复合键。维度表实际上充当查找或引用表能让你选择你查询所要请求的值。

星型转换的目的
在连接事实表与维度表时，星型转换可能避免对事实表执行完全扫描。星型转换通过只获取连接到约束维度行记录的相关事实行记录来提高性能。在有些情况下，查询已经在维度表的其它列上有限制性过滤了。过滤组合可以大大减少数据库从事实表中要处理的数据集大小。

星型转换的工作原理
星型转换增加了子查询谓词，叫作位图半连接谓词，关联到约束维度表。当在实际连接列上存在索引时优化器执行转换。通过驱动位图and和or来操作由子查询所提供的键值，数据库只需要从事实表中检索相关行记录。如果维度表上的谓词过滤掉了大量数据，那么星型转换比对事实表完全扫描更有效。

在数据库从事实表中检索相关行记录之后，数据库可能需要使用原始谓词连接这些行记录回维度表。当以下条件满足时数据库可以消除连接回维度表:
.维度表上的所有谓词是半连接子查询谓词的一部分
.从子查询中所选择的列具有唯一性
.维度列不在select列，group by子句中等等

控制星型转换
star_transformation_enabled参数控制着星型转换。这个参数有以下参数值:
.true
优化器通过自动识别事实与约束维度表来执行星型转换。只有转换后的执行计划成本比原始执行计划成本低时优化器才执行星型转换。当物化提高性能时优化器也会尝试临时表转换。

.false(缺省值)
优化器不执行星型转换

.temp_disable
这个值与true相同，只是优化器不会尝试临时表转换

星型转换:应用场景
下面的查询找出1999年Q1和Q2季度在California的所有城市中的总的网络销售额:

SELECT c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,
times t,
customers c,
channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc = 'Internet'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;

示例输出如下:

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 1
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 1865285285

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |       |   957 (100)|          |       |       |     22 |00:00:00.18 |    1812 |       |       |          |
|   1 |  HASH GROUP BY                 |           |      1 |     22 |  1672 |   957   (2)| 00:00:01 |       |       |     22 |00:00:00.18 |    1812 |  1022K|  1022K| 1382K (0)|
|*  2 |   HASH JOIN                    |           |      1 |    138 | 10488 |   956   (2)| 00:00:01 |       |       |    964 |00:00:00.14 |    1812 |  1538K|  1538K| 1588K (0)|
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | TIMES     |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  5 |    HASH JOIN                   |           |      1 |    964 | 57840 |   938   (2)| 00:00:01 |       |       |    964 |00:00:00.11 |    1747 |  1448K|  1448K| 1521K (0)|
|   6 |     MERGE JOIN CARTESIAN       |           |      1 |   3341 |   127K|   426   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1531 |       |       |          |
|*  7 |      TABLE ACCESS FULL         | CHANNELS  |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |       |       |          |
|   8 |      BUFFER SORT               |           |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1522 |   178K|   178K|  158K (0)|
|*  9 |       TABLE ACCESS FULL        | CUSTOMERS |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |       |       |          |
|  10 |     PARTITION RANGE JOIN-FILTER|           |      1 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
|  11 |      TABLE ACCESS FULL         | SALES     |      2 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

从上面的执行中可以看到，在没有使用星型转换时id=11，对表sales执行的是全表扫描。在这个例子中，sales表是事实表，并且其它的表是维度表。sales表对于每一个销售的产品都有一行记录，因此它可以包含上十亿行销售记录。然而，只有少量产品在指定的季度通过网络销售到了California的客户手中。

使用星形转换

SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

从10053跟踪文件中找到的星型转换后的语句如下:

ST: Query after star xformation:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE (T1) */
 T1.C1 CUST_CITY,
 T.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC,
 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
  FROM SH.SALES                       S,
       SH.TIMES                       T,
       SYS.SYS_TEMP_0FD9D6684_63D6F82 T1
 WHERE S.CUST_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE (T1) */
         T1.C0 C0
          FROM SYS.SYS_TEMP_0FD9D6684_63D6F82 T1)
   AND S.CHANNEL_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         CH.CHANNEL_ID ITEM_1
          FROM SH.CHANNELS CH
         WHERE CH.CHANNEL_DESC = 'Internet')
   AND S.TIME_ID = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         T.TIME_ID ITEM_1
          FROM SH.TIMES T
         WHERE T.CALENDAR_QUARTER_DESC = '1999-01'
            OR T.CALENDAR_QUARTER_DESC = '1999-02')
   AND S.TIME_ID = T.TIME_ID
   AND S.CUST_ID = T1.C0
   AND (T.CALENDAR_QUARTER_DESC = '1999-01' OR
       T.CALENDAR_QUARTER_DESC = '1999-02')
 GROUP BY T1.C1, T.CALENDAR_QUARTER_DESC

其执行计划如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 3
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |       |   573 (100)|          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION         |                            |      1 |        |       |            |          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D667F_63D6F82 |      1 |        |       |            |          |       |       |      0 |00:00:01.07 |    1539 |      2 |     10 |  1042K|  1042K|          |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |      1 |    383 |  9958 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |      0 |      0 |       |       |          |
|   4 |   HASH GROUP BY                    |                            |      1 |    542 | 30894 |   150   (1)| 00:00:01 |       |       |     22 |00:00:00.33 |    7538 |     93 |      0 |  1022K|  1022K| 1346K (0)|
|*  5 |    HASH JOIN                       |                            |      1 |   1681 | 95817 |   149   (0)| 00:00:01 |       |       |    964 |00:00:00.31 |    7538 |     93 |      0 |  1572K|  1572K| 1677K (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  5745 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |     10 |      0 |       |       |          |
|*  7 |     HASH JOIN                      |                            |      1 |   1681 | 70602 |   147   (0)| 00:00:01 |       |       |    964 |00:00:00.29 |    7520 |     83 |      0 |  1538K|  1538K| 1686K (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|   9 |      VIEW                          | VW_ST_A3F94988             |      1 |   1685 | 43810 |   129   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  10 |       NESTED LOOPS                 |                            |      1 |   1685 | 96045 |   106   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  11 |        PARTITION RANGE SUBQUERY    |                            |      1 |   1684 | 47167 |    52   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|    964 |00:00:00.22 |    7271 |     83 |      0 |       |       |          |
|  12 |         BITMAP CONVERSION TO ROWIDS|                            |      2 |   1684 | 47167 |    52   (0)| 00:00:01 |       |       |    964 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  13 |          BITMAP AND                |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  14 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.05 |      15 |     10 |      0 |  1024K|   512K| 4096  (0)|
|  15 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.04 |      15 |     10 |      0 |       |       |          |
|  16 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.01 |       9 |      0 |      0 | 73728 | 73728 |          |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |      2 |        |       |            |          |KEY(SQ)|KEY(SQ)|      2 |00:00:00.04 |       6 |     10 |      0 |       |       |          |
|  19 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.03 |     445 |     10 |      0 |  1024K|   512K|39936  (0)|
|  20 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    181 |00:00:00.02 |     445 |     10 |      0 |       |       |          |
|  21 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |    362 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |    362 |        |       |            |          |KEY(SQ)|KEY(SQ)|    181 |00:00:00.01 |     380 |     10 |      0 |       |       |          |
|  24 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.14 |    6744 |     63 |      0 |  1024K|   512K|45056  (0)|
|  25 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    403 |00:00:00.14 |    6744 |     63 |      0 |       |       |          |
|  26 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |   6682 |00:00:00.01 |      18 |      0 |      0 |  5512K|   964K|  174K (0)|
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  1915 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |   6682 |        |       |            |          |KEY(SQ)|KEY(SQ)|    403 |00:00:00.10 |    6726 |     63 |      0 |       |       |          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |    964 |      1 |    29 |    77   (2)| 00:00:01 | ROWID | ROWID |    964 |00:00:00.01 |     184 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$D5EF7599
   2 - SEL$F6045C7B
   3 - SEL$F6045C7B / C@SEL$F6045C7B
   6 - SEL$D5EF7599 / T1@SEL$9C741BEB
   8 - SEL$D5EF7599 / T@SEL$1
   9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
  10 - SEL$5E9A798F
  12 - SEL$5E9A798F / S@SEL$1
  17 - SEL$6EE793B7 / CH@SEL$6EE793B7
  22 - SEL$ACF30367 / T@SEL$ACF30367
  27 - SEL$E1F9C76C / T1@SEL$E1F9C76C
  29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$F6045C7B")
      OUTLINE_LEAF(@"SEL$ACF30367")
      OUTLINE_LEAF(@"SEL$6EE793B7")
      OUTLINE_LEAF(@"SEL$E1F9C76C")
      OUTLINE_LEAF(@"SEL$5E9A798F")
      TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1")
      OUTLINE_LEAF(@"SEL$D5EF7599")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$0E028FD0")
      OUTLINE(@"SEL$C3AF6D21")
      ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1")
      OUTLINE(@"SEL$5208623C")
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))
      FULL(@"SEL$D5EF7599" "T"@"SEL$1")
      NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH_AGGREGATION(@"SEL$D5EF7599")
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
      ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F")
      SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION)
      USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")
      SEMIJOIN_DRIVER(@"SEL$E1F9C76C")
      FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")
      SEMIJOIN_DRIVER(@"SEL$6EE793B7")
      FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")
      SEMIJOIN_DRIVER(@"SEL$ACF30367")
      FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")
      SEMIJOIN_DRIVER(@"SEL$F6045C7B")
      END_OUTLINE_DATA
  */

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
   3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40]
   4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22]
   6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30]
   7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22]
   8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
   9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22]
  10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]
  11 - ROWID[ROWID,10]
  12 - ROWID[ROWID,10]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22]
  16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22]
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7]
  21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7]
  24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22]
  26 - (#keys=1) "C0"[NUMBER,22]
  27 - (rowset=256) "C0"[NUMBER,22]
  28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22]
  29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - cbqt star transformation used for this statement
   - this is an adaptive plan

从Note部分的cbqt star transformation used for this statement信息可知执行了星型转换，从执行计划中的ID=29这个步骤可知对表sales使用了索引扫描而不是全表扫描。对于子查询中的times(第22行),customers(第3行),channels（第17行）表中的每个键值，数据库使用事实表sales(第23,28,18行)上索引检索位图。

Oracle 12CR2查询转换之谓词推送

Posted on by YongJing(敬勇)

在谓词推送中，优化器将包含在查询块中的相关谓词推送到视图查询块中。对于不能合并的视图，这种技术可以提高不能合并视图的执行计划。数据库可以使用推送谓词来访问索引或作为过滤。
例如，假设创建了一个hr.contract_workers表:

SQL> drop table contract_workers;

Table dropped.

SQL> create table contract_workers as (select * from employees where 1=2);

Table created.

SQL> insert into contract_workers values (306, 'bill', 'jones', 'bjones','555.555.2000', '07-jun-02', 'ac_account', 8300, 0,205, 110);

1 row created.

SQL> insert into contract_workers values (406, 'jill', 'ashworth', 'jashworth','555.999.8181', '09-jun-05', 'ac_account', 8300, 0,205, 50);

1 row created.

SQL> insert into contract_workers values (506, 'marcie', 'lunsford', 'mlunsford','555.888.2233', '22-jul-01', 'ac_account', 8300, 0,205, 110);

1 row created.

SQL> commit;

Commit complete.

SQL> create index contract_workers_index on contract_workers(department_id);

Index created.

创建一个视图引用employees与contract_workers表。视图使用了union集合操作:

SQL> create view all_employees_vw as
  2  select employee_id, last_name, job_id, commission_pct, department_id
  3  from employees 
  4  union
  5  select employee_id, last_name, job_id, commission_pct, department_id
  6  from contract_workers;

View created.

然后对视图执行查询:

select last_name from all_employees_vw where department_id = 50;

因为视图是一个union集合操作查询，优化器不能合并视图的查询到主查询块。优化器可以通过推送谓词来转换查询，where子句条件department_id=50，会推送到视图的union集合操作查询中，转换后的等价查询如下:

select last_name
from ( 
select employee_id, last_name, job_id, commission_pct, department_id
from employees
where department_id=50
union
select employee_id, last_name, job_id, commission_pct, department_id
from contract_workers
where department_id=50 );

转换后的查询现在可以考虑对每个查询块使用索引或全表扫描,查询视图语句的执行计划如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  265ccrp674n30, child number 0
-------------------------------------
select last_name from all_employees_vw where department_id = 50

Plan hash value: 1422200799

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |       |       |  1018 (100)|          |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   1 |  VIEW                | ALL_EMPLOYEES_VW |      1 |    100K|  2637K|       |  1018   (1)| 00:00:01 |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   2 |   SORT UNIQUE        |                  |      1 |    100K|  2540K|  3936K|  1018   (1)| 00:00:01 |    100K|00:00:01.18 |     955 |    942 |  8416K|  1135K| 7480K (0)|
|   3 |    UNION-ALL         |                  |      1 |        |       |       |            |          |    100K|00:00:00.76 |     955 |    942 |       |       |          |
|*  4 |     TABLE ACCESS FULL| EMPLOYEES        |      1 |    100K|  2540K|       |   273   (1)| 00:00:01 |    100K|00:00:00.41 |     948 |    942 |       |       |          |
|*  5 |     TABLE ACCESS FULL| CONTRACT_WORKERS |      1 |      1 |    60 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / ALL_EMPLOYEES_VW@SEL$1
   2 - SET$1
   4 - SEL$2 / EMPLOYEES@SEL$2
   5 - SEL$3 / CONTRACT_WORKERS@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "ALL_EMPLOYEES_VW"@"SEL$1")
      FULL(@"SEL$3" "CONTRACT_WORKERS"@"SEL$3")
      FULL(@"SEL$2" "EMPLOYEES"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   4 - filter("DEPARTMENT_ID"=50)
   5 - filter("DEPARTMENT_ID"=50)

从执行计划的Predicate Information部分可以看到4,5操作使用了department_id=50来分别对表employees和contract_workers来进行过滤，也证明了可以将谓词推送到了视图中的查询块。

Oracle 12CR2查询转换之视图合并

Posted on by YongJing(敬勇)

这里的测试数据库版本为12.2.0.1,在视图合并中，优化器代表视图的查询块到包含视国的查询块中。视图合并通过让优化器考虑额外的连接顺序，访问方法与其它转换来提高性能。例如，在一个视图被合并后并且在一个查询块中有多个表，内置在视图中的一个表可以允许优化器使用连接消除来删除视图外部的一个表。

对于特定的简单视图执行视图合并总是会生成更好的执行计划，优化器自动合并视图不会考虑成本。另外的优化器使用成本来进行决定。由于许多原因，包括成本或有效的限制优化器可能选择不执行视图合并。

如果optimizer_secure_view_merging设置为true(缺省值)，那么Oracle数据库将执行检查来确保视图合并与谓词推送不会违反视图创建者的安全意图。为了对特定视图禁用这些额外的安全检查，可以给创建视图的用户授予merge view权限。为了对特定的用户的所有视图禁用额外的安全检查，可以给用户授予merge any view权限。

视图合并之查询块
优化器通过单独的查询块来代表每个嵌套子查询或未合并视图。数据库自下而上优化每一个单独的查询块。因此，数据库首先优化最内部的查询块，生成执行计划的一部分，然后为外部的查询块生成执行计划。解析器展开查询中的每个视图成为了一个单独的查询块。查询块的本质代表了视图定义和视国结果。优化器的一个选项是用来分别分析视图查询块，生成一个视图子执行计划，然后通过使用视图子计划来处理查询中的剩余部分来生成整个查询的执行计划。然而，这种技术因为导致了视图被分别优化而可能产生次优的执行计划。视图合并有些可能会提高性能。

简单视图合并
在简单视图合并中，优化器合并select-project-join视图。例如，查询employees表的一个查询包含一个子查询连接departments与locations表。

因为在视图合并后有额外的连接顺序与访问路径可用所以简单视图合并通常会生成更优化的执行计划。对于简单视图合并不生效，因为:
.视图包含了不允许出现在select-project-join视图中的结构，比如:
-group by
-distinct
-Outer join
-MODEL
-connect by
-Set operators
-Aggregation
–

.视图出现在semijoin或antijoin的右边
.在select列表中包含子查询
.外部查询块包含PL/SQL函数
.视图参与外连接并且不满足视图被合并的几个条件中的任何一个

下面的查询连接hr.employees表与dept_locs_v视图，查询将返回每个部门的街道地址。dept_locs_v视图连接departments与locations表。

SELECT e.first_name,
       e.last_name,
       dept_locs_v.street_address,
       dept_locs_v.postal_code
  FROM employees e,
       (SELECT d.department_id,
               d.department_name,
               l.street_address,
               l.postal_code
          FROM departments d, locations l
         WHERE d.location_id = l.location_id) dept_locs_v
 WHERE dept_locs_v.department_id = e.department_id
   AND e.last_name = 'Smith';

数据库执行上面的查询通过连接departments与locations表来为视图生成行记录，然后用这个结果与employees表连接。因为查询包含视图dept_locs_v，并且这个视图包含两个表，优化器必须使用以下一种连接顺序:
.employees,dept_locs_v(departments,locations)
.employees,dept_locs_v(locations,departments)
.dept_locs_v(departments,locations),employees
.dept_locs_v(locations,departments),employees

连接方法也受到约束。对于以employees表开始的连接顺序基于索引的嵌套循环不合适因为对于视图中的列不存在索引。不使用视图合并，优化器生成的执行计划如下:

-----------------------------------------------------------------
| Id | Operation                   | Name        | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT            |             |      7 (15)|
|* 1 |  HASH JOIN                  |             |      7 (15)|
|  2 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      2  (0)|
|* 3 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1  (0)|
|  4 |  VIEW                       |             |      5 (20)|
|* 5 |   HASH JOIN                 |             |      5 (20)|
|  6 |    TABLE ACCESS FULL        | LOCATIONS   |      2  (0)|
|  7 |    TABLE ACCESS FULL        | DEPARTMENTS |      2  (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

视图合并将视图中的表合并到外部查询块中，并删除内部查询块。在视图合并之后，查询语句如下:

SELECT e.first_name, e.last_name, l.street_address, l.postal_code
FROM employees e, departments d, locations l
WHERE d.location_id = l.location_id
AND d.department_id = e.department_id
AND e.last_name = 'Smith';

因为所有三个表都出现在一个查询块，优化器可以从以下6种连接顺序中选择一种:
.employees, departments, locations
.employees, locations, departments
.departments, employees, locations
.departments, locations, employees
.locations, employees, departments
.locations, departments, employees

连接employees与departments表现在可以使用索引，在视图合并之后，优化器将选择更有效的执行计划，使用嵌套循环连接:

-------------------------------------------------------------------
| Id | Operation                      | Name        | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |       4 (0)|
|  1 |  NESTED LOOPS                  |             |            |
|  2 |   NESTED LOOPS                 |             |       4 (0)|
|  3 |    NESTED LOOPS                |             |       3 (0)|
|  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |       2 (0)|
|* 5 |      INDEX RANGE SCAN          | EMP_NAME_IX |       1 (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |       1 (0)|
|* 7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |       0 (0)|
|* 8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |       0 (0)|
|  9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |       1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

复杂视图合并
在视图合并中，优化器合并包含group by与distinct操作的视图，像简单视图合燕一样，复杂视图合并能让优化器考虑额外的连接顺序和访问路径。

优化器可能会延迟对group by或distinct操作进行评估直到优化器评估完连接之后。延迟这些操作可能提高或损害性能这依赖于数据的特征。如果连接使用过滤，那么延迟这些操作在连接之后可以减少这些操作将要处理的数据集。尽
早评估可以减少后续连接所要处理的数据量或者连接可能增加这些操作所要处理的数据量。优化器使用成本来评估视图合并并且只有当合并操作之后成本更低才会执行。

除了成本之外，由于以下原因成本可能不会执行复杂视图合并操作:
.外部查询表没有rowid或唯一约束列
.视图出现在connect by查询块中
.视图包含grouping sets,rollup或pivot子句
.视图或外部查询块包含model子句

包含group by子句的复杂视图连接下面的查询使用了group by子句

CREATE VIEW cust_prod_totals_v AS
SELECT SUM(s.quantity_sold) total, s.cust_id, s.prod_id
FROM sales s
GROUP BY s.cust_id, s.prod_id;

下面的查询将找出来那些自United States并且买了至少100件毛衣的所有客户:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p, cust_prod_totals_v
WHERE c.country_id = 52790
AND c.cust_id = cust_prod_totals_v.cust_id
AND cust_prod_totals_v.total > 100
AND cust_prod_totals_v.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

cust_prod_totals_v视图满足复杂视图合并的条件。在合并之后，查询语句如下:

SELECT c.cust_id, cust_first_name, cust_last_name, cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater'
GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c.cust_last_name,
c.cust_first_name, c.cust_id
HAVING SUM(s.quantity_sold) > 100;

转换后的查询成本比没转换的查询成本要低，因此优化器选择了合并视图。在没有转换的语句中，group by操作是对视图中的整个sales表进行操作。在转换后的查询中，连接products与customers表过滤掉了sales表中的大部分数据，因此group by操作的成本低。连接成本更高因为sales表没有被减少，但它的成本并不会高很多，因为group by操作不会在原始查询中减少太多的行记录。如果之前的特征发生了改变，合并视图后的成本将不会减少。最终的执行计划不包含视图，如下:

--------------------------------------------------------
| Id | Operation             | Name      | Cost (%CPU)|
--------------------------------------------------------
|  0 | SELECT STATEMENT      |           |  2101 (18)|
|* 1 |  FILTER               |           |           |
|  2 |   HASH GROUP BY       |           |  2101 (18)|
|* 3 |    HASH JOIN          |           |  2099 (18)|
|* 4 |     HASH JOIN         |           |  1801 (19)|
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |     96 (5)|
|  6 |      TABLE ACCESS FULL| SALES     |  1620 (15)|
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |   296 (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

使用distinct的复杂视图连接
下面的查询对cust_prod_v视图使用了distinct操作:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p,
( SELECT DISTINCT s.cust_id, s.prod_id
FROM sales s) cust_prod_v
WHERE c.country_id = 52790
AND c.cust_id = cust_prod_v.cust_id
AND cust_prod_v.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

在决定视图合并后生成的执行计划成本更低，优化器使用以下等价查询来重写原始查询:

SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_email
FROM ( SELECT DISTINCT(c.rowid), p.rowid, s.prod_id, s.cust_id,
c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw;

上面查询的执行计划如下:

-------------------------------------------
| Id | Operation             | Name      |
-------------------------------------------
|  0 | SELECT STATEMENT      |           |
|  1 |  VIEW                 | VM_NWVW_1 |
|  2 |   HASH UNIQUE         |           |
|* 3 |    HASH JOIN          |           |
|* 4 |     HASH JOIN         |           |
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |
|  6 |      TABLE ACCESS FULL| SALES     |
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

上面的执行计划即使在视图合并后还是包含了一个名叫vm_nwvw_1的视图，也叫projection view。在查询中的distinct视图已经合并后出现了projection视图，或者group by视图被合并到外部查询块并且包含group by,having或聚合操作。在后一种情况下，projection视图包含了group by,having和原始外部查询块中的聚合操作。

在上面的projection视图中，当优化器合并视图时，它将distinct操作移动到外部查询块中，并且增加了几个额外列来维护与原始查询的等价性。在这之后，查询可以只从外部查询块中的select列表中选择所需要的列。优化器保留了视图合并的所有好处:一个查询块中的所有表，优化器可能会在最终的连接顺序中变换它们的顺序，并且distinct操作可能会延迟到所有连接完成之后。

Oracle 12c Automatic Reoptimization

Posted on by YongJing(敬勇)

在自动重优化中，优化器会在初次执行后为后续的执行选择一个执行计划。对于所有类型的计划变化自适应查询计划是不可行的。例如，一个使用效率低的连接顺序的查询可以生成次优的执行计划，但自适应查询计划不支持在执行时调整连接顺序。在一个语句第一次执行之后，优化器使用在执行时所收集到的信息来判断使用自动重优化是否可以降低执行成本。如果收集到的执行信息与优化器所评估的存在显著差异，那么优化器会在下一次执行时查找一个可替代的执行计划。

优化器使用在之前执行时所收集到的信息来帮助决定一个替代执行计划。优化器可能会重新优化一个查询多次，每次收集额外的数据并且将来用于改进执行计划。自动重新优化包含以下两种形式:
统计信息反馈
性能反馈

统计信息反馈
一种重新优化形式叫作统计信息反馈(之前叫作基数反馈)，它能对存在错误基数评估的重复查询进行自动改进。优化器可能因为许多原因而造成基数错误评估，比如丢失统计信息，不正确的统计信息或者复杂的谓词条件。使用统计反馈执行重新优化的基本过程如下:
1.在第一次执行一个SQL语句时，优化器会生成一个执行计划。优化器可能在以下情况下对共享SQL区的统计反馈进行监控:
.没有统计信息的表
.一个表上的多个连接或分离过滤谓词
.包含复杂操作的谓词优化器不能精确的计算选择性

2.在第一次执行结束后，优化器将对初次所评估的基数与在执行时执行计划中每步操作所返回的真实行记录进行比较。
如果评估的基数与真实的基数存在显著差异，那么优化器将存储真实的基数给后续的执行所使用。优化器也会创建一个SQL计划指令因此其它的SQL语句可以从初次执行后所获得的信息中获益。

3.如果查询再次执行，那么优化器使用正确的基数评估来代替它的常用评估。optimizer_adaptive_statistics参数不能控制自动重优化的所有功能。这个参数只在自动重优化上下文中控制着对连接基数的统计信息反馈。例如，将
optimizer_adaptive_statistics参数设置为false时将禁用对连接基数错误评估进行统计信息反馈，但它不会禁用对单表基数错误评估进行统计信息反馈。

下面的例子显示了数据库如何使用统计信息反馈来调整不正的基数评估
1.用户oe来对表orders,order_items与product_information表进行查询:

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

2.查询游标中的执行计划显示评估的行记录(E-Rows)远远小于实际行记录(A-Rows)

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID  5sq2n361a0938, child number 2
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 1906736282

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |    269 |00:00:00.07 |    1339 |       |       |          |
|   1 |  NESTED LOOPS         |                     |      1 |      1 |    269 |00:00:00.07 |    1339 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|                     |      1 |      4 |   9135 |00:00:00.03 |      34 |       |       |          |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |      1 |      1 |     87 |00:00:00.01 |      33 |       |       |          |
|   4 |    BUFFER SORT        |                     |     87 |    105 |   9135 |00:00:00.01 |       1 |  4096 |  4096 | 4096  (0)|
|   5 |     INDEX FULL SCAN   | ORDER_PK            |      1 |    105 |    105 |00:00:00.01 |       1 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |   9135 |      1 |    269 |00:00:00.02 |    1305 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

3.用户oe重新执行步骤1中的查询

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

4.查询游标中的执行计划显示对于第二次执行优化器使用了统计信息反馈(Note部分)并且选择了一个不同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------


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

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

Note
-----
   - statistics feedback used for this statement

在上面的输出中，评估的行记录是(269)与实际行记录相匹配。

5.用户oe再执行查询多次(两次以上)

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

6.查询游标中的执行计划显示对于第四次执行优化器使用了与第二次执行相同的执行计划(Note部分)并且选择了一个相同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

 

Note
-----
   - this is an adaptive plan

从上面的输出的Note部分this is an adaptive plan可知使用了自适应执行计划。

性能反馈
重新优化的另一种形式是性能反馈。这个重新优化当parallel_degree_policy参数设置为adaptive时用来帮助重复执行SQL语句自动选择并行度。使用性能反馈执行重新优化的过程如下:
1.当parallel_degree_policy参数设置为adaptive时，在第一次执行SQL语句时，优化器将决定是否以并行方式来执行SQL语句 ，如果使用并行，使用什么样的并行度来执行。优化器选择并行度是基于语句的所评估的性能。对于所有语句会启用额外的性能监控。

2.在第一次执行结束后，优化器将比较以下信息:
.优化器所选择的并行度。
.基于在实际执行语句时所收集的性能统计数据所计算出来的并行度。

如果两个并行度存在显著差异，那么数据库将会标记语句重新解析，并且存储每一次执行的统计数据作为反馈。这种反馈将用来对后续的执行更好的计算并行度。

3.如果查询再次执行，优化器将使用第一次执行所收集到的性能统计数据来更好的决定执行语句的并行度。

注意即使parallel_degree_policy没有被设置为adaptive，统计信息反馈也可能影响对语句的并行度的选择。

Proudly powered by WordPress | Indrajeet by Sus Hill.