Re: Explain Plan with Toad - no cost displayed for statements
From: joel garry <joel-garry_at_home.com>
Date: Mon, 18 May 2009 11:36:06 -0700 (PDT)
Message-ID: <c893c355-00aa-4c85-8112-b26006e9faa9_at_x1g2000prh.googlegroups.com>
On May 17, 12:32 am, William Robertson <williamr2..._at_googlemail.com> wrote:
> On May 16, 9:56 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
> > Comments embedded.
>
> > On May 15, 9:32 am, PerMa <pmlov..._at_gmail.com> wrote:
>
> > > Hi!
>
> > > I have a lot of heavy queries I need to analyze, and I was hoping to
> > > be able to use Toads "Explain Plan" funtionality for doing this.
>
> > > So far I have installed the explain plan tables, and when I run
> > > queries for Explain plan, I get some information, but not the
> > > essential information such as cost, bytes, cpu_cost, io_cost etc
>
> > > Why can't I see this information?
> > > I have a couple of theories:
>
> > > 1)
> > > Could there be some net8 / protocol problems between Toad and the
> > > database?
>
> > No.
>
> > > We have the following system:
> > > Oracle9i Release 9.2.0.6.0 - Production
> > > Toad Xpert v 9.7.2.5 w SQL optimizer
> > > Oracle Net8 client 8.1.700
>
> > > When I start Toad, it tells me that "Oracle client version is
> > > significantly older than server. Client should be same version or
> > > newer".
>
> > > ..but Toad in general works, so this is just a hunch!
> > > I've tried to figure out how to upgrade net8, but not found it on
> > > Oracles pages so far.
>
> > > 2)
> > > Could there be some rights problem when activating Explain Plan? I
> > > have installed the explain plan tables with the admin user we have
> > > access to. I beleive this user has maximum priveliges, but perhaps
> > > there could be a way to verify this?
>
> > You're getting a plan, so the answer to that is no
> > .
>
> > > 3)
> > > Perhaps Toad needs to enable cost logging some way?
>
> > No.
>
> > > Hope someone could help me with this!
>
> > > Br,
> > > PerMa
>
> > TOAD needs to use a current version of the PLAN_TABLE. Install the
> > current plan_able usng $ORACLE_HOME/rdbms/admin/utlxplan.sql and
> > you'll see the difference in output.
>
> > David Fitzjarrell
>
> IIRC TOAD uses its own TOAD_PLAN_TABLE.
Date: Mon, 18 May 2009 11:36:06 -0700 (PDT)
Message-ID: <c893c355-00aa-4c85-8112-b26006e9faa9_at_x1g2000prh.googlegroups.com>
On May 17, 12:32 am, William Robertson <williamr2..._at_googlemail.com> wrote:
> On May 16, 9:56 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
> > Comments embedded.
>
> > On May 15, 9:32 am, PerMa <pmlov..._at_gmail.com> wrote:
>
> > > Hi!
>
> > > I have a lot of heavy queries I need to analyze, and I was hoping to
> > > be able to use Toads "Explain Plan" funtionality for doing this.
>
> > > So far I have installed the explain plan tables, and when I run
> > > queries for Explain plan, I get some information, but not the
> > > essential information such as cost, bytes, cpu_cost, io_cost etc
>
> > > Why can't I see this information?
> > > I have a couple of theories:
>
> > > 1)
> > > Could there be some net8 / protocol problems between Toad and the
> > > database?
>
> > No.
>
> > > We have the following system:
> > > Oracle9i Release 9.2.0.6.0 - Production
> > > Toad Xpert v 9.7.2.5 w SQL optimizer
> > > Oracle Net8 client 8.1.700
>
> > > When I start Toad, it tells me that "Oracle client version is
> > > significantly older than server. Client should be same version or
> > > newer".
>
> > > ..but Toad in general works, so this is just a hunch!
> > > I've tried to figure out how to upgrade net8, but not found it on
> > > Oracles pages so far.
>
> > > 2)
> > > Could there be some rights problem when activating Explain Plan? I
> > > have installed the explain plan tables with the admin user we have
> > > access to. I beleive this user has maximum priveliges, but perhaps
> > > there could be a way to verify this?
>
> > You're getting a plan, so the answer to that is no
> > .
>
> > > 3)
> > > Perhaps Toad needs to enable cost logging some way?
>
> > No.
>
> > > Hope someone could help me with this!
>
> > > Br,
> > > PerMa
>
> > TOAD needs to use a current version of the PLAN_TABLE. Install the
> > current plan_able usng $ORACLE_HOME/rdbms/admin/utlxplan.sql and
> > you'll see the difference in output.
>
> > David Fitzjarrell
>
> IIRC TOAD uses its own TOAD_PLAN_TABLE.
I'm not using toad, but you may be right: http://www.toadsoft.com/get2know9/#Explain
>
> I must say I've never found GUI Explain Plan tools all that helpful,
> and I find dbms_xplan easier to read and more informative.
I have very little pinned up on my cube at work, and mostly it is screen prints of bogus sql from dbconsole. Of course, I'm dealing with a sql generator messing up dynamically in production, rather than people writing sql directly, so I may have a skewed idea of helpfulness. It tends to be answering "WTF is taking over the server?"
jg
-- _at_home.com is bogus. I know, let's just tell everybody everybody else's personal information! http://www.washingtonpost.com/wp-dyn/content/article/2009/05/11/AR2009051102299.htmlReceived on Mon May 18 2009 - 13:36:06 CDT