Re: Hash clusters

I would suggest that this buffer cache hit ratio is your first tuning target. That figure on an OLTP system should be in the nineties rather than the fifties. Have a look at the sga sizing to start with. (sorry if this is teaching the proverbial aged female relative).

It might also be worth talking with the developer to try and understand the reasons behind the hash cluster decision in the first place. That said I agree with the other opinions exxpressed here about the use of hash clusters on a table subject to freqent dml.

--
Niall Litchfield
Oracle DBA
Audit Commission UK


"blue" <blue_at_yahoo.com> wrote in message news:3A195FE5.279EE5B1_at_yahoo.com...


> 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.

>