7 Replies Latest reply: Jan 10, 2013 4:19 PM by sb92075 RSS

    Solaris 10g and Oracle DB performance

    984188
      Forgive me if this is a dumb question, or has been answered before but I can't find this info. I'm not a heavy Solaris user by any means, having been mostly a windows guy.

      We're running a Sunfire x4240 with a large Oracle database on it. We began having issues with messages about "Concurrent Session Limit". After speaking with the vendor who maintains this device (over a week of constant nagging) they finally said we didn't have enough RAM. We had 12 GB RAM, and the files for the DB are ~370GB on disk. We upgraded the RAM to 44 GB, we're a non for profit company and have limits on finances, so we couldn't go any higher than that. We continue having issues with the exact same error, and see no change. Same number of users 160, we hit the 12 GB RAM according to SNMP. After 2 or 3 weeks arguing to try and get some resolution, they said it's the SAN we're attached to for storage. The SAN is a 26 disk array, attached via iSCSI over 10GbE copper. They said performance is so slow that no matter how much RAM we throw at it, we're still at a bottleneck.

      These guys have irked me a bit, so I'm wanting some opinion from REAL experts. CAN disk speed affect the maximum number of concurrent sessions? I haven't read anything good from searching the web about iSCSI on Solaris, could this be the problem and would NFS be a better solution?

      I appreciate any input from the community. Thanks.
        • 1. Re: Solaris 10g and Oracle DB performance
          sb92075
          981185 wrote:
          Forgive me if this is a dumb question, or has been answered before but I can't find this info. I'm not a heavy Solaris user by any means, having been mostly a windows guy.

          We're running a Sunfire x4240 with a large Oracle database on it. We began having issues with messages about "Concurrent Session Limit".
          Real Oracle errors have both Error Number & Message like below

          ORA-01555 Snapshot Too Old.

          Where & when does "Concurrent Session Limit" appear; since I am not certain if it is an Oracle error or not?
          • 2. Re: Solaris 10g and Oracle DB performance
            SteveS
            I'm a Solaris guy, not a DBA. The number of 'Concurrent Users' sounds like an Oracle message rather than a Solaris message. Solaris would report a different error such as "Cannot Connect". How many users you are licenced for

            Assuming a 10GR2 environment, the documentation says - http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams092.htm#i1128342

            LICENSE_MAX_SESSIONS
            Property      Description
            Parameter type      Integer
            Default value      0
            Modifiable      ALTER SYSTEM
            Range of values      0 to number of session licenses
            Basic      No
            Real Application Clusters      Multiple instances can have different values, but the total for all instances mounting a database should be less than or equal to the total number of sessions licensed for that database.

            LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions allowed. When this limit is reached, only users with the RESTRICTED SESSION privilege can connect to the database. Users who are not able to connect receive a warning message indicating that the system has reached maximum capacity.

            A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a nonzero number, you might also want to set LICENSE_SESSIONS_WARNING (see "LICENSE_SESSIONS_WARNING").

            Do not enable both concurrent usage licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.

            See Also:
            Oracle Database Administrator's Guide for more information on setting this parameter

            ----

            LICENSE_SESSIONS_WARNING
            Property      Description
            Parameter type      Integer
            Default value      0
            Modifiable      ALTER SYSTEM
            Range of values      0 to value of LICENSE_MAX_SESSIONS parameter
            Basic      No
            Real Application Clusters      Multiple instances can have different values.

            LICENSE_SESSIONS_WARNING specifies a warning limit on the number of concurrent user sessions. When this limit is reached, additional users can connect, but Oracle writes a message in the alert file for each new connection. Users with RESTRICTED SESSION privilege who connect after the limit is reached receive a warning message stating that the system is nearing its maximum capacity.

            If this parameter is set to zero, no warning is given as you approach the concurrent usage (session) limit. If you set this parameter to a nonzero number, you should also set LICENSE_MAX_SESSIONS (see "LICENSE_MAX_SESSIONS").

            See Also:

            Oracle Database Administrator's Guide for more information on setting this parameter

            Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information on calculating an appropriate value for this parameter
            • 3. Re: Solaris 10g and Oracle DB performance
              984188
              It usually states the following:
              ORA-00604: error occured at recursive SQL level 1
              ORA-00018: maximum number of sessions exceeded
              • 4. Re: Solaris 10g and Oracle DB performance
                sb92075
                981185 wrote:
                It usually states the following:
                ORA-00604: error occured at recursive SQL level 1
                ORA-00018: maximum number of sessions exceeded
                [oracle@localhost ~]$ oerr ora 18
                00018, 00000, "maximum number of sessions exceeded"
                // *Cause:  All session state objects are in use.
                // *Action: Increase the value of the SESSIONS initialization parameter.
                [oracle@localhost ~]$


                sqlplus
                / as sysdba
                SHOW PARAMETER SESSIONS
                ALTER SYSTEM SET SESSIONS=<???> SCOPE=SPFILE;
                --- where ??? is larger than current value
                The DB must be restarted to actually make use of the new, larger value for SESSIONS
                • 5. Re: Solaris 10g and Oracle DB performance
                  984188
                  We don't have direct access to the DB, but do have access to a local root account. Is there any way to find out what that setting is without SQL access? And is there any reason that disk access speeds could be a problem that could limit this?

                  Thanks!
                  • 6. Re: Solaris 10g and Oracle DB performance
                    JustinCave
                    If you have a local root account on the database server, you should be able to access the database
                    sqlplus / as sysoper
                    show parameter sessions
                    If you really can't access the database, you'd have to poke around and figure out which pfile or spfile the database is set to start with and look up the settings there. Since it doesn't sound like you're either a Solaris expert or an Oracle expert and since it sounds like you're not the person that set up the server, it's probably easier to simply ask whoever is acting as your DBA what the SESSIONS initialization parameter is set to.
                    And is there any reason that disk access speeds could be a problem that could limit this?
                    Is it possible? Sure. If, for example, you're using a normal three-tier application with a connection pool, the longer each individual session takes to complete its work the more sessions the connection pool needs to have open at any given point in time. If your disk I/O suddenly gets much slower, that could increase the total number of simultaneous sessions.

                    Is it likely? Not particularly. Slow I/O is something that should have been obvious from the AWR/ Statspack reports that the vendor presumably had access to before suggesting that it was a RAM issue and before suggesting that you throw 32 GB of RAM at the problem. Plus, the seemingly obvious solution to that particular error would have been to bump up the SESSIONS parameter. If you really have a significant performance bottleneck, that would potentially have made the bottleneck worse and you would be complaining that performance had decreased dramatically. If the seemingly obvious solution hadn't been tried long before talking about adding RAM, that makes me rather suspicious that the vendor is just guessing at things that might conceivably be the problem rather than doing the analysis to figure out what the actual problem is.

                    Justin
                    • 7. Re: Solaris 10g and Oracle DB performance
                      sb92075
                      981185 wrote:
                      We don't have direct access to the DB, but do have access to a local root account. Is there any way to find out what that setting is without SQL access? And is there any reason that disk access speeds could be a problem that could limit this?

                      Thanks!
                      find / -name sqlplus -ls 2>/dev/null
                      # issue LONG running command above as root to learn which OS user owns the Oracle software
                      for this discussion assume it is OS user "oracle" then from root account do as below

                      su - oracle

                      after doing so you can follow my previous directions