11g为何v$session记录数远小于实际值

Yong Huang

Check the fixed table under v$session, i.e. x$ksuse. For easy reference of column mapping, refer to
http://oracledba.ru/fv11gr2/gv$session.html

Basically, you still join v$process (or its fixed table x$ksupr) to the session view (x$ksuse), but don't restrict the result with the where-clause. The where-clause should only contain the minimum conditions to join the two views. See if you can find entries in the session view corresponding to those "ghost" processes.

sundog315

myownstars 发表于 2012-11-16 14:14
SQL> select count(*) from v$session;

  COUNT(*)

加上v$process的program字段看看

myownstars

目前已经恢复正常了，期间数据库被别的DBA多次重启，
SQL>  select count(*) from v$process;
  COUNT(*)
----------
        52
SQL> select count(*) from v$session;
  COUNT(*)
----------
        51
开了SR，一系列回复后得到如下结论
对仅存在于v$process的5个进程做dump errorstack 3 发现一个共性
Dump of memory from 0xfffffffffff9fcb to 0xfffffffffffa10b
FFFFFFFFFFF9FC0 00000001 08D776D0 [......v.]
FFFFFFFFFFF9FD0 00000000 00000000 00000001 10050864 [...............d]
FFFFFFFFFFF9FE0 00000000 00000001 00000001 10014980 [..............I.]
FFFFFFFFFFF9FF0 00000000 00000000 2842204F 10014980 [........(B O..I.]
FFFFFFFFFFFA000 4F52412D 32383030 313A2074 68652070 [ORA-28001: the p]
FFFFFFFFFFFA010 61737377 6F726420 68617320 65787069 [assword has expi]
FFFFFFFFFFFA020 72656400 00000000 00000000 00000000 [red.............]
FFFFFFFFFFFA030 00000000 00000000 00000000 00000000 [................]
即请求的schema已经expired, 查看dba_users,发现若干expired用户
os上有一个job，每30分钟连接数据库且该用户已经expired，初步估计可能由于此
但是仍有很多疑惑，根本原因还不能确认，需观察一段时间

Yong Huang

It's so obvious, and I can easily reproduce it:

Find your IP. Say it's 10.0.0.5
sqlplus
create user u identified by u;
alter user u password expire;
conn u/u

You'll be prompted to enter a new password. Go to the server. Suppose it's UNIX/Linux.
netstat -anp | grep 10.0.0.5
You'll see a server process opening a TCP socket between your client and the server. The server process pid is after "ESTABLISHED" but before "/SID". You can go to sqlplus and find the entry in v$process:
select * from v$process where spid=<the pid you just saw>;

The session object has not been created yet. It does not exist even in x$ksuse.

What other questions do you have?

myownstars

Yong Huang 发表于 2012-12-1 01:46
It's so obvious, and I can easily reproduce it:

Find your IP. Say it's 10.0.0.5

多谢 是我的测试用例写的有问题 现在可以正确的重现该问题了
此类现象应该是所谓的expected behaviour吧
且此类进程仍然耗用processes名额的

Yong Huang

Indeed it's an expected behavior. The server is created first. Once the password is entered, a session will be created associated with this server process.

Many such processes will not only consume process slots, but use memory as well. The shared memory can be ignored (it's shared!). But each process will have a little of its private memory. Many of them add up. In addition, open file descriptors are also being used.