-
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
-
It could affect performance, but the more likely culprit would be the query plan changing.
Jeff Hunter
-
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
-
thanks for the replies... I will dig further.
-
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?
-
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
-
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!
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|