oracle - Hibernate Index Query Slow -

my question similar 1 posed in thread: how avoid heavy query slows down application?

we checked missing indexes on foreign keys , found some. adding missing indexes had opposite effect in slowed query more. 1 important piece of information our customer has single oracle install our schema replicated on 21 times. each schema has shy of 1,000 tables in it. asking of oracle such large number of tables (and of course indexes)? don't know hardware question whether reasonable approach or be better break users different sids?

below query being executed hibernate. customer telling query consuming 45% of processor when being executed (though don't know how long).

any suggestions appreciated, steve

select null table_cat,        owner table_schem,        table_name,        0 non_unique,        null index_qualifier,        null index_name,        0 type,        0 ordinal_position,        null column_name,        null asc_or_desc,        num_rows cardinality,        blocks pages,        null filter_condition   all_tables  table_name = 'booking'        , owner = 'forward_tn' union select null table_cat,        i.owner table_schem,        i.table_name,        decode (i.uniqueness, 'unique', 0, 1),        null index_qualifier,        i.index_name,        1 type,        c.column_position ordinal_position,        c.column_name,        null asc_or_desc,        i.distinct_keys cardinality,        i.leaf_blocks pages,        null filter_condition   all_indexes i,        all_ind_columns c      i.table_name = 'booking'        , i.owner = 'forward_tn'        , i.index_name = c.index_name        , i.table_owner = c.table_owner        , i.table_name = c.table_name        , i.owner = c.index_owner order non_unique,          type,          index_name,          ordinal_position 

you're not hitting kind of capacity issue 1,000 tables. that's still relatively small in oracle world. doing quick check of our e-business suite install , has 23,000 tables. query using ton of cpu execution plan problem. things at

have collected optimizer statistics? without them optimizer may making poor decision on how execute query.

the next step execution plan itself. if have enterprise manager running, has query right on front page consuming resources. can click on , see it's doing. without have use sql_trace or explain plan see what's happening.