Oracle Logbook

May 18, 2012

Which index does my Oracle need?

Filed under: Uncategorized — heliodias @ 8:46 pm

Oracle 10g and below have performance issues regarding lack of indexes at foreign key table. You can even get a dead lock, due to a sort of lock scalation (Oracle actually doesn´t scalate locks, but since it lock the whole table instead of a single row, in this case I rather call it lock scalation)

This query will show you a list of most likelly in need indexes for your environment

SELECT DISTINCT T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME
FROM DBA_CONSTRAINTS T1,DBA_INDEXES T2
WHERE CONSTRAINT_TYPE=’R’
AND T1.STATUS=’ENABLED’
AND T2.STATUS=’VALID’
AND T1.OWNER=T2.OWNER
AND T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER NOT IN (‘SYS’,’SYSTEM’,’WKSYS’)
AND NOT EXISTS
   (SELECT NULL FROM DBA_CONS_COLUMNS T3 WHERE
    T1.OWNER=T3.OWNER
    AND T1.TABLE_NAME=T3.TABLE_NAME
    AND T1.CONSTRAINT_NAME=T3.CONSTRAINT_NAME
    AND EXISTS (
             SELECT NULL FROM DBA_IND_COLUMNS T4 WHERE
   T3.OWNER=T4.INDEX_OWNER AND T3.TABLE_NAME=T4.TABLE_NAME
       AND T3.COLUMN_NAME=T4.COLUMN_NAME
       AND T4.INDEX_NAME=T2.INDEX_NAME))
order by 1,2,3;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: