| 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: V$session_wait question and rollback segments
Yeap, Sorry Jonathan.
This is the correct infomation from v$lock (ID1 and ID2):
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK --------- -- --------- --------- --------- --------- --------- ---------
112 TX 196661 514403 0 6 439874 0
I know I can get rollback segment ID from "trunc(ID1/65536)". Do you know how to get the slot number?
BTW, I think I have finally found the cause of the problem from my other question: (the TX 4 enqueue wait --)
It was not an ITL shortage.
Two sessions were executing the same procedure and I believe (I cannot be sure cause I can't get the bind values of a command that has already been executed)
they were using the same values.I took a process dump on both sessions and they showed that the same INSERT there.
Session 112 inserted a value in the table PO_REQUISITION_LINES_ALL, which has no contraints but has 2 unique indexes. It did not commit or rollback. And after a while session 121 performed the same insert. And it is still there waiting for a TX 4 on session 112 TX enqueue.
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK --------- -- --------- --------- --------- --------- --------- ---------
102 TX 196661 514403 6 0 440693 1
102 TM 45172 0 2 0 440542 0
102 TM 45137 0 2 0 440693 0
112 TX 1114137 17942 6 0 439874 1
112 TM 45137 0 3 0 439874 0
112 TM 45172 0 3 0 439874 0
112 TX 196661 514403 0 6 439874 0
121 TX 1179714 7865 6 0 438439 0
121 TM 45172 0 3 0 438439 0
121 TX 1114137 17942 0 4 438439 0
This is the block header where Session 112 is holding the TX 6 (and Session 121 is waiting for.)
Block header dump: 0x3b003b81
Object id on Block? Y
seg/obj: 0xb074 csc: 0x00.28dacfab itc: 10 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0011.019.00004616 uba: 0x5b404350.0188.50 ---- 1 fsc 0x00 <<<<<<<<<<<<<<<<<<00.00000000
v$transaction
SID name XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN U_BLK status START_TIME
---------------- ----- ------ ------- ------ ------ ------ ------ ----- ------ ----------- 102 RBS06 6 8 541484 357 5572 -30503 1 ACTIVE 05/04 08:55 112 RBS17 17 25 17942 365 17233 392 2 ACTIVE 04/29 13:38 121 RBS18 18 66 7865 365 3289 177 1 ACTIVE 04/29 14:02
Let me know if you have any additional input.
Thank you
and
Regards
Diego.
>
> You've lost a bit of your wait.
> From your previous post:
>
> name|mode
> 0000000054580006
> id1
> 0000000000030035
> id2
> 000000000007D963
>
> Name/Mode:
> 5458 / 06
> T X / mode 6 (exclusive)
>
> ID1
> 3 35 Undo segment 3, slot 0x35
> ID2
> 7D963 Sequence number
>
> The ID1 / ID2 correspond to the id1/id2 in v$lock
> which also correspond, after slicing up into the
> XIDUSN
> XIDSLOT
> XIDSQN
> from v$transaction.
>
> This is one of the ones that is easier to read in Hex
> than it is in the decimal form.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Optimising Oracle Seminar
> http://www.jlcomp.demon.co.uk/seminar.html
>
> June 2004 UK Manchester
> July 2004 Iceland
> July 2004 USA California
> Aug 2004 USA North Carolina
> Sept 2004 UK Manchester
> Sept 2004 USA NYC
> Oct 2004 USA Boston
>
>
> ----- Original Message -----
> From: "Diego Cutrone" <diegocutrone_at_yahoo.com.ar>
> To: "Oracle List" <oracle-l_at_freelists.org>
> Sent: Tuesday, May 04, 2004 4:25 AM
> Subject: V$session_wait question and rollback segments
>
>
> Hi List,
> Does anybody know how to derivate the rollback number and the slot number
> within the rollback transaction table from v$session_wait id1 and id2 on an
> enqueue TX wait?
>
> SID ID1 ID2
> 112 enqueue 0000000054580006 0000000000030035
>
> I know there's a formula for this, but I just can't find it.
>
>
> Thank you
> I really appreciate your help.
> Regards
> Diego.
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
-- 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 May 04 2004 - 15:38:25 CDT
 |
 |