RE: high latch free

Hi

> > The latch free is taking less than 10% of the response time. So, if
you
> > have a problem is not the latch... The main problem I see is that
you
> > have no partition key in the WHERE clause. Therefore no partition
> > pruning is used and PARTITION RANGE ALL is causing too much logical
I/O
> > for nothing.
> >
>
> I agree with this, do u mean that trans_id column isn't local index?

What do you mean for "trans_id column isn't local index"? A column is for sure not an index and the index I_EVENT_IPT_CALL__TRANSID is already a local index on TRANS_ID.

The problem is that without partition pruning Oracle has to scan all partitions of that index. For example if you have 20 partitions, 20 separate index scans will be performed. To avoid that there are only three possibilities:
- Take advantage of partition pruning by adding a restriction on OBJ_ID0.
- Create I_EVENT_IPT_CALL__TRANSID as a non-partitioned index. - Create I_EVENT_IPT_CALL__TRANSID as a hash partitioned global index.

> > > I guess because of that index, so we could have problem with
> > > latch free event, or any other clues?
> >
> > It would be interesting to know why...
> > Which is the latch you are waiting for?
>
> most of latch is buffer_cache_chain.

i.e. the latch contention is caused by excessive logical I/O. Thus reducing logical I/O will reduce contention for that latch.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l