Re: FW: concepts document part about separating indexes and tablespaces

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

--
http://www.freelists.org/webpage/oracle-l