Re: single table hash clusters in 9.2 - anyone tried them out yet?

Jonathan,

Thanks for the info. I've actually visited your website on a number of occasions in the last few years. I was stumped on some parallel query problems a while back, and it seems like you had more info than Metalink if I remember correctly.

Anyway...

It has happened to me on a number of occasions that a single table hash cluster appears faster when you trace a few single table hash lookups vs. a PK. However, when you run a test job that accesses data all over the place the index wins on occasion. It could be that I've ran into exactly what you are talking about on a subset of the records, and the cost for this is very high.

One of the things, I've been thinking to test lately is how bad performance goes down the tubes once you insert more rows than the "hash keys" value. From what I remember off the top of my head, it's not really clear in the Oracle documentation as to what happens at that point. I know it can't be good, but still I'm curious how fast the onset of a serious performance problem occurs if you let it boil over the initial setting. (Maybe it just breaks, I can't remember.)

The other thing that is not that clear is how uniform the distribution of the data needs to be in order for the hashing function to work correctly. For example, if I have large breaks in the value of one of the hash keys, does that screw up the algorithm?

What I'm saying is do the hash columns have to be like

"1 2 3 5 6 7 8 10 11 12..."
  or will
"1 3 9 15 28 37 82 91..."

work equally as well. I've always assumed the data has to be like the first scenario in order to work well. If you know for certain, I'd be interested to know the answer.

On a side note...

If I remember right, there's a white paper on hotsos.com that graphs out the scaling function for each of the different access methods. They've got some other cool stuff over there too. I especially like the paper they have on tracing the 10053 event.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c32k6m$9e5$1_at_hercules.btinternet.com>...
> I wasn't at the Steve Adams' day, but at the
> Miracle Masterclass 2004, he introduced the
> very special optimisation available in single
> table hash clusters. Oracle can turn a hashkey
> into a rowid, and includes a special latching
> optimisation if you have built your hash cluster
> perfectly.
>
> Paul,
> If you want a prime example of how effective
> this can be, read the full disclosure document
> for Oracle's 1M transaction per minute TPC-C
> benchmark. There are about 5 PK indexes,
> and everything else is done by single table hash
> clusters.
>
> Mark,
> One reason why you may have had variable
> results with single table hash clusters, is that if
> you get a collision in a block, the block is flagged
> as an exception, and Oracle falls back to a more
> CPU intensive operation, with extra latching, to
> acquire the target row.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Mark" <simmons_mark_at_yahoo.com> wrote in message
> news:5366fb41.0403121446.19a6faed_at_posting.google.com...
> > I wish I could have gone.
> >
> > I have used almost every object that you had mentioned at some point
> > in time. I personally have found that you have to test to find out
> > what works best. I'm not sure why, but single table hash clusters are
> > sometimes faster than index lookups and sometimes they are slower.
> > Maybe they covered that at the symposium. :)
> >
> > Mark Simmons
> > Sr. Oracle DBA
> > Sabre-Holdings, Southlake, TX
> >
> > drak0nian_at_yahoo.com (Paul Drake) wrote in message
> news:<1ac7c7b3.0403120939.39511a5a_at_posting.google.com>...
> > > If you attended Steve Adams presentation yesterday at the Hotsos
> > > Symposium, you probably have quite a list of new methods (not tips and
> > > tricks) that you are looking forward to test and possibly introduce
> > > into your environment.
> > >
> > > I wanted to open this thread for anyone that cared to share their
> > > results from moving from heap tables + b+tree indexes to either IOTs,
> > > index clusters, hash clusters or single table hash clusters.
> > >
> > > still seeing block dumps when I close my eyes ...
> > >
> > > Pd
Received on Sun Mar 14 2004 - 23:57:57 CST