Thanks for the question, Selva.

Asked: June 10, 2008 - 4:05 pm UTC

Last updated: November 28, 2011 - 10:55 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

We got few partitioned tables (range partitioned by month) and use pl/sql procedure to update the date column so that the data will move from default partitions to month specific partition.

When the application run 15+ concurrent instances of the pl/sql procedure randomly we get the dead lock reported. Attached are few lines from the deadlock trace

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00140029-0013c58f 39 218 X 36 216 S
TX-00180024-000a720c 36 216 X 39 218 S
session 218: DID 0001-0027-00003699 session 216: DID 0001-0024-00003A68
session 216: DID 0001-0024-00003A68 session 218: DID 0001-0027-00003699
Rows waited on:
Session 216: no row
Session 218: obj - rowid = 00000000 - D/////AAFAAAANpAAA
(dictionary objn - 0, file - 5, block - 873, slot - 0)
Information on the OTHER waiting sessions:
Session 216:
pid=36 serial=52171 audsid=3549600 user: 18/<none>
O/S info: user: jfedba, term: , ospid: 439194, machine: pqs1s
program: archive_doc_file@pqs1s (TNS V1-V3)
application name: archive_doc_file@pqs1s (TNS V1-V3), hash value=0
Current SQL Statement:
UPDATE MAILPIECE PARTITION(MAILPIECE_DEF) SET ARCHIVED_DT=:B2 WHERE DOC_ID=:B1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE MAILPIECE PARTITION(MAILPIECE_DEF) SET ARCHIVED_DT=:B2 WHERE DOC_ID=:B1
......
SO: 700000a0f317ae0, type: 4, owner: 700000a0c27dbb8, flag: INIT/-/-/0x00
(session) sid: 218 trans: 7000009ffaaf938, creator: 700000a0c27dbb8, flag: (80100041) USR/- BSY/-/-/-/-/-
DID: 0001-0027-00003699, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, sql: 700000a0d3d58c0, psql: 700000a0ea20718, user: 18/<none>
O/S info: user: jfedba, term: , ospid: 262728, machine: pqs1s
program: archive_doc_file@pqs1s (TNS V1-V3)
application name: archive_doc_file@pqs1s (TNS V1-V3), hash value=0
last wait for 'enq: TX - allocate ITL entry' blocking sess=0x700000a0f316630 seq=2188 wait_time=2929726 seconds since wait start
ed=33
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
Dumping Session Wait History
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929726
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929728
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929725
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929730
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929809
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929764
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929725
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929726
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929727
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c
for 'enq: TX - allocate ITL entry' count=1 wait_time=2929729
name|mode=54580004, usn<<16 | slot=180024, sequence=a720c

After reviewing this trace & related posts from asktom/oracle site we increased initrans value to 15 for the default partitions, considering we could have 15 concurrent sessions of pl/sql procedure. The other options suggested on your post is to increase pctfree of the block so we could have move space for ITL growth.

Also in past PL/SQL procedure is designed to commit every 100k records, currently i removed the intermittent commits and perform single commit before exit of procedure.

My question is
1) Currently application is not sure about the number of concurrent transaction, this being said do you suggest to increase initrans or increase the pctfree from default 10% to higher value?

2) Opportunity of improvement on the pl/sql procedure. Code as attached

CREATE OR REPLACE PROCEDURE MACS.PROC_ARCHIVE_DOC_FILE (
  update_cnt OUT int,  /* Number of doc records updated */
  in_doc_file_id IN doc_file.doc_file_id%TYPE,
  in_process_status_cd IN doc_file.current_status_cd%TYPE, /* Can change it to something besides ARC... */
  in_archived_dt IN doc_file.archived_dt%TYPE  /* Can change it to something besides sysdate... */
)
IS
    null_input_exc EXCEPTION;

    cursor mycursor is
    select doc_id
    from doc partition(doc_def)
    where doc_file_id = in_doc_file_id;

    my_doc_id doc.doc_id%TYPE;

BEGIN
   IF in_doc_file_id IS NULL or in_process_status_cd IS NULL THEN
     RAISE null_input_exc;
   END IF;

   update_cnt := 0;

   /**********************************/
   /* Looping through all docs */
   /**********************************/

   OPEN mycursor;
   LOOP
      FETCH mycursor INTO my_doc_id;
      EXIT WHEN mycursor%NOTFOUND;

      /**********************************/
      /* Archiving all doc_cic with doc_id */
      /**********************************/
      IF in_process_status_cd = 'NOP'
      THEN
    update doc_cic partition(doc_cic_def)
    set archived_dt=in_archived_dt,
    suspended_reason_cd='NOP'
    where doc_id=my_doc_id;
      ELSE
    update doc_cic partition(doc_cic_def)
    set archived_dt=in_archived_dt
    where doc_id=my_doc_id;
      END IF;

      /**********************************/
      /* Archiving all doc_dtl with doc_id */
      /**********************************/
    update doc_dtl partition(doc_dtl_def)
    set archived_dt=in_archived_dt
    where doc_id=my_doc_id;

      /**********************************/
      /* Archiving all doc_addr with doc_id */
      /**********************************/
    update doc_addr partition(doc_addr_def)
    set archived_dt=in_archived_dt
    where doc_id=my_doc_id;

      /**********************************/
      /* Archiving each sub_doc */
      /**********************************/
    update sub_doc partition(sub_doc_def)
    set archived_dt=in_archived_dt
    where doc_id=my_doc_id;

      /**********************************/
      /* Archiving each mailpiece */
      /**********************************/
      if in_process_status_cd != 'ARC'
      then
    update mailpiece partition(mailpiece_def)
    set archived_dt=in_archived_dt,
    process_status_cd=in_process_status_cd
    where doc_id=my_doc_id;
      Else
    update mailpiece partition(mailpiece_def)
    set archived_dt=in_archived_dt
    where doc_id=my_doc_id;
      END IF;


      /**********************************/
      /* Archiving each doc */
      /**********************************/
    update doc partition(doc_def)
    set current_status_cd=in_process_status_cd,
    archived_dt=in_archived_dt
    where doc_id=my_doc_id;

      update_cnt := update_cnt + 1;

   END LOOP;

   CLOSE mycursor;

   /**********************************/
   /* Archiving doc_file with doc_file_id */
   /* Doing this last...  If done, then entire file is complete */
   /**********************************/
    update doc_file
    set archived_dt=in_archived_dt,
    current_status_cd=in_process_status_cd
    where doc_file_id=in_doc_file_id;

    commit;

EXCEPTION
    WHEN null_input_exc
    THEN
    update_cnt := 0;
END PROC_ARCHIVE_DOC_FILE;

and Tom said...

1) if you have 15 running, you have 15 transactions, having 15 be the Initrans size would more than cover you. You would use pctfree to reserve space for subsequent updates if necessary.

2) let us remove the slow by slow code.....

Instead of all of the updates in the loop, I would evaluate just doing:

update doc_dtl partition(doc_dtl_def)
set archived_dt=in_archived_dt
where doc_id IN (select doc_id
from doc partition(doc_def)
where doc_file_id = in_doc_file_id );

using "as of" on the IN subquery if doc is being modified as you process and you need a consistent read on it across all of the updates.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Selva Muthukumar, June 11, 2008 - 5:21 pm UTC

Tom

Thanks for the quick turnaround. I got a question on " using "as of" on the IN subquery if doc is being modified as you process and you need a consistent read on it across all of the updates. " - all of the updates - do you mean updates through different concurrent instances of procedure proc_archive_doc_file.

Also if i want to use "as of" opeartor one approach i could think of is to get systimestamp at start of the proc and using it on as of clause. Is there any other approaches available

Thanks

Selva

June 11, 2008 - 9:08 pm UTC

right now, your code is:

for x in ( select doc_id from t )
loop
update t1 ... where doc_id = x.doc_id;
update t2 ... where doc_id = x.doc_id;
...
end loop;

in all of t1, t2, t3 (each as of their own points in time) the same doc_id values are updated - if t1 had doc_id = (1,2,3) applied to it, so did t2 and t3.

If you go to:

update t1 ... where doc_id in (select doc_id from t);
update t2 ... where doc_id in (select doc_id from t);
....

it could be that t1 has doc_id = (1,2,3) applied and
(because someone deleted doc_id =1 and added doc_id = 4 and commited)
t2 has doc_id = (2,3,4) applied.

If you start by getting a timestamp and use "as of that_timestamp" on the subquery - all of t1, t2, t3 ... will have the same consistent set of doc_ids applied to them.

it need not be "across" processes, the "as of" in each process would replicate what you have right now - the same set of doc id's being applied by that thread.

ITL allocate wait on index

Andr, November 18, 2011 - 3:22 am UTC

Tom, I periodically see long waits 'Enq: ITL allocate entry' on some sessions (wating for 10-60 sec). but v$session.row_wait_object#, row_wait_block# point to an index. The index is normal (not unique/bitmap/compress/functional) and it's table is not IOT. What reason of that waits ?

PS itl entries slots(in the block dump) are much more then waiting sessions.

November 21, 2011 - 10:59 am UTC

the itl entries don't have to be much more than the waiting sessions - the itl entries have to be much more than all concurrently interested sessions.

Say you have an index block that is pretty nearly full - and has 10 ITL entries allocated on it. No room to grow. If you have 12 sessions that try to concurrently modify that block - then all of a sudden, you'll have 2 waiting on the block and 10 on the block. You have "many more entries (10) than waiting sessions (2)", but it doesn't matter - you have 10 slots, 10 are used, and 12 are trying to be used.

Do you have transactions that are long running? It sounds like it. You may need to have a higher initrans on this index - or if it is a right hand side index (eg: on a sequence for example), look at ways to spread the modifications out.

For example

o reverse key index (spreads inserts out over the breadth of the leaf blocks)
o global hash partition the index (create say 64 "warm" right hand sides, instead of 1 really hot one)
o use a better surrogate - like a SYS_GUID(), or perhaps consider making a sequence be returned as:

to_number(to_char( instance_id, 'fm00' ) || to_char( mod( process_id, 1000 ), 'fm000' ) || to_char( s.nextval, 'fm0000000000' ))

that'll be good in a clustered environment (instance id spreads them out - you'll have instances 01, 02, 03, ... - they each have therefore their own index 'subtree').

in a given instance, taking the process id of the current connection will cause it to be spread out within the instance key.

And then you have 10 billion unique numbers to play around with after that.

ITL allocate wait on index

Andr, November 22, 2011 - 1:21 am UTC

Tom, thanks for the answer.
You said "Say you have an index block that is pretty nearly full - and has 10 ITL entries allocated on it. No room to grow. If you have 12 sessions that try to concurrently modify that block - then all of a sudden, you'll have 2 waiting on the block and 10 on the block"
I've read recently Jonathan Lewis, and he says that shortage itl entries for indexes causes the block split and probably the wait "ITL index contention", not ITL allocate entries. I'm in "deadlock" now about this.

November 22, 2011 - 8:11 am UTC

reference please. I want to see what he said in context.

ITL allocate wait on index

Andr, November 24, 2011 - 6:28 am UTC

Tom,
http://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/
Jonathan Lewis wrote in comments:
"As far as I know if there are no free ITL entries in a leaf block the process that wants one will simply intitiate a leaf block split" - ?

November 28, 2011 - 10:55 am UTC

I contacted Jonathan regarding that and we discussed it a bit.

He wrote:

... Possibly this can occur if two sessions need to do a leaf block split at the same time to get an ITL. Possibly it happens when a session wants an ITL. and a leaf block split is currently taking place because of a full block (rather than full ITL). ...

He'll be following up on his note to make a couple things more clear as well.

ITL allocate wait on index

Andr, November 29, 2011 - 1:40 am UTC

Tom, thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

ITL allocate wait on index

ITL allocate wait on index

ITL allocate wait on index

ITL allocate wait on index

More to Explore

PL/SQL demos

PL/SQL docs

Deadlock - enq: TX - allocate ITL entry

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

ITL allocate wait on index

ITL allocate wait on index

ITL allocate wait on index

ITL allocate wait on index

More to Explore

PL/SQL demos

PL/SQL docs