RE: Bad plan selected at the beginning of the month for partitioned table

Thank you for the idea to check but unfortunately no, it’s not set.

Jay Miller
Sr. Oracle DBA
201.369.8355

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich J Sent: Thursday, February 01, 2018 11:38 AM To: oracle-l_at_freelists.org
Subject: Re: Bad plan selected at the beginning of the month for partitioned table

On 2018/02/01 09:29, Jay.Miller_at_tdameritrade.com<mailto:Jay.Miller_at_tdameritrade.com> wrote: One of our databases has 2 frequent queries that run against a partitioned table. At the beginning of the month when the partition is empty they start doing a full partition scan instead of an index range scan (no surprise). However even after rows start being loaded to the partition and stats are gathered that night the plan does not change back. I have verified that the stats have been updated, numrows is populated, and the stats do not show as stale. But even after 3 days the bad plan continues to be used and app performance gets so poor that I manually purge the sql_id from the shared pool. It then reverts to the index range scan and everything is fine.

Are you specifying (the logically confusing) "no_invalidate=>true" when collecting stats? I would think that could account for what you're seeing. Smarter people than I (and ones with better memory, too) would also know if an invalidation from stats causes a new child cursor that you would be able to see, depending on DB version. Just a thought...
Rich

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