Re: Behaviour within DBA_TAB_MODIFICATIONS

You did change the rows. And then you reversed your changes. This does not mean that the changes never took place. In Oracle, rollback does not completely wipe away the occurrence of that transaction. In fact, the transaction is recorded in the redo logs. And the end of that transaction is the COMMIT or ROLLBACK. So you changed rows and monitoring the table knew that. It would be a lot of work for monitoring to go back and "un-monitor" changes that were never committed, especially in an environment with a high degree of concurrency.

HTH,
Brian

Roger Jackson wrote:
>
> Hi,
>
> I started investigating the option of using DBMS_STATS in our Oracle
> 9i environments (Oracle 9.2.0.3 OS AIX 5.1). One of the tests I
> performed is shown below.
>
> SQL>
> SQL> drop table t1;
>
> Table dropped.
>
> SQL>
> SQL> create table t1
> 2 (a varchar(1),
> 3 b varchar(1),
> 4 c varchar(1)
> 5 );
>
> Table created.
>
> SQL>
> SQL> alter table t1 monitoring;
>
> Table altered.
>
> SQL>
> SQL> declare
> 2 begin
> 3 for i in 1..20000
> 4 loop
> 5 insert into t1 values ('a','b','c');
> 6 end loop;
> 7 commit;
> 8 end;
> 9 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select table_owner, table_name, inserts, updates, deletes,
> timestamp
> 2 from sys.dba_tab_modifications
> 3 where table_name='T1';
>
> TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
> TIMESTAMP
> --------------- --------------- ---------- ---------- ---------
> ---------
> ORACLE T1 20000 0 0
> 30-JUL-03
>
> SQL>
> SQL> exec dbms_stats.GATHER_SCHEMA_STATS(OWNNAME =>'ORACLE', CASCADE
> => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, GRANULARITY
> =>'ALL', OPTIONS => 'GATHER EMPTY');
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select table_owner, table_name, inserts, updates, deletes,
> timestamp
> 2 from sys.dba_tab_modifications
> 3 where table_name='T1';
>
> no rows selected
>
> SQL>
> SQL> delete from t1 where rownum < 10001;
>
> 10000 rows deleted.
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL>
> SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select table_owner, table_name, inserts, updates, deletes,
> timestamp
> 2 from sys.dba_tab_modifications
> 3 where table_name='T1';
>
> TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
> TIMESTAMP
> --------------- --------------- ---------- ---------- ---------
> ---------
> ORACLE T1 0 0 10000
> 30-JUL-03
>
> SQL>
> SQL> select count(*) from t1;
>
> COUNT(*)
> ----------
> 20000
>
> Even though I rollback the transaction, DBA_TAB_MODIFICATIONS is still
> updated to indicate that a modification was made to the t1 table.
>
> As result of the modification we need to update the statistics because
> it thinks the total number of rows have changed by more than 10%.
>
> I would have thought that the rows were not changed since I performed
> a rollback but Oracle thinks differently...
>
> Does anybody know why?
>
> I'm interested to hear what you think.
>
> Roger

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"