Database Tuning (MOSC)

MOSC Banner

maintenance window gathering oracle stats results in optimizer choosing bad plan

in Database Tuning (MOSC) 11 commentsAnswered

This is oracle 19c.

I know this is not new behavior at times. But recently one AM, queries on one of our tables really started to back up and show poor performance. Upon review, during the maintenance window stats were gathered on one of our tables, resulting in a new plan not using the index, and performance greatly deteriorated. Stats were restored to the previous day, old plan became used again and everything good. For now the table has been locked so no new stats will be gathered.

I've read a bunch of articles/discussions on similar and thought I would ask here as well for insight. Question is how to go about rectifying this as I do not want to permanently exclude the table from having current stats just not knowing how that may eventually effect other queries down the line, although the table does grow slowly, but steadily. It will never have rows deleted.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center