Performance issue after creating higher block size tablespace
- From: Saad Khan <saad4u@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 25 Feb 2010 11:23:52 -0500
Hi gurus.
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.
Any suggestions?
Thanks in advance.
Other related posts:
