Home » SQL & PL/SQL » SQL & PL/SQL » SQL query hanging with CU lock
SQL query hanging with CU lock [message #209569] Fri, 15 December 2006 10:27 Go to next message
arun_nair24
Messages: 2
Registered: December 2006
Junior Member
Hi,

I am facing a very unique issue , any help would be highly appreciated. I am tuning Siebel analytics server,What I see is never ending process on the database server using a lot of cpu .each of them use consistently around 4% CPu and at a time there are around 10 processes.
As a test i killed those processes, ran a report from Siebel analytics and found two sessions which were consistently taking 4% CPU and they were hung in the database doing nothing. V$lock shows those sessions having a CU lock, Cusor bind lock

Following is the steps i had done and dont know wat to check

$prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
28877 oracle 8283M 7397M cpu513 0 0 0:06:24 4.2% oracle/1
1397 oracle 8282M 7397M cpu484 0 0 0:06:02 4.2% oracle/1


SELECT NVL (a.username, 'Null') username, b.pid, a.sid,DECODE
(a.terminal, '?', 'Detached', a.terminal) terminal, b.program,b.spid,
a.osuser, a.serial#,a.status,a.module,a.sql_hash_value FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND b.spid='28877'

USERNAME PID SID TERMINAL PROGRAM
SPID OSUSER SERIAL# STATUS
MODULE SQL_HASH_VALUE
------------ ---------- ------- ------------
------------------------------ ------------ ------------------------------ ------- --------
------------------------------------------------ --------------
SRMW 20 64 oracle@gb02qds70pgpps7
(TNS V1 28877 analytic 1359 ACTIVE
nqsserver.exe@gb02qap72pgpps7 (TNS V1-V3) 1936894157
-V3)




SQL> select sql_hash_value,module from v$session where sid=64 and
serial#=1359;


SQL_HASH_VALUE MODULE
-------------- ------------------------------------------------
1936894157 nqsserver.exe@gb02qap72pgpps7 (TNS V1-V3)



SQL> select sid,type, block from v$lock where sid =64;


SID TYPE BLOCK
------- ------------ ----------
64 CU 0


the lock type is CU. The cursor_sharing parameter is 'EXACT'.
Re: SQL query hanging with CU lock [message #209579 is a reply to message #209569] Fri, 15 December 2006 11:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I'd try starting a trace on the session (even better if you could anticipate the situation and start the trace first). Doing a tkprof with "record=" seems to be quite good at capturing the calling statement - even somehow with a partial trace...

http://www.orafaq.com/node/14
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:497421739750
Re: SQL query hanging with CU lock [message #209630 is a reply to message #209579] Sat, 16 December 2006 00:56 Go to previous message
arun_nair24
Messages: 2
Registered: December 2006
Junior Member
Thanks A lot for ur help. what i see from the trace is the following:
WAIT #1: nam='library cache pin' ela= 2939411 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939252 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939473 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939371 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939416 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939066 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939133 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939118 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939291 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939470 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939298 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939322 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939432 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939369 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939428 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939358 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939252 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939294 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939324 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939280 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939160 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939354 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939161 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939282 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939316 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939326 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939225 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939241 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939216 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939235 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939315 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939335 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939386 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939335 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939100 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939448 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939211 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939259 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939410 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939540 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939263 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939282 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939089 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939287 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939429 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939243 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939427 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939249 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939078 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939158 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939576 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939179 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939120 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939412 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939427 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939377 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939168 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939479 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939366 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939295 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939387 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939354 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939403 p1=23560616944 p2=21591365144 p3=200
WAIT #1: nam='library cache pin' ela= 2939128 p1=23560616944 p2=21591365144 p3=200


Would it be some bug i am hitting?
Previous Topic: sqlca
Next Topic: error ORA-00054:
Goto Forum:
  


Current Time: Tue Dec 06 08:53:40 CST 2016

Total time taken to generate the page: 0.09160 seconds