Re: Explain Plan with Toad - no cost displayed for statements
From: William Robertson <williamr2019_at_googlemail.com>
Date: Sun, 17 May 2009 00:32:18 -0700 (PDT)
Message-ID: <bc59ee58-b4fd-461c-b033-548b4f2390eb_at_f16g2000vbf.googlegroups.com>
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
Date: Sun, 17 May 2009 00:32:18 -0700 (PDT)
Message-ID: <bc59ee58-b4fd-461c-b033-548b4f2390eb_at_f16g2000vbf.googlegroups.com>
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 must say I've never found GUI Explain Plan tools all that helpful, and I find dbms_xplan easier to read and more informative. Received on Sun May 17 2009 - 02:32:18 CDT