| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Table Monitoring in 10G versus 8i
I have been reading "Practical Oracle8i" by Jonathan Lewis
(Addison-Wesley:2001). I came across "Up-to-Date Statistics (Nearly)" on
pp.73-74 and the MONITORING option for tables.
I tried out this example under 10.1.0.0 on WinXP Pro. I wanted to see if the TRUNCATE and ROLLBACK 'bugs' were still there and if the
update period had changed from every three (3) hours or so.
Since this is a rather long post. I will give the summary here:
(1) TRUNCATE does not reset the statistics;
(2) Statistics are updated between five (5) and ten (10) minutes after last
DML;
(3) Insertion counts are not adjusted for ROLLBACKs.
In conclusion, the only thing to have changed between 8i and 10G has been the statistics update interval.
SQL> alter session set nls_date_format='DD/MM/YY HH24:MI:SS';
Session altered.
SQL> SELECT inserts, updates, deletes, timestamp FROM user_tab_modifications WHERE table_name='T1';
INSERTS UPDATES DELETES TIMESTAMP
---------- ---------- ---------- -----------------
38917 0 0 29/03/04 00:05:56
SQL> truncate table t1;
Table truncated.
SQL> exec tkyte.print_table('SELECT * FROM USER_TAB_MODIFICATIONS WHERE table_name=''T1''')
TABLE_NAME : T1 PARTITION_NAME : SUBPARTITION_NAME : INSERTS : 38917 UPDATES : 0 DELETES : 0 TIMESTAMP : 29/03/04 09:42:05 TRUNCATED : YES DROP_SEGMENTS : 0 -----------------
The INSERTS column have not been reset by the TRUNCATE command.
SQL> exec tkyte.print_table('SELECT * FROM USER_OBJECTS WHERE object_name=''T1''')
OBJECT_NAME : T1 SUBOBJECT_NAME : OBJECT_ID : 52242 DATA_OBJECT_ID : 52243 OBJECT_TYPE : TABLE CREATED : 28/03/04 23:59:52 LAST_DDL_TIME : 29/03/04 09:42:05 TIMESTAMP : 2004-03-28:23:59:52 STATUS : VALID TEMPORARY : N GENERATED : N SECONDARY : N -----------------
The statistics were updated at 6 minutes past midnight (which is on a three
(3) hour boundary).
I then inserted 38,921 rows and committed. I then repeated the insertion but rolled back.
SQL> exec tkyte.print_table('SELECT * FROM USER_TAB_MODIFICATIONS WHERE table_name=''T1''')
TABLE_NAME : T1 PARTITION_NAME : SUBPARTITION_NAME : INSERTS : 116759 UPDATES : 0 DELETES : 0 TIMESTAMP : 29/03/04 10:55:47 TRUNCATED : YES DROP_SEGMENTS : 0 -----------------
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
38921
The number of insertions is calculated as 38,917 (Original) + 38,921
(committed) + 38,921 (rolled back) = 116,759.
It looks like the three (3) hour update rule has changed.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
SQL> INSERT INTO T1 SELECT rownum, object_name FROM all_objects;
38921 rows created.
SQL> commit;
Commit complete.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
SQL> exec tkyte.print_table('SELECT * FROM USER_TAB_MODIFICATIONS WHERE table_name=''T1''')
TABLE_NAME : T1 PARTITION_NAME : SUBPARTITION_NAME : INSERTS : 155680 UPDATES : 0 DELETES : 0 TIMESTAMP : 29/03/04 11:41:52 TRUNCATED : YES DROP_SEGMENTS : 0 -----------------
My best guess is that the statistics are now updated between five (5) and ten (10) minutes after the last DML. It is a pity that they are not more accurate.
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
77842
Douglas Hawthorne Received on Sun Mar 28 2004 - 19:54:13 CST
 |
 |