3 Replies Latest reply on Dec 30, 2010 9:07 PM by Nicolas.Gasparotto

    How do I force a gather stats command?

    costumer
      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;