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,
       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'
SELECT NULL AS table_cat,
       i.owner AS table_schem,
       DECODE (i.uniqueness, 'UNIQUE', 0, 1),
       NULL AS index_qualifier,
       1 AS TYPE,
       c.column_position AS ordinal_position,
       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,


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

System.Windows.Forms.Label.Text contents displayed incorrectly

c# .net windows winforms 32bit-64bit

I have a winform with a label. The label has property Text set to abc(123):

Qt and Webkit not showing all CSS 3, HTML 5 effects

c++ css3 qt webkit

I am a beginner. I am creating a web browser with Qt and webkit (simply added a webview). I am using the latest version of Ubuntu.

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.