A custom concurrent program is running for a long time - The first time I run it, it takes 15 minutes to complete and the consecutive runs with different set of parameter values , it takes only a minute. Please let me know the steps to trouble-shoot this issue whilst I can try and get the explain plan for the query used in the PLSQL package.
P.S : If I run the query in SQL plus, it takes like 3-4 minutes.
You can obtain the trace file and TKPROF for the trace file to see which sql statement takes longer time for execution. Obtain the trace files for multiple iterations and compare to see which query is taking long time.
Enabling Concurrent Program Traces (Doc ID 123222.1)