DBA_TAB_MODIFICATIONS not updated

[ACE-SAP]

Hi experts,

I would like to share with you a strange behavior I've noticed on a test system.

OS                   Win 2008R2 SP1

Oracle             11.2.0.3 PATCH 15

SAP                 ECC 6 Ehp (NW 731)

Brtools             7.20 (31)

I've noticed some wrong execution plans after client copy.

I did run an other copy this weekend and checked closely what was going on and it's not matching at all what I've read/learned.

Even if table monitoring is active the DBA_TAB_MODIFICATIONS has not been updated after the client copy (copy of production client on test system).

show parameter statistics

NAME                                 TYPE        VALUE

optimizer_use_pending_statistics     boolean     FALSE

statistics_level                     string      TYPICAL

timed_os_statistics                  integer     0

timed_statistics                     boolean     TRUE

select monitoring, count(1) from dba_tables where owner = 'SAPSR3' group by monitoring;

MON   COUNT(1)

YES   85195

Before copy:

select sum(INSERTS), sum(UPDATES),  sum(DELETES) from sys.dba_tab_modifications where TABLE_OWNER ='SAPSR3';

COUNT(1) SUM(INSERTS) SUM(UPDATES) SUM(DELETES)

   18030     71041559      5727108     63098779

After copy:

select sum(INSERTS), sum(UPDATES),  sum(DELETES) from sys.dba_tab_modifications where TABLE_OWNER ='SAPSR3';

COUNT(1) SUM(INSERTS) SUM(UPDATES) SUM(DELETES)

   18030     71041559      5727108     63098779

I did check last time stat has been update on one of our largest table:

select 'dba_tables', to_char(LAST_ANALYZED, 'Dy DD-Mon-YYYY HH24:MI:SS') from dba_tables tbls where table_name ='FAGLFLEXA'

union

select 'dba_tab_statistics', to_char(LAST_ANALYZED, 'Dy DD-Mon-YYYY HH24:MI:SS') from dba_tab_statistics where table_name ='FAGLFLEXA';

'DBA_TABLES' TO_CHAR(LAST_ANALYZED,'DYDD-MON-YYYY

dba_tab_statistics Mon 02-Sep-2013 01:37:07

dba_tables         Mon 02-Sep-2013 01:37:07

I checked the table number of rows

select num_rows from dba_tables where table_name ='FAGLFLEXA';

  NUM_ROWS

109225667

select count(1) from sapsr3.FAGLFLEXA;

  COUNT(1)

134074127

According to the stat internal rules B*Tools should update stats...

106047 - DB21: Customizing the DBSTATC

http://help.sap.com/saphelp_nw73ehp1/helpdata/en/46/CE3317A5171827E10000000A1553F6/content.htm 

#old rows + # inserted rows >= # old rows * (100 + threshold) / 100

109225667+134074127 >= 109225667 * (100+50)/100

243299794 >= 163838501

But it is not ...

brconnect -u / -c -f stats -t FAGLFLEXA

BR0280I BRCONNECT time stamp: 2013-09-15 21.52.30

BR0879I Statistics checked for 1 table

BR0878I Number of tables selected to collect statistics after check: 0

BR0880I Statistics collected for 0/0 tables/indexes

The fact is that after the client copy some reports doing heavy DB access are performing very poorly and some unoptimized access path are selected.

There is always a possibility to force statistics updates for all tables...

brconnect -u / -c -f stats -t all -m +I -s P10 -f allsel,collect,method -p 12

... but  I would like to understand what's wrong with my understanding and/or my analysis...

Thanks for your help !

[Former Member · Accepted solution]

Hi,

For the below calculation you should used DB20.

#old rows + # inserted rows >= # old rows * (100 + threshold) / 100

109225667+134074127 >= 109225667 * (100+50)/100

243299794 >= 163838501

<old_number_records> + <number_records_added>

+ <number_records_changed> >= <old_number_records> * (100 + 50) / 100

or

<old_number_records> - <number_records_deleted>

- <number_records_changed> <= <old_number_records> * 100 / (100 + 50)

What sap note says that the entry will not change on the shot you need to give some time for them to chnage.

Please check this,

1865098  - Deletes and inserts are not updated in transaction code DB20.

Thanks

Rishi Abrol

[ACE-SAP · Accepted solution]

Hi Rishi,

Thanks you very much for the information, I did not knew that note... but did already get that information from elsewhere.

1865098 - Deletes and inserts are not updated in transaction code DB20.

But DBA_TAB_MODIFICATIONS is not updated at once after DML statements were executed.
In oracle 11g, it will be refreshed once per 3 hours. So if you check it later, you will see correct result.

If the information is necessary at once, run FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate this view with the latest information.

https://mwidlake.wordpress.com/2009/05/26/counting-the-cost-4-the-speedy-way/

- The data is flushed from memory to the data dictionary “regularly”. Every 3 hours in Oracle 9, every 15 minutes in Oracle 10.1 and it seems only when a dbms_stats.gather statement is run on 10.2.

The bad thing is that Br*Tool are starting statistics analysis by firing an DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

(I did activate sql trace for Br*Tools when runnig stat update with option -TRC 2 ). Thus it should have update DBA_TAB_MODIFICATIONS  and compute statistics. The copy scenario was a client creation thus only few lines where deleted. It runs for 6 hours and by the end nothing was updated in DBA_TAB_MODIFICATIONS.

The worst thing is that I did not checked DBA_TAB_MODIFICATIONS after running Br*Tools to see if it has been updated.

I'll run a new copy in few days, I'll check if DBA_TAB_MODIFICATIONS is updated if issuing a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

Regards

[stefan_koehler · Accepted solution]

Hi Yves,

> Even if table monitoring is active the DBA_TAB_MODIFICATIONS has not been updated after the client copy (copy of production client on test system).

Prior to Oracle 10g there was a monitoring attribute for the tables. Starting with Oracle 10g the MONITORING and NOMONITORING keywords are deprecated and the table monitoring is controlled by STATISTICS_LEVEL. However you have set it to TYPICAL and so it should work.

> I'll run a new copy in few days, I'll check if DBA_TAB_MODIFICATIONS is updated if issuing a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

.. and if not - check the join factorization for the UNION ALL in DBA_TAB_MODIFICATIONS. There is a known bug if  "_optimizer_join_factorization" is set to TRUE. (disregarding stuff like custom table partitioning, etc. at all - http://scn.sap.com/thread/3413348)

Regards

Stefan

[ACE-SAP · Accepted solution]

Hi Stefan

Thank you very much for the information. I was not aware that monitoring attribute was deprecated.

_optimizer_join_factorization is set to TRUE on the system where I did get the error.

I'll check the modifications during next client copy running directly the query used by DBA_TAB_MODIFICATIONS view without using the union operator.

Best regards

Yves

[ACE-SAP · Accepted solution]

You were true Stefan

I's fully the bug you quoted, and it's a scary one !

SQL> select count(1) from DBA_TAB_MODIFICATIONS;

  COUNT(1)

      414

SQL> select * from  DBA_TAB_MODIFICATIONS;

no rows selected

I've tested changing the predicate order (underlined in yellow) of the code used by the DBA_TAB_MODIFICATIONS view as said in the here under MOC Kb and it works...

-- the regular view code

SQL> select u.name, o.name, null, null,

  2 m.inserts, m.updates, m.deletes, m.timestamp,

  3 decode(bitand(m.flags,1),1,'YES','NO'),

  4 m.drop_segments

  5  from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u

  6 where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#

  7 union all

  8 select u.name, o.name, o.subname, null,

  9 m.inserts, m.updates, m.deletes, m.timestamp,

10 decode(bitand(m.flags,1),1,'YES','NO'),

11 m.drop_segments

12  from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u

13 where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19

14 union all

15 select u.name, o.name, o2.subname, o.subname,

16 m.inserts, m.updates, m.deletes, m.timestamp,

17 decode(bitand(m.flags,1),1,'YES','NO'),

18 m.drop_segments

19  from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,

20 sys.user$ u

21 where o.obj# = m.obj# and o.owner# = u.user# and o.obj# = tsp.obj# and o2.obj# = tsp.pobj#;

no rows selected

-- the view code with predicate order changed

SQL> select u.name, o.name, null, null,

  2 m.inserts, m.updates, m.deletes, m.timestamp,

  3 decode(bitand(m.flags,1),1,'YES','NO'),

  4 m.drop_segments

  5  from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u

  6 where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#

  7  union all

  8 select u.name, o.name, o.subname, null,

  9 m.inserts, m.updates, m.deletes, m.timestamp,

10 decode(bitand(m.flags,1),1,'YES','NO'),

11 m.drop_segments

12  from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u

13 where o.obj# = m.obj# and o.owner# = u.user# and o.type#=19

14  union all

15 select u.name, o.name, o2.subname, o.subname,

16 m.inserts, m.updates, m.deletes, m.timestamp,

17 decode(bitand(m.flags,1),1,'YES','NO'),

18 m.drop_segments

19  from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,

20 sys.user$ u

21 where o.obj# = m.obj# and o.obj# = tsp.obj# and o2.obj# = tsp.pobj# and o.owner# = u.user#;

414 rows selected.

So I will set the parameter "_optimizer_join_factorization"=false to fix this issue.

Thanks a lot for your help !

Best regards

13984324  wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected	11.2.0.3 11.2.0.2
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.4 (Future Patch Set)

Wrong result are possible with :

1) at least 3 branches in UNION ALL

2) at least 2 join factorization predicates

3) Predicate order in branches is different

4) Correct result with "_optimizer_join_factorization"=false;