This discussion is archived
6 Replies Latest reply: Mar 5, 2013 12:58 PM by Guess2 RSS

Tracking down which unindexed foreign keys are the biggets problem

Guess2 Newbie
Currently Being Moderated
I joined a new project recently. I checked prod and our biggest bottleneck is unindexed foreign keys. It is high enough that I can see that it is causing problems. So I ran a query and got a list of all the unindexed foreign keys. Unfortunately there are about 80 of them. This application was inherited. The last team lost the project and I think one of the issues is with an off the shelf application (which we can't get rid of).

I really don't like the idea of adding 80 indexes in a big rollout. It is too big of a change to do at once. It is also hard to measure whether those indexes may cause other problems. So what I would like to do is take my Enqueue waits for unindexed foreign keys and somehow figure out which unindexed foreign keys are causing us the biggest problem. With this many there is a strong possibility that some of these tables are having their parents hit more than others and some of these tables are blocking other sessions more than others.

any suggestions on how to do this? It doesn't need to be exact. However, Id like to propose adding indexes that will give us the biggest bang for the buck.

I am not sure how to take my system wide enqueue/deque waits down to particular tables being hit with DML that cause locking on child tables that in turn cause other sessions to be blocked.
  • 1. Re: Tracking down which unindexed foreign keys are the biggets problem
    Balazs Papp Expert
    Currently Being Moderated
    monitor dba_lock, v$locked_object, v$session
    with the combination of these, you can identify who blocks who on what objects and what type of locks - when blocking sessions exist
    analyze the locking hierarchy, and create indexes starting with the top tables
  • 2. Re: Tracking down which unindexed foreign keys are the biggets problem
    sb92075 Guru
    Currently Being Moderated
    Guess2 wrote:

    I am not sure how to take my system wide enqueue/deque waits down to particular tables being hit with DML that cause locking on child tables that in turn cause other sessions to be blocked.
    Unindexed foreign key is a problem when PARENT table is UPDATED & whole child table needs to be locked

    You know which tables contribute to this problem, so just query V$SQL for UPDATE statements & order by EXECUTIONS.

    I would expect that Parent table that get updated more than other tables would be good candidate to identify which Child table gets a new INDEX.
  • 3. Re: Tracking down which unindexed foreign keys are the biggets problem
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    sb92075 wrote:
    Unindexed foreign key is a problem when PARENT table is UPDATED & whole child table needs to be locked
    I would expect that Parent table that get updated more than other tables would be good candidate to identify which Child table gets a new INDEX.
    Only when the parent KEY is updated (and, in a couple of versions, when any extra columns you've added to the parent key index are updated).

    So the most-frequently updated parent tables might cause no problems at all - the OP would have to examine each SQL carefully, not just check for the table name. On top of which, checking the SQL doesn't identify cases where the parent table has been hidden inside a view ;(

    Regards
    Jonathan Lewis
  • 4. Re: Tracking down which unindexed foreign keys are the biggets problem
    Justin_Mungal Journeyer
    Currently Being Moderated
    Jonathan Lewis wrote:
    sb92075 wrote:
    Unindexed foreign key is a problem when PARENT table is UPDATED & whole child table needs to be locked
    I would expect that Parent table that get updated more than other tables would be good candidate to identify which Child table gets a new INDEX.
    Only when the parent KEY is updated (and, in a couple of versions, when any extra columns you've added to the parent key index are updated).

    So the most-frequently updated parent tables might cause no problems at all - the OP would have to examine each SQL carefully, not just check for the table name. On top of which, checking the SQL doesn't identify cases where the parent table has been hidden inside a view ;(

    Regards
    Jonathan Lewis
    Tom Kyte has a nice short article on this:
    http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

    He also mentions that delete cascade operations can cause locking issues as well, which makes sense. I'm thinking that updating the parent key and delete cascade operations should be rare though...
  • 5. Re: Tracking down which unindexed foreign keys are the biggets problem
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Guess2 wrote:

    I am not sure how to take my system wide enqueue/deque waits down to particular tables being hit with DML that cause locking on child tables that in turn cause other sessions to be blocked.
    Depending on your version of Oracle, and whether or not you are licensed to run the performance pack and diagnostic pack, you could query v$active_session_history (and it's repository dba_hist_active_sess_history).
    The type of query you need would be something like:
    select
            blocking_session, current_obj#, substr(to_char(p1,'xxxxxxxx'),-1), count(*)
    from
            v$active_session_history
    where
            event like 'enq: TM - contention'
    and     session_state = 'WAITING'
    and     sample_time between sysdate - 1/24 and sysdate
    group by
            blocking_session, current_obj#, substr(to_char(p1,'xxxxxxxx'),-1)
    /
    The counts would give you relative time for blocking due to each "current_obj#" - which you'd have to look up against object_id.
    I've also broken this down by blocking_session_id, and the lock mode (which ought to be 4 or 5) - 4 would SUGGEST simple parent/child collisions, 5 would SUGGEST that the probem could be exacerbated by "on delete cascade" constraints.

    Regards
    Jonathan Lewis
  • 6. Re: Tracking down which unindexed foreign keys are the biggets problem
    Guess2 Newbie
    Currently Being Moderated
    Thank you jonathan. We keep the DBA_HIST_SESSION_HISTORY for 7 days. so reviewing this over a week or two should give me a good idea what to look at. It doesn't need to be perfect. I just don't want to guess.

    The v$lock stuff is an issue because i have to either write something to snapshot it (I don't think the AWR snapshots this) or stare at it for days and this would not really give me the whole picture because I'm not staying up all night. Writing my own is an issue. We don't have the production support contract and this data is considered sensitive. So I would need to get my snapshot scripts (say snapshot v$lock, etc... every 30 minutes an dstore to a table) into a release which are often scheduled months in advance. If I was working as a production DBA I like the v$lock snapshots because I could just write it myself and run it from a cronjob.

    As far as looking at individual queries. I generally do that with v$sql_plan or the dbA_hist versions of those tables and look at what tables are being hit.

    Very good answers guys. Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points