-
query hanging with CU lock
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'.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|