Re: Performance issue after creating higher block size tablespace

From: <brent_day_at_comcast.net>
Date: Thu, 25 Feb 2010 16:32:56 +0000 (UTC)
Message-ID: <407364972.7809651267115576643.JavaMail.root_at_sz0026a.emeryville.ca.mail.comcast.net>

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_at_gmail.com> To: oracle-l_at_freelists.org
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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 25 2010 - 10:32:56 CST

This message: [ Message body ]
Next message: Denis: "Re: ** ssh to copy/ftp files with compress"
Previous message: Mark W. Farnham: "RE: ** ssh to copy/ftp files with compress"
In reply to: Saad Khan: "Performance issue after creating higher block size tablespace"
Next in thread: Martin Klier: "Re: Performance issue after creating higher block size tablespace"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message