Niall's Oracle Pages

A little while back I posted a brief demo of the fact that whilst segment monitoring in 9i and 10g gives precise statistics, the statistics may not be accurate. I have been asked to recap this, so here is the question and the demo.

> Niall, monitoring will show table as used, but statistics is updated
> at the time of commit. First, there is no statistics in USER_TABLES that
> would count the number of times that blocks have been referenced by I/O
> and second, statistics for monitored tables is not updated until the
> transaction commits. To do otherwise would seriously break the relational
> rules.

Clearly I wasn't advocating using table monitoring but I would refer
you to the following piece of rule breaking

SQL> connect / as sysdba
Connected.
SQL> drop user u1;

User dropped.

SQL> create user u1 identified by u1
 2  default tablespace users
 3  temporary tablespace temp
 4  quota unlimited on users;

User created.

SQL> grant create session,create table to u1;

Grant succeeded.

SQL> connect u1/u1
Connected.
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;

TO_CHAR(
--------
20:39:18

SQL> create table t1 (col1 number);

Table created.

SQL> insert into t1
 2  select rn
 3  from (select rownum rn,'x' from all_objects,all_objects where rownum < 1001
);

1000 rows created.

SQL> rollback;

Rollback complete.

SQL> host time 23:55

SQL> select to_char(sysdate,'hh24:mi:ss') from dual;

TO_CHAR(
--------
23:55:06

SQL> select table_name,inserts,updates,deletes from useR_tab_modifications;

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
T1                                   1000          0          0

SQL>

precise but wrong and certainly a violation of relational rules. This
is why Oracle refer to it as an estimate.