| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Parallel server very slow
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
8.1.7.4 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,
decode(substr(dl.lockstate,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'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 dl.pid = 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,
Giovanni
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 04 2006 - 17:32:55 CST
 |
 |