Tom,
when I test it using one table in our application, I couldn' get the same result as you--the v$object_usage dictionary view doesn't show any information for that index which is enabled monitoring usage.
Please see bolow:
SQL> select index_name,table_name,column_name from all_ind_columns where table_name='INDIVIDUALS';
INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
XPKINDIVIDUALS INDIVIDUALS PTY_ID
XIF127INDIVIDUALS INDIVIDUALS LANG_ID
2 rows selected.
SQL> select * from v$object_usage where table_name='INDIVIDUALS';
no rows selected
SQL> alter index core.XIF127INDIVIDUALS monitoring usage;
Index altered.
SQL> analyze table core.individuals compute statistics;
Table analyzed.
SQL> alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';
Session altered.
SQL> select table_name,last_analyzed from all_tables where table_name='INDIVIDUALS';
TABLE_NAME LAST_ANALYZED
------------------------------ --------------------
INDIVIDUALS 18-MAY-2004 13:24:15
1 row selected.
SQL> set autotrace on explain
SQL> select count(*) from core.individuals where lang_id=451;
COUNT(*)
----------
1032694
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=112 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'XIF127INDIVIDUALS' (NON-UNIQU
E) (Cost=112 Card=158706 Bytes=476118)
SQL> set autotrace off
SQL> select * from v$object_usage where table_name='INDIVIDUALS';
no rows selected
Why I can't see it from dictionary view of v$object_usage? What could be wrong?
While I try to submit my question above, I see you have some
comment for how to submit question. So I try to use
create table/insert table to show the problem. But when I create a another small table based on that table's struction, I get the result as you--the v$object_usage dictionary view shows the information for that index which is enabled monitoring usage and table is using CBO.
:
CREATE TABLE TEST_IND
(
PTY_ID NUMBER NOT NULL,
LANG_ID NUMBER,
SALUTATION_TXT VARCHAR2(65),
TITLE_TXT VARCHAR2(35),
FIRST_NM VARCHAR2(35),
MIDDLE_NM VARCHAR2(35),
LAST_NM VARCHAR2(35),
NAME_SUFFIX_TXT VARCHAR2(20),
NAME_UNIQUENESS_NUM NUMBER(2),
EMPLOYER_NM VARCHAR2(65),
POSITION_AT_EMPLOYER_NM VARCHAR2(65),
BIRTH_DT DATE,
DEATH_DT DATE,
GENDER_CODE VARCHAR2(5),
MAIDEN_NM VARCHAR2(80),
LAST_UPDATE_DT DATE NOT NULL,
LAST_UPDATE_USER_ID NUMBER NOT NULL,
DELETED_FLG VARCHAR2(1) NOT NULL
);
INSERT INTO TEST_IND ( PTY_ID, LANG_ID, SALUTATION_TXT, TITLE_TXT, FIRST_NM, MIDDLE_NM, LAST_NM,
NAME_SUFFIX_TXT, NAME_UNIQUENESS_NUM, EMPLOYER_NM, POSITION_AT_EMPLOYER_NM, BIRTH_DT, DEATH_DT,
GENDER_CODE, MAIDEN_NM, LAST_UPDATE_DT, LAST_UPDATE_USER_ID,
DELETED_FLG ) VALUES (
72678835, 451, 'Dear Sir or Madam', NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL
, NULL, NULL, NULL, TO_Date( '10/29/2001 03:13:17 PM', 'MM/DD/YYYY HH:MI:SS AM')
, -1, 'N');
INSERT INTO TEST_IND ( PTY_ID, LANG_ID, SALUTATION_TXT, TITLE_TXT, FIRST_NM, MIDDLE_NM, LAST_NM,
NAME_SUFFIX_TXT, NAME_UNIQUENESS_NUM, EMPLOYER_NM, POSITION_AT_EMPLOYER_NM, BIRTH_DT, DEATH_DT,
GENDER_CODE, MAIDEN_NM, LAST_UPDATE_DT, LAST_UPDATE_USER_ID,
DELETED_FLG ) VALUES (
72678842, 451, 'Dear Miss Chuang', 'Miss', 'Christine', NULL, 'Chuang', NULL, 1, NULL
, NULL, NULL, NULL, NULL, NULL, TO_Date( '10/30/2001 12:29:31 PM', 'MM/DD/YYYY HH:MI:SS AM')
, -1, 'N');
commit;
SQL> Alter table test_ind add constraint pk_test_ind primary key(pty_id);
Table altered.
SQL> CREATE INDEX XIFtest_ind ON TEST_IND
(LANG_ID);
Index created.
SQL> select * from v$object_usage where table_name='TEST_IND';
no rows selected
SQL> alter index XIFtest_ind monitoring usage;
Index altered.
SQL> select * from v$object_usage where table_name='TEST_IND';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
XIFTEST_IND TEST_IND YES NO 05/18/2004 13:59:18
1 row selected.
SQL> set autotrace on explain
SQL>
SQL> select count(*) from TEST_IND where lang_id=451;
COUNT(*)
----------
2
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'XIFTEST_IND' (NON-UNIQUE)
SQL> set autotrace off
SQL> select * from v$object_usage where table_name='TEST_IND';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
XIFTEST_IND TEST_IND YES YES 05/18/2004 13:59:18
I have been confused about these 2 cases. Please help.
Thanks very much.
Pauline