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: image.png]
>

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




image/png attachment: image.png