Re: Latch Free Problem

From: Anil Sikri <anil_sikri_at_hotmail.com>
Date: Thu, 31 Aug 2000 00:09:46 CDT
Message-Id: <10604.115929@fatcity.com>

Sandy,

     I would definitely recommend tracing the ongoing query by
     executing dbms_system.set_sql_trace_in_session(<sid>,<serial#,TRUE)
     or using dbms_system.set_ev with the event 10046 enabled to glean
     more information. You could then use TKPROF on the trace file
     generated in user_dump_dest. This has helped me a couple of times.

     Also, could you check the following :
     1) The statistics for CBO and make sure that they aren't stale
        Also, are you generating statistics for CBO using estimate or
        compute ?
        Maybe, you could try deleting the statistics for the CBO and
        regenerating them using dbms_stats ( only available on Oracle8i)
        This did help me once even though it was way back in v7.3.
     2) I would have suggested checking the selectivity of your
        concatenated index but since you mentioned that CBO picked it up
        by modifying the query, the selectivity must be pretty high.

FYI, the cache buffer chains latch contention is caused most of the time due to inefficient SQL.

Let me know if you need more help/info

Best of Luck.

Regards,
Anil Sikri

Original Message ----------------------------

>From: "Sandy Druar" <sdruar_at_eckerd.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Latch Free Problem - Need Help
>Date: Wed, 30 Aug 2000 12:41:43 -0800
>
>Diana,
>
>I tried using a hint to force the index to be used, but
>it changed the explain plan from hash join to 3 nested loops
>with the index. We are still getting Latch Free.
>
>I will try using the between.
>
>Any ideas will be more than helpful.
>
>Thanks,
>Sandy Druar
>Oracle DBA
>sdruar_at_eckerd.com
>
>
>
>--
>Author: Sandy Druar
> INET: sdruar_at_eckerd.com
>

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at Received on Thu Aug 31 2000 - 00:09:46 CDT