New statistics threshold and dba_tab_modifications

[Former Member]

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

[stefan_koehler · Accepted solution]

Hello,

please look at sapnote #408527. There is formula.

Regards

Stefan