Plan change with difference in Note and plan outline section.

From: Lok P <loknath.73_at_gmail.com>
Date: Fri, 16 Apr 2021 17:07:49 +0530
Message-ID: <CAKna9VYWaNSji2MDAwbAh9HC_g92Kx5bU-dkCpaC7R5eZzqwvw_at_mail.gmail.com>



Hello Listers,

We are seeing one of the queries occasionally change its execution path. And as i understand, in such types of cases normally stats is the cause but i was not able to point out any stats which can be the cause here. However, what I found is some odd difference in the "Note" section pointing to some parallel execution. But this query is executed in serial only, no parallel hint used. And also the outline section is different in terms of parameters for both the plans. We have our database version 12.1 with optimizer_feature_enable as 11.2.0.4, but in the case of outline section in good plan , i am seeing OFE as '11.2.0.3' wondering from where is this coming from. This sql is executed by one and the same process but we are seeing the plan change twice in the month of April.

I have attached the good and the bad plan with sql monitor and outline section. The index-MFE_IX4 which the bad plan is opting is on column (STAT, CODE2) both having 2 distinct values each in that ~48million rows of partition table SFE. Wondering what is causing that not so selective index to be chosen in case of a bad path. Is the note section pointing to anything suspicious?

fyi, Oracle version 12.1.0.2 with optimizer_feature_enable- 11.2.0.4.



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


Received on Fri Apr 16 2021 - 13:37:49 CEST

Original text of this message