Re: if talbe only serveral hundred mb, can partition table prove the performance?

Michael Wu wrote:

> Thank you for your response, our server was
>
> Sun E3500, CPU * 4, 4GB RAM
> A1000, 9GB HDD * 12, Raid 5 set ( there is only 1 logical disk )
>
> OS: Sun OS 5.7
>
> DB: Oracle 8.1.6.0.0 32 bit
>
> SGA: about 1.9G,
> Share pool: 600MB
> Data Buffer: about 1.3G
>
> Table names: ORDER1
> Tablespace: Order1
>
> Order split into 4 partition but still the same tablespace, because there is
> only logical disk, so I think there is no meaning to use second tablespace.
>
> The index only create one for order_no.
>
> optimizer was CBO,
>
> I am sorry about that I can not offer the 6,7,8 to you right now, after
> Monday, I will in office and check the 6, 7, 8 detail.
>
> Thank you very much!!
>
> Regards
> Michael Wu
> kuohwa_at_ms21.hinet.net
>
> >
> > Insufficient information from which to formulate an answer:
> >
> > 1. What hardware?
> > 2. What operating system?
> > 3. What Oracle version and edition?
> > 4. Indexes?
> > 5. Optimizer?
> > 6. Current statistics with DBMS_STATS?
> > 7. Copy of the SQL statement being executed?
> > 8. Explain Plan?
> >
> > We can't look over your shoulder.
> >
> > Daniel Morgan
> >

Bugs in the 8.1.6 optimizer have been reported and known for years ... 3 or 4 I think. Had you run explain plan on your SQL you likely would have noticed indexes not being used and/or the optimizer not doing its job.

You can either patch 8.1.6, which I wouldn't advise or upgrade to the latest 8.1.7 or 9.2. Upgrading to 8.1.7 being the easier of the two.

Doing so and running DBMS_STATS to obtain current statistics nightly, weekly, whatever will make a huge difference.

Daniel Morgan Received on Mon Dec 16 2002 - 02:00:45 CST