Re: high latch free

antogini,

this is 3rd party application, my role as DBA could not touch the app.

my thinking only how to redirect the execution plan to another "my own index" through outline.

now, i'm trying on test server.

do you know how to edit outline entry, so that I can redirect that sql above (the execution plan) will use my own index?

regards
ujang

On 5/2/07, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> 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