Re: Optimisation issues.

Hi Craig,

Us Canberra guys have got to stick together :)

There could be a number of reasons some of which include:

• The statistics not being accurate (not sure how you've got the stats with toad but look at the dbms_stats package)
• Histogram statistics being present in prod but not in testing if the low cardinality of status is non-typical
• The clustering factor of the status column being somewhat different between the 2 databases (with the values of status being deemed to be distributed among more blocks in test than in prod)
• Key parameters such as db_file_multiblock_read_count, optimizer_index_cost_adj, optimizer_index_caching being somewhat different
• Parallelism kicking in on test
• Rows being more compactly stored in the "fresher" test table vs the possibly fragmented prod table resulting in somewhat different High Water Marks
• etc, the list goes on....

The reason why selecting just the status column uses the index in test is probably due to the fact that it doesn't need to visit the table if using the status index hence making the index more appealing to the cbo.

Are the statistics as listed in the DD similar between the two databases? What are the execution plans ?
What's the cost of using the index (via a hint) and how does it compare to the FTS ?

Good Luck

Richard

"Craig Burtenshaw" <crb_at_amsa.gov.au> wrote in message news:3f160e57$0$95045$c30e37c6_at_lon-reader.news.telstra.net...
> 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)
> >
> >
>
>
Received on Thu Jul 17 2003 - 08:07:33 CDT