Re: Oracle Myths- Tablespace placement answered by Oracle TPC-C

This crazy "old dba's tale" comes up every so often.

There is no circumstance under which, in a give single transaction, a table and its index(es) will ever be accessed concurrently. Full table scan, range scan, fast full scan, whatever. Therefore the separation of tables and associated indexes is completely irrelevant to performance tuning.

Any concurrent access to such logically related objects is a function of concurrent session usage, and so must be viewed as aggregate statistical values. If a particular object is busy and causes contention on that device, move it to a less busy device.

No fancy trickery is needed. The only consideration for IO tuning is to balance IO across devices. Look at the actual IO per disk, paying particular attention to average access times and wait times, then locate objects on devices according to the numbers, not the logical relationships. For a given storage scheme, tune for smoothness.

Better yet, just stripe and mirror everything.

• ricky

Pablo Sanchez wrote:
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:ac41ca$r87$1_at_lust.ihug.co.nz...
> >
> > > > So yes, you can get performance benefit by splitting the data
> and indexes
> > > onto different disks."
> > >
> >
> > So now you go back to Metalink and say "prove it". Preferably with
> some
> > statistics. They won't be able to.
>
> I think the burden of proof is on folks who don't believe it's
> important.
>
> As of this writing, the seventh place TPC-C result is an HP 9000
> Superdome Enterprise Server running Oracle 9i. Reviewing the FDR, on
> page 98, we see Appendix E - Disk Storage. Notice that no table
> segments are co-mingled on the same tablespace as any index/cluster
> tablespaces:
>
> http://www.tpc.org/results/FDR/TPCC/tpcc_hp_sd_750_fdr.pdf
> [1410KB]
> --
> Pablo Sanchez, High-Performance Database Engineering
> mailto:pablo_at_hpdbe.com
> http://www.hpdbe.com
> Available for short-term and long-term contracts
Received on Thu Jun 13 2002 - 20:17:16 CDT