Oracle Database Discussions

I am using 10g on Unix. With help from users on this site I was able to construct a stored procedure to gather stats and alter indices on a table that is populated and emptied twice within a process. If the stats aren't gathered after each population, the program will run for hours, rather than minutes. The job log shows three seconds between the call and the return, so I had assumed that the statements had executed. However when examining the stats record it indicated that the stats had not been updated for several days. I have been given to understand that Oracle will not perform a gather stats if it feels it is not needed. How do I make it execute the commands regardless?


This is the program code, by this point the temp table has been populated by SQLLDR. This sequence of code will run twice.

PreScrub_Ztemp_ESI_Accum;
ReportData(ErrorFileLocation,trim(ParamStr(3))); {also checks for zero count Error}
dmMainRX.spAlterIndex.ExecProc;
TD := BeginTransaction(TDBXIsolations.ReadCommitted);
ZeroCounterRecords;
DeleteRecords_ZTEMP;
DeleteRecords_Counter_x;
InsertRecords;
CommitFreeAndNil(TD);
TruncateTable;

This is the stored proc

PROCEDURE ALTER_ACCUM_INDEX
IS
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('CCOK','ZTEMP_ESI_ACCUM',METHOD_OPT=>'for all indexed columns',CASCADE=>TRUE);
EXECUTE IMMEDIATE 'ALTER INDEX CCOK.INX1_ZTEMP_ESI_ACCUMM_1 COMPUTE STATISTICS';
EXECUTE IMMEDIATE 'ALTER INDEX CCOK.INX1_ZTEMP_ESI_ACCUM_2 COMPUTE STATISTICS';
END;