Oracle Database Discussions

Hi,

My Oracle DB Version is:
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

In our application, we have users uploading files resulting in insert of records into a table. file could contain records ranging from 10000 to 1 million records.
I have written a procedure to bulk insert these records into this table using limit clause. After the insert, i noticed my queries run slow against these tables if huge files are uploaded simultaneously. After gathering stats, the cost reduces and the queries executed faster.

We have 2 such tables which grow based on user file uploads. I would like to schedule a job to gather stats during a non peak hour apart from the nightly automated oracle job for these two tables.
Is there a better way to do this?

I plan to execute the below procedure as a scheduled job using DBMS_SCHEDULER.

--Procedure
create or replace 
PROCEDURE p_manual_gather_table_stats AS
TYPE ttab
IS
    TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    ltab ttab;

BEGIN
    ltab(1) := 'TAB1';
    ltab(2) := 'TAB2';
    FOR i IN ltab.first .. ltab.last
    LOOP
        dbms_stats.gather_table_stats(ownname => USER, tabname => ltab(i) , estimate_percent => dbms_stats.auto_sample_size, 
        method_opt => 'for all indexed columns size auto', degree =>
        dbms_stats.auto_degree ,CASCADE => TRUE );
    END LOOP;
END p_manual_gather_table_stats;

--Scheduled Job
BEGIN
    -- Job defined entirely by the CREATE JOB procedure.
    DBMS_SCHEDULER.create_job ( job_name => 'MANUAL_GATHER_TABLE_STATS', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'BEGIN p_manual_gather_table_stats; END;', 
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=12;BYMINUTE=45;BYSECOND=0', 
    end_date => NULL, 
    enabled => TRUE, 
    comments => 'Job to manually gather stats for tables: TAB1,TAB2. Runs at 12:45 Daily.');
END;

Thanks,
Somiya