| Avg time taken by an execution plan [message #582040] |
Fri, 12 April 2013 12:27  |
 |
getamaze
Messages: 3
Registered: April 2013
|
Junior Member |
|
|
How can i check the avg time taken by an execution plan.
Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.
|
|
|
|
|
|
|
|
|
|
| Re: Avg time taken by an execution plan [message #582054 is a reply to message #582040] |
Fri, 12 April 2013 14:59  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I can't read your code or exec plans (I don't think you can either!) but perhaps I can give you a few pointers in response to your original question.
Query v$sql on your sql_id and plan_hash_value and you will find the number of executions with each plan and the total elapsed time. So you will know which is is quickest on average. But you have a lot of bind variables in there, so almost certainly what you are seeing is the result of adaptive cursor sharing: different plans for different binds. If you want to nail down one plan, do it with a baseline (if you have Enterprise Edition licences) or an outline (Standard Edition). Or trust Uncle Oracle to get it right, but remember that ACS is highly sensitive to patchsets. You need the latest.
|
|
|
|
|
|
| Re: Avg time taken by an execution plan [message #582061 is a reply to message #582056] |
Fri, 12 April 2013 15:21 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Oh, right. That makes it harder to see why there would be several child cursors for one statement. Does release 10.x have v$sql_shared_cursor to explain why a new version was created?
I wouldn't worry too much about the exec plans showing one row, most of the access methods are index unique scan so it should be one or zero. But there is one funny: the access to OFFER by index range scan, is that really going to return only one row?
|
|
|
|
| Re: Avg time taken by an execution plan [message #582066 is a reply to message #582061] |
Fri, 12 April 2013 15:55 |
|
getamaze
Messages: 3
Registered: April 2013
|
Junior Member |
|
|
Thanks, Finally i got the query i think which i was trying to find.
select plan_hash_value,elapsed_time,cpu_time from v$sql where sql_id = 'abcd....';
But i couldnt follow you guys, i am just a commerce developer and a new learner for oracle.
I have this severe issue where this particular query keeps changing its plan and whenever the data changes due to data loads , it is really tough to control it and occupies all resources and blocks everything. I have seen once around 20 plans for this query and currently with have created an SQL profile and locked it for the query execution, since then we dont see any issue with this query but i have a doubt for how long i could keep that profile. Do you have any suggestion on this?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
