| Different EXPLAIN PLAN Before and after execution [message #457524] |
Tue, 25 May 2010 01:20  |
prateekgupta1210
Messages: 5
Registered: May 2010
Location: Mumbai
|
Junior Member |
|
|
Hello,
I was working upon a query (database : Oracle , Version 10.2.0.4.0) , I found that the EXPLAIN PLAN for the query before execution and after execution were different.
Can this be possible ?
Note :- Statistics of the underline tables were gather properly.
Thanks,
-Prateek
|
|
|
|
|
|
|
|
|
|
| Re: Different EXPLAIN PLAN Before and after execution [message #457559 is a reply to message #457531] |
Tue, 25 May 2010 03:44  |
prateekgupta1210
Messages: 5
Registered: May 2010
Location: Mumbai
|
Junior Member |
|
|
Thanks Michel,
Even this has not resolve my problem.
I will list down my observations :-
1) I generated the explain plan of a query (say , query A) , ran the query A (the query A keep running for 20 mins , then i cancel the execution).
2) I added some HINTS (ordered) and modifed query A (name the modified query with hint as query B).
3) Check the Explain Plan for query B (different for that of step 1) , ran the query B (it took around 2 min to complete).
4) I again checked the Explane Plan for query A (this time it was same as query B [with hint one]) and it completed in less that 2 mins as well.
Thanks,
-Prateek
|
|
|
|
|
|
| Re: Different EXPLAIN PLAN Before and after execution [message #457849 is a reply to message #457559] |
Wed, 26 May 2010 02:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The reason why we want to see evidence of what's happened is that it is not unheard of for people to make mistakes, or misunderstand what is going on.
With something like output from SQL*Plus, we can all see what happened.
In this case, you're saying that without anything else happening, the execution plan of query A changed between step 1 and 4.
This is a little tricky to believe on face value.
If you can show us that it's definitely happened, then that's a different matter.
|
|
|
|
| Re: Different EXPLAIN PLAN Before and after execution [message #457853 is a reply to message #457849] |
Wed, 26 May 2010 03:33 |
prateekgupta1210
Messages: 5
Registered: May 2010
Location: Mumbai
|
Junior Member |
|
|
Hey ,
I would definitly like to show you , but now its difficult for me to replicate the same .
Any how my problem is resolved .
What my main aim was to gain performance , earlier , underlying tables used in the query A were populated
just before the exection of the query A . Statistics are gather on the underlying tables long after that .
What I did in my stored procedure is to ANALYZE the underlying tables just before the query A execution.
This has improve the performance considerably .(earlier execution time 7 hrs 10 min & execution time after change : 9 mins )
Cheers  ,
-Prateek
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
