Hello,
In 19c I have a fairly complex query (involving 14 tables, some of them used twice or more in sub-queries...) with a table X containing column BDG with very skewed data in it.
X has 10400 rows only, count() on column BDG with skewed data shows something like:
6716 values appear once
27 values appear twice
2 values appear 3 times
3 values appear 4 times
1 value appear 5 times
2 value appear 7 times
2 value appear 8 times
then you have:
BDG COUNT
------------ -------
SAN01 10
SOC01 12
ODE01 17
WHI01 24
CABGOV 32
SMALS 34
IBZ04 48
CAB NONGOV 50
FAG01 90
IBZ03 98
CONTRACT 135
IBZ05 180
IBZ06 196
IBZ02 268
IBZ01 376
STATUT 396
FAG02 480
REG01 1139
6771 rows selected.
I was submitted the query with a bind variable on IBZ03, and noticed that if I remove the histogram on BDG the query returns 66131 rows selected in 00:00:49.43; if I leave it, query hangs *for hours*, with an enormous amount of 'ASM IO for non-blocking poll' and 'direct path write temp' waits...
In both cases the execution plan *is the same* (!)...
I have no idea where I could start trying to figure out why it behaves so. I ran 10053 traces but it didn't help me (much too complex...).
Anybody has a suggestion ?
Thanks a lot.
Sebino