Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Behaviour within DBA_TAB_MODIFICATIONS
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)
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
--------------- --------------- ---------- ---------- ---------
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
--------------- --------------- ---------- ---------- ---------
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 Received on Wed Jul 30 2003 - 07:01:06 CDT
![]() |
![]() |