RE: Query went from millisec/exe response time to 1-2 seconds per exe
Date: Wed, 13 Nov 2024 21:55:41 -0500
Message-ID: <1e7201db3640$b238df00$16aa9d00$_at_rsiz.com>
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
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
Subject: Query went from millisec/exe response time to 1-2 seconds per exe
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 14 2024 - 03:55:41 CET
(image/jpeg attachment: image002.jpg)