Morten Torstensen wrote:
John R Pierce wrote:
in our case, our production systems are a very large very complex realtime oracle database running on large scale Sun enterprise hardware on bigiron EMC storage, using dozens and dozens of raid10 logical volumes as you do NOT want to have a single 10TB volume, sorry. by hand optimizing the tablespace layouts of the applications tables and indicies, which have very specific access patterns, we can get double the throughput of the blind 'just stripe the universe' approach. Since
Oh you would not have a single 10TB volume of course: and it is still a little quaint to hand optimize tablespaces. That is something we did in the 90s. Our own tests on multi-TB databases and modern SAN systems, the shotgun approach beat hand optimization every time. And we are talking about people with dozens of years with SQL optimization behind them. Intelligent I/O prefetch adaption, intelligent and dynamic access plans... the world of performance in the RDBM world is changing and old rules for performance are changing with it.
Im not sure im following the language correctly ... Morten and John - you seem to be at opposite ends of the opinion scale about hand optimising tablespaces?
Maybe you have different definitions of "hand optimising tablespaces"?
Morten - Are your systems similar enough to Johns to make a valid comparison about the merits of the shotgun approach vs "hand optimising tablespaces"?
For the benefit of the rest-of-us DBAs, i understand "hand optimising tablespaces" to mean ... "choosing where a table (or a partition of a table) will physically live, and where its indexes will live so that roughly; sum(reads/s + writes/s on spindle [1..n])^2 ) is as low as possible. (ie balanced across all spindles). I have never had a db span anything bigger than a redundant fibre channel array controller (yep - a bit outdated now), but if it did, then balance across n controllers as well. I imagine you also decide if/which tables or indexes are pinned in memory?
Thanks to both of you for the info so far :)
Regards,
MrKiwi