Thanks for the question, NARESH.
Asked: March 21, 2017 - 7:34 pm UTC
Last updated: March 23, 2017 - 11:54 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi,
In my Application,
one query with fixed filter columns and dynamic values, performs in msecs most of the time but once in a while the query is taking up to 1 min.
By using SQL ID I was able to get explain plan, which is same for long running query and regular fast running query.
how can I narrow down in which scenario and what actually causing the query to run slow?
and Connor said...
A couple of possibilities here
1) differing predicate values yielding different resource costs. For example,
select *
from PEOPLE_WHO_WEAR_HIGH_HEELS
where gender = :bind_val
might have the same execution plan for "bind_val=Male" and "bind_val=Female", but obviously the workload for each would be very different. Adaptive cursor sharing *might* jump in here and assist - you would see that as different child numbers in v$sql and hence potentially different plans.
2) the table is volatile in terms of transactions. Hence when it runs, it needs to undo a lot of transactions to get a consistent read, but it would be very unlikely for that to be *minutes*.
You might want to try tracing that particular sql using:
alter system set events 'sql_trace [sql:b6z8h59a39gv9]';
so you can mine the trace data for that particular SQL for all of its executions.
Is this answer out of date? If it is, please let us know via a Comment