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 foreign 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.