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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Mark W. Farnham
Sent: Saturday, October 12, 2019 8:39 AM
To: gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Friday, October 11, 2019 2:40 PM
To: oracle-l@xxxxxxxxxxxxx
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.
