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

Issue self resolved as of Friday and went back to typical performance. Have not had a chance to dig in. But again, a lot of helpful information in all of the responses. Thanks.

On Fri, Nov 15, 2024 at 10:25 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

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

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






(image/jpeg attachment: image002.jpg)