Re: Performance issue after creating higher block size tablespace
- From: brent_day@xxxxxxxxxxx
- To: saad4u@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 25 Feb 2010 16:32:56 +0000 (UTC)
Why would you want to change your block size? Consider tuning your SQL before
changing block size or creating new tablespace with larger block size.
I am working through a similar exercise for one of our stored procs that had a
very ugly query taking 2 hours and generating 7+ million physical reads. I was
able to rewrite the query and now the process finishes in 9 minutes and only
296,554 physical read.
Is your query one that you could post to the list?
Brent
----- Original Message -----
From: "Saad Khan" <saad4u@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, February 25, 2010 9:23:52 AM GMT -07:00 US/Canada Mountain
Subject: Performance issue after creating higher block size tablespace
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:
