Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 06, 2020 - 1:23 pm UTC

Last updated: November 12, 2021 - 4:54 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom, from couple of months some queries in production environment are intermittently producing 2 hash plan values. Obviously one is better and other is bad. We run stats gather Job daily in the morning. Have check many other platforms and understood that there are many reasons for this behaviour. Some of them reasons for above behaviour, that I have check are below
1. Parsing a SQL statement with bind variable defined as wrong type.
2. Not running stats gather Job daily.

above two reasons are not valid for my case. Other than this there are some more reasons that I wanted to ask you.

a. Invalidation of cursors. I understand that, this is handled by parameter 'NO_INVALIDATE'. This is set to AUTO_INVALIDATE for me. So even though my stats gather Job has produced a bad hash plan value, this will not be effective immediately.

b. Adaptive cursor sharing. How to make sure that because of this feature my SQL is producing multiple Hash plan value?

Could you please help me with above understanding and suggest me for point a and b.

Thanks a lot.

and Connor said...

a) NO_INVALIDATE is design to avoid parsing storms. If I have 1000 queries against a table, and I gather stats and invalidate all those 1000 queries at once *and* they are run frequently, then I might have 1000 brand new parses to do *all at once* which could cause problems. "AUTO_INVALIDATE" spreads that out over several hours to avoid that.

This can mean it can be hours before a plan can switch. If the parsing is not an issue for you, then you can invalidate to make sure it happens immediately. Alternatively, you can modify "_optimizer_invalidation_period" under the guidance of Support (because its an underscore parameter).

b) ACS is on by default unless you have explicitly disabled it. I've got a video series on that here

https://www.youtube.com/watch?v=MnISfllmK74&list=PLJMaoEWvHwFLLDAm8BMcFSc8z-fbdVSIK

which can walk through its workings and how to monitor it etc.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Small confusion for point a

Alok Kumar, July 07, 2020 - 7:09 am UTC

Thanks Tom for quick reply. Appreciate your help.
———————————
I have a small double for point a.
When you say Auto_Invalidate will spread out the hard parsing for all the 1000 queries , how? Default value of auto invalidate is 5 hours. So my understanding is after 5 hours of running the stats gather job, hardparsing will start immediately for all these queries. So, for any reason if my stats gather job has produced some bad plans it’s effect and system slowness will be visible after 5 hours. Please correct me if i am wrong.

Thanks
Connor McDonald
July 08, 2020 - 6:40 am UTC

Think of it like a "raffle" or "lottery".

- We have an SQL with an existing plan.
- We gather stats which mean (theoretically) that plan should ditched and the query re-parsed
- We take that SQL and "roll the dice" that comes up between 1 and 18000. If the number of seconds elapsed since gather stats is more than our number, then we parse, otherwise we keep using the old plan.

Thus over the course of 5 hours, we'll slowly parse everything again (assuming its requested to run again).


NO_INVALIDATE in Oracle19

Andy, November 11, 2021 - 2:40 pm UTC

Hi Tom,

Above question was related to parameters in oracle11g, I am upgrading database to 19.
Could you please let me know if NO_INVALIDATE parameter is still valid for Oracle19? Any documentation related to this will help.

Thanks
Connor McDonald
November 12, 2021 - 4:54 am UTC

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.