Explain Plan

[Reed, Penny]

Hello,

I am trying to get explain plan to work in Toad version 7.4.0.3 on an Oracle
8.1.7.4 database.

Due to security issues, I ran notoad for a given schema user.

In the options I have TOAD_PLAN_SQL as the plan table name and user name for
explain plan is the schema user I ran notoad under.

In the explain plan tab if you right click I have tried both options:

Use Connected user/schema (this option gives me an error ORA-02403:
plan table does not have correct format)
Always set session to statement user (not sure what this implies but I get
this error ORA-02403: plan table does not have correct format)

What can I do to get this to work?

To download the beta: http://www.toadsoft.com/beta.zip
To unsubscribe, mailto:toad-***@yahoogroups.com
Archives: http://www.yahoogroups.com/list/toad

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

[Katherine Scott]

In the options, set the table name to TOAD_PLAN_TABLE. The notoad.sql
script creates 2 tables -- TOAD_PLAN_SQL and TOAD_PLAN_TABLE. The
TOAD_PLAN_TABLE is the plan table, and the TOAD_PLAN_SQL is the table that
holds the SQLs if you check the option to save previous explain plan
statements.

"Always set session to statement user" performs an "ALTER SESSION SET
CURRENT_USER=..." before it runs the explain plan.

"Use connected user/schema" does not perform the ALTER SESSION command.

The difference is:

Suppose I am logged in as user DBAUSER, and I select a session belonging to
the SCOTT user in the Kill/Trace window. I see that SCOTT has run the
statement "Select * from EMP".

If I choose "Always set session to statement user" and do an explain plan,
then when TOAD does the explain plan, Oracle assumes that the EMP table
belongs to SCOTT. It also assumes that TOAD_PLAN_TABLE also belongs to
SCOTT. A workaround to that is to either create a public synonym to
TOAD_PLAN_TABLE (as the toadprep.sql script does), or to include the user
name in the options -- e.g. DBAUSER.TOAD_PLAN_TABLE

If I choose "Use connected user/schema" and do an explain plan, then when
TOAD does the explain plan, Oracle assumes that the EMP table belongs to me,
DBAUSER. It also assumes that TOAD_PLAN_TABLE belongs to DBAUSER.

If I'm explaining the statement "SELECT * FROM SCOTT.EMP", then there is no
ambiguity, and either option produces the same plan, as long as it correctly
finds the plan table.

-----Original Message-----
Sent: Wednesday, May 07, 2003 1:46 PM
Subject: [toad] Explain Plan
Hello,
I am trying to get explain plan to work in Toad version
7.4.0.3 on an Oracle
8.1.7.4 database.
Due to security issues, I ran notoad for a given schema user.
In the options I have TOAD_PLAN_SQL as the plan table name
and user name for
explain plan is the schema user I ran notoad under.
Use Connected user/schema (this option gives me an
plan table does not have correct format)
Always set session to statement user (not sure what this
implies but I get
this error ORA-02403: plan table does not have correct format)
What can I do to get this to work?
To download the beta: http://www.toadsoft.com/beta.zip
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

[Tom Van Natta]

There are various versions of NOTOAD.SQL too. I've run an old one before and
got errors when I tried to do an Explain Plan

Post by Reed, Penny
Hello,
I am trying to get explain plan to work in Toad version 7.4.0.3 on an Oracle
8.1.7.4 database.
Due to security issues, I ran notoad for a given schema user.
In the options I have TOAD_PLAN_SQL as the plan table name and user name for
explain plan is the schema user I ran notoad under.
plan table does not have correct format)
Always set session to statement user (not sure what this implies but I get
this error ORA-02403: plan table does not have correct format)
What can I do to get this to work?
To download the beta: http://www.toadsoft.com/beta.zip
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

To download the beta: http://www.toadsoft.com/beta.zip
To unsubscribe, mailto:toad-***@yahoogroups.com
Archives: http://www.yahoogroups.com/list/toad

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

[mark cary]

One of the tables NOTOAD creates on the version I have
is missing several columns.
If you turn sql to screen on then try to get the
explain plan it will give you the query it is running.

Grab it and run it seperately and you'll see which
columns it is looking for which are not there.
I always get 3 missing, dimension, something_start and
something_end.

Add the columns manually and it will work.

I assume there is a correct version of NOTOAD lurking
somewhere though...


--- Tom Van Natta <***@bms.com> wrote: >
There are various versions of NOTOAD.SQL too. I've

Post by Tom Van Natta
run an old one before and
got errors when I tried to do an Explain Plan

Post by Reed, Penny
Hello,
I am trying to get explain plan to work in Toad

version 7.4.0.3 on an Oracle

Post by Reed, Penny
8.1.7.4 database.
Due to security issues, I ran notoad for a given

schema user.

Post by Reed, Penny
In the options I have TOAD_PLAN_SQL as the plan

table name and user name for

Post by Reed, Penny
explain plan is the schema user I ran notoad

under.

Post by Reed, Penny
In the explain plan tab if you right click I have
Use Connected user/schema (this option
plan table does not have correct format)
Always set session to statement user (not sure

what this implies but I get

Post by Reed, Penny
this error ORA-02403: plan table does not have

correct format)

Post by Reed, Penny
What can I do to get this to work?

http://www.toadsoft.com/beta.zip

Post by Reed, Penny
To unsubscribe,
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/
http://www.toadsoft.com/beta.zip
To unsubscribe,
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

Post by Reed, Penny
begin:vcard

n:Van Natta;Tom
tel;fax:[phone]
tel;work:908-904-2214
x-mozilla-html:FALSE
org:ConvaTec / Bristol Myers Squibb;Information
Management
adr:;;100 Headquarters Drive;Skillman;NJ;08558;USA
version:2.1
title:Principal Analyst
fn:Tom Van Natta
end:vcard

__________________________________________________
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer

To download the beta: http://www.toadsoft.com/beta.zip
To unsubscribe, mailto:toad-***@yahoogroups.com
Archives: http://www.yahoogroups.com/list/toad

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

[Bert Scalzo]

I guess you guys are not seeing that there are options here
besides notoad. You or your DBA can run Oralce's UTLXPLAN
script and use that table - you just need to set the TOAD
options to use it. There is no requirment that explain plans
must use a table created by our scripts. Talk to your DBA...

Bert

Post by mark cary
One of the tables NOTOAD creates on the version I have
is missing several columns.
If you turn sql to screen on then try to get the
explain plan it will give you the query it is running.
Grab it and run it seperately and you'll see which
columns it is looking for which are not there.
I always get 3 missing, dimension, something_start and
something_end.
Add the columns manually and it will work.
I assume there is a correct version of NOTOAD lurking
somewhere though...
There are various versions of NOTOAD.SQL too. I've

Post by Tom Van Natta
run an old one before and
got errors when I tried to do an Explain Plan

Post by Reed, Penny
Hello,
I am trying to get explain plan to work in Toad

version 7.4.0.3 on an Oracle

Post by Reed, Penny
8.1.7.4 database.
Due to security issues, I ran notoad for a given

schema user.

Post by Reed, Penny
In the options I have TOAD_PLAN_SQL as the plan

table name and user name for

Post by Reed, Penny
explain plan is the schema user I ran notoad

under.

Post by Reed, Penny
In the explain plan tab if you right click I have
Use Connected user/schema (this option
plan table does not have correct format)
Always set session to statement user (not sure

what this implies but I get

Post by Reed, Penny
this error ORA-02403: plan table does not have

correct format)

Post by Reed, Penny
What can I do to get this to work?

http://www.toadsoft.com/beta.zip

Post by Reed, Penny
To unsubscribe,
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/
http://www.toadsoft.com/beta.zip
To unsubscribe,
Archives: http://www.yahoogroups.com/list/toad
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/

Post by Reed, Penny
begin:vcard

n:Van Natta;Tom
tel;fax:[phone]
tel;work:908-904-2214
x-mozilla-html:FALSE
org:ConvaTec / Bristol Myers Squibb;Information
Management
adr:;;100 Headquarters Drive;Skillman;NJ;08558;USA
version:2.1
title:Principal Analyst
fn:Tom Van Natta
end:vcard

__________________________________________________
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer

To download the beta: http://www.toadsoft.com/beta.zip
To unsubscribe, mailto:toad-***@yahoogroups.com
Archives: http://www.yahoogroups.com/list/toad

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