Re: Need suggestions for filesystem config, spindle separation, striping for HP-UX/PeopleSoft

Kyle -that's rather taken out of context. My response referred to the suggestion that "[the] performance tuning manuals ... say that ... since the index is read first, then the table afterward ... therefore there is no disk contention issue."

Clearly there is. The question I suppose then comes down to how serious that contention is, and whether any given implementation can minimise or eliminate it. Striping is a perfectly fine way of doing just that, given enough spindles (ye olde RAID 0+1 being a thoroughly recommended implementation methodology). Separating tables and indexes is also a perfectly fine way.

Your take on performance being what the Users notice, and therefore write contention not being an issue is an interesting one -but as you go on to explain that anything that slows down DBWR is capable of provoking performance degradation that Users *will* start noticing, we needn't pursue it.

Regards
HJR "kyle hailey" <oraperf_at_yahoo.com> wrote in message news:7rms8t849hpfab33p2ng6kb04hei7a17q8_at_4ax.com...
> RE putting indexes and data in the same tablespace:
>
> > Howard J. Rogers wrote:
> >
> > I don't know what version of the Performance Tuning Manuals you are
 using,
> > but that piece of advice is utter nonsense. It might be fine for reads,
 but
> > what about DML? If you update a table, you also have to update the
> > index(es) on that table -and if indexes and tables are on the same
 spindle,
> > your i/os will inevitably have to queue up one behind the other.
>
>
> "utter nonsense" - that's a bit strong sounding to me. Lets think
> about this ... it is unclear as to whether Indexes and Data need to be
> separate for perfomance reasons.
>
> When we are talking about performance, we are talking about users
> performance. Users wait for data file reads not data file writes
> (with some special case exceptions). It is the DBWR that actually does
> the write, and it does the write at some later time than the actual
> transaction and the order in which the write is done depends of
> various factors. The fact that a user dirties a block in the SGA, ie
> in memory not on disk, from a table and an index which are stored in
> same tablespace (maybe on the same disk or not) doesn't necessarily
> make a difference to the users performance.
>
> There are the cases of interference, when writes are being done on
> files being read. There are the cases where DBWR gets behind in taking
> care of the buffer cache with resulting free buffer waits, write
> complete waits, checkpoint problems etc, but none of this is obviously
> helped or hurt by putting INDEX and DATA in different tablespaces
> because of options like striping and the fact that we don't know when
> and in what order the writes will be done anyway.
>
> The idea of ridgidly dividing data for performance breaks down in the
> world of striping and write back cache. Benchmarks done by Oracle tend
> to stripe data and indexes over pretty much everything available up to
> cetain limits. I know the EMC guys are really touchy about striping
> beyond 16 axes or something, but EMCs are a whole nother ball game
> with typically large write caches. Most serious client applications I
> worked on, and certainly the newer ones that had access to recent disk
> technogy for their original implementations, depended on striping (
> Air France, Bougues, Coface, Aerospatial, Cogema, Swiss PTT, Italy
> PTT, Carrefour, Dresdner, European Community, Alcatel, Baan
> benchmarks, Sap benchmarks, SNECMA, APHP, St Gobain, Total Gaz,
> SNCF, France Telecom, Societe General, MAIF, Gravelow, etc). I have
> seen some important databases that didn't stripe (Handspring for
> example) but those databases weren't even in archive log mode so lots
> to complain about there. I've seen SAP benchmarks done with with
> strategic data layout and then again I've seen them on no-brainer
> striping layouts and the striping cases were definitly better. I take
> it for granted that anyone really interested in performance of an
> important databse is going to stripe. I can imagine some very, very
> specific application that depends on non-striped disk and datafile
> layout but I have yet to run accross one of these that worked better
> than its striped version not to mention with write cache. Separating
> data in the striping, especially with write back cache, becomes more
> of and adminstrative choice.
>
> There are performance cases for separting things that have unusual
> read or write access, such as the special cases of RBS and TEMP of
> course but also batch tables, but this is more DSS.
> OLTP tends to be a good place for striping because of the random
> and frequent transaction natures but its also worth noting that
> Oracle TPC-D (DSS) benchmarks have been massively striped.
>
> Best
> Kyle
>
> www.geocities.com/oraperf
>
Received on Fri Feb 16 2001 - 22:39:05 CST