AW: How do commits release row level locks?

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Mon, 16 Feb 2004 11:53:54 +0100
Message-ID: <87F172BCF111D0489340AB3CB16A011E0DD1B4@EBMS20.bov.int>

Hi Anjo

I see. We used to have an additional index on these tables, but we're reorganizing the tables, using IOTs.
The contentation was on the actual table, though. Unless the wait event shows the table as the "problem" object anyway, regardless wether the = itl of
the actual table or the index caused the problem. Is there anyway to = find
out about this?

The main problem is, that the deletes occur while some data loading is = done.
Afterwards, these tables become pretty stable. That's why I didn't want inittrans to be too high. Maybe I should set it to a high value, do the load, then reset it to something more reasonable.

Thanks for the reply,
Stefan

-----Urspr=FCngliche Nachricht-----
Von: Anjo Kolk [mailto:anjo_at_oraperf.com] Gesendet: Montag, 16. Februar 2004 11:31 An: oracle-l_at_freelists.org
Betreff: RE: How do commits release row level locks?

Another thing to consider is the number of ITL entries for the index = (if
they exists on the table). You need +1 for the index.

Anjo.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Jahnke Sent: Monday, February 16, 2004 10:47 AM To: 'oracle-l_at_freelists.org'
Subject: AW: How do commits release row level locks?

Hi Arup

Your paper brings me to a possible solution of a problem we encountered while doing mass deletes (had to be delete, not truncate). Do you think =3D the following might be possible:

Table created with blocks of size 8k, inittrans 10, maxtrans 255.
Table only contains one column, number(28) (it holds key values).
mass delete on table.
delete hangs indefinitely.

Is it possible, since so many rows fit into one block, and each row my transaction wants to delete results in an itl entry (is that the case = =3D
anyway =3D3D> one entry per row? or is it one entry per transaction per block?), =3D that there aren't enough slots left in a block and the = delete
hangs with an enqueue wait?

Any input appreciated,
Stefan

-----Urspr=3DFCngliche Nachricht-----
Von: Arup Nanda [mailto:orarup_at_hotmail.com] Gesendet: Freitag, 13. Februar 2004 05:37 An: oracle-l_at_freelists.org
Betreff: Re: How do commits release row level locks?

I wrote an article some time ago for DBAZine on this exact question. = =3D
Pleas check this out at http://www.dbazine.com/nanda3.html.

Original Message -----=3D20 From: "Ryan" <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org> Sent: Thursday, February 12, 2004 11:13 PM Subject: How do commits release row level locks?

> Now Oracle does not track which rows are locked. How processes know =
=3D
when a
lock has been released? I can't find it in the docs. I'm assuming there =3D is something similiar to an SCN written to the block header of each row. However, the SCN would not be sufficient, since it does not identify =3D whether the transaction is complete.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org put=20
> '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
> -----------------------------------------------------------------
>

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
-----------------------------------------------------------------


=3D20
----------------------------------------------------------------
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
-----------------------------------------------------------------


----------------------------------------------------------------
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
-----------------------------------------------------------------


=20
----------------------------------------------------------------
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 Mon Feb 16 2004 - 04:53:54 CST