Home » RDBMS Server » Performance Tuning » Table lock Blocking
Table lock Blocking [message #266128] Sun, 09 September 2007 22:55 Go to next message
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.
Re: Table lock Blocking [message #266132 is a reply to message #266128] Sun, 09 September 2007 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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)!

icon7.gif  Re: Table lock Blocking [message #266136 is a reply to message #266132] Mon, 10 September 2007 00:11 Go to previous messageGo to next message
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.
Re: Table lock Blocking [message #266156 is a reply to message #266136] Mon, 10 September 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
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
Re: Table lock Blocking [message #266163 is a reply to message #266128] Mon, 10 September 2007 01:24 Go to previous messageGo to next message
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.
Re: Table lock Blocking [message #266167 is a reply to message #266163] Mon, 10 September 2007 01:35 Go to previous message
Michel Cadot
Messages: 64145
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:
  


Current Time: Fri Dec 09 11:42:17 CST 2016

Total time taken to generate the page: 0.10476 seconds