2 Replies Latest reply: Feb 7, 2013 7:18 AM by jariola RSS

    11G XE sessions parameter

    jariola
      Hi,

      I have installed 11G XE to Linux CentOs 6.3.
      I did increase sessions parameter value
      -bash-4.1$ sqlplus '/as sysdba'
      
      SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 7 07:06:42 2013
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      
      SQL> alter system set sessions = 250 scope=spfile;
      
      System altered.
      
      SQL>
      Here is file /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora content after change
      XE.__java_pool_size=4194304
      XE.__large_pool_size=4194304
      XE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
      XE.__pga_aggregate_target=293601280
      XE.__sga_target=545259520
      XE.__shared_io_pool_size=0
      XE.__shared_pool_size=318767104
      XE.__streams_pool_size=4194304
      *._disk_sector_size_override=TRUE
      *.audit_file_dest='/var/log/oracle/admin/XE/adump'
      *.cell_offload_processing=FALSE
      *.compatible='11.2.0.0.0'
      *.control_files='/u01/app/oracle/oradat^AC^@^@C"^@^@^C^@^@^@^@^@^@^@^@^@^A^DZ^\^@^@a/XE/control.dbf'
      *.db_name='XE'
      *.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
      *.db_recovery_file_dest_size=26843545600
      *.diagnostic_dest='/var/log/oracle'
      *.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
      *.filesystemio_options='SETALL'
      *.job_queue_processes=4
      *.memory_max_target=838860800
      *.memory_target=838860800
      *.open_cursors=800
      *.plsql_code_type='NATIVE'
      *.processes=200
      *.remote_login_passwordfile='EXCLUSIVE'
      *.sessions=250
      *.shared_servers=4
      *.undo_management='AUT^AC^@^@C"^@^@^D^@^@^@^@^@^@^@^@^@^A^DA^C^@^@O'
      *.undo_tablespace='UNDOTBS1'
      When I shutdown and start database I can see from alert log
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.
      Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
      System parameters with non-default values:
        processes                = 200
        sessions                 = 322
        filesystemio_options     = "SETALL"
        _disk_sector_size_override= TRUE
        memory_target            = 800M
        memory_max_target        = 800M
        control_files            = "/u01/app/oracle/oradata/XE/control.dbf"
        compatible               = "11.2.0.0.0"
        db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"
        db_recovery_file_dest_size= 25G
        undo_management          = "AUTO"
        undo_tablespace          = "UNDOTBS1"
        remote_login_passwordfile= "EXCLUSIVE"
        dispatchers              = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
        shared_servers           = 4
        plsql_code_type          = "NATIVE"
        job_queue_processes      = 4
        audit_file_dest          = "/var/log/oracle/admin/XE/adump"
        cell_offload_processing  = FALSE
        db_name                  = "XE"
        open_cursors             = 800
        diagnostic_dest          = "/var/log/oracle"
      And when I check sessions parameter value
      -bash-4.1$ sqlplus '/as sysdba'
      
      SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 7 07:11:09 2013
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      
      SQL> show parameter sessions
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      java_max_sessionspace_size           integer     0
      java_soft_sessionspace_limit         integer     0
      license_max_sessions                 integer     0
      license_sessions_warning             integer     0
      sessions                             integer     322
      shared_server_sessions               integer
      SQL>
      Why sessions parameter value is 322 even I did set it to 250?

      Regards,
      Jari
      -----
      My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
      Twitter: http://www.twitter.com/jariolai
        • 1. Re: 11G XE sessions parameter
          asahide
          Hi,

          According to manual 11.2,
          Oracle uses the default value of this parameter as its minimum.
          <<http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams231.htm#REFRN10197>>

          And As you may know, Default value is follows.
          Default value      Derived: (1.5 * PROCESSES) + 22
          So, this is my understanding, Default value is set to 322.
          regards,
          • 2. Re: 11G XE sessions parameter
            jariola
            Hi,

            Thanks.

            Regards,
            Jari
            -----
            My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
            Twitter: http://www.twitter.com/jariolai