Identify Lock

Guys,

Sometimes my users call me and tell me that the system is slow, then I
open IPM and I see a lot of users in WAIT state, then I have to identify
who is the responsable for the lock and I kill him. ;-) (kill the
session lol...).

Is there a way to identify this locks before my users call me ? One
shell script, something like that...


--
Regards,

Thiago Zerbinato
Oracle/Ingres DBA
Ribeirao Preto/SP
Brazil

On Sep 5, 2008, at 8:01 AM, thiagomz wrote:

> Guys,
>
> Sometimes my users call me and tell me that the system is slow, then I
> open IPM and I see a lot of users in WAIT state, then I have to
> identify
> who is the responsable for the lock and I kill him. ;-) (kill the
> session lol...).
>
> Is there a way to identify this locks before my users call me ? One
> shell script, something like that...
>


You can do it with IMA. I am too pressed for time to find the exact
sql, but it might have been posted here in the past. Use
ima_locks and ima_lock_lists to get at the blocking session.

Karl

You can use the following line in a shell script. It will return the number of sessions that are currently blocked by locks.

lockstat | grep -c "State: WT"

Damien Owen
Sandyx Systems

thiagomz wrote:

Guys, Sometimes my users call me and tell me that the system is slow, then I open IPM and I see a lot of users in WAIT state, then I have to identify who is the responsable for the lock and I kill him. ;-) (kill the session lol...). Is there a way to identify this locks before my users call me ? One shell script, something like that...



-------------------------------------------------------------------------------------------
This email and the files transmitted with it are m eant solely for the use of the individual addresse e named above. They may contain confidential an d/or legally privileged information. If you are not  the addressee or responsible for delivery of the m essage to the addressee, please delete it from you r system and contact the sender right away. Sandyx  Systems Ltd has taken steps to ensure that this e mail and any attachments are virus-free, but it remains your responsibility to confir m and ensure this.

Damien,

I like this better than using IMA. I've developed scripts
to monitor locking using this output and some cached table id
mappings.

This approach works very well when the server is extremely
busy or wrapped around an axle in some awful mutex wait state.

Cheers,

Mike Leo


Damien Owen wrote:
> You can use the following line in a shell script. It will return the
> number of sessions that are currently blocked by locks.
>
> lockstat | grep -c "State: WT"
>
> *Damien Owen*
> Sandyx Systems
>
> thiagomz wrote:
>> Guys,
>>
>> Sometimes my users call me and tell me that the system is slow, then I
>> open IPM and I see a lot of users in WAIT state, then I have to identify
>> who is the responsable for the lock and I kill him. ;-) (kill the
>> session lol...).
>>
>> Is there a way to identify this locks before my users call me ? One
>> shell script, something like that...
>>
>>
>>
>
>
> ------------------------------------------------------------------------
> -------------------------------------------------------------------------------------------
> This email and the files transmitted with it are meant solely for the use of the individual addressee named above. They may contain confidential and/or legally privileged information. If you are not the addressee or responsible for delivery of the message to the addressee, please delete it from your system and contact the sender right away. Sandyx Systems Ltd has taken steps to ensure that this email and any attachments are virus-free, but it remains your responsibility to confirm and ensure this.
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres-at-kettleriverconsulting.com
> http://www.kettleriverconsulting.com...fo/info-ingres

thiagomz wrote:
> Guys,
>
> Sometimes my users call me and tell me that the system is slow, then I
> open IPM and I see a lot of users in WAIT state, then I have to identify
> who is the responsable for the lock and I kill him. ;-) (kill the
> session lol...).
>
> Is there a way to identify this locks before my users call me ? One
> shell script, something like that...
>
>


Hi,

I run this SQL against imadb to find blocking sessions:
SELECT s.server,

s.effective_user,

s.db_name,

s.session_terminal,

s.session_query,

s.client_host

FROM ima_locks lg,

ima_locks lb,

ima_resources r,

ima_locklists ll,

ima_server_sessions s

WHERE lg.lock_state = 'GRANTED' AND

lg.resource_id = r.resource_id AND

lb.resource_id = r.resource_id AND

lb.lock_state = 'WAITING' AND

lg.locklist_id = ll.locklist_id AND

ll.locklist_session_id = s.session_id AND

ll.locklist_server_pid = s.server_pid


HTH
Gerhard

Gerhard Hofmann escreveu:
> thiagomz wrote:
>> Guys,
>>
>> Sometimes my users call me and tell me that the system is slow, then I
>> open IPM and I see a lot of users in WAIT state, then I have to
>> identify who is the responsable for the lock and I kill him. ;-)
>> (kill the session lol...).
>>
>> Is there a way to identify this locks before my users call me ? One
>> shell script, something like that...
>>
>>
>
>
> Hi,
>
> I run this SQL against imadb to find blocking sessions:
> SELECT s.server,
>
> s.effective_user,
>
> s.db_name,
>
> s.session_terminal,
>

Gerhard,

I was wondering, I have to execute the sql for each DBMS ? I have 4.

--
Regards,

Thiago Zerbinato
Oracle/Ingres DBA
Ribeirao Preto/SP
Brazil