Behaviour within DBA_TAB

From: Roger Jackson <rjackson1_at_hotkey.net.au>
Date: 30 Jul 2003 05:01:06 -0700
Message-ID: <94614c11.0307300401.6dd2b3a5@posting.google.com>

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 Received on Wed Jul 30 2003 - 07:01:06 CDT

Behaviour within DBA_TAB_MODIFICATIONS