Hibernate Index Query Slow

My question is similar to the one posed in this thread: How to avoid this very heavy query that slows down the application?

We checked for missing indexes on foreig开发者_开发问答n keys and found some. Adding the missing indexes actually had the opposite effect in that it slowed the query even more. One important piece of information is that our customer has a single Oracle install with our schema replicated on it 21 times. Each schema has just shy of 1,000 tables in it. Are we asking too much of Oracle with such a large number of tables (and of course indexes)? I don't know what their hardware is but my question is whether this is a reasonable approach or would it be be better to break up the users to different SIDs?

Below is the query that is being executed by Hibernate. The customer is telling us that this query is consuming about 45% of the processor when it is being executed (though I don't know for how long).

Any suggestions are appreciated, Steve

SELECT NULL AS table_cat,
       owner AS table_schem,
       table_name,
       0 AS non_unique,
       NULL AS index_qualifier,
       NULL AS index_name,
       0 AS TYPE,
       0 AS ordinal_position,
       NULL AS column_name,
       NULL AS asc_or_desc,
       num_rows AS CARDINALITY,
       blocks AS pages,
       NULL AS filter_condition
  FROM all_tables
 WHERE table_name = 'BOOKING'
       AND owner = 'FORWARD_TN'
UNION
SELECT NULL AS table_cat,
       i.owner AS table_schem,
       i.table_name,
       DECODE (i.uniqueness, 'UNIQUE', 0, 1),
       NULL AS index_qualifier,
       i.index_name,
       1 AS TYPE,
       c.column_position AS ordinal_position,
       c.column_name,
       NULL AS asc_or_desc,
       i.distinct_keys AS CARDINALITY,
       i.leaf_blocks AS pages,
       NULL AS filter_condition
  FROM all_indexes i,
       all_ind_columns c
 WHERE     i.table_name = 'BOOKING'
       AND i.owner = 'FORWARD_TN'
       AND i.index_name = c.index_name
       AND i.table_owner = c.table_owner
       AND i.table_name = c.table_name
       AND i.owner = c.index_owner
ORDER BY non_unique,
         TYPE,
         index_name,
         ordinal_position

You're not hitting any kind of capacity issue with 1,000 tables. That's still relatively small in the Oracle world. Just doing a quick check of our E-Business Suite install and it has 23,000 tables. A query using up a ton of CPU is almost always an execution plan problem. Some things to look at

Have you collected optimizer statistics? Without them the optimizer may be making a really poor decision on how to execute the query.

The next step is to look the execution plan itself. If you have the enterprise manager running, it probably has that query right up on the front page for consuming resources. You can just click on it and see what it's doing. Without that you have to use sql_trace or explain plan to see what's happening.

You could be easily hit by excessive parse time on the Oracle server if all of your statements use slightly different, literal SQL (i.e. no bind variables) for each of the 22.000 tables. If this is the case, just switch to bind variables and the CPU consumption should lower substantially.

Can your customer tell in what Oracle function the CPU time is spent? Oracle offers the neccessary statistics. As the CPU consumption is reported to be a significant part of the whole instance's consumption, your customer could run a statspack report (or ASH if he has licensed the diagnostic pack). That should show where exactly the CPU time is being spent.

Our customer reviewed their Oracle configuration and changed the configuration to the following values: optimizer_index_caching=90 optimizer_index_cost_adj=15 optimizer_mode='CHOOSE'

They report that this seems to have fixed their speed issue.