Skip to Content
avatar image
Former Member

New statistics threshold and dba_tab_modifications

Hi experts ,

want to know WHEN the tab modifications triggering a statistics run.

BRCONNECT manual:

"If the table has the MONITORING attribute set, BRCONNECT reads the number

of inserted, deleted, and updated rows from the DBA_TAB_MODIFICATIONS

table (this is available from Oracle 8.1 onwards)."

BRCONNECT uses the number of new rows for each table in the working set, as

derived in the previous step, to see if either of the following is true:

(1) Number of new rows is greater than or equal to number of old rows * (100

+threshold) / 100

(2) Number of new rows is less than or equal to number of old rows * 100 / (100 +

+threshold)

Is the system adding up the modifications in dba_tab_modifications like that :

"new" rows = old_rows + ( inserted - deleted )

and compared against the old rows ?

i.e.

stats_change_threshold = 20.

90000 = old recs

10000 = inserted

500 = deleted

99500 = new

let's take the formulars above (1) and (2):

(1) 99500 >= 90000 * (100 + 20) / 100 = 108000 -


> no stats calculated

(2) 99500 <= 90000 * (100 + 20) / 100 = 75000 -


> no stats calculated

90000 = old recs

20000 = inserted

500 = deleted

109500 = new

(1) 109500 >= 90000 * (100 + 20) / 100 = 108000 -


> stats calculated

What do you think ?

bye

yk

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 01, 2009 at 02:02 PM

    Hello,

    please look at sapnote #408527. There is formula.

    Regards

    Stefan

    Add comment
    10|10000 characters needed characters exceeded