Re: Optimisation issues.

Craig Burtenshaw wrote:

> Hi,
> Okay, so I didn't realise what information was required.
> Here it goes:
>
> Sun Solaris 2.7 - Enterprise 450.
> Oracle 8.1.7
> Optimiser = CHOOSE
> Statistics were with Explain Plan in TOAD.
>
> Cheers
>
> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3F16072F.A725B5C3_at_exxesolutions.com...
> <SNIP
> >
> > Lets see ... no hardware, no operating system, no version, no edition, no
> init
> > parmeters, no indication of RBO or CBO, no indication as to whether
> statistics
> > were created using DBMS_STATS, no EXPLAIN PLAN.
> >
> > So you want someone to guess?
> >
> > I guess it is the humidity. ;-)
> >
> > Seriously ... give us something to work with.
> > --
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > damorgan_at_x.washington.edu
> > (replace 'x' with a 'u' to reply)
> >
> >

Those statistics are worthless. You must run DBMS_STATS regularly as follows:

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schema_name', CASCADE=>TRUE); TOAD is incapable of performing this function. Then, if the problem persists run EXPLAIN PLAN and post the two plans.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)