SQL & PL/SQL

Hi all,

we have a batch process , which usually takes 8hrs to complete. But for the past couple of weeks, this process is running for 12 hrs. This process has around 25 SPs(populating 90+ tables) inside and all the SPs are like truncate table... , insert..select next followed by commit. No pl/sql specific codes like loop, constructs, bulk collect or if statements used inside.

I checked the rowcount of source & target tables (with the rowcount on a week in which the process completed in 8hrs), but there is no big diff, only +-5% is the difference which is normal. I also have the execution plans of all the queries which was taken 3 weeks back. When I checked the plan now, there is no difference between these two plans. All the plans are same as it was two weeks back.

we are recording the time taken by each query every week. So when we compared it, we found all the queries are taking more time than it usually takes(two weeks back).

Will there be any performance degrade if the statspack report is enabled when the process is running. If yes, how much impact it could have... (%). How to identify whether the STATSPACK was enabled when this process was running. Where do I find, STATSPACK report related info like when it was enabled and disabled(Other than asking the DBA, because it takes long time to get this info from them as they are sitting in different country and work in different time zone).

Note: Parallel DML is used in 40% of the queries(enabled parallel DML and used Parallel hint). Oracle version 9i

Kindly let me know what are the possibilities...

How do I investigate it further...

I can't post the Package as it is very big(10.000 lines).

Thanks in advance,
Jaggyam