1 2 Previous Next 17 Replies Latest reply: Oct 29, 2007 7:55 AM by 108476 Go to original post RSS
      • 15. Re: Db file sequential read
        601585
        Troach, Syb, Don, Dion,
        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?
        1. Yes, tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.

        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".
        • 16. Re: Db file sequential read
          51034
          Is the creation of this large non-standard block size tablespace a bad idea?

          Yes.

          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.
          • 17. Re: Db file sequential read
            108476
            Hi Wilhelm,
            Thank you. I have your great book "Oracle Tuning: The Definitive Reference"
            Thanks for the kind words, I work vey hard at it! BTW, here are some of my current notes:

            http://www.dba-oracle.com/art_builder_io_speed.htm

            http://www.dba-oracle.com/t_disk_access_full_scan_scattered_reads.htm
            Going to buy your other book 'Easy Oracle PL/SQL programming' this week.
            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:

            http://www.dba-oracle.com/resume_john.htm

            Also, the best deal for Rampant books in Germany is Amazon, as overseas shipping is very costly!

            Good Luck!


            Don Burleson
            Oracle Press author
            Author of “Oracle Tuning: The Definitive Reference”
            http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
            1 2 Previous Next