Oracle查询执行计划

Question

我对 Oracle 查询的执行计划有点困惑。这是在 IBM AIX 6.1 平台上的 Oracle 企业版 11.2.0.1.0 中。我有一个表 TEST1(100 万行)和另一个表 TEST2(50,000 行)。两个表都有相同的列。有一个 View 创建为这 2 个表的联合。我在这个 View 上触发一个查询，在 WHERE 子句中有一个索引列。我能找到的是没有使用索引，结果是全表扫描。通过对查询稍作修改，它开始使用索引。我想知道这种特殊变化如何导致计划变化。

请在下面找到完整的 DDL + DML。我给出了简化的例子。实际模式和要求要复杂一些。事实上，所讨论的查询是由 OCI 代码生成器动态构建和执行的。我在这里的目的不是获得替代方案，而是真正了解计划变更背后的逻辑推理(我是应用程序程序员而不是数据库管理员)。非常感谢您的帮助。

DROP TABLE TEST1 CASCADE CONSTRAINTS ;  
DROP TABLE TEST2 CASCADE CONSTRAINTS ;  

CREATE TABLE TEST1  
(  
    ID      NUMBER(20)     NOT NULL,  
    NAME    VARCHAR2(40),  
    DAY     NUMBER(20)  
)  
PARTITION BY RANGE (DAY)  
(  
    PARTITION P001 VALUES LESS THAN (2),  
    PARTITION P002 VALUES LESS THAN (3),  
    PARTITION P003 VALUES LESS THAN (4),  
    PARTITION P004 VALUES LESS THAN (5),  
    PARTITION P005 VALUES LESS THAN (6),  
    PARTITION P006 VALUES LESS THAN (7),  
    PARTITION P007 VALUES LESS THAN (8),  
    PARTITION P008 VALUES LESS THAN (9),  
    PARTITION P009 VALUES LESS THAN (10),  
    PARTITION P010 VALUES LESS THAN (11),  
    PARTITION P011 VALUES LESS THAN (12),  
    PARTITION P012 VALUES LESS THAN (13),  
    PARTITION P013 VALUES LESS THAN (14),  
    PARTITION P014 VALUES LESS THAN (15),  
    PARTITION P015 VALUES LESS THAN (16),  
    PARTITION P016 VALUES LESS THAN (17),  
    PARTITION P017 VALUES LESS THAN (18),  
    PARTITION P018 VALUES LESS THAN (19),  
    PARTITION P019 VALUES LESS THAN (20),  
    PARTITION P020 VALUES LESS THAN (21),  
    PARTITION P021 VALUES LESS THAN (22),  
    PARTITION P022 VALUES LESS THAN (23),  
    PARTITION P023 VALUES LESS THAN (24),  
    PARTITION P024 VALUES LESS THAN (25),  
    PARTITION P025 VALUES LESS THAN (26),  
    PARTITION P026 VALUES LESS THAN (27),  
    PARTITION P027 VALUES LESS THAN (28),  
    PARTITION P028 VALUES LESS THAN (29),  
    PARTITION P029 VALUES LESS THAN (30),  
    PARTITION P030 VALUES LESS THAN (31)  
) ;  

CREATE INDEX IX_ID on TEST1 (ID) INITRANS 4 STORAGE(FREELISTS 16) LOCAL  
(  
    PARTITION P001,  
    PARTITION P002,  
    PARTITION P003,  
    PARTITION P004,  
    PARTITION P005,  
    PARTITION P006,  
    PARTITION P007,  
    PARTITION P008,  
    PARTITION P009,  
    PARTITION P010,  
    PARTITION P011,  
    PARTITION P012,  
    PARTITION P013,  
    PARTITION P014,  
    PARTITION P015,  
    PARTITION P016,  
    PARTITION P017,  
    PARTITION P018,  
    PARTITION P019,  
    PARTITION P020,  
    PARTITION P021,  
    PARTITION P022,  
    PARTITION P023,  
    PARTITION P024,  
    PARTITION P025,  
    PARTITION P026,  
    PARTITION P027,  
    PARTITION P028,  
    PARTITION P029,  
    PARTITION P030  
) ;  

CREATE TABLE TEST2  
(  
    ID      NUMBER(20)      PRIMARY KEY   NOT NULL,  
    NAME    VARCHAR2(40),  
    DAY     NUMBER(20)  
) ;  

CREATE OR REPLACE VIEW TEST_V AS  
SELECT  
    ID, NAME, DAY  
FROM  
    TEST1  
UNION  
SELECT  
    ID, NAME, DAY  
FROM  
    TEST2 ;  

begin  
    for count in 1..1000000  
    loop  
        insert into test1 values(count, 'John', mod(count, 30) + 1) ;  
    end loop ;  
end ;  
/  

begin  
    for count in 1000000..1050000  
    loop  
        insert into test2 values(count, 'Mary', mod(count, 30) + 1) ;  
    end loop ;  
end ;  
/  

commit ;  

set lines 300 ;  
set pages 1000 ;  

-- Actual query  
explain plan for  
    SELECT Key FROM  
    (  
        WITH recs AS  
        (  
            SELECT * FROM TEST_V WHERE ID = 70000  
        )  
        (  
            SELECT 1 AS Key FROM recs WHERE NAME = 'John'  
        )  
        UNION  
        (  
            SELECT 2 AS Key FROM recs WHERE NAME = 'Mary'  
        )  
    ) ;  

select * from table(dbms_xplan.display()) ;  

PLAN_TABLE_OUTPUT  
------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                   | Name                         | Rows  | Bytes  | TempSpc | Cost (%CPU)  |    Time  | Pstart | Pstop |  
------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |                              | 1611K | 4721K  |         |   13559 (1)  | 00:02:43 |        |       |  
|   1 |  VIEW                       |                              | 1611K | 4721K  |         |   13559 (1)  | 00:02:43 |        |       |  
|   2 |   TEMP TABLE TRANSFORMATION |                              |       |        |         |              |          |        |       |  
|   3 |    LOAD AS SELECT           | SYS_TEMP_0FD9D6610_34D3B6C   |       |        |         |              |          |        |       |  
|*  4 |     VIEW                    | TEST_V                       | 805K  | 36M    |         |   10403 (1)  | 00:02:05 |        |       |  
|   5 |      SORT UNIQUE            |                              | 805K  | 36M    |   46M   |   10403 (8)  | 00:02:05 |        |       |  
|   6 |       UNION-ALL             |                              |       |        |         |              |          |        |       |  
|   7 |        PARTITION RANGE ALL  |                              | 752K  | 34M    |         |   721 (1)    | 00:00:09 |    1   |   30  |  
|   8 |     TABLE ACCESS FULL       | TEST1                        | 752K  | 34M    |         |   721 (1)    | 00:00:09 |    1   |   30  |  
|   9 |        TABLE ACCESS FULL    | TEST2                        | 53262 | 2496K  |         |   68 (0)     | 00:00:01 |        |       |  
|  10 |    SORT UNIQUE              |                              | 1611K | 33M    |   43M   |   13559 (51) | 00:02:43 |        |       |  
|  11 |     UNION-ALL               |                              |       |        |         |              |          |        |       |  
|* 12 |      VIEW                   |                              | 805K  | 16M    |         |   1429 (1)   | 00:00:18 |        |       |  
|  13 |       TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_34D3B6C   | 805K  | 36M    |         |   1429 (1)   | 00:00:18 |        |       |  
|* 14 |      VIEW                   |                              | 805K  | 16M    |         |   1429 (1)   | 00:00:18 |        |       |  
|  15 |       TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_34D3B6C   | 805K  | 36M    |         |   1429 (1)   | 00:00:18 |        |       |  
------------------------------------------------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):  
---------------------------------------------------  
   4 - filter("ID"=70000)  
  12 - filter("NAME"='John')  
  14 - filter("NAME"='Mary')  


-- Modified query (only change is absence of outermost SELECT)  
explain plan for  
    WITH recs AS  
    (  
        SELECT * FROM TEST_V WHERE ID = 70000  
    )  
    (  
        SELECT 1 AS Key FROM recs WHERE NAME = 'John'  
    )  
    UNION  
    (  
        SELECT 2 AS Key FROM recs WHERE NAME = 'Mary'  
    ) ;  

select * from table(dbms_xplan.display()) ;  

PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |  
-----------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                         |                            |   4   |  88   |    6 (67)   | 00:00:01 |        |       |  
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |             |          |        |       |  
|   2 |   LOAD AS SELECT                         | SYS_TEMP_0FD9D6611_34D3B6C |       |       |             |          |        |       |  
|   3 |    VIEW                                  | TEST_V                     |   2   |  96   |    4 (50)   | 00:00:01 |        |       |  
|   4 |     SORT UNIQUE                          |                            |   2   |  96   |    4 (75)   | 00:00:01 |        |       |  
|   5 |      UNION-ALL                           |                            |       |       |             |          |        |       |  
|   6 |       PARTITION RANGE ALL                |                            |   1   |  48   |    1 (0)    | 00:00:01 |    1   |   30  |  
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID | TEST1                      |   1   |  48   |    1 (0)    | 00:00:01 |    1   |   30  |  
|*  8 |     INDEX RANGE SCAN                     | IX_ID                      |   1   |       |    1 (0)    | 00:00:01 |    1   |   30  |  
|   9 |       TABLE ACCESS BY INDEX ROWID        | TEST2                      |   1   |  48   |    1 (0)    | 00:00:01 |        |       |  
|* 10 |        INDEX UNIQUE SCAN                 | SYS_C001242692             |   1   |       |    1 (0)    | 00:00:01 |        |       |  
|  11 |   SORT UNIQUE                            |                            |   4   |  88   |    6 (67)   | 00:00:01 |        |       |  
|  12 |    UNION-ALL                             |                            |       |       |             |          |        |       |  
|* 13 |     VIEW                                 |                            |   2   |  44   |    2 (0)    | 00:00:01 |        |       |  
|  14 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6611_34D3B6C |   2   |  96   |    2 (0)    | 00:00:01 |        |       |  
|* 15 |     VIEW                                 |                            |   2   |  44   |    2 (0)    | 00:00:01 |        |       |  
|  16 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6611_34D3B6C |   2   |  96   |    2 (0)    | 00:00:01 |        |       |  
-----------------------------------------------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):  
---------------------------------------------------  
   8 - access("ID"=70000)  
  10 - access("ID"=70000)  
  13 - filter("NAME"='John')  
  15 - filter("NAME"='Mary')  

quit ;  

谢谢和问候，
热吉

Answer 1

我无法在 11.2.0.3 中重现此行为，我认为除了以下行为之外没有合理的解释:您遇到了一个错误，显然已在 11.2.0.3 中解决。

我立即想到的一件事是缺少对象统计信息 - 如果您的输出是完整的 - OPTIMIZER_DYNAMIC_SAMPLING 设置为 0 的事实。您可以尝试使用 OPTIMIZER_DYNAMIC_SAMPLING=2 进行重现。在这种情况下，如果对象统计信息丢失，动态采样器就会启动。顺便说一句:不要使用此功能来代替正确的优化器统计信息。有关动态采样的更多信息 Dynamic sampling and its impact on the Optimizer

在您的 - 记录良好 - 问题和脚本/测试用例中，您尝试使用附加和无日志记录。这仅适用于批量插入，不适用于带值的行插入。每次插入都会发生什么:上推高水位线并将完整的数据 block 转储到空闲 block 中，在您的情况下只有 1 行....幸运的是，数据库忽略了此指令。

在对表执行 SQL 之前，请确保为其提供优化器统计信息。这肯定会对您的情况有所帮助。