Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Any performance benefits in going to db_16k_cache_size or db_32k_cache_size

RE: Any performance benefits in going to db_16k_cache_size or db_32k_cache_size

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 10 Jan 2007 10:12:41 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF8DEEA1@WIN02.hotsos.com>


I have the same opinion as the one Mark describes here.  

One more comment: Why guess, when you can KNOW.  

If you need to know, test it, and measure the performance.    

Cary Millsap

Hotsos Enterprises, Ltd.

http://www.hotsos.com

Nullius in verba  

Hotsos Symposium 2007 / March 4-8 / Dallas

Visit www.hotsos.com for curriculum and schedule details...


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D Sent: Wednesday, January 10, 2007 9:43 AM To: oracle-l_at_freelists.org
Subject: RE: Any performance benefits in going to db_16k_cache_size or db_32k_cache_size  

Contrary to what some authors have stated about using a larger block size I am of the opinion that there is generally little to no performance benefit to using 16k or 32k block sizes on most systems especially when you can into consideration the increased concurrent update activity that can end up directed to the same block.  

From chapter 4 Memory Structures of his book, Expert Oracle Database Architecture, Tom Kyte says, "multiple blocksizes were not intended as a performance or tuning feature, but rather came about in support of transportable tablespaces" (p 147).  

It you really know what you are doing (understand the application and data) I am sure the feature can be put to good use in some cases, but overall I think this is a feature that should be used with caution.  

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Abhay Suresh Kulkarni

	Sent: Wednesday, January 10, 2007 3:05 AM
	To: arunchakrapanirao_at_gmail.com; oracle-l_at_freelists.org
	Subject: RE: Any performance benefits in going to
db_16k_cache_size or db_32k_cache_size

> I was trying to do some bench marking here for one of the
queries with 8,16 and 32k index tablespaces.

> All I am seeing is the logical reads going down by half but
the elapsed time for these queries are still the same.          

        For operations like nested loop, PIO may go down by 2 but LIO will be same. As far as timing of queries is concerned, tune it to use better access paths if any.          

        Collecting histograms with appropriate bucket size will to the trick for many a bad queries.          

        Rgds

        Abhay          

        Manhattan Assoicates

        Phone: 40418080 Ext: 4635          

	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of arun chakrapani rao
	Sent: Wednesday, January 10, 2007 10:10 AM
	To: oracle-l_at_freelists.org
	Subject: Any performance benefits in going to db_16k_cache_size
or db_32k_cache_size          

        Hi all,

        I am currently working on a 1.5T DSS running on 8k blocksize.

        This db is 90% of the time doing sequential reads.

        Was wondering if we go for a 16k or 32k blocksize just on index alone

        would there be any benefits in performance.

        Has anybody impletemented this and seen any performance benefits,

        Please do share your experience.

        I was trying to do some bench marking here for one of the queries with

        8,16 and 32k index tablespaces.

        All I am seeing is the logical reads going down by half but the

        elapsed time for these queries are still the same.

        Now what am i missing?

        Please do share your thoughts.          

        --

        thanks in advance.

        Arun

        --

        http://www.freelists.org/webpage/oracle-l                    

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 10:12:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US