Re: Query went from millisec/exe response time to 1-2 seconds per exe
Date: Thu, 14 Nov 2024 03:56:43 +0000
Message-ID: <CAOVevU60QjyT-aC5KGzQo-4vEqso=DOL6QGyvpuU8z-w_cYp2A_at_mail.gmail.com>
Hi Ethan,
I suppose you are talking about averages, so first of all you need to check
if you have too slow executions that were caught by rtsm (real time sql
monitor) and analyse them.
If you have no rtsm reports, you can use asqlmon by Tanel Poder to find
which plan lines become so slow.
There way too many different potential reasons for such problems, so you
need to provide details.
For example, one of the most often reasons can be a change of input bind
variables, like date_column between :a and :b, where previously you had
just a day between :a and :b, but later users decided to request a year.
But you haven't provided even sql text and plan, so we don't know anything
about it.
-- Kind regards, Sayan On Thu, Nov 14, 2024, 03:35 Mark W. Farnham <mwf_at_rsiz.com> wrote:Received on Thu Nov 14 2024 - 04:56:43 CET
> I have seen this when storage indexes on Exadata became unusable. The plan
> would be the same but the storage index pruning would not be executed.
>
>
>
> I have seen this when a the needed blocks of a file remained unchanged and
> had been cached for a long time and then there was an instance restart or
> in the case of RAC the query was executed on a different node.
>
>
>
> I have seen this when parallel query was used and the required number of
> “servers” were not available and the query waited to actually execute until
> enough servers were available.
>
>
>
> I **think** that is all if this: “No indexes were touched, the table was
> not purged or anything” is true.
>
>
>
> (This presumes a read only query.)
>
>
>
> I have also seen a range of releases and patches for 12.x on Exadata where
> inserting into a unique index locked the entire index until the transaction
> was complete, so that if many were fired off concurrently they serialized
> to run one at a time as each previous one was complete.
>
>
>
> There might be more, but that’s all I recall seeing.
>
>
>
> Good luck,
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ethan Post
> *Sent:* Wednesday, November 13, 2024 6:39 PM
> *To:* oracle-l
> *Subject:* Query went from millisec/exe response time to 1-2 seconds per
> exe
>
>
>
> 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)
(image/jpeg attachment: 02-image002.jpg)