This content has been marked as final. Show 17 replies
Troach, Syb, Don, Dion,1. Yes, tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
I am certainly going to look into ways of
rewriting the query as Troach and Syb has said. What
about partitioning as Dion has mentioned? Will
partitioning work for this scenario?
2. Also, check clustering factor. If you scan wide range of index which has bad clustering factor, unnecessary "db file sequential read" is unabovidable.
3. Partitioning itself has (generally) nothing to do with "db file sequential read" wait time. What i meant was to "distribute I/O reqeust to multiple independent devices". Partitioning was just one of the a lot of methods to achieve this. If you have multiple independent devices(disks), you should distribute hot indexes/tables to as many indepedent devices as possible. Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
Is the creation of this large non-standard block size tablespace a bad idea?
Donald Burleson will wow you with tales of Unisys or HP achieving high TPC benchmarks with large block sizes: and if you have a million or so dollars to spend on your hardware; and if you are happy building a system that is designed to produce one great result but not long-term, consistent, safe throughput; and if you are happy to stick 17 underscore (i.e., hidden and unsupported) parameters into your init.ora as HP did, or 4 as Unisys did; and if you don't mind running with only one control file or 32 log groups not one of which is multiplexed... then be my guest and take Don's advice.
But if you recognise any of those things to be very special configuration steps you undertake when you've a benchmark to beat and certainly not things you'd want to ape in a production setting... then be my guest and take Oracle's own advice (and that of people like Tom Kyte) and steer well clear of multiple block sizes in a database as a performance tuning technique.
The use of non-standard block sizes will instead complicate tuning; you won't be able to automatically manage your SGA; you won't be able to have a keep or recycle cache for segments in the non-default block size cache; and you'll be using a code path which is "novel" (I don't say "unsafe", but it's not been around anywhere near as long as the default blocksize code path).
Non-default block sizes were invented to permit transportable tablespace to work properly and for no other reason. That they can be abused by those in pursuit of high TPC one-off, short-term results is not a reason to go out and deploy them in the production database you care about long-term.
Thanks for the kind words, I work vey hard at it! BTW, here are some of my current notes:
Thank you. I have your great book "Oracle Tuning: The Definitive Reference"
That was not written by me, it was written by Col. John Garmany, quite an impressive resume, an Army Ranger who can kill people 15 different ways:
Going to buy your other book 'Easy Oracle PL/SQL programming' this week.
Also, the best deal for Rampant books in Germany is Amazon, as overseas shipping is very costly!
Oracle Press author
Author of “Oracle Tuning: The Definitive Reference”