Search by "Title" in the instances tab and also "Tree Finder" in an individual instance page got really slow - 10 - 24 secs, in 10.1.3.4 instance.
The following custom indexes were added to bring them below 0.06 secs. in our instance.
See the highlighted indexes added recently.
SQL> select index_name, index_type, table_name, uniqueness from all_indexes where table_name = 'CUBE_INSTANCE';
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
----------------- --------------------- -------------- ----------
STATE_IND NORMAL CUBE_INSTANCE NONUNIQUE
CI_CUSTOM4 FUNCTION-BASED NORMAL CUBE_INSTANCE NONUNIQUE
CI_PARENT_ID_IDX NORMAL CUBE_INSTANCE NONUNIQUE
CI_TITLE_IDX NORMAL CUBE_INSTANCE NONUNIQUE
CI_ROOT_ID_IDX NORMAL CUBE_INSTANCE NONUNIQUE
CI_CONV_ID_IDX NORMAL CUBE_INSTANCE NONUNIQUE
CI_PK NORMAL/REV CUBE_INSTANCE UNIQUE
CI_CUSTOM2 NORMAL CUBE_INSTANCE NONUNIQUE
CI_CUSTOM3 NORMAL CUBE_INSTANCE NONUNIQUE
No comments:
Post a Comment