| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: what's mean of "enqueue hash chains" latch?
hello,Riyaj ;
This is not a OLTP database,it's a finacial db,we need to produce
report every month.
In that moment,only the session execute the only SQL,no commit still.
Before yesterday,we have the report run about 2 hours. With this heavy
latch contention, it's run out of 10 hours and have no result.
thanks for help.
On Sat, 08 Jan 2005 12:21:24 -0600, Riyaj Shamsudeen
<rshamsud_at_jcpenney.com> wrote:
> Hi eygle
>
> DML locks protects objects from concurrent modification.
> Frequently, dml lock allocation latch contention is seen with enqueue
> hash chain latches, as dml_locks are implemented through TM enqueue
> locks. These resource structures are hanging from enqueue hash chains
> serialized by enqueue hash chain latches. So, reducing dml lock
> allocation latch contention should resolve enqueue hash chain latch
> contention.
> As Peter mentioned, these locks are released after each commits.
> Since you are using append hint, I am positive that you must commit
> after every statement, so your commit frequency is probably high.
> Further, if you must have this much contention, then I *guess* this is
> probably OLTP kind of application and append and parallel hints might
> not be better suited.
> Also, append hint will add data after the high water mark and
> not sure, how your extents are setup. So if your extents are smaller,
> this will lead in to TM locking contention for dictionay objects itself.
>
> If I were you, I would looking to eliminate these append and
> parallel hints for inserts. Sorry, I didn't test this *theory* ;-(
>
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> Peter.Hitchman_at_thomson.com
> Sent: Saturday, January 08, 2005 11:01 AM
> To: oracle-l_at_freelists.org
> Subject: RE: what's mean of "enqueue hash chains" latch?
>
> Hi,
> From MetaLink:-
>
> "dml lock allocation
> This latch protects the list of State Objects (dml locks). Every time a
> transaction modifies a table, a DML lock is gotten and released when the
> change is committed. The number of State Objects for dml locks is
> determined by the init.ora <Parameter:DML_LOCKS>".
>
> So I would be looking at how often you commit.
> Also what sort of turn over are you getting on the online redo logs? It
> may well be that they are too small. You may want to investigate having
> multiple freelists for the table you are inserting into, having many
> parallel inserts (assuming you have enabled parallel DML), may well be
> causing a lot of contention for the header block at the start of the
> segment.
>
> Good luck.
>
> Pete
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 08 2005 - 21:24:39 CST
 |
 |