Some processes are missing from V$SESSION view
Posted by Kamran Agayev A. on January 22nd, 2013
Today, while checking both V$PROCESS and V$SESSION view, I realized that there’re some processes that are missing from V$SESSION view:
SQL> select count(*) from v$process where addr not in (select paddr from v$session);
COUNT(*)
———-
120
How can a process be created without having any corresponding entry in V$SESSION view?
I started to check those processes on OS level. I got SPID of one of those processes and firstly tried to debug it using ORADEBUG from sqlplus:
sqlplus / as sysdba
ORADEBUG SETOSPID 3422
ORADEBUG TRACEFILE_NAME;
By getting the trace file name, I’ve checked the file but didn’t find any helpful message inside.
Then I decided to debug it on OS level. As the OS was HP-UX, I used truss executable to trace the unix system calls:
/usr/local/bin/truss -o truss.out -vall -laefdD -p 3422
And checked the output again:
tail -f truss.out
( Attached to process 18997 (“oraclePRODDB (LOCAL=NO)”) [64-bit] )
18997/9934051: 18446744073.7095 read(16, 0x600000000021a616, 2064) [sleeping]
This output wasn’t helpful (however, Tanel uses pfiles executable to get all opened files in the process by getting the first parameter of the read function – which is 16) , so I decided to use the next executable – lsof – which lists the opened files by the process:
/usr/local/bin/lsof -p 3422
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 20472 oracle cwd DIR 64,0×4 2048 3660 /home/oracle/product/10g/dbs
oracle 20472 oracle txt REG 64,0×4 284477360 101589 /home/oracle/product/10g/bin/oracle
oracle 20472 oracle mem REG 64,0×4 5337208 25311 /home/oracle/product/10g/lib/libnnz10.so
oracle 20472 oracle mem REG 64,0×7 21281 27339 /usr/lib/tztab
oracle 20472 oracle mem REG 64,0×5 312440 54074 /opt/star-ncf-prod/ep_patch/usr/lib/hpux64/libxti.so.1
oracle 20472 oracle mem REG 64,0×7 4371800 14392 /usr/lib/hpux64/libc.so.1
oracle 20472 oracle mem REG 64,0×7 1649152 91060 /usr/lib/hpux64/libnsl.so.1
oracle 20472 oracle mem REG 64,0×7 67664 91056 /usr/lib/hpux64/libnss_compat.so.1
oracle 20472 oracle mem REG 64,0×4 20041776 8569 /home/oracle/product/10g/lib/libjox10.so
oracle 20472 oracle mem REG 64,0×7 1046480 11645 /usr/lib/hpux64/libpthread.so.1
oracle 20472 oracle mem REG 64,0×7 55088 491 /usr/lib/hpux64/libuca.so.1
oracle 20472 oracle mem REG 64,0×7 706832 91078 /usr/lib/hpux64/libunwind.so.1
oracle 20472 oracle mem REG 64,0×7 6444800 91068 /usr/lib/hpux64/libm.so.1
oracle 20472 oracle mem REG 64,0×7 78168 90961 /usr/lib/hpux64/libdl.so.1
oracle 20472 oracle mem REG 64,0×7 89208 91053 /usr/lib/hpux64/libnss_dns.so.1
oracle 20472 oracle mem REG 64,0×7 44832 3881 /usr/lib/hpux64/librt.so.1
oracle 20472 oracle mem REG 64,0×4 199296 8277 /home/oracle/product/10g/lib/libdbcfg10.so
oracle 20472 oracle mem REG 64,0×4 134552 13143 /home/oracle/product/10g/lib/libocrutl10.so
oracle 20472 oracle mem REG 64,0×4 533792 13142 /home/oracle/product/10g/lib/libocrb10.so
oracle 20472 oracle mem REG 64,0×4 731352 25330 /home/oracle/product/10g/lib/libocr10.so
oracle 20472 oracle mem REG 64,0×4 1296848 25478 /home/oracle/product/10g/lib/libhasgen10.so
oracle 20472 oracle mem REG 64,0×4 315008 13264 /home/oracle/product/10g/lib/libskgxp10.so
oracle 20472 oracle mem REG 64,0×7 1163152 90960 /usr/lib/hpux64/dld.so
oracle 20472 oracle mem REG 64,0×7 184592 90962 /usr/lib/hpux64/uld.so
oracle 20472 oracle 0u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 1u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 2u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 3u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 4u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 5u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 6u CHR 3,0×2 0t0 74 /dev/null
oracle 20472 oracle 7u IPv4 0xe0000001cbfe0900 0t0 TCP *:* (IDLE)
oracle 20472 oracle 8u REG 64,0×4 849408 13244 /home/oracle/product/10g/rdbms/mesg/oraus.msb
oracle 20472 oracle 14u REG 64,0×4 268644352 79279 /home/oracle/product/10g/cdata/localhost/local.ocr
oracle 20472 oracle 27u IPv4 0xe0000001c34f0740 0t3603 TCP ***.***.***.***:1521->name.ofthehost.com:4934 (ESTABLISHED)
oracle 20472 oracle 28w FIFO 0xe00000017b3ba748 0t0 184090157
oracle 20472 oracle 29r FIFO 0xe0000001f99d3e48 0t0 184090158
At the end of the output, I saw an IP address and the host name where the process were connecting. After checking that server, I saw that it was opening a connection to the database and created a process on OS. Then it wasn’t able to create a session and didn’t detect the dead process and kill it. So it remained in the V$PROCESS view and on the OS as a process and was missing in the V$SESSION view.
I killed all missing sessions from OS level and rebooted the server which was opening connections to the database. It seems it has memory or network issues, so I asked sysadmins to check it
You can also check the following metalink note and learn how you can enable Dead Connection Detection (DCD)
A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes [ID 601605.1]
February 8th, 2013 at 2:58 pm
Hi Kamran,
They can also be just some pre-spawned processes like PX slaves which have currently no work to do (thus no sessions in them) or idle job queue processes without a task. And the PSEUDO process too for shared servers:
SQL> select program from v$process where addr not in (select paddr from v$session);
PROGRAM
------------------------------------------------
PSEUDO
oracle@enkdb02.enkitec.com (P000)
oracle@enkdb02.enkitec.com (P001)
oracle@enkdb02.enkitec.com (P002)
...
Tanel
February 15th, 2013 at 1:11 pm
Hi Tanel
Thanks for the comment! What about having them more than 100 processes which consume a lot of system memory?