Wrong optimizer estimate ?

Hi all,

while doing performance tests of some new queries in our application I noticed 
one query where the estimated rows differ much from the actual rows in the 
execution plan (Line 7 in the attached execution plan).  Now I ask you kindly 
to help me finding out if this is a bug in the optimizers estimation or is 
there another reason (maybe strange data) which could explain the difference. 

Fortunately there is no performance problem with the query yet, but I've 
learned that even small differences in data combined with small 
miscalculations may have a huge impact on the query perfomance in the future.

For better readability I attached the tests I did so far as an text file (hope 
this is ok ?). If there are any numbers, statistics ... helping  to debug this 
further, please let me know and I'll post them to the list.

In the attached file you'll find the query in question along with the 
execution plan gathered with the  gather_plan_statistics hint. There are two 
executions plans with two different sets of optimizer statistics. (first test 
without histograms, the second test with histograms). Also attached the column 
stats for the main table involved. The table t_sendung_import1 has only 1 row.

Thank you very much in advance for helping my deepen my oracle knowledge.

Very best regards

Michael




  

 

PLAN_TABLE_OUTPUT (without histograms)

BEGIN
   DBMS_STATS.
   GATHER_TABLE_STATS (ownname            => 'xxx',
                       tabname            => 'produkttitelinstanzen',
                       estimate_percent   => NULL,
                       method_opt         => 'for all columns size 1',
                       cascade            => TRUE);
END;
/

SQL_ID  0x4sfjdj0wshv, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */ 
        pt1.id quellprodukt_id,
            pt2.id zielprodukt_id
from    
        t_sendung_import1 timp,
        produkttitelinstanzen pt1,
        produkttitelinstanzen pt2
where   
        timp.id                    = pt1.produkt_id
        and pt1.produkt_id        <> pt2.produkt_id
    and pt1.titelsuche_id          = pt2.titelsuche_id
        and pt1.objektbereich_id   = 0
        and pt1.objektbereich_id   = pt2.objektbereich_id
        and pt1.produkttitelart_id = 1
        and pt1.produkttitelart_id = pt2.produkttitelart_id
        and pt1.reihenfolge        = 0
        and pt1.reihenfolge        = pt2.reihenfolge
        and timp.jobid = 1111


Plan hash value: 2472102189

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | 
E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |       
 |    197 |00:00:00.01 |     125 |
|   1 |  NESTED LOOPS                  |                       |      1 |       
 |    197 |00:00:00.01 |     125 |
|   2 |   NESTED LOOPS                 |                       |      1 |      
1 |    198 |00:00:00.01 |      25 |
|   3 |    NESTED LOOPS                |                       |      1 |      
1 |      1 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN           | T_SENDUNG_IMPORT1_PK  |      1 |      
1 |      1 |00:00:00.01 |       2 |
|   5 |     TABLE ACCESS BY INDEX ROWID| PRODUKTTITELINSTANZEN |      1 |      
1 |      1 |00:00:00.01 |       5 |
|*  6 |      INDEX RANGE SCAN          | PRTI_UK2_I            |      1 |      
1 |      1 |00:00:00.01 |       4 |
|*  7 |    INDEX RANGE SCAN            | PRTI_TISU_FK_I        |      1 |     
12 |    198 |00:00:00.01 |      18 |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | PRODUKTTITELINSTANZEN |    198 |      
1 |    197 |00:00:00.01 |     100 |
------------------------------------------------------------------------------------------------------------------

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

   4 - access("TIMP"."JOBID"=1111)
   6 - access("TIMP"."ID"="PT1"."PRODUKT_ID" AND "PT1"."PRODUKTTITELART_ID"=1 
AND
              "PT1"."OBJEKTBEREICH_ID"=0 AND "PT1"."REIHENFOLGE"=0)
   7 - access("PT1"."TITELSUCHE_ID"="PT2"."TITELSUCHE_ID")
   8 - filter(("PT2"."REIHENFOLGE"=0 AND "PT2"."OBJEKTBEREICH_ID"=0 AND 
"PT2"."PRODUKTTITELART_ID"=1 AND
              "PT1"."PRODUKT_ID"<>"PT2"."PRODUKT_ID"))

                          
PLAN_TABLE_OUTPUT (with histograms)

BEGIN
   DBMS_STATS.
   GATHER_TABLE_STATS (ownname            => 'xxx',
                       tabname            => 'produkttitelinstanzen',
                       estimate_percent   => NULL,
                       method_opt         => 'for all columns size auto',
                       cascade            => TRUE);
END;
/

                          
SQL_ID  6k2stg2srtq2w, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */ 
        pt1.id quellprodukt_id,
            pt2.id zielprodukt_id
from    
        t_sendung_import1 timp,
        produkttitelinstanzen pt1,
        produkttitelinstanzen pt2
where   
        timp.id                    = pt1.produkt_id
        and pt1.produkt_id        <> pt2.produkt_id
    and pt1.titelsuche_id          = pt2.titelsuche_id
        and pt1.objektbereich_id   = 0
        and pt1.objektbereich_id   = pt2.objektbereich_id
        and pt1.produkttitelart_id = 1
        and pt1.produkttitelart_id = pt2.produkttitelart_id
        and pt1.reihenfolge        = 0
        and pt1.reihenfolge        = pt2.reihenfolge
        and timp.jobid = 1111


Plan hash value: 2472102189

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | 
E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |       
 |    197 |00:00:00.01 |     129 |
|   1 |  NESTED LOOPS                  |                       |      1 |       
 |    197 |00:00:00.01 |     129 |
|   2 |   NESTED LOOPS                 |                       |      1 |  
39799 |    198 |00:00:00.01 |      29 |
|   3 |    NESTED LOOPS                |                       |      1 |      
1 |      1 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN           | T_SENDUNG_IMPORT1_PK  |      1 |      
1 |      2 |00:00:00.01 |       3 |
|   5 |     TABLE ACCESS BY INDEX ROWID| PRODUKTTITELINSTANZEN |      2 |      
1 |      1 |00:00:00.01 |       8 |
|*  6 |      INDEX RANGE SCAN          | PRTI_UK2_I            |      2 |      
1 |      1 |00:00:00.01 |       7 |
|*  7 |    INDEX RANGE SCAN            | PRTI_TISU_FK_I        |      1 |     
11 |    198 |00:00:00.01 |      18 |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | PRODUKTTITELINSTANZEN |    198 |  
57767 |    197 |00:00:00.01 |     100 |
------------------------------------------------------------------------------------------------------------------

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

   4 - access("TIMP"."JOBID"=1111)
   6 - access("TIMP"."ID"="PT1"."PRODUKT_ID" AND "PT1"."PRODUKTTITELART_ID"=1 
AND
              "PT1"."OBJEKTBEREICH_ID"=0 AND "PT1"."REIHENFOLGE"=0)
   7 - access("PT1"."TITELSUCHE_ID"="PT2"."TITELSUCHE_ID")
   8 - filter(("PT2"."PRODUKTTITELART_ID"=1 AND "PT2"."REIHENFOLGE"=0 AND 
"PT2"."OBJEKTBEREICH_ID"=0 AND
              "PT1"."PRODUKT_ID"<>"PT2"."PRODUKT_ID"))


39 rows selected.
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS AVG_COL_LEN NUM_BUCKETS HISTOGRAM
ID
22878255
C102 C432034B63
4,37096273295319E-8
0
6
1
NONE
OBJEKTBEREICH_ID
113
80 C403011A04
2,1854813664766E-8
0
3
113
FREQUENCY
TITELTEXT_ID
2129449
C415010102 C419190123
0,000602515818749885
0
6
254
HEIGHT BALANCED
TITELSUCHE_ID
1992312
C415010102 C41909231C
0,000740863626550299
0
6
254
HEIGHT BALANCED
REIHENFOLGE
393
80 C2045D
0,0020138646157787
0
3
254
HEIGHT BALANCED
PRODUKT_ID
8581895
C40B1B5923 C50329633844
1,16524380687482E-7
0
7
1
NONE
PRODUKTTITELART_ID
9
80 C109
2,1854813664766E-8
0
3
9
FREQUENCY
ORIGINALTITELINSTANZ_ID
22874695
C42A563F05 C50212231435
4,37164298802673E-8
3560
7
1
NONE

Other related posts: