How to exclude tables to be run in the UpdateStats job ?

[Former Member]

In our system, there are about 2000 PSA tables (/BIC/B) + 1000 temporary tables (/BIC/02 & /BIC/06*)

that does not need to gather statistic. Exclude those tables from UpdateStats job, I can save at least 5hours of execution time.

I don't want to use <stats_exclude> parameter because of maintenance on that list.

Whenever there is 1 table non-exist in list, the UpdateStats job failed.

Also it does not accept the wild char *. Table name should be entered one by one.

I'm thinking of set ACTIVE flag in the DBSTATC table to I. But again I have to enter table per table.

We user Brtools 7.10 patch 19

BW version 6.5

Thanks for help.

[Former Member · Accepted solution]

Hello,

the stats_exclude parameter should also work for these tables. Can you post the exact value to which you have set this parameter?

Best Regards,

Michael

[Former Member · Accepted solution]

Hi Michael,

You are right. There were a typo in my command line (special hidden character), that's why it did not work.

With that line in init<INST>.sap

stats_exclude = ( SAPR3./BIC/B, SAPR3./BIC/B02, SAPR3./BIC/B06* )

those tables are not be analyzed in UpdateStats.

Thanks for your help.

[stefan_koehler]

Hello Hai-Thuy,

you can run write a little SQL script, that select all table names beginning with /BIC/B, /BIC/02 or /BIC/06* and lock the statistics for these tables.

DBMS_STATS.LOCK_TABLE_STATS:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1043993

With this "workaround" the BR*Tools still try to collect statistics but will skip those tables with locked statistics.

Regards

Stefan

[Former Member]

Thanks for your input.

I will try your suggestion.