Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: July 08, 2020 - 6:40 am UTC

Category: Database Administration - Version: 11g

Viewed 100+ 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 we 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.

and you rated our response

  (1 rating)

Reviews

Small confusion for point a

July 07, 2020 - 7:09 am UTC

Reviewer: Alok Kumar from UK

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

Followup  

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).


More to Explore

Performance

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