Client is implementin some of PeopleSoft HRMS' modules. Its production environment uses Sun 2.6 and Oracle 8.1.6. That server has 1GB memory and plenty 13 GB ultra-wide scsi HDD. In testing environment, software and dbms are the same but hardware and processing capability are 1/4 of the production.
When running huge amount of COBOL batch processing in testing enviroment, it usually takes 4 hours. After mirgrating to production, it takes 8 hours to complet at the first run. Then, it becomes 16 hours and then 24 hours after second and third run respectively.
SQL has been tuned. Appropriate indexes are created. No error or complaints issued from Oracle. My hunch is to tune I/O distribution. Any other tuning areas that anyone would add? Thanks.
well you have only one disk I assume?
or one CPU?
check v$system_event for wait events
1. SQL tuning again(it well may be that SQL plans are different in test db from prod db or in test db some SQL tuning bugs still exists):
a) v$sqlarea view
1) to get TOP execute count sql, 2) to get TOP buffer_gets/execut_count SQL, etc
b) Use SQL trace and TKPROF
2. shared pool tuning
3. db_buffers tuning