Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Parallel server very slow

Parallel server very slow

From: Giovanni Cuccu <>
Date: 2006-01-04 17:32:55

Hi all,

     I'm facing a very difficult situation (at least for me). Tomorrow morning I was asked for resolving a problem on an OPS (2 instances on on IBM AIX 4) because the dba responsbile for it will be out of office for a long time. The main problem is that I have very little knowledge of OPS, but this seems rather unimportant to the manager that required me to solve the problem. The task is now assigned to me and I cannot do anything else if not trying to solve it. The DB is at a customer site and since now it was managed by the customer itself. I can reach the db only via RAS access and I cannot have the unix oracle accounts credentials.
The problem is that a particular stored procedure became extremely slow and it seems it is hanging.
Here is what I've done since this morning: 1) I read the J.Lewis's book 'Practical 8i' chapter on OPS 2) I consulted the metalink in order to find some query able to translate the concepts acquired at point 1) in some data 3) I found opsdiag.sql and decided to use them in order to begin to practice with OPS
4) I connected to the db and I discovered that timed_statistics was false, I changed it to true
5) I used event 10046 (level 8) to trace the stored proc when the procedure was running and I was executing some queries (some from opsdiag, some others in order to know the sql executed, the current wait,etc) the RAS line dropped.
The only relevant fact that I can report is that the stored procedure was waiting on a lock conversion, more precisely the following query

select dl.inst_id, s.sid, p.spid, dl.resource_name1, decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
'KJUSERCA','Cancelling','KJUSERCV','Converting') as lockstate,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$dlm_locks dl, gv$process p, gv$session s, gv$session_wait sw where blocked = 1

and (dl.inst_id = p.inst_id and = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc

was showing only one line with resource_name1 valued [0x35][0x0][TM] and it was converting a sx lock to ssx, the second columns was constantly increasing
The corresponding view showing blockers had no rows. the sql executed by the stored proc dirung the wait was a delete from a table using a part of the pk.
Now my question are:

1)What could be the cause of this long wait?
2)How can I decode the column resource_name1?
3)Does anyone have some good advice?
Thanks to all,

P.S. I must wait until tomorrow before I can get the trace file produced by the stored proc.

Another free oracle resource profiler Now version 0.9
Received on Wed Jan 04 2006 - 17:32:55 CST

Original text of this message