Avatar

Correct answer by
Level 2

Hi @CedricRey ,

 

We have a solution. We did two things, so I'm not sure which (or both) was the actual fix. 

1. Set the Batch Size to some value greater than the number of records to-be inserted.

2. Modify the up_UpdateTableStats procedure to ignore the pain-point table:

 

CREATE OR REPLACE PROCEDURE DADOBESYS.up_UpdateTableStats(sSchema varchar2, sTable varchar2, iPercent INTEGER) IS

BEGIN

  IF UPPER(sTable) IN ('TMPBROADCAST', 'TMPBROADCASTPAPER') THEN

    DBMS_STATS.SET_TABLE_STATS(USER, 'TMPBROADCAST', numrows=> 2000000, numblks=>40000);

ELSIF UPPER(sTable) IN ('HIDDEN') THEN
dbms_output.put_line('Skipping stats for HIDDEN table');

  ELSE

    DBMS_STATS.GATHER_TABLE_STATS(ownname => sSchema,

                                  tabname => sTable,

                                  estimate_percent => iPercent,

                                  cascade => true,

         degree =>24);

  END IF;

END;

/

 

Thanks for your help!

Jim

View solution in original post