Re: Question on statistics feedback

Your good plan is differing from the bad plan by mainly the "bloom filter pruning" and that is in turns gets influenced by the estimation of rows in your driving table - TAB_STAGE. So somehow optimizer is seeing the rows in your table TAB_STAGE is varying and thus changing the decision to opt or not to opt bloom filter pruning. My 2 cents.

On Tue, 20 Sep 2022, 4:40 pm yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> Hello Experts,
>
> Its version 19C of the oracle database. A Select query changing the plan
> itself without any stats gather change and the note section is showing
> "statistics feedback" is used.
>
> I have captured and published the good and bad execution path and sql
> monitor for both of them in below github link. Although i did see this
> query alreday having hints added in it, But wanted to understand, what we
> should do to fix this issue in a proper way(say for e.g by correcting any
> statistics etc) rather adding additional hint/baseline/profile to fix this
> plan change issue? And if there is any change in behaviour of the
> 'statistics feedback' between version 11.2 and 19c, as it seems to work
> fine during 11.2 version period?
>
> https://gist.github.com/databasetech0073/efae5292bb47ed4951e339e91a14c69e
>
>
> Regards
> Yudhi
>

--
http://www.freelists.org/webpage/oracle-l