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

Great info from everyone, thanks. This gives me a lot of ideas. This is OCI EE Extreme Performance. I will update when I get a chance or find something.

On Thu, Nov 14, 2024 at 3:24 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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