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

Answers


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.


Need Your Help

What is an elegant way to track the size of a set of objects without a single authoritative collection to reference?

algorithm computer-science design-principles

Update: Please read this question in the context of design principles, elegance, expression of intent, and especially the "signals" sent to other programmers by design choices.

What's the word that encompasses Desktop & Laptop computer?

javascript inheritance website naming-conventions

I'm creating many child classes from one parent class in javascript. Each child will represent a set of devices that view my website in different ways. So far I have these:

Coverity, Enumerable.Where(this …), and IDisposable

c# .net linq ienumerable idisposable

So code analysis is telling me that Enumarble.Where(this ...) is returning an instance of WhereListIterator<T>, which is (looks to be) an internal type within the .NET framework that implements

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.