Re: FW: concepts document part about separating indexes and tablespaces
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Mon, 19 May 2008 09:36:31 -0700
O
>
> ... Let's grant Richard's contention that it is unlikely you'll win on
> performance of an individual index range scan query requiring table access.
> Now consider dozens of users with queries completely satisfied by indexes
> not competing with i/o for a full table scan in another tablespace. Unless
> you're going to argue that a full table scan is never the right thing to do
> and that none of your queries are satisfied from an index alone, I believe
> that means you sometimes get a win from separation of data and indexes,
> because statisitcally you will have less seek. If your underlying disk farm
> is SAME, you'll minimize that win and protect yourself from the possibility
> of unbalanced i/o generating sufficient queueing to degrade performance. ...
> PS: as for finding support for any stance in the Oracle documents, they do
> indeed call for SAME as a best practice, which effectively statistically
> spreads everything on the underlying disk farm.
>
In a perfect world we might be able to design a system based on known access
patterns, and it will yield the most balanced IO possible.
SAME just eliminates a lot a problems in an imperfect world.
* few people have time for the type of analysis required to know all access
patterns,
or even a significant portion of them.
* access patterns will change with changes in data. These could be due to
new lines
of business, acquisitions, divestitures, new reporting systems that query
the data
differently, application upgrades, ...
* the analysis ultimately fails when simultaneous sessions are exercising
different access patterns.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
