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'.