[翻译]Oracle 12c优化器白皮书(完整版WORD文档已上传)

dingjun123

newkid 发表于 2014-4-19 02:23
达梦把这个叫平坦化。ORACLE会不会判断行间缓存的情况？如果有行间缓存，标量子查询有时候更合算。
下周 ...

有缓存的，包括FILTER,关联UPDATE。。。这些带：B1。。。的绑定变量的，都有缓存

当缓存起作用，可能效果比JOIN好。。。

newkid

完整版WORD附件已上传到一楼，修订了错别字。

newkid

下面就来试验“部分连接取值”特性（图29）。
在11G中设置了如下数据：

DESC SH.CUSTOMERS;

Name                         Null?    Type
---------------------------- -------- --------------------
CUST_ID                      NOT NULL NUMBER
CUST_FIRST_NAME              NOT NULL VARCHAR2(20)
CUST_LAST_NAME               NOT NULL VARCHAR2(40)
CUST_GENDER                  NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH           NOT NULL NUMBER(4)
CUST_MARITAL_STATUS                   VARCHAR2(20)
CUST_STREET_ADDRESS          NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE             NOT NULL VARCHAR2(10)
CUST_CITY                    NOT NULL VARCHAR2(30)
CUST_CITY_ID                 NOT NULL NUMBER
CUST_STATE_PROVINCE          NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID       NOT NULL NUMBER
COUNTRY_ID                   NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER       NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL                     VARCHAR2(30)
CUST_CREDIT_LIMIT                     NUMBER
CUST_EMAIL                            VARCHAR2(30)
CUST_TOTAL                   NOT NULL VARCHAR2(14)
CUST_TOTAL_ID                NOT NULL NUMBER
CUST_SRC_ID                           NUMBER
CUST_EFF_FROM                         DATE
CUST_EFF_TO                           DATE
CUST_VALID                            VARCHAR2(1)

jsu@JSU> SELECT COUNT(*) FROM SH.CUSTOMERS;

  COUNT(*)
----------
     55500
     
CREATE TABLE CUSTOMERS AS SELECT * FROM SH.CUSTOMERS;

ALTER TABLE CUSTOMERS ADD CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUST_ID);

CREATE TABLE ORDERS (order_id NUMBER, order_date DATE NOT NULL, amount NUMBER NOT NULL, cust_id NUMBER NOT NULL);

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_PK PRIMARY KEY (ORDER_ID);

ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_CUST_ID_FK FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS(CUST_ID);

INSERT INTO ORDERS
SELECT n,SYSDATE-1000+n/10,n*10, cust_id
  FROM (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL<=10000)
      ,(SELECT ROWNUM RN,cust_id FROM CUSTOMERS WHERE ROWNUM<=10000)
WHERE n=rn;
   
COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'CUSTOMERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ORDERS');

SET AUTOT TRACEONLY;

SELECT DISTINCT order_id
  FROM orders o, customers c
WHERE o.cust_id = c.cust_id
       AND order_id < 2400;


Execution Plan
----------------------------------------------------------
Plan hash value: 286617579

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |  2399 |  9596 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| ORDERS_PK |  2399 |  9596 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("ORDER_ID"<2400)

可见，不但没有SORT UNIQUE, 连HASH JOIN也没有，甚至都没有扫描CUSTOMER表！因为我们有外键，CBO知道这个连接不会改变行数。所以我说这个例子举得不好，DISTINT 以及JOIN都是多余的。

把ORDERS.CUST_ID上的非空约束去掉：

ALTER TABLE ORDERS MODIFY CUST_ID NULL;

再来看计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 1275100350

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  2399 | 19192 |    13   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS |  2399 | 19192 |    13   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ORDER_ID"<2400 AND "O"."CUST_ID" IS NOT NULL)
   
CBO还是很聪明，没有JOIN, 只是多了个AND "O"."CUST_ID" IS NOT NULL的过滤谓词，因为外键还在。

把外键去掉：
ALTER TABLE ORDERS DROP CONSTRAINT ORDERS_CUST_ID_FK;

Execution Plan
----------------------------------------------------------
Plan hash value: 2416930460

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  2399 | 31187 |    14   (8)| 00:00:01 |
|   1 |  HASH UNIQUE        |              |  2399 | 31187 |    14   (8)| 00:00:01 |
|   2 |   NESTED LOOPS      |              |  2399 | 31187 |    13   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| ORDERS       |  2399 | 19192 |    13   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| CUSTOMERS_PK |     1 |     5 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   3 - filter("ORDER_ID"<2400)
   4 - access("O"."CUST_ID"="C"."CUST_ID")

这时没有外键，不能确定ORDERS.CUST_ID是一个有效的ID, CBO只好做了连接，用的是NESTED LOOPS。不知道为什么还有HASH UNIQUE, 明明不需要去重。如果把这个完全多余的 DISTINCT 拿掉，这个HASH UNIQUE就消失了。CBO还是不够聪明。


去掉CUSTOMERS上的唯一索引(此时DISTINCT就不再多余了)：

ALTER TABLE CUSTOMERS DROP CONSTRAINT CUSTOMERS_PK;

终于得到图29中的计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 358993716

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  2399 | 31187 |   420   (1)| 00:00:06 |
|   1 |  HASH UNIQUE        |           |  2399 | 31187 |   420   (1)| 00:00:06 |
|*  2 |   HASH JOIN         |           |  2399 | 31187 |   419   (1)| 00:00:06 |
|*  3 |    TABLE ACCESS FULL| ORDERS    |  2399 | 19192 |    13   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| CUSTOMERS | 55500 |   270K|   405   (1)| 00:00:05 |
---------------------------------------------------------------------------------

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

   2 - access("O"."CUST_ID"="C"."CUST_ID")
   3 - filter("ORDER_ID"<2400)

把索引加回去，只是不再唯一：
CREATE INDEX CUSTOMERS_IDX ON CUSTOMERS(CUST_ID);


Execution Plan
----------------------------------------------------------
Plan hash value: 3716455140

----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |  2399 | 31187 |    50   (4)| 00:00:01 |
|   1 |  HASH UNIQUE           |               |  2399 | 31187 |    50   (4)| 00:00:01 |
|*  2 |   HASH JOIN            |               |  2399 | 31187 |    49   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL   | ORDERS        |  2399 | 19192 |    13   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| CUSTOMERS_IDX | 55500 |   270K|    35   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("O"."CUST_ID"="C"."CUST_ID")
   3 - filter("ORDER_ID"<2400)

此时的全表扫描变成了全索引扫描，这是把索引当作瘦表来用了。

把整个试验在12C中做一遍，当把ORDERS上的外键拿掉之后，计划变成：

Execution Plan
----------------------------------------------------------
Plan hash value: 2121488774

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  2399 | 31187 |    13   (0)| 00:00:01 |
|   1 |  HASH UNIQUE        |              |  2399 | 31187 |    13   (0)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI |              |  2399 | 31187 |    13   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| ORDERS       |  2399 | 19192 |    13   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| CUSTOMERS_PK | 55500 |   270K|     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   3 - filter("ORDER_ID"<2400)
   4 - access("O"."CUST_ID"="C"."CUST_ID")

11g中的NESTED LOOPS变成了NESTED LOOPS SEMI, 这就是新特性在起作用了。随后的两个测试中的HASH JOIN 也变成了HASH JOIN SEMI。但是那个HASH UNIQUE还在，难道CBO没看到ORDER_ID是唯一的吗？

helonten

学习

yyp2009

dongxucs

占位学习

smithpaul

Thanks for your sharing

zcs0237

lhqmccn

newkid 发表于 2014-4-17 21:38
乱用成语，这两个意思是反的。

应该是篡改成语，改后意思应该是一样的

流浪的野狼

太牛X了，速度不是一般的快啊