8 Replies Latest reply on Apr 24, 2018 3:43 PM by mdtaylor

    EBS 12.1.3 Performance issue

    Beauty_and_dBest

      EBS 12.1.3

      11gR1

      RHEL5

       

       

       

      Hi ALL,

       

      We are encountering performance slow in our EBS.

       

      Our Nagios Monitoring tool show critical alerts below.

      Can you help what to tuning in our server based in these alerts?

       

      Have you heard about Nagios monitoring tools? Is it accurate  in telling or identifying the  issue?

      What tuning parameter should I adjust if the there are alerts showing below?

       

      Critical nagios alert has been a recurring error since last year and

      has been a chicken-and-egg issue. This is often referred to our technical

      team - server admin/ncd/maintenance vendor.

       

      Whenever we get the nagios alert, the full details of the error is

      that it is a socket timeout (see below) or CPU => 90%. We have referred

      this issue to technical team and they always say that this is an

      application issue.

       

      1) ***** Nagios *****

       

      Notification Type: PROBLEM

       

      Service: SVC Phoenix Oracle WebPage

      Host: Application - Oracle Financials (x.x.x.x)

      Address: x.x.x.x

      State: CRITICAL

       

      Date/Time: Tue Apr 17 14:05:36 +08 2018

       

      Additional Info:

       

      CRITICAL - Socket timeout after 10 seconds

      Duration: 0d 0h 2m 10s

      Notification Count: 1

       

      2) ***** Nagios *****

       

      Notification Type: PROBLEM

       

      Service: PERF CPU Usage

      Host:  Oracle Server/DB Prod (x.x.x.x)

      Address: x.x.x.x

      State: WARNING

       

      Date/Time: Tue Apr 17 14:04:22 +08 2018

       

      Additional Info:

       

      CPU WARNING : user=74% system=15% iowait=2% all=90%

      Duration: 0d 0h 2m 2s

      Notification Count: 1

       

      3) ***** Nagios *****

       

      Notification Type: PROBLEM

       

      Service: PERF LFS /fin_app

      Host: Application - Oracle Financials (x.x.x.x)

      Address: x.x.x.x

      State: CRITICAL

       

      Date/Time: Mon Apr 16 13:00:46 +08 2018

       

      Additional Info:

       

      CHECK_NRPE: Socket timeout after 30 seconds.

      Duration: 0d 0h 2m 30s

      Notification Count: 1

       

       

      Kind regards,

      jc

        • 1. Re: EBS 12.1.3 Performance issue
          mdtaylor

          Hi jc,

           

          Is this a physical or virtual machine?  Are you sure the sga_target and db_cache_sizes are sized for the current database size?  Maybe memory was sized OK when it was a 100G database, but needs to be increased if the database has grown to over 1 TB in size.  It is also advisable to refresh hardware every three years or so to get faster CPUs, faster memory and better storage to maintain good performance as the database grows.

           

          It is also a good idea to gather stats on SYS maybe once or twice a year if the database is growing.

           

          I normally use Enterprise Manager for EBS server monitoring over Nagios.

           

          Regards,

          Michael

          • 2. Re: EBS 12.1.3 Performance issue
            Beauty_and_dBest

            Thanks Michael,

             

             

            This are physical machines, although we are planning to migrate them to VMs, since they are more than 5 yrs.

             

            How do I gather stats on SYS? Do you mean gather schema statistics like all the apps schema?

             

             

            Below are set parameter values:

             

            Please see the parameters for PROD database :

             

            Connected to:

            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

            Production

            With the Partitioning, OLAP, Data Mining and Real Application Testing

            options

             

            Loading login.sql file...

            SYS@PROD AS SYSDBA> spool output.txt

            SYS@PROD AS SYSDBA> show parameter processes

             

            NAME TYPE VALUE

            ------------------------------------ -----------

            ------------------------------

            aq_tm_processes integer 1

            db_writer_processes integer 4

            gcs_server_processes integer 0

            global_txn_processes integer 1

            job_queue_processes integer 2

            log_archive_max_processes integer 4

            processes integer 2000

            SYS@PROD AS SYSDBA> 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 4000

            shared_server_sessions integer

            SYS@PROD AS SYSDBA> show parameter sga_target

             

            NAME TYPE VALUE

            ------------------------------------ -----------

            ------------------------------

            sga_target big integer 10G

            SYS@PROD AS SYSDBA> show parameter shared_pool_size

             

            NAME TYPE VALUE

            ------------------------------------ -----------

            ------------------------------

            shared_pool_size big integer 832M

            SYS@PROD AS SYSDBA> show parameter shared_pool_reserved_size

             

            NAME TYPE VALUE

            ------------------------------------ -----------

            ------------------------------

            shared_pool_reserved_size big integer 80M

            SYS@PROD AS SYSDBA> show parameter pga_aggregate_target

             

            NAME TYPE VALUE

            ------------------------------------ -----------

            ------------------------------

            pga_aggregate_target big integer 6G

            SYS@PROD AS SYSDBA>

             

            [oraPROD@DBSERVER DBA]$ cat /etc/security/limits.conf

            # /etc/security/limits.conf

            #

            #Each line describes a limit for a user in the form:

            #

            #<domain> <type> <item> <value>

            #

            #Where:

            #<domain> can be:

            # - an user name

            # - a group name, with @group syntax

            # - the wildcard *, for default entry

            # - the wildcard %, can be also used with %group syntax,

            # for maxlogin limit

            #

            #<type> can have the two values:

            # - "soft" for enforcing the soft limits

            # - "hard" for enforcing hard limits

            #

            #<item> can be one of the following:

            # - core - limits the core file size (KB)

            # - data - max data size (KB)

            # - fsize - maximum filesize (KB)

            # - memlock - max locked-in-memory address space (KB)

            # - nofile - max number of open files

            # - rss - max resident set size (KB)

            # - stack - max stack size (KB)

            # - cpu - max CPU time (MIN)

            # - nproc - max number of processes

            # - as - address space limit

            # - maxlogins - max number of logins for this user

            # - maxsyslogins - max number of logins on the system

            # - priority - the priority to run user process with

            # - locks - max number of file locks the user can hold

            # - sigpending - max number of pending signals

            # - msgqueue - max memory used by POSIX message queues (bytes)

            # - nice - max nice priority allowed to raise to

            # - rtprio - max realtime priority

            #

            #<domain> <type> <item> <value>

            #

             

            #* soft core 0

            #* hard rss 10000

            #@student hard nproc 131072

            #@faculty soft nproc 131072

            #@faculty hard nproc 50

            #ftp hard nproc 0

            #@student - maxlogins 4

             

            * hard nofile 65535

            * soft nofile 65535

            * hard nproc 131072

            * soft nproc 131072

             

            # End of file

             

             

             

            Thanks!

            • 3. Re: EBS 12.1.3 Performance issue
              mdtaylor

              Hi jc,

               

              First, I would set db_writer_processes=1.

               

              Then gather stats for SYS using the following script:

               

              execute dbms_stats.unlock_schema_stats('SYS');

              execute dbms_stats.unlock_schema_stats('SYSTEM');

              exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree =>8);

              !date

              exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree =>8);

              !date

              exec dbms_stats.gather_schema_stats('SYSMAN',options=>'GATHER', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree =>8);

              !date

              exec dbms_stats.gather_fixed_objects_stats();

              commit;

               

              The officially approved method for gathering stats for EBS is to use adstats.sql, but you cannot run that while the system is up.  You can use the script above over a weekend to compute stats on SYS, and I have improved performance with this method especially when SYS queries are many of the top SQLs in the SQL Area.

               

              Regards,

              Michael

              • 4. Re: EBS 12.1.3 Performance issue
                Beauty_and_dBest

                Thanks Michael,

                 

                The db_writer_processes was originally set to 4, Does setting it high affect performance?

                Do I permanently set it to 1? or will I revert it back after gathering stats?

                 

                 

                Kind regards.

                • 5. Re: EBS 12.1.3 Performance issue
                  mdtaylor

                  Hi jc,

                   

                  I would just permanently set db_writer_processes to 1.  I think the default is 1/2 the number of CPUs, but this causes more performance problems than it solves.

                   

                  https://kevinclosson.net/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/

                   

                  I have recently set some busy ASCP/EBS systems to use 1 db_writer_processes and performance improved considerably,

                   

                  Regards,

                  Michael

                  • 6. Re: EBS 12.1.3 Performance issue
                    Beauty_and_dBest

                    Thanks Michael,

                     

                    We will test it

                    • 7. Re: EBS 12.1.3 Performance issue
                      Beauty_and_dBest

                      Hi Michael,

                       

                      Re:

                      The officially approved method for gathering stats for EBS is to use adstats.sql, but you cannot run that while the system is up. You can use the script above over a weekend to compute stats on SYS, and I have improved performance with this method especially when SYS queries are many of the top SQLs in the SQL Area.

                       

                      Can I run adstats.sql if the apps tier is down? and only the db tier is up? Is adstats.sql takes long time to complete?

                       

                       

                      Thanks.

                      • 8. Re: EBS 12.1.3 Performance issue
                        mdtaylor

                        Hi jc,

                         

                        In order to run adstats.sql, you have to shutdown the database, start in restricted mode, run the script, then shutdown.  The SQLs I provided can be added to a cron job and run monthly without any shutdown required.

                         

                        Regards,

                        Michael