请帮助了解嵌套表基数估计在Oracle 12C中是如何工作的

ASKTOM 2019-02-18

261

问题描述

嗨，团队，

就我们面临的一个问题请求您的帮助。

我们将嵌套表作为变量传递给select语句。示例SQL是:

SELECT CAST ( MULTISET ( SELECT DEPTNBR
                                FROM DEPT 
                                WHERE (DEPTNBR) IN (SELECT DEPTNBR 
                                                    FROM EMP 
                                                    WHERE (IDNBR) IN (SELECT IDNBR 
                                                                        FROM TABLE (:EmpID ))) ) AS TL_DEPT ) 复制

我的理解是，Oracle将表的基数估计默认为一个块的大小。这是可以理解的，因为Oracle没有关于表中存在的数据的信息。

但是，我在Oracle 12.1.0.2上的测试结果表明，Oracle现在大多数时候都正确地猜测基数，而其他时候则使用8168的魔术数。知道这是怎么工作的吗？甲骨文如何在大多数情况下正确猜测基数，为什么会在某个时候失败？

我已经检查过了，没有迹象表明统计数据反馈正在启动。V $ SQL显示列IS_Reoptimizable设置为N。执行计划也没有注释。当Oracle创建一个糟糕的计划时，它会继续遵循同样的计划，直到我们通过刷新shared_pool引发一个硬解析。

我无法确定Oracle何时正确猜测的模式。对我来说似乎是随机的。最后粘贴的是复制此行为的代码。

这种行为在我们客户的环境中引起了问题。一些执行了数百万次的查询，在某些特定的日子里偶尔会采取次优计划。

查询由应用程序动态生成。因此，实现提示和/或修改代码将需要大量的重新工作。我们正在尝试查找是否有任何DB设置会影响此行为。

谢谢，
AB

----------Setup Script
create or replace TYPE T_ID AS OBJECT (IDNBR  NUMBER(22,0));
create or replace TYPE TL_ID AS TABLE OF T_ID;

create or replace TYPE T_DEPT AS OBJECT (DEPTNBR  NUMBER(22,0));
create or replace TYPE TL_DEPT AS TABLE OF T_DEPT;

CREATE SEQUENCE GENERATEID;

CREATE TABLE EMP AS
SELECT GENERATEID.NEXTVAL idnbr,ROUND(DBMS_RANDOM.VALUE(1,10),0)deptnbr
FROM ALL_OBJECTS;

ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(idnbr);

CREATE TABLE DEPT AS 
SELECT LEVEL deptnbr
FROM dual
CONNECT BY     LEVEL <= 10;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(:SCHEMA,'EMP');
    DBMS_STATS.GATHER_TABLE_STATS(:SCHEMA,'DEPT');
END;

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;


----Test Code
DECLARE
    EmpID   TL_ID ;
    RandomVal  INTEGER;
    I    INTEGER;
    DeptNbr   TL_DEPT := TL_DEPT();
BEGIN
    I := 0;
    LOOP
         I := I +1;
         EXIT WHEN I = 100;
         EmpID := TL_ID();
         select round(dbms_random.value(1,10),0) num
         INTO RandomVal
         from dual;
         
         IF ( RandomVal < 7 AND RandomVal > 3 ) THEN
            EmpID.extend(1);    
            EmpID(1) := T_ID(30000);
         ELSIF (RandomVal < 3) THEN
            EmpID.extend(60);
            EmpID(1) := T_ID(1);
            EmpID(2) := T_ID(2);
            EmpID(3) := T_ID(200);
            EmpID(4) := T_ID(500);
            EmpID(5) := T_ID(3);
            EmpID(6) := T_ID(4000);
            EmpID(7) := T_ID(201);
            EmpID(8) := T_ID(40000);
            EmpID(9) := T_ID(10);
            EmpID(10) := T_ID(601);
            EmpID(11) := T_ID(8010);
            EmpID(12) := T_ID(801);
            EmpID(13) := T_ID(8123);
            EmpID(14) := T_ID(8342);
            EmpID(15) := T_ID(85812);
            EmpID(16) := T_ID(80032);
            EmpID(17) := T_ID(8712);
            EmpID(18) := T_ID(8162);
            EmpID(19) := T_ID(81612);
            EmpID(20) := T_ID(2062);
            EmpID(21) := T_ID(23312);
            EmpID(22) := T_ID(33462);
            EmpID(23) := T_ID(3412);
            EmpID(24) := T_ID(48862);
            EmpID(25) := T_ID(50312);
            EmpID(26) := T_ID(1762);
            EmpID(27) := T_ID(3212);
            EmpID(28) := T_ID(3542);
            EmpID(29) := T_ID(2335);
            EmpID(30) := T_ID(23352);
            EmpID(31) := T_ID(3359);
            EmpID(32) := T_ID(82462);
            EmpID(33) := T_ID(8312);
            EmpID(34) := T_ID(33362);
            EmpID(35) := T_ID(4812);
            EmpID(36) := T_ID(66262);
            EmpID(37) := T_ID(67712);
            EmpID(38) := T_ID(69162);
            EmpID(39) := T_ID(70612);
            EmpID(40) := T_ID(72062);
            EmpID(41) := T_ID(73512);
            EmpID(42) := T_ID(74962);
            EmpID(43) := T_ID(76412);
            EmpID(44) := T_ID(77862);
            EmpID(45) := T_ID(79312);
            EmpID(46) := T_ID(80762);
            EmpID(47) := T_ID(82212);
            EmpID(48) := T_ID(83662);
            EmpID(49) := T_ID(85112);
            EmpID(50) := T_ID(86562);
            EmpID(51) := T_ID(88012);
            EmpID(52) := T_ID(89462);
            EmpID(53) := T_ID(90912);
            EmpID(54) := T_ID(2362);
            EmpID(55) := T_ID(3812);
            EmpID(56) := T_ID(5262);
            EmpID(57) := T_ID(6712);
            EmpID(58) := T_ID(8162);
            EmpID(59) := T_ID(9612);
            EmpID(60) := T_ID(1062);
         END IF;
    
        SELECT CAST ( MULTISET ( SELECT DEPTNBR as oz15
                                FROM DEPT 
                                WHERE (DEPTNBR) IN (SELECT DEPTNBR 
                                                    FROM EMP 
                                                    WHERE (IDNBR) IN (SELECT IDNBR 
                                                                        FROM TABLE (EmpID ) )) ) AS TL_DEPT ) 
        into DeptNbr
        FROM DUAL;
    END LOOP;
END;


----------------CleanUP Script
DROP TYPE TL_ID;
DROP TYPE T_ID;
DROP TYPE TL_DEPT;
DROP TYPE T_DEPT;

DROP SEQUENCE GENERATEID;

DROP TABLE EMP;
DROP TABLE DEPT; 复制

Generated execution plans:
--------------------------------------

SQL_ID  4tdf58rjtsmk6, child number 0
-------------------------------------
SELECT CAST ( MULTISET ( SELECT DEPTNBR AS OZ15 FROM DEPT WHERE 
(DEPTNBR) IN (SELECT DEPTNBR FROM EMP WHERE (IDNBR) IN (SELECT IDNBR 
FROM TABLE (:B1 ) )) ) AS TL_DEPT ) FROM DUAL
 
Plan hash value: 838607300
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |    86 (100)|          |
|*  1 |  HASH JOIN SEMI                      |          |    10 |   160 |    84   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | DEPT     |    10 |    30 |     3   (0)| 00:00:01 |
|   3 |   VIEW                               | VW_NSO_1 |  8168 |   103K|    81   (2)| 00:00:01 |
|*  4 |    HASH JOIN                         |          |  8168 | 81680 |    81   (2)| 00:00:01 |
|   5 |     COLLECTION ITERATOR PICKLER FETCH|          |  8168 | 16336 |    29   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL                | EMP      | 95100 |   742K|    52   (2)| 00:00:01 |
|   7 |  FAST DUAL                           |          |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNBR"="DEPTNBR")
   4 - access("IDNBR"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

   
   
SQL_ID  4tdf58rjtsmk6, child number 0
-------------------------------------
SELECT CAST ( MULTISET ( SELECT DEPTNBR AS OZ15 FROM DEPT WHERE 
(DEPTNBR) IN (SELECT DEPTNBR FROM EMP WHERE (IDNBR) IN (SELECT IDNBR 
FROM TABLE (:B1 ) )) ) AS TL_DEPT ) FROM DUAL
 
Plan hash value: 1000407924
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |       |       |    64 (100)|          |
|*  1 |  HASH JOIN SEMI                       |          |    10 |   160 |    62   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                   | DEPT     |    10 |    30 |     3   (0)| 00:00:01 |
|   3 |   VIEW                                | VW_NSO_1 |    60 |   780 |    59   (0)| 00:00:01 |
|   4 |    NESTED LOOPS                       |          |    60 |   600 |    59   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                      |          |    60 |   600 |    59   (0)| 00:00:01 |
|   6 |      COLLECTION ITERATOR PICKLER FETCH|          |    60 |   120 |    29   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                | EMP_PK   |     1 |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID       | EMP      |     1 |     8 |     1   (0)| 00:00:01 |
|   9 |  FAST DUAL                            |          |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNBR"="DEPTNBR")
   7 - access("IDNBR"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
复制

专家解答

当对象表没有元素时，似乎会发生这种情况。即它的计数为零:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

alter session set statistics_level = all;
set serveroutput on
DECLARE
  EmpID   TL_ID ;
  RandomVal  INTEGER;
  DeptNbr   TL_DEPT := TL_DEPT();
BEGIN
  EmpID := TL_ID();
        
  dbms_output.put_line ( 'size ' || empid.count ) ;
    
  SELECT CAST ( MULTISET ( SELECT DEPTNBR as oz15
                          FROM DEPT 
                          WHERE (DEPTNBR) IN (SELECT DEPTNBR 
                                              FROM EMP 
                                              WHERE (IDNBR) IN (SELECT IDNBR 
                                                                  FROM TABLE (EmpID ) )) ) AS TL_DEPT ) 
  into DeptNbr
  FROM DUAL;
END;
/

size 0

select * 
from   table(
  dbms_xplan.display_cursor(
    '4tdf58rjtsmk6', null, 
    'ALLSTATS LAST +NOTE')
);

Plan hash value: 3674797437

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      1 |00:00:00.01 |       0 |      0 |
|*  1 |  HASH JOIN SEMI                      |          |      1 |     10 |      0 |00:00:00.01 |       2 |      6 |
|   2 |   TABLE ACCESS FULL                  | DEPT     |      1 |     10 |     10 |00:00:00.01 |       2 |      6 |
|   3 |   VIEW                               | VW_NSO_1 |      1 |   8168 |      0 |00:00:00.01 |       0 |      0 |
|*  4 |    HASH JOIN                         |          |      1 |   8168 |      0 |00:00:00.01 |       0 |      0 |
|   5 |     COLLECTION ITERATOR PICKLER FETCH|          |      1 |   8168 |      0 |00:00:00.01 |       0 |      0 |
|   6 |     TABLE ACCESS FULL                | EMP      |      0 |  90268 |      0 |00:00:00.01 |       0 |      0 |
|   7 |  FAST DUAL                           |          |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
--------------------------------------------------------------------------------------------------------------------
    
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

DECLARE
  EmpID   TL_ID ;
  RandomVal  INTEGER;
  I    INTEGER;
  DeptNbr   TL_DEPT := TL_DEPT();
BEGIN
  EmpID := TL_ID();
  EmpID.extend(5);
  EmpID(1) := T_ID(1);
  EmpID(2) := T_ID(2);
  EmpID(3) := T_ID(200);
  EmpID(4) := T_ID(500);
  EmpID(5) := T_ID(3);
        
  dbms_output.put_line ( 'size ' || empid.count ) ;
    
  SELECT CAST ( MULTISET ( SELECT DEPTNBR as oz15
                          FROM DEPT 
                          WHERE (DEPTNBR) IN (SELECT DEPTNBR 
                                              FROM EMP 
                                              WHERE (IDNBR) IN (SELECT IDNBR 
                                                                  FROM TABLE (EmpID ) )) ) AS TL_DEPT ) 
  into DeptNbr
  FROM DUAL;
END;
/

size 5
                                                                        
select * 
from   table(
  dbms_xplan.display_cursor(
    '4tdf58rjtsmk6', null, 
    'ALLSTATS LAST +NOTE')
);

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      1 |00:00:00.01 |       0 |      0 |
|*  1 |  HASH JOIN SEMI                       |          |      1 |      5 |      4 |00:00:00.01 |      14 |     22 |
|   2 |   TABLE ACCESS FULL                   | DEPT     |      1 |     10 |     10 |00:00:00.01 |       2 |      6 |
|   3 |   VIEW                                | VW_NSO_1 |      1 |      5 |      5 |00:00:00.01 |      12 |     16 |
|   4 |    NESTED LOOPS                       |          |      1 |      5 |      5 |00:00:00.01 |      12 |     16 |
|   5 |     NESTED LOOPS                      |          |      1 |      5 |      5 |00:00:00.01 |       7 |      8 |
|   6 |      COLLECTION ITERATOR PICKLER FETCH|          |      1 |      5 |      5 |00:00:00.01 |       0 |      0 |
|*  7 |      INDEX UNIQUE SCAN                | EMP_PK   |      5 |      1 |      5 |00:00:00.01 |       7 |      8 |
|   8 |     TABLE ACCESS BY INDEX ROWID       | EMP      |      5 |      1 |      5 |00:00:00.01 |       5 |      8 |
|   9 |  FAST DUAL                            |          |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------------复制

当这是空的时候，查询返回一个空的对象，你可以在应用程序中进行简单的检查吗？

如果没有，请查看SQL计划管理。使用基线，您可以将查询锁定到 “良好” 计划。

阅读更多关于这方面的信息https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

或者您可以调查可扩展优化器:

https://oracle-base.com/articles/misc/pipelined-table-functions#cardinality

oracle asktom

文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

请帮助了解嵌套表基数估计在Oracle 12C中是如何工作的

问题描述

专家解答

评论

相关阅读