OraFAQ Forum: Performance Tuning » Table lock Blocking

Home » RDBMS Server » Performance Tuning » Table lock Blocking

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table lock Blocking [message #266128]

Sun, 09 September 2007 22:55

kflee2000
Messages: 3
Registered: September 2007
Location: SG

Junior Member

Hi,
I have read the article on 'what's blocking my lock'
and have written a script to check for blocking. However, sometime you will see some blocking but couldn't find the object when occurs. I wonder is this the normal or something got wrong?

eg.
** blocking ** QAD 67
No object found 41 -1

* to select the object_id from request sid, the row_object_id get is -1*

select row_wait_obj# into v_object_id from v$session where sid = v_blk_sid;

Thanks.

Report message to a moderator

Re: Table lock Blocking [message #266132 is a reply to message #266128]

Sun, 09 September 2007 23:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have read the article on 'what's blocking my lock'
but you decided that we don't need to read the same article to know what in the world you are talking about.

>and have written a script to check for blocking.
but you decided that we don't need to see the actual code to know what in the world it does.

>However, sometime you will see some blocking but couldn't find the object when occurs.

You may see it, but since we can't reproduce the same results we'll have to take your word for the results.

>I wonder is this the normal or something got wrong?
I wonder too, but have NOTHING to test against since you assumed we are mind readers

You're On Your Own (YOYO)!

Report message to a moderator

Re: Table lock Blocking [message #266136 is a reply to message #266132]

Mon, 10 September 2007 00:11

kflee2000
Messages: 3
Registered: September 2007
Location: SG

Junior Member

maybe is my mistake to assume that most of them had read the article (http://www.orafaq.com/node/854), after you have read you could understand what I'm trying to say (maybe not for somebody).

The important is not what the script I have wrote therefore I didn't attached. I just wonder anybody had encountered before and shared the info.

We learn from our mistake,I learn from mistake as well.

Thanks.

Report message to a moderator

Re: Table lock Blocking [message #266156 is a reply to message #266136]

Mon, 10 September 2007 01:05

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

The important is not what the script I have wrote therefore I didn't attached. I just wonder anybody had encountered before...

Encounter what? "No object found 41 -1"? No I never encounter that.

Quote:

to select the object_id from request sid, the row_object_id get is -1

To select from what?

Why don't you want to post the code? You don't want to share?
Post your code if you want help and read Reference manual about v$session.

Regards
Michel

Report message to a moderator

Re: Table lock Blocking [message #266163 is a reply to message #266128]

Mon, 10 September 2007 01:24

kflee2000
Messages: 3
Registered: September 2007
Location: SG

Junior Member

Encounter what? "No object found 41 -1"? No I never encounter that.

I already post the code above.

select row_wait_obj# into v_object_id from v$session where sid = v_blk_sid;
(point 3)

Explain:
1. First,get the block from v$lock where is grather than 0.
(to find out any blocking)

2. From v$lock which block >0,use id1,id2 to get sid which is the requesting user. (request >0)
(to find out the requester)

3. Use sid to get the row_wait_obj# (code above).
(to find out the requester object)

Question: Although there is blocking and also requester, when getting the object from request it shown -1 instead of an object_id.

Thanks.

Report message to a moderator

Re: Table lock Blocking [message #266167 is a reply to message #266163]

Mon, 10 September 2007 01:35

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I already post the code above.
select row_wait_obj# into v_object_id from v$session where sid = v_blk_sid;

This does not work:

SQL> select row_wait_obj# into v_object_id from v$session where sid = v_blk_sid;
select row_wait_obj# into v_object_id from v$session where sid = v_blk_sid
                                                                 *
ERROR at line 1:
ORA-00904: "V_BLK_SID": invalid identifier

Quote:

Explain:

Don't explain POST THE CODE.

Quote:

Question: Although there is blocking and also requester, when getting the object from request it shown -1 instead of an object_id.

Answer: who say there is blocking? You didn't post anything that proves it And: "read Reference manual about v$session"

WHY DON'T YOU WANT TO POST YOUR CODE?

Regards
Michel

[Updated on: Mon, 10 September 2007 01:37]

Report message to a moderator

Previous Topic:	How to identify a parse of SQL statement?
Next Topic:	Too many redo log file (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:39:44 CDT 2024