Re: Oracle 10g RAC performance

Joel,

> At Collaborate07 I went to some session about performance case studies
> by a fellow named Gaja (dbperfman.com), and he answered something I
> had been idly wondering about since hearing rumors of people doing it
> at an Openworld a couple of years ago. Namely, dedicating one
> instance to OLTP, and the other to reporting.
>
> Seems when you do that, there might be severe performance degradation
> as the reporting instance is constantly having to query the OLTP
> instance undo to maintain consistency. For a FTS - oops!

Gaja strikes again,

I completely understand what Gaja is talking. However there are lot of internal optimizations (and you can further tune them if required) done in this area like 'lightwork rule ' and 'fairness threshold'. If you ever had a chance to have a look at my book you will understand what I am talking here. In case if you could not find the book in the bookstore near to you, here again.

***Quoting from Oracle 10g RAC Handbook****

 Lightweight Rule and Fairness Threshold

When there are too many CR requests for a particular buffer, it will be too much of work for the holder to serve the CR buffer to the requestors. In these situations, the holder just disowns the lock on the buffer and writes the block to the disk. Now the requestor can read the block from the disk after acquiring the required lock on the object. The number of consecutive request after the holder down converts the lock elements is configurable via _fairness_threshold parameter. This parameter defaults to 4, which is usually enough for most of the instances. However, it requires special setting when one instance in the cluster is always used for queries.

Lightwork rule is invoked when the CR construction involves too much of work when there is no current block or PI blocks are not available in the cache for block cleanouts. The number of times the instance does the fairness down covertion and lightwork rule is invoked is shown in the V$CR_BLOCK_SERVER. And the view V$CR_BLOCK_SERVER also lists additional details about the CR request processing and the distribution of block requests.

V$CR_BLOCK_SERVER

Name                  		Type		Notes
------------------------	---------	------------------------
CR_REQUESTS 			NUMBER	CR+CUR =Total Requests
CURRENT_REQUESTS    		NUMBER
DATA_REQUESTS       		NUMBER
UNDO_REQUESTS      	 	NUMBER
TX_REQUESTS         		NUMBER	DATA+UNDO+TX= CR+CUR
CURRENT_RESULTS     		NUMBER
PRIVATE_RESULTS     		NUMBER
ZERO_RESULTS        		NUMBER
DISK_READ_RESULTS   		NUMBER
FAIL_RESULTS        		NUMBER
FAIRNESS_DOWN_CONVERTS   	NUMBER	# of downconverts from X
FAIRNESS_CLEARS          	NUMBER	# of time Fairness counter cleared
FREE_GC_ELEMENTS         	NUMBER
FLUSHES                  	NUMBER	Log Flushes
FLUSHES_QUEUED           	NUMBER
FLUSH_QUEUE_FULL         	NUMBER
FLUSH_MAX_TIME           	NUMBER
LIGHT_WORKS              	NUMBER	# of times light work rule evoked
ERRORS                   	NUMBER

The number of times the down coverts happens and the lightweight rule invoked from the instance startup can be obtained form the following query.

SQL> SELECT CR_REQUESTS, LIGHT_WORKS ,DATA_REQUESTS, FAIRNESS_DOWN_CONVERTS
   2 FROM
   3 V$CR_BLOCK_SERVER; CR_REQUESTS LIGHT_WORKS DATA_REQUESTS FAIRNESS_DOWN_CONVERTS

----------- ----------- ------------- ----------------------
      80919        5978         80918                  17029

When the data request to down convert ratio is more than 40%, then lowering the _fairness_threshold from default may improve the performance and greatly reduce the interconnect traffic for the CR messages. This parameter can be set to 0 when the systems, which are used for, query only purposes. Setting _fairness_threshold to 0 disables the fairness down converts. This parameter can only be altered with the consent of Oracle Support.

***END QUOTE*** Having said that, it is the trade off between the disk-IO to the cached-networked-IO. If your disk or IO subsystem is the bottleneck this will further complicate the issue as it goes back to disk to read the blocks (like your old OPS 'ping') instead of asking the holder to ship the block via wire.

Dedicating one node to reports has its own advantages/disadvantages. You have to carefully benchmark and test things in RAC before doing it in prime-time. All I can say is it can be (further) optimized if you know your application well.

YMMV! Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004 http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006 http://www.amazon.com/gp/product/007146509X/ Received on Sat Apr 28 2007 - 14:27:37 CDT