Skip to Main Content
  • Questions

  • Please help to understand how Nested Table cardinality estimation works in Oracle 12C

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Achal.

Asked: February 18, 2019 - 2:56 pm UTC

Last updated: February 22, 2019 - 3:15 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Team,

Request your help with one issue that we are facing.

We pass a Nested table as a variable to a select statement. Example SQL is:

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 )



My understanding was that Oracle defaults cardinality estimate of the table to size of one block. This is understandable as Oracle don't has information about the data present in the table.

However, My test results on Oracle 12.1.0.2 shows that Oracle now most of the times guesses cardinality correctly whereas other times it uses the magic number of 8168. Any idea how this is working? How oracle is able to guess cardinality correctly most of the times and why it fails some time?

I have checked and there is no indication that Statistics Feedback is kicking in. V$SQL shows that column IS_Reoptimizable is set to N. There is no note in execution plan as well. When Oracle creates a bad plan, it continues to follow same till we induce a hard parse by flushing shared_pool.

I am not able to identify a pattern of when Oracle is guessing correctly. It seems random to me. Pasted at the end is code to replicate this behavior.

This behavior is causing an issue in our client's environment. Some queries, which are executed millions of times, sporadically take sub-optimal plan on some particular days.

The queries are generated dynamically by the application. Therefore, implementing hints and or modifying code will need good amount of re-work. We are trying to find if any DB setting affects this behavior.

Thanks,
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))

and Chris said...

This appears to be happening when the object table has no elements. i.e. its count is zero:

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 |
---------------------------------------------------------------------------------------------------------------------


As when this is empty, the query returns an empty object, could you put a simple check in the application for this?

If not, look into SQL Plan Management. With a baseline you can lock the query to the "good" plan.

Read more about this at https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

Or you could investigate the extensible optimizer:

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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perhaps a simplier solution

Tubby, February 20, 2019 - 9:03 pm UTC

I believe the primary suggestion is the best one (check the collection for an empty condition and take the appropriate code path based on that) but if that's not an option I would personally utilize the CARDINALITY hint before heading down the road of a baseline or the extensible optimizer. This assumes the collection will have a relatively consistent set of data (it doesn't go from 100 elements on one execution to 1,000,000 the next for example).

Just my 2 cents.

Cheers,
Chris Saxon
February 21, 2019 - 12:01 pm UTC

Apparently

implementing hints and or modifying code will need good amount of re-work

so the cardinality hint is probably off the table for now...

Any idea how Oracle estimates cardinality of nested table in absence of stats?

AB, February 21, 2019 - 6:11 pm UTC

Hello Chris,

Thanks a lot for advising on this issue.

I will check if the application is passing an empty table.

Any idea how oracle is estimating cardinality correctly in absence of statistics? Has anything changed in this release of Oracle? My impression from available information was that Oracle will default it to size of one block.

I tested for a pipelined function and seems that for it Oracle always defaults cardinality to 8168 rows in my DB.

I am also trying to understand if empty table is the only problematic scenario of there could be something else as well.

As you recommended, creating baselines (or profiles) for the affected SQLs looks best option in our case.

Once again, thank you for helping on this.
Connor McDonald
February 22, 2019 - 6:41 am UTC

Any idea how oracle is estimating cardinality correctly in absence of statistics?


I think we added dynamic sampling came in for table functions. That could potentially also explain why 'zero' is a problem. In most dynamic sampling scenarios, when we find zero, we no longer trust the results of the dynamic sample because the "sample" may have been unlucky.

(Because normally dynamic sampling only touches a subset of the rows)

Cardinality misestimation, in couple of cases, even when the table has rows.

Achal Bansal, February 22, 2019 - 10:57 am UTC

Hi Team,

I did some additional tests and encountered the issue sometimes even when the Nested table has records. Pasted below is one such execution plan:

SQL_ID  3wpykfqw63gaf, child number 0
-------------------------------------
SELECT CAST ( MULTISET ( SELECT DEPTNBR AS IAK2 FROM DEPT WHERE 
(DEPTNBR) IN (SELECT DEPTNBR FROM EMP WHERE (IDNBR) IN (SELECT IDNBR 
FROM TABLE (:B1 ) )) ) AS TL_DEPT ) FROM DUAL WHERE 1 = 1
 
Plan hash value: 838607300
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      1 |00:00:00.01 |       0 |       |       |          |
|*  1 |  HASH JOIN SEMI                      |          |      1 |     10 |      1 |00:00:00.05 |     177 |  2440K|  2440K| 1504K (0)|
|   2 |   TABLE ACCESS FULL                  | DEPT     |      1 |     10 |     10 |00:00:00.01 |       3 |       |       |          |
|   3 |   VIEW                               | VW_NSO_1 |      1 |   8168 |      1 |00:00:00.05 |     174 |       |       |          |
|*  4 |    HASH JOIN                         |          |      1 |   8168 |      1 |00:00:00.05 |     174 |  1696K|  1696K|  625K (0)|
|   5 |     COLLECTION ITERATOR PICKLER FETCH|          |      1 |   8168 |      1 |00:00:00.01 |       0 |       |       |          |
|   6 |     TABLE ACCESS FULL                | EMP      |      1 |  95100 |  95100 |00:00:00.01 |     174 |       |       |          |
|   7 |  FAST DUAL                           |          |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNBR"="DEPTNBR")
   4 - access("IDNBR"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))


For above mentioned execution, the code is modified to ensure that the collection is never empty. I also did in-significant changes in the SQL to ensure a hard parse. Most of the times Oracle guessed cardinality accurately. However, it failed in a couple of cases.

I also did same test with similar code that uses different tables and encountered this behavior one time when the collection had 100 rows. However, the results were correct most of the times. Also, as you pointed out, the cardinality is consistently wrong when the table has 0 rows.

It would be great if you can share any more pointers with me.

Thanks,
Chris Saxon
February 22, 2019 - 3:15 pm UTC

Can you use baselines or the extensible optimizer? Have you tried?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library