深度剖析：ORA-00018: maximum number of sessions exceeded

最新推荐文章于 2022-09-29 19:38:57 发布

cotchte0421

最新推荐文章于 2022-09-29 19:38:57 发布

阅读量919

点赞数

文章标签：数据库 java

[i=s] 本帖最后由 wei-xh 于 2013-7-3 17:55 编辑

今天朋友遭遇了如下的问题，远程联系我解决
SQL> select * from xxx ;
select * from xxx
ERROR at line 1:
ORA-00018: maximum number of sessions exceeded

相信只要是干过一两年DBA的对与这个错误ORA-00018是比较熟悉的，也知道问题的解决办法，那就是增加processes的参数设置，由于sessions是依据processes推导出来的，因此processes的增大，会导致sessions也会增大，也就解决了这个问题。具体推导的公式，可能9I,10G版本基本遵循的是sessions=(1.1*processes)+5，11G以后（可能是11GR2之后，抱歉我没11GR1的版本来做测试），不是这样了，在我11GR2的环境下，3000 processes的设置，已经有4560个sessions,大大的增加了session的数量。

这个错大部分情况下，都是发生在建立连接的时候，由于超出了sessions的设置值，导致连接抛错。但是。。。
但是我朋友的这个案例是，他已经连接了！！！！
其实这个问题不难回答，因为只要看过TOM书的同学都知道，一个连接，可能会导致多个session。
举个例子：
比如在你数据库刚启动的时候、或者flush shared pool之后，或者你查询的表的数据字典信息已经不在共享池里
你建立连接后，ORACLE分配给你一个session，我们称之为session1
然后你select * from xxx的时候
Oracle会独立新开一个session，我们称之为session2 ,这个session2会去递归的查询数据字典（硬解析的需要），比如tab$,col$,seg$以及一些统计信息的数据字典基表
等它做完这些工作后，就立即消失了。

当然除了查询，还会很多操作会产生这种行为，比如create,drop，alter等等，由于这些操作都修改了数据字典基表，因此他们也会产生一个递归的session的来去帮他们完成这些数据字典的操作，而且你还会“惊奇”的发现，这些递归session的用户竟然不是你当时连接的用户。
比如你用test/test做的连接，但是递归语句的session的用户是sys!!!!

说到这里，你可能觉得，你大概知道了这里面的奥秘，那就是，我朋友当时发出这个查询的时候，连接session的数量刚好等于了允许存在session的最大数量，因此导致递归的session无法创建而报错。
在确认这个问题前，还有些问题要解决。

是不是查询都要递归的产生session ?
不是的，只有需要的数据字典信息不在数据字典cache里的时候。
当时那个环境下，查询其他常用的表会不会报错？
经过我的测试，当时查询一些常用的表，是不报错的！！！因为它的数据字典信息都是在数据字典cache里的。

好，上面的疑问解决后，我们来看下，当时session的数量，和设置的最大值分别是多少
select count(*) from v$session;
  COUNT(*)
----------
   216

show parameter sessions

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------                                           sessions                            integer             232

奇怪的事又发生了！
当前session的数量还没达到设置的最大值，如果是要创建递归的session，那仅仅需要一个session就够了。而他的环境里，还剩余232-216=16个session可以用。
问题出哪了呢？
我的猜测应该是v$session这个视图，对于递归的session做了过滤。
经过查询V$FIXED_VIEW_DEFINITION，我们可以知道gv$session是依据数据字典基表X$KSUSE创建出来的（创建语句我就不贴出来了）
可以看到创建的最后where语句后，过滤掉了一些内容，我数学没学好，不确认是不是过滤掉的递归的session ，但是我还有办法，继续往下看。
怎么来证明呢？看我的。
SESSION1：
select sid from v$mystat where rownum=1;
   SID
----------
   1768
select paddr from v$session where sid=1768;
PADDR
----------------
0000000AE9530BD0

SESSION 2：
lock table tab$ IN exclusive MODE;

SESSION 1：
CREATE TABLE A (ID NUMBER);
会被hang住，因为创建表需要往数据字典基表$tab里插入记录。而且这个工作是由递归产生session做的

session 2:
select paddr from v$session where sid=1768;
PADDR
----------------
0000000AE9530BD0

select decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna
from x$ksuse s where ksusepro = '0000000AE9530BD0';
DECODE(BITAND(KSUSEF KSUUDSNA
-------------------- ------------------------------------------------------------
USER                ILSMP23ST
RECURSIVE          SYS

上面测试的思路是：创建一张表，由于这种DDL，会往数据字典基表tab$里插入记录，如果我们把tab$表锁住，那么就可以观察到递归的session在那里被hang住等待锁，这样我们就能观察到这个“神奇”的会话了。

经过我们的测试发现：果然，v$session里只有一条记录，而数据字典基表里，已经有2个会话了，其中一个是递归产生的，sys用户的，而这个递归会话在v$session里没看到，也就是说过滤掉了。

说到这里，我们差不多知道这个案例的原因了
虽然v$session里总共的sessoin数量与设置的参数值之间还有16个session可以用，但是由于v$session屏蔽了递归session的数量，因此实际上，非常可能这16个你认为可以用的session早被递归的session占用了。
如果你看DSI比较多的话，特别是锁那一节，你会知道有一个视图其实已经暴露给我们，方便的查询各种资源的使用情况以及最大值设置情况。

select * from v$resource_limit;
RESOURCE_NAME                CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                                     120          121    3000                3000
sessions                                     205          206    4560                4560
enqueue_locks                                  27             31    55020             55020
enqueue_resources                            22             22    20284          UNLIMITED
ges_procs                                     0             0       0                   0
ges_ress                                        0             0       0          UNLIMITED
ges_locks                                     0             0       0          UNLIMITED
ges_cache_ress                                  0             0       0          UNLIMITED
ges_reg_msgs                                  0             0       0          UNLIMITED
ges_big_msgs                                  0             0       0          UNLIMITED
ges_rsv_msgs                                  0             0       0                   0
gcs_resources                                  0             0  UNLIMITED          UNLIMITED
gcs_shadows                                     0             0  UNLIMITED          UNLIMITED
smartio_overhead_memory                         0             0       0          UNLIMITED
smartio_buffer_memory                         0             0       0          UNLIMITED
smartio_metadata_memory                         0             0       0          UNLIMITED
smartio_sessions                               0             0       0          UNLIMITED
dml_locks                                     0             0    20064          UNLIMITED
temporary_table_locks                         0             0  UNLIMITED          UNLIMITED
transactions                                  0             0    5016          UNLIMITED
branches                                        0             0    5016          UNLIMITED
cmtcallbk                                     0             0    5016          UNLIMITED
max_rollback_segments                         0             0    5016             65535
sort_segment_locks                            0             0  UNLIMITED          UNLIMITED
k2q_locks                                     0             0    9120          UNLIMITED
max_shared_servers                            1             1  UNLIMITED          UNLIMITED
parallel_max_servers                            0             0    1600                3600
27 rows selected.

select count(*) from v$session;
  COUNT(*)
----------
   116
v$resource_limit里显示的第二行，列为CURRENT_UTILIZATION 的值，就是真正的已经被占用的sessoin，这个值比较我查询v$session多出了很多，这些多余的session就是递归的session。