Printable View
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.
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.
thanks for the replies... I will dig further.
Yes slower, but is that going to be significant with read-ahead caching and Oracle's multi-block reading?Quote:
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.
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.Quote:
Originally posted by DaPi
Yes slower, but is that going to be significant with read-ahead caching and Oracle's multi-block reading?
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.
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.
That would halve the size of the buffer cache for start!Quote:
Originally posted by marist89
I have a suspician that they changed the blocksize but kept the init.ora parameters the same.
Not necessary.. Same plans can take twice the time for 4K block size as it took with 8K block size..Quote:
Originally posted by marist89
but the more likely culprit would be the query plan changing.
Yes am hinting at sequential scans.. (and ofcourse with scattered scans also unless DBMBRC is made twice as was with 8K block size)..
Abhay.
I didn't say "THAT'S IT!", I said "more likely". You can shrug your shoulders and resign yourself to the fact that your queries are now slower, or you can figure out the reason. With three lines of explanation of the problem without knowing any details, I'd spend my time figuring out of the query plan changed. But that's just me...Quote:
Originally posted by abhaysk
Not necessary.. Same plans can take twice the time for 4K block size as it took with 8K block size..
Yes am hinting at sequential scans.. (and ofcourse with scattered scans also unless DBMBRC is made twice as was with 8K block size)..
Abhay.
Jeff,Quote:
Originally posted by marist89
I didn't say "THAT'S IT!", I said "more likely". You can shrug your shoulders and resign yourself to the fact that your queries are now slower, or you can figure out the reason. With three lines of explanation of the problem without knowing any details, I'd spend my time figuring out of the query plan changed. But that's just me...
I didnt mean to pin point you.. was just concerned if the original poster go and try comparing plans of each and every query, and by chance if he know that his application access data mostly by Index Look ups .. then it would be useless to do such a excercise..
Abhay.
Try setting this parameter in your init.ora file then re-start your database.
optimizer_features_enable=8.1.7
I had the same problem when I moved one of our 8.1.7 database to 9.2.0.1. Luckily I have another copy of the database and I was able to compare the execution plan.
excellent point!Quote:
Originally posted by edsperez
Try setting this parameter in your init.ora file then re-start your database.
optimizer_features_enable=8.1.7