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.
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.
Actually you should have collect the SQL tunining Sets before the application move to ODA then you should have compared the with SPA.
Pavan Kumar N
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,
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 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;
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.
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.