Queries Running For Hours Because Of Execution Plan

I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

I have 2 schemas in my application.

1. Application schema
2. EOD(End of day) schema.

End of day schema is populated from Application schema whenever user runs EOD process. The tables are pulled like this.

1. Master tables : Always deleted and reinserted at each EOD process
2. Log tables for each transaction table: Delta between the last EOD and current EOD data is pulled and are used for populating transaction tables
3. Transaction tables: These are populated from log tables pulled from previous step. The logic is like this

Now based on these tables about 30 reports are generated in EOD schema. Please note that each transaction table will have a EOD_ID and any report generated uses the where condition <transaction table>.EOD_ID = <current EOD_ID>

My log table contract_log and transaction table is contract in EOD schema.

contract_log table has data like this

contract_number contract_date customer_idqtyrateeffective_date

11/Jan/2010CUST-0110101/Jan/2010

1NULLNULLNULL112/Jan/2010

EOD on 1st Jan 2010 constructs contract table as

contract_numbercontract_datecustomer_idqtyrateeod_id

11/Jan/2010CUST-011010EOD-1

(Since the change of rate 11 is not visible on 1st Jan 2010 because it is effective on 2nd Jan 2010)

EOD on 2nd Jan 2010 constructs contract table as

contract_numbercontract_datecustomer_idqtyrateeod_id

11/Jan/2010CUST-011011EOD-2

(Since the change of rate 11 is visible on 2nd Jan 2010)

This logic is working fine. But we run more than 20-30 EODs the processing time increased to 10-15 hours.

It took some time to figure out the issue as a single query when run from toad or pl sql developer runs in few seconds but as a part of the whole package it takes 2-3 hours(each query).

The problem found was that oracle execution plan gets corrupted when the process starts. So what we did was to analyze the tables
after they are pulled. This perfectly solved our problem. Currently the whole process is taking only about 12-13 minutes where about 3 minutes is lost on analyze tables and indexes. I know this is a temporary solution as I need to get out of online analyze of tables and indexes.

My code for table and index regeneration is as below

PROCEDURE sp_gather_table_index_stats(pc_table_name VARCHAR2) IS
CURSOR cur_ind IS
SELECT index_name
FROM user_indexes
WHERE table_name = pc_table_name;
BEGIN
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_table_stats(user,' || '''' ||
pc_table_name || '''' || '); end;';
FOR cur_ind_rows IN cur_ind LOOP
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_index_stats(user,' || '''' ||
cur_ind_rows.index_name || '''' || '); end;';
END LOOP;
END;