Friday, August 15, 2008

Index and Table Monitoring

create table test (id integer primary key, text varchar2(10));
create index text_idx on test(text);

ALTER INDEX TEXT_IDX MONITORING USAGE;
ALTER INDEX SYS_C005203 MONITORING USAGE;

select index_name, used from v$object_usage;

SQL> select index_name, used from v$object_usage;

INDEX_NAME USE
------------------------------ ---
TEXT_IDX NO
SYS_C005203 NO

insert into test values (1,'test1');

SQL> select index_name, used from v$object_usage;

INDEX_NAME USE
------------------------------ ---
TEXT_IDX NO
SYS_C005203 NO

SQL> select * from test where id = 1;

ID TEXT
---------- ----------
1 test1

SQL> select index_name, used from v$object_usage;

INDEX_NAME USE
------------------------------ ---
TEXT_IDX NO
SYS_C005203 YES

# Table monitoring

alter table PROGRAM_AUDIT_BAK monitoring;

select table_name, inserts, updates, deletes from user_tab_modifications where table_name = 'PROGRAM_AUDIT_BAK';

exec dbms_stats.flush_database_monitoring_info;

or

statistics_level=typical