Home » RDBMS Server » Performance Tuning » Avg time taken by an execution plan (Oracle 10 G)
Avg time taken by an execution plan [message #582040] Fri, 12 April 2013 12:27 Go to next message
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 #582042 is a reply to message #582040] Fri, 12 April 2013 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Actually i have a very big query and it changes its execution plan very often
I find the statement above hard to believe.

>How can i check the avg time taken by an execution plan.
I am unaware that such a number is automagically computed for you.

does the query utilize Bind variables?

post two examples of different plan from same SQL showing fast & slow times.
Re: Avg time taken by an execution plan [message #582046 is a reply to message #582042] Fri, 12 April 2013 12:58 Go to previous messageGo to next message
getamaze
Messages: 3
Registered: April 2013
Junior Member
Please find it attached.
Query as well as the plan.
Since i am new to this forum, i am sorry if i break any of your protocol.
  • Attachment: query.txt
    (Size: 142.90KB, Downloaded 56 times)
Re: Avg time taken by an execution plan [message #582048 is a reply to message #582046] Fri, 12 April 2013 13:18 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
I accept that you tried, but I found the content unusable.
I could not obtain a single valid SQL statement.
The line wrap resulted in EXPLAIN PLAN be difficult to read & decipher;
but it appeared that many of the Plan ran in 1 second & most tables contained only 1 row.
I doubt they represented what you complain about.

Good Luck!
Re: Avg time taken by an execution plan [message #582054 is a reply to message #582040] Fri, 12 April 2013 14:59 Go to previous messageGo to next message
John Watson
Messages: 4863
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 #582056 is a reply to message #582054] Fri, 12 April 2013 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
John,

OP reports V10 & ACS is V11 feature.
Right?
Re: Avg time taken by an execution plan [message #582061 is a reply to message #582056] Fri, 12 April 2013 15:21 Go to previous messageGo to next message
John Watson
Messages: 4863
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 Go to previous message
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?
Previous Topic: Tuning options while using RBO
Next Topic: Application services
Goto Forum:
  


Current Time: Sun Dec 21 06:08:28 CST 2014

Total time taken to generate the page: 0.08460 seconds