Oracle Bug with streaming stats, heavy impact
From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Mon, 23 Aug 2021 12:02:45 +0200
Message-ID: <031ff32f-ecd1-e404-ec38-522a8a2bf6f4_at_bluewin.ch>
Hi,
| Rows | Bytes |TempSpc| Cost (%CPU)|
| 18E| 15E| | 18E(100)|
| Id | Operation | Rows |
| 42 | TABLE ACCESS BY LOCAL INDEX ROWID | 1188M| |* 43 | INDEX UNIQUE SCAN | 1 | Andi has made a testcase and is detailing his results here: https://twitter.com/AndiSchloegl/status/1429739669458231301 and here: https://twitter.com/AndiSchloegl/status/1429739845363113988
Date: Mon, 23 Aug 2021 12:02:45 +0200
Message-ID: <031ff32f-ecd1-e404-ec38-522a8a2bf6f4_at_bluewin.ch>
Hi,
I found this together with Andi Schlögl last week. It is a heavy bug and
happens when in a merge statement no rows are inserted.
In such case the distinct keys Statistic for unique and primary keys is
updated to 0.
That results in much to high cost and cardinality estimates in joins.
I have even seen the infamous 18(E) estimates, which are indicating IMHO
an overflow in the cost and cardinality calculation.
| Rows | Bytes |TempSpc| Cost (%CPU)|
| 18E| 15E| | 18E(100)|
In addition you can see wrong estimates for the table access by index rowid step in a nested loop. (Correct estimate should be 1).
| Id | Operation | Rows |
| 42 | TABLE ACCESS BY LOCAL INDEX ROWID | 1188M| |* 43 | INDEX UNIQUE SCAN | 1 | Andi has made a testcase and is detailing his results here: https://twitter.com/AndiSchloegl/status/1429739669458231301 and here: https://twitter.com/AndiSchloegl/status/1429739845363113988
This bug should be around since 12.2.
We just wonder if anybody has seen it before?
Thanks
Lothar
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 23 2021 - 12:02:45 CEST