Performance issue after creating higher block size tablespace
Date: Thu, 25 Feb 2010 11:23:52 -0500
I've a production database running on oracle10.2.0.3 at SUSE linux 10. The default DB_BLOCK_SIZE for the database is 4K.
There was a performance complain coming from the users and developers asked me to look into that. They particularly complained about one stored procedure that was taking too much time. Now when I looked into the stored proc, I saw the insert statement in one particular table which is something more than 4 million rows while selecting from a bunch of other tables.
So what I did, I created a new tablespace with the db_block_size 8K and moved all the tables that were used in that SP in the new tbs.
And guess what, the new response came after that showed its taking almost double the time as it was taking earlier. The AWR report shows a lot of user IO activity and the tablespace that is hit most is the new one. Now is it due to the different block size for this new tablespace? Is Oracle finding it hard to manage 8k blocks inside the SGA designed for 4K originally?
The db_cache_size is set to 8192 and db_8k_cache_size is also set to 8192.
Is there any other step I can take? I dont want to revert it back to 4k , I think it should work.
Thanks in advance.Received on Thu Feb 25 2010 - 10:23:52 CST