(running system: Oracle 9i (9.024), Windows 2000, Pentium 4, dual processes, 2GB RAM)
Here's the problem. I have a few scheduled nightly jobs and for some reason lately, they take a very long time to run. Originally, this one particular job used to take about 15 minutes; now it takes about 2 hours. I haven't changed any parameters on the dB nor did I make any changes to the software. Also, the flat files that are being imported haven't changed either (data amount that is). So now I'm trying to figure out that the problem may be. I did analyze tables for all schemas, but that didn't help much. the job process would decrease to about 1 hour and the next day it would go back to 2 hours.
Would anyone know what I should try or do to bring this job process back to normal? Please let me know.
Here are some stats from OEM:
Cache hit %:75.46.
SGA Buffer Cache advice: it's ok..currently at 120,000 reads, next lower is about 90,000
SGA Shared Pool is flat.
here's the output for the faulty job that I"m having problems with. As you can see I'm uptading about 7400 rows. That's not that much, yet it takes over 2 hours.
SQL> EXEC ADM_LOAD('LOAN_PRODUCT_DIMENSION_MAP','&2','&1');
7363 row(s) updated for DLS_DM.LOAN_PRODUCT_DIMENSION
38 row(s) inserted into DLS_DM.LOAN_PRODUCT_DIMENSION
PL/SQL procedure successfully completed.