RE: Query went from millisec/exe response time to 1-2 seconds per exe

If memory serves (fingers crossed), I *think* you have from your start plus community contributions a pretty comprehensive laundry list of things to contemplate when there is a change in job duration that is not expected and someone *thinks* they didn’t change anything.  

Slightly after a replied with a short list of things that can change performance without even changing parameters I wondered whether that list was still on your site (but I was too lazy to look for it.)  

In addition to your and Sayan’s points in this thread, the OP reading that laundry list as a “rule-out” might save everyone time.  

When someone notices a change to only a maximum of 2 seconds, that implies to me that it probably runs very often, so this is not a single query on a private test bed problem and there is also a very real possibility that nothing about the query in question changed, but rather other loads on the machine changed.  

I will be clear that zero of that contradicts what you and Sayan have contributed to the thread.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, November 14, 2024 4:23 AM To: oracle-l
Subject: Re: Query went from millisec/exe response time to 1-2 seconds per exe    

The class of problem has shown up 3 or 4 times in the last few months on the Oracle Forums. As others have said there are a variety of reasons, and there's little point in running off a list in the absence of any detailed information. Version, Platform (Exadata or not), SQL, plan, any available activity stats for before and after.  

Your comment that the plan hasn't changed may be wrong - the plan hash value may be the same but a change in the use of predicates won't be echoed by a change in the plan_hash_value, and different use of exactly the same (initial) predicates could make a huge difference to the work done. See, for example:

https://jonathanlewis.wordpress.com/2024/08/09/num_index_keys-2/  

Regards

Jonathan Lewis      

On Wed, 13 Nov 2024 at 23:39, Ethan Post <post.ethan_at_gmail.com> wrote:

I can't recall a time when I have ever seen query performance change and it only be CPU time when the query and the plan have not changed. I have a lot of history and it is running with the same plan as it always has. No indexes were touched, the table was not purged or anything. It is a large 100+ GB table. Anyone seen this type of thing and have any ideas as to what might cause this? OCI cloud and only this query so not a CPU changed behind the scenes - in the cloud thing.  

image.png

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






(image/jpeg attachment: image002.jpg)