ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深度剖析:ORA-00018: maximum number of sessions exceeded

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

原创 Linux操作系统 作者:wei-xh 时间:2013-07-03 10:48:30 0 删除 编辑
[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。



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-765372/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2302388