Re: ** latch free wait contention, log file sync

"About what is slow : some updates and reports/lookup are running slow"

Can you select few that are the most important or, perhaps, ones that get most of user complaints? You would want to trace them and produce the profile - where and how much time was spent during the slow action. Here is the condensed summary how to enable extended tracing - http://www.hotsos.com/e-library/abstract.php?id=29 . A bit more about why you want to profile your activity - http://www.hotsos.com/e-library/abstract.php?id=131 .

You will need to analyze the trace collected and this program is usually called profiler. There are number of free profilers (tkprof and Trace Analyzed from Oracle, OraSRP) and commercial (Hotsos Profiler used in examples of the link above). tkprof is the easiest but is not ideal tool. Trace Analyzed is more advanced but requires some installation with database. OraSRP is easier to use and it has some minor issues back in time when it was written in Python and now was re-written. Haven't tried it for some time. Hotsos Profiler costs money. Nothing beats reading raw trace file ;-) but it's probably to much details for you at this stage. You can google on those tools to find how to use them.

If you are on 10g and have Diagnostic Pack licensed, you would probably be just fine using ASH. Since you mention, simple "latch free", I'm afraid you are on 9i as in 10g you usually get latch specific events.

Another approach would be to use Tanel's Session Snapper - http://blog.tanelpoder.com/2007/12/06/oracle-session-snapper-v106-released/

Statspack is also there if you are absolutely positive that you problem is instance-wide but I'm not sure it's your case - as you mentioned important keyword *some* while identifying slow functionality. On the other hand, having top events latch free and log file sync is hardly considered healthy unless it's relatively insignificant time of your total time capacity (number of CPU x report interval). It's also not very straightforward and reliable to identify which latch is actually your problem (there are many latches and each need it's own approach).

Having said all that, experienced performance analyst can make relatively good conclusion on whether parsing is your problem (possibly, because of absence of bind variables) or not. So it might make sense to paste here the first 50 lines of your Statspack report with some comments what's been done on the system during that time.

I deliberately stay more generic and not mention possibilities like not using bind variables with non-adhoc queries - it depends on developers' creativity.

I think I didn't emphasize it well enough, BEFORE TRYING TO FIX SOMETHING, YOU'D RATHER DIAGNOSE THAT IT'S *YOUR* PROBLEM. I should have started with it, actually. :-)

Don't get me wrong - reading Tom Kyte is great. However, it doesn't mean you have the same problem you found on AskTom.

On 7-Apr-08, at 9:49 PM, A Joshi wrote:

> Alex,
> Thanks. I did go thru statspack and I am seeing very high number
> for this and also for log file sync waits. About what is slow : some
> updates and reports/lookup are running slow and I am investigating
> those too. Just wanted to know a way to address these. I found on
> asktom that latch free might be related to bind variable non usage.
> We do have some ad hoc but those are unavoidable right now so I just
> want to know if anything else can be done. If someone had similar
> high waits. Thanks
>
> Alex Gorbachev <ag_at_oracloid.com> wrote:
> No offense please but before I type anything else... This is a
> typical candidate for a reply with reference to (http://catb.org/~esr/faqs/smart-questions.html
> )
>
> Now back to the topic.
>
> Most *probably* it's not the root cause of you problem but a symptom
> and there's probably no point fixing the symptom itself. You might
> get some lessons about the latches from this list but that might not
> be what you need at this point.
>
> I would recommend to provide more details about your issue -
> starting with what is slow. After that you would have some options
> like system wide analysis with Statspack/AWR or scope down to a slow
> functionality if you can and use ASH or extended trace.
>
> So the investigation *might* be like that:
> 1. Identify what's slow
> 2. Confirm that latching is your current bottleneck for the
> functionality that's slow
> 3. If it's latching indeed, determine which latch it is (might be
> straightforward in 10g but less obvious in 9i)
> 4. Here is when your question below becomes valid with additional
> information which latch it is about and rephrased by not asking not
> how to increase timeout but how to avoid latching or make waiting on
> latches lower.
>
> You might have actually gone through this sequence already but so
> far I can conclude only the opposite based on how you form the
> question.
>
> Alex
>
> On 7-Apr-08, at 12:18 PM, A Joshi wrote:
>> Hi,
>> Can someone tell ways to improve latch free waits. It seems to
>> be timing out a lot. Any parameters can be increased? Any relevant
>> docs/url/info. Thanks
>>
>> You rock. That's why Blockbuster's offering you one month of
>> Blockbuster Total Access, No Cost.
>
>
>
> You rock. That's why Blockbuster's offering you one month of
> Blockbuster Total Access, No Cost.

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