My question is similar to the one asked in this thread:
How to avoid this very heavy request slowing down the application?
We checked the missing indexes on foreign keys and found some. Adding missing indexes actually had the opposite effect, as it slowed down the query even more. One important piece of information is that our client has a single Oracle installation with our schema replicated to it 21 times. Each schema has only 1000 tables. Are we asking too much Oracle with so many tables (and, of course, indexes)? I don’t know what their equipment is, but my question is whether this is a reasonable approach or would it be better to split users into different SIDs?
The following is a query that is executed by Hibernate. The client tells us that this request consumes about 45% of the processor when it is running (although I do not know how long).
Any suggestions are welcome, 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