RE: LGWR, EMC or app cursors?

Oh man, now I feel bad. I thought I was pretty good at retaining notes. I attended a presentation by Steve at Hotsos '04 in which I *think* he covered some of that. I vaguely remember him discussing it, but have no saved notes about hash clusters, just what I think was the course title - "Data Block Internals and Access Path Optimization". I'm sure the internet and Oracle doc will be my friends if I think I can convince others to lean that way. Thx for the reminder!

Regards,

Dave

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham Sent: Saturday, October 12, 2019 8:39 AM To: gogala.mladen_at_gmail.com; oracle-l_at_freelists.org Subject: RE: LGWR, EMC or app cursors?

CAUTION: This email originated from outside of D&B. Please do not click links or open attachments unless you recognize the sender and know the content is safe.

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