Re: Behaviour within DBA_TAB_MODIFICATIONS

Brian,

After sending the post I did some more research to understand how redo an undo work. You are right changes are made to the data blocks and written out to redo log file this includes changes to undo blocks. This happens regardless of whether I choose to COMMIT or ROLLBACK.

Thanks.

Roger.

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3F27D64F.5AD8F2E5_at_remove_spam.peasland.com>...
> 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"
Received on Wed Jul 30 2003 - 18:33:39 CDT