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 |
