Performance of concurrent program changes over time
614090Feb 16 2010 — edited Jan 23 2012Environment: Oracle Apps R12, Oracle 10g
Scenario: I have a concurrent program which truncates two custom tables (along with many other logic)and
insert new data(around 30K records) into those tables. Once the above concurrent program is completed,
I have another program to be submitted which fetches data from the above two custom tables and
many other seeded tables and display in Program Output.
Observation: If the second concurrent program is submitted immediately after the first concurrent program, it is
taking only 15 mins to get completed. If it is submitted after 2-3 days, then it is taking 5-6 hours to get
completed. If we look at the trace, the execution plan used is different in both runs.
Analysis: Since the first concurrent program truncates the two tables, it sets the HighWaterMark to the minumum
and hence the performance is good. But my question is, even if there is no data insert/update in the custom tables
in 2-3 days and if we run the second program again, why is it taking more time(5-6 hours) to complete?
Please put your thoughts/suggestions here.
Regards,