RE: LGWR, EMC or app cursors?

and (not but), I forgot to mention, for really tiny tables, almost always but especially in RAC, a single table hash cluster is the neatest thing since sliced bread. Or at least the neatest thing since I read a note by Steve Adam about how cool they are and tested them myself. (Longer ago than most of you have been on the planet.)

Hash on that column that tells you the row is for a particular node and Bob’s yer uncle unless by some anti-miracle they hash together.  

(This is NOT to say that single table hash clusters are bad for big tables. Big tables are not included in the prediction “almost always.” Big tables are often better served by partitioning and there are other considerations depending on how big “big” is.)  

I encourage you to try a single table hash cluster for this particular one row per instance table and report the results.  

If nothing else, this should remove the gcc issues and retrieve only the single relevant block by plan so that remaining waits and service times focus more cleanly on any infrastructure problems.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Friday, October 11, 2019 2:40 PM
To: oracle-l_at_freelists.org
Subject: Re: LGWR, EMC or app cursors?  

And it is possible to set so called Haqqani factor by using "minimize records_per_block". That number can make a difference for the optimizer. Jonathan was writing about that long time ago.

On 10/11/19 10:55 AM, Mark W. Farnham wrote:

Apart from any i/o issue, you CAN at least put the rows into different blocks and avoid the gcc block contention.  

You won’t miss the extra 2 blocks.  

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