Explain plan

[Stephen Rooney]

Using TOAD 7.5.2 on an Oracle 9iR2 database, I can explain plan a
query in the SQL editor and set it off. If I open up another TOAD
session and view the explain plan in the Kill/Trace window for the
query running in the other session, quite often the plan shown in
Kill/Trace is different to the one in the SQL editor window. I've
even altered the session to set cursor_sharing=exact but often I
still see different plans in SE and KT for the same query.

Does anybody have any idea why this might be the case or what I can
do to ensure that I see consistent results?

Regards,
Steve Rooney





Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

Do you know about the other TOAD Yahoo Message Boards? Go here to find out more - http://www.toadsoft.com/maillist.htm
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[Erwin Rollauer, Mr]

Is this possibly an Oracle problem. When you do an Explain Plain Oracle
says what it thinks it will do. But when you actually run it, it may end
up being different. Kill/Trace shows what is actually being used. I
vaguely remember hearing about something like this. Maybe somebody who
knows stuff can shed more light on this.

I seem to be forgetting more and more that has happened and remembering
more and more that has not happened.

Erwin Rollauer, ISR, McGill University, 514 398-5023 ext 00626

-----Original Message-----
From: Stephen Rooney [mailto:***@yahoo.co.uk]
Sent: October 29, 2004 7:01 AM
To: ***@yahoogroups.com
Subject: [toad] Explain plan



Using TOAD 7.5.2 on an Oracle 9iR2 database, I can explain plan a
query in the SQL editor and set it off. If I open up another TOAD
session and view the explain plan in the Kill/Trace window for the
query running in the other session, quite often the plan shown in
Kill/Trace is different to the one in the SQL editor window. I've
even altered the session to set cursor_sharing=exact but often I
still see different plans in SE and KT for the same query.

Does anybody have any idea why this might be the case or what I can
do to ensure that I see consistent results?

Regards,
Steve Rooney





Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

Do you know about the other TOAD Yahoo Message Boards? Go here to find
out more - http://www.toadsoft.com/maillist.htm
Yahoo! Groups Links









Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

Do you know about the other TOAD Yahoo Message Boards? Go here to find out more - http://www.toadsoft.com/maillist.htm
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

[Stephen Rooney]

Erwin,
I thought kill/trace just did an explain plan for whatever is in the
current statement tab. In Oracle 9i if you have the parameter
cursor_sharing set to "similar" any literals in the current statement
tab show up as system generated bind variables. If you have a literal
in a GROUP BY clause TOAD will error with "ORA-00979 Not a GROUP BY
expression" from the kill/trace window if you attempt an explain
plan. If you alter your session to set cursor_sharing=exact, the
literals show up in the current statement tab in kill/trace and an
explain plan will work fine. If kill/trace explain plan showed what
Oracle actually did, surely it would not care a jot about these
literals, and this is what leads me to believe that kill/trace
explain plan doesn't show what Oracle did but what it thinks it will
do. Which of course gets back to the original question about why they
are different in the first place.

Regards,
Steve Rooney

Post by Erwin Rollauer, Mr
Is this possibly an Oracle problem. When you do an Explain Plain Oracle
says what it thinks it will do. But when you actually run it, it may end
up being different. Kill/Trace shows what is actually being used. I
vaguely remember hearing about something like this. Maybe somebody who
knows stuff can shed more light on this.
I seem to be forgetting more and more that has happened and

remembering

Post by Erwin Rollauer, Mr
more and more that has not happened.
Erwin Rollauer, ISR, McGill University, 514 398-5023 ext 00626
-----Original Message-----
Sent: October 29, 2004 7:01 AM
Subject: [toad] Explain plan
Using TOAD 7.5.2 on an Oracle 9iR2 database, I can explain plan a
query in the SQL editor and set it off. If I open up another TOAD
session and view the explain plan in the Kill/Trace window for the
query running in the other session, quite often the plan shown in
Kill/Trace is different to the one in the SQL editor window. I've
even altered the session to set cursor_sharing=exact but often I
still see different plans in SE and KT for the same query.
Does anybody have any idea why this might be the case or what I can
do to ensure that I see consistent results?
Regards,
Steve Rooney
Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html
Do you know about the other TOAD Yahoo Message Boards? Go here to find
out more - http://www.toadsoft.com/maillist.htm
Yahoo! Groups Links

Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

Do you know about the other TOAD Yahoo Message Boards? Go here to find out more - http://www.toadsoft.com/maillist.htm
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/