Re: Hash clusters

The other thing that is notable is that the buffer cache hit rate is only around 53%

blue wrote:

> Looks like there was only one index on the table (the Primary key).
> This table is responsible for quite a bit -- massive amounts of inserts,
> deletes, updates via a java program, and a job that runs once a day
> selecting into another table.
>
> I need to go thru and determine other potential indexing -- but don't want
> to overdo it, as too many indexes will kill performance too.
>
> Besides a hash cluster, I must ask what other approach to take.
> (I haven't been here very long, and it sounds like it's been a problem for
> awhile).
>
> David Fitzjarrell wrote:
>
> > In our last gripping episode blue <blue_at_yahoo.com> wrote:
> > > We are running Oracle 8 on Solaris 2.6
> > >
> > > A developer has placed a hash cluster on one of our tables,
> > > and the performance of this table has been degrading ever since.
> > > It is not necessarily static data -- altho the amount doesn't
> > > necessarily grow much -- just a lot of inserts and deletes.
> > > This table gets analyzed on a regular basis.
> > >
> > > I've noticed sql jobs that run against this particular table are now
> > > doing
> > > full table scans, and index hints don't work either.
> > >
> > > Could someone provide some info/views on when hash clusters are
> > > effective,
> > > how to size the keys, rebuilding guidelines, etc.? If not, maybe some
> > > good links that give some guidelines?
> > >
> > > TIA
> > >
> > >
> >
> > A hash cluster is not placed on a table, the table is placed into a
> > hash cluster.
> >
> > Hash clusters are effective when the tables in the cluster are fairly
> > static in size and most queries are equality queries on the cluster
> > key. With equality queries [WHERE cluster_key = ...] the hashing
> > algorithm is effective in locating the key value, thus reducing I/O.
> >
> > Hash clusters are not effective when the tables in the cluster are not
> > static and/or the queries are non-equality queries [WHERE cluster_key
> > < ... , as an example].
> >
> > My thoughts tend to focus on the inserts and deletes on the table in
> > the hash cluster. Since the overall size of the table is increasing,
> > even slightly, the cluster key values are changing [due to the inserts
> > and deletes] and the number of rows cannot be determined to any
> > reliable degree the hash function has trouble determining an accurate
> > distribution of cluster key values. Performance will suffer under
> > these conditions, of which you are well aware.
> >
> > Under these conditions your table is not a good candidate for a hash
> > cluster. However dropping the cluster will also drop the table and all
> > associated indexes. You might try to copy the table as a non-clustered
> > table and rebuild the non-cluster indexes on it:
> >
> > create table <newtab>
> > as select *
> > from <clusteredtab>
> > /
> >
> > (Hopefully you have the script used to initially create the table which
> > should include the indexes; you can then re-create the table with a
> > different name and populate it with data from the clustered table, then
> > drop the cluster and rename the new table to the original name.)
> >
> > These are my thoughts on the matter; other opinions are more than
> > welcome.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Mon Nov 20 2000 - 11:31:17 CST