up_UpdateTableStats called multiple times during an insert activity | Community
Skip to main content
J
jims52473243
Level 2
June 20, 2022
Solved

up_UpdateTableStats called multiple times during an insert activity

  • June 20, 2022
  • 1 reply
  • 1121 views

Hello,

 

We're writing 4,000,000 at a time to a custom broadlog table (on prem instance, Oracle back end, 19c, AC build 9349). This table is partitioned on tscreated and is quite large (163,740,279 records, 256 GB). Stats are gathered each morning by a cron job on the database server. Old partitions are dropped as the data ages out.

 

As I watch the job loading the 4mm records via TOAD Session browser, the behavior is this: a few hundred thousand records are written, and then up_UpdateTableStats is called. Then a few more hundred thousand are written, and up_UpdateTableStats is again called. This process really slows down the load. 

 

So, the question is: how can we turn off the automated up_UpdateTableStats call during insert/update?

 

Thanks,

Jim

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by jims52473243

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

1 reply

CedricRey
CedricRey
Level 5
June 22, 2022

Hi @jims52473243 

I'm not sure this is linked (because you talk about hundred thousand, and default is ten thousand), but can you try to change the "Bacth size" parameter in the writer activity ?

As I sayed default value is 10k, but you can give a try to see if it's have an effect on the "up_UpdateTableStats" calls frequency ?

Cedric

J
jims52473243AuthorAccepted solution
Level 2
June 29, 2022

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

    Terms & ConditionsAccessibility statement

    Loading footer...