5 Replies Latest reply: Feb 17, 2013 11:37 PM by Maahjoor RSS

    calculating CPU for 300 Concurent users on Oracle shared server

    Maahjoor
      dear all,

      i have windows server 2003, with oracle database 10g r2, 2.4 GHz (2 CPU) processor, and GB of RAM.
      we have concurrently connected 300 users.
      and it happens often that our connection to that database hangs, and no further connections are allowed to connect.
      i check the performance tab in the WINDOWS TASK MANAGER, and i find the CPU 100% in use.Ram was 40%.


      my oracle server is not dedicated server, it is shared server.

      is this possible that 300 concurrent user could occopy usch a cpu?

      kindly guide me.

      Thanks
        • 1. Re: calculating CPU for 300 Concurent users on Oracle shared server
          asahide
          Hello,

          I think you have to check your system's bottle neck first.
          If you have statspack or awr report, Pls show us.
          And show your init parameter.


          Regards,
          • 2. Re: calculating CPU for 300 Concurent users on Oracle shared server
            Aman....
            Maahjoor wrote:
            dear all,

            i have windows server 2003, with oracle database 10g r2, 2.4 GHz (2 CPU) processor, and GB of RAM.
            we have concurrently connected 300 users.
            and it happens often that our connection to that database hangs, and no further connections are allowed to connect.
            i check the performance tab in the WINDOWS TASK MANAGER, and i find the CPU 100% in use.Ram was 40%.


            my oracle server is not dedicated server, it is shared server.

            is this possible that 300 concurrent user could occopy usch a cpu?
            It's the user's work that would burn or use the CPU than the users. Even if it's some user, I doubt that all 300 users are burning the cpu to the max. And even further, it's fairly possible that nothing in the database is consuming the cpu but something running on the system is actually doing it. So if you are licensed to use AWR , either it's report or from the Statspack report, pull up a report and post it here.

            Aman....
            • 3. Re: calculating CPU for 300 Concurent users on Oracle shared server
              Maahjoor
              i was avoiding asahideO reply because of the AWR or STATSPACK, since i am not aware of that...sorry, i think you will never imagine a DBA without knowing AWR or STATSPACK,,, my mistake, could you guide me a little how to configure statspack or awr, which one should i use and why? which one is free?

              i am sorry for my un-awareness.

              Regards.
              • 4. Re: calculating CPU for 300 Concurent users on Oracle shared server
                Dave Rabone
                How many shared servers do you have configured for your 300 users?

                That figure determines the maximum number of simultaneous active sessions - other sessions wait for one to become free.

                That might be the cause of your apparent system hang.
                • 5. Re: calculating CPU for 300 Concurent users on Oracle shared server
                  Maahjoor
                  hi Dave,

                  i have in alert_log file the following info.
                  Windows Server 2003 Version V5.2 Service Pack 2
                  CPU             : 2 - type 586, 1 Physical Cores
                  Process Affinity: 0x00000000
                  Memory (A/P)    : PH:1162M/2038M, PG:4654M/5970M, VA:1953M/2047M
                  Sun Feb 17 14:19:51 2013
                  Starting ORACLE instance (normal)
                  LICENSE_MAX_SESSION = 0
                  LICENSE_SESSIONS_WARNING = 0
                  Picked latch-free SCN scheme 2
                  KCCDEBUG_LEVEL = 0
                  Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
                  Autotune of undo retention is turned on. 
                  Dynamic strands is set to TRUE
                  Running with 2 shared and 110 private strand(s). Zero-copy redo is FALSE
                  IMODE=BR
                  ILAT =110
                  LICENSE_MAX_USERS = 0
                  SYS auditing is disabled
                  Warning: Total dispatchers (1) > MAX_DISPATCHERS (0)
                  Starting up ORACLE RDBMS Version: 10.1.0.4.2.
                  System parameters with non-default values:
                    processes                = 500
                    sessions                 = 1000
                    sga_max_size             = 524288000
                    __shared_pool_size       = 163577856
                    shared_pool_size         = 150994944
                    __large_pool_size        = 16777216
                    large_pool_size          = 8388608
                    __java_pool_size         = 67108864
                    java_pool_size           = 67108864
                    sga_target               = 524288000
                    control_files            = E:\ORACLEAS\ORADATA\ORCL\CONTROL01.CTL, E:\ORACLEAS\ORADATA\ORCL\CONTROL02.CTL, E:\ORACLEAS\ORADATA\ORCL\CONTROL03.CTL
                    db_block_size            = 8192
                    __db_cache_size          = 272629760
                    db_cache_size            = 50331648
                    max_commit_propagation_delay= 0
                    compatible               = 10.1.0.2.0
                    db_file_multiblock_read_count= 16
                    db_recovery_file_dest    = E:\OracleAS\flash_recovery_area
                    db_recovery_file_dest_size= 2147483648
                    undo_management          = AUTO
                    undo_tablespace          = UNDOTBS
                    remote_login_passwordfile= EXCLUSIVE
                    db_domain                = hct.org
                    dispatchers              = (protocol=TCP)
                    shared_servers           = 3
                    max_shared_servers       = 3
                    max_dispatchers          = 0
                    local_listener           = vhost_alias
                    job_queue_processes      = 5
                    background_dump_dest     = E:\ORACLEAS\ADMIN\ORCL\BDUMP
                    user_dump_dest           = E:\ORACLEAS\ADMIN\ORCL\UDUMP
                    core_dump_dest           = E:\ORACLEAS\ADMIN\ORCL\CDUMP
                    db_name                  = orcl
                    open_cursors             = 300
                    pga_aggregate_target     = 33554432
                    aq_tm_processes          = 1
                  there is a warning for dispatcher Warning: Total dispatchers (1) > MAX_DISPATCHERS (0).

                  kindly guide me.