Hi everyone
I'm currently working on a migration project that involves the use of some PL/SQL ETL routines. These routines are "slow by slow" (as Tom Kyte refers to them) but there's nothing I can do about that - I just have to live with it.
Anyway, when running the routines (which load and validate patient records and all associated demographic data), the performance varies a lot and can change for no apparent reason. For example, on the last test run, the rate at which patients were being processed was ~66 per second (as I said...slow by slow!); this continued for about an hour and then it suddenly shot up to ~500 per second. I'm assuming that Oracle suddenly changed the execution plan but I'm not sure how to track down what caused that to happen? That kind of increase would suggest that an index somewhere suddenly started being used when it hadn't been before but again, how does one go about tracking it down? Is it a case of analyzing every single query and cursor within the PL/SQL routines and adding hints?
Oracle version is 11G R2 and it's running on Windows.
Any suggestions would be very much appreciated...