Re: Latch Free

From: Tim Gorman <tim_at_sagelogix.com>
Date: Tue, 27 Apr 2004 20:27:46 -0600
Message-ID: <BCB472C2.13F7F%tim@sagelogix.com>

Syed,

An UPDATE statement generally has a WHERE clause, which is either generated by the application code at run-time or developed by developers at development. The WHERE clause determines which rows (and which database blocks) you will be updating.

So, application design is the first cause (and fix) for such problems as you're describing. The database is very much at the mercy of this application and there is probably precious little you can do without fixing the application.

So, first and foremost, please understand that the application has a problem, not the database. Best to fix problems at their root cause, if possible...

However, a couple things you can try at the database level:

• Rebuild the table in question with PCTFREE 99, to force only one row per block. Obviously, this is only desirable if the table is small. Also, it will only help if the UPDATE statements are not always accessing the exact same row(s)...
• if the "latch free" contention is actually on an index and the UPDATE statement is updating with a sequentially-ascending data value (such as SYSDATE or a sequence number), then perhaps the index on the column in question can be rebuilt REVERSE (i.e. A reverse-key index). There are some costs to using reverse-key indexes, such as lack of support for range-scan operations, so be sure that this is the case before deciding to try them...

Just some ideas. To be sure, both are pretty draconian...

Hope this helps...

-Tim

on 4/27/04 8:40 AM, syed jaffar hussain at sjaffarhussain_at_hotmail.com wrote:

>
> Hello List,
>
> We have ePayment system and we use TIBCO application. An adaptor has a
> update sql statements that runs continuously. I found the following top
> 5 waits events for this session
>
> Log file sysn(redo allocation retries + Avergae redo wirte size = are
> very high)
> Latch free (Cache buffers chains + Library cache)
>
> I know that latch free(cache buffers chains) accessing the same
> block/buffers again & again. How can I avoid hot block/buffers problem?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Apr 27 2004 - 21:23:59 CDT