Re: Optimizer Hints and Explain Plan

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 20 Oct 2009 06:47:07 -0700 (PDT)
Message-ID: <04d76dff-d54b-419d-985f-ba67495c24e2_at_m1g2000vbi.googlegroups.com>

On Oct 20, 7:51 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Oct 20, 2:03 am, DeB <debashish.majum..._at_gmail.com> wrote:
>
> snip
>
> > Hi All,
> >    I'm using /* no_cpu_costing */ hint in my select statements while
> > generating the explain plan. Is it a good idea to use it while
> > analyzing the Explain Plan output? Please suggest.
>
> > --
> > Thanks
> > DeB
>
> You have a comment in your select statement not a hint.
>
> No it's not a good idea don't you want to use the real explain plan?
>
> Try looking at structured data or optimizer magic blogs as well as
> Tanel Poders site for some better ideas.  Both the optimizer magic and
> structured data as done by Oracle Corp experts and Tanel has a whole
> library of useful scripts.

Deb, hp is right you left out the "+" in your post so we have to assume you left it out of your actual SQL statement also.

Does your system even use cpu costing?

Generally when you first explain SQL you want to explain the SQL exactly as it is running. That is you want to capture the actual SQL from the shared pool and you want your session parameters set the same as the session that runs the SQL of interest.

Then once you know what Oracle is doing with the SQL you can use hints and change session parameters to see the effect on the plan.

HTH -- Mark D Powell -- Received on Tue Oct 20 2009 - 08:47:07 CDT