5 Replies Latest reply on Apr 13, 2018 2:38 AM by mdtaylor

    How to Compare Performance?

    Beauty_and_dBest

      EBS R12.1.3

      11gR2

      OL6

       

       

      Hi ALL,

       

      We have migrated our EBS 12.1.3 to new ODA server (Oracle Database Appliance).

      The OS installed (OL 6.8) and other setup are both the same since we just cloned it.

      The users feedback seems good and they tell us that the system or their process seems faster.

      But our boss want a back&white report that shows the comparison of performance.

       

      Is there a database statistics where we can query and compare, which will tells us that the performance is better?

      Can I query the concurrent programs average run time and compare both old and new servers?

      Usually the longest processing time is "Create Accounting" programs. How can we compare it with the previous run? Or is it logical to do so?

      Can I also run RMAN full backup and compare the processing time?

      Is RMAN the most logical representation of the entire database performance?

       

       

      Please help how can we prepare a performance report comparison.

       

       

      Kind regards,

      jc

        • 1. Re: How to Compare Performance?
          Eric Z-Oracle

          Hi JC,

           

          If you have licsense of AMP, it can be of great help.

          It is a handy plugin that can be integrated with EBS for performance monitoring.

           

          https://blogs.oracle.com/stevenchan/e-business-suite-plug-in-12103-for-enterprise-manager-12c-now-available

           

          Or you can utilize OEM to monitor database in real time statistics.

           

          But it frankly speaking, it can never be easy to compare 2 running instances when they have different workload, considering one of them is a production. Unless, you can set up 2 test instances with the same workload and put the same heavy workload on both and see which one performs better.

           

          I bet your old server is not as often accessed as the new server is, so running individua programs for comparison may not give you precise behaviors as they should really be, unless the new server’s hardware is way much better than the old one, you will not see much difference.

           

          It represents my personal opinion, and I am also looking forward to some insight from other guys.

           

          Cheers!

          Eric

          • 2. Re: How to Compare Performance?
            Pavan Kumar

            Hi,

            Actually you should have collect the SQL tunining Sets before the application move to ODA then you should have compared the with SPA.

            https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

             

            - Thanks
            Pavan Kumar N

            • 3. Re: How to Compare Performance?
              mdtaylor

              Hi jc,

               

              A good way to check throughput and benefit of moving to new hardware is to check timings of backups and run times of requests.  You should remember that backup times could be dependent on the wrote speeds of the target storage.

               

              SELECT b.session_key, b.session_recid, b.session_stamp, b.command_id, b.status, b.start_time, b.time_taken_display, b.input_type, b.output_device_type,

              b.input_bytes_display, b.output_bytes_display, b.output_bytes_per_sec_display

              FROM V$RMAN_BACKUP_JOB_DETAILS b  WHERE  ( b.start_time > (SYSDATE - 30) ) and b.input_type like 'DB%'  order by b.start_time desc;

               

              You can use Oracle Applications Manager to view longest running requests before and after the move, or just select longest average requests if you have OAM CP Charts and Collections request scheduled.  Hopefully Gather Schema Statistics runs faster.

               

              SELECT CR.REQUEST_ID "Request", U.USER_NAME "User", CP.USER_CONCURRENT_PROGRAM_NAME "Program", --RESPONSIBILITY_NAME "Responsibility",

                     TO_CHAR(CR.REQUESTED_START_DATE,'DD-MON-YY HH24:MI:SS') "Requested Start",

                     TO_CHAR(CR.ACTUAL_START_DATE,'DD-MON-YY HH24:MI:SS') "Actual Start",

                     ROUND((CR.ACTUAL_START_DATE-REQUESTED_START_DATE)*24*60*60,0) "Pending Time (sec)",

                     ROUND((CR.ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60,0) "Duration (sec)",

                     ROUND((CR.ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,2) "Duration (min)",

                     CR.PHASE_CODE "Phase", CR.STATUS_CODE "Status", CR.ARGUMENT_TEXT "Arguments"

              FROM FND_CONCURRENT_REQUESTS CR,

                    FND_USER U,

                    FND_CONCURRENT_PROGRAMS_TL CP,

                    FND_RESPONSIBILITY_TL R

              WHERE CR.REQUESTED_BY = U.USER_ID

                 AND CR.PROGRAM_APPLICATION_ID = CP.APPLICATION_ID

                 AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID

                 AND CP.LANGUAGE='US'

                 AND CR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

                 AND CR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND R.LANGUAGE='US'

                 AND TO_CHAR(CR.REQUESTED_START_DATE,'DD-MON-YYYY') BETWEEN '01-APR-2018' and '12-APR-2018'

                 AND CP.USER_CONCURRENT_PROGRAM_NAME like 'Create Accounting%'

              ORDER BY CR.REQUEST_ID DESC;

              • 4. Re: How to Compare Performance?
                Beauty_and_dBest

                Thanks Michael, Pavan, & Eric

                 

                Can I run the above script on both of our Old Prod vs. New Prod ? Say last week run in OLD PROD and first week run in NEW PROD.

                 

                 

                Kind regards,

                • 5. Re: How to Compare Performance?
                  mdtaylor

                  Hi JC,

                   

                  Run both on new prod and you should see run times from both as long as you are looking back longer than you have been on new prod.

                   

                  Regards,

                  Michael