db block size difference in performance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: db block size difference in performance

  1. #1
    Join Date
    Jan 2001
    Posts
    66

    db block size difference in performance

    I have converted my application from an 8.1.7db to a 9.2.0.1db. It takes the 9i db twice as long as 8i to processes the same queries. I have converted all indexes and analyzed all tables. When I created the db I made the mistake of using a 4k block size on 9i but had an 8k block size on 8i. Should this effect performance this severely?

    Other differences:
    o 9i uses LMT as opposed DMT

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It could affect performance, but the more likely culprit would be the query plan changing.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    It may also depend on the type of data you're getting from disks. For instance like LOBS. If you're snatching a 200meg jpg and you're using a 4k block size of course it's going to take longer to put it into the buffer cache than 8k would.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    Jan 2001
    Posts
    66
    thanks for the replies... I will dig further.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by OracleDoc
    . . . If you're snatching a 200meg jpg and you're using a 4k block size of course it's going to take longer to put it into the buffer cache than 8k would.
    Yes slower, but is that going to be significant with read-ahead caching and Oracle's multi-block reading?

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by DaPi
    Yes slower, but is that going to be significant with read-ahead caching and Oracle's multi-block reading?
    Hmmm, good point. To be honest I can only share from my experiences. I'm running a spaital database that has many lobs (tif's)that range from 23 megs to 500megs. I've recently rebuilt my database from 8k block size to a 32k block size and have seen a noticable performance increase. Now granted it's nothing dramatic but it is enough to perk your eyebrows up.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Good for your LOB's - is the overall performance still good?

    I've seen it suggested that if you have a 32K Oracle blocksize and an OS/hardware blocksize of (say) 4K, then any Oracle single block read looks like an OS/hardware multi-block read and so triggers read-ahead, which in turn tends to flush the OS/hardware buffer unnecessarily. But then these flushed blocks should be in Oracle buffers by then . . . shouldn't they? Any comments?

    P.S. wouldn't spend too much time on this - as Jeff says, look to the plans!

  8. #8
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Ohh I totally agree that you should check into the plans. I was just taking it for granted that - that was the #1 thing to look at before you start messing with block size.

    As over all performance, all I can say is that my queries aren't taking as long. I've got a pretty beefy box so I've got a lot of memory thrown at it.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I have a suspician that they changed the blocksize but kept the init.ora parameters the same. This in turn could influence the optimizer to do more FTS then before.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by marist89
    I have a suspician that they changed the blocksize but kept the init.ora parameters the same.
    That would halve the size of the buffer cache for start!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width