Skip to content

Oracle Script to find unindexed Foreign key that cause blocking

This script suggest index on foreign key that cause blocking

run this code and enable dbms output to see result. it will show existing index on the table and recommended index


SET SERVEROUTPUT ON
DECLARE
    PROCEDURE print_all (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;

    PROCEDURE print_ddl (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;
BEGIN
    DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform,
                                       'SEGMENT_ATTRIBUTES',
                                       FALSE);

    FOR a
        IN (  SELECT COUNT (*)                                       samples,
                     event,
                     p1,
                     p2,
                     o.owner                                         c_owner,
                     o.object_name                                   c_object_name,
                     p.object_owner                                  p_owner,
                     p.object_name                                   p_object_name,
                     id,
                     operation,
                     MIN (p1 - 1414332420 + 4)                       lock_mode,
                     MIN (sample_time)                               min_time,
                     MAX (sample_time)                               max_time,
                     CEIL (10 * COUNT (DISTINCT sample_id) / 60)     minutes
                FROM dba_hist_active_sess_history
                     LEFT OUTER JOIN dba_hist_sql_plan p USING (dbid, sql_id)
                     LEFT OUTER JOIN dba_objects o ON object_id = p2
                     LEFT OUTER JOIN dba_objects po
                         ON po.object_id = current_obj#
               WHERE     event LIKE 'enq: TM%'
                     AND p1 >= 1414332420
                     AND sample_time > SYSDATE - 15
                     AND p.id = 1
                     AND operation IN ('DELETE', 'UPDATE', 'MERGE')
            GROUP BY event,
                     p1,
                     p2,
                     o.owner,
                     o.object_name,
                     p.object_owner,
                     p.object_name,
                     po.owner,
                     po.object_name,
                     id,
                     operation
            ORDER BY COUNT (*) DESC)
    LOOP
        print_ddl (
               '--  '
            || a.operation
            || ' on '
            || a.p_owner
            || '.'
            || a.p_object_name
            || ' has locked '
            || a.c_owner
            || '.'
            || a.c_object_name
            || ' in mode '
            || a.lock_mode
            || ' for '
            || a.minutes
            || ' minutes between '
            || TO_CHAR (a.min_time, 'dd-mon hh24:mi')
            || ' and '
            || TO_CHAR (a.max_time, 'dd-mon hh24:mi'));

        FOR s
            IN (SELECT DISTINCT
                       REGEXP_REPLACE (
                           CAST (SUBSTR (sql_text, 1, 2000) AS VARCHAR2 (60)),
                           '[^a-zA-Z ]',
                           ' ')    sql_text
                  FROM dba_hist_active_sess_history
                       JOIN dba_hist_sqltext t USING (dbid, sql_id)
                 WHERE     event LIKE 'enq: TM%'
                       AND p2 = a.p2
                       AND sample_time > SYSDATE - 15)
        LOOP
            print_all ('--      ' || 'blocked statement: ' || s.sql_text);
        END LOOP;

        FOR c
            IN (WITH
                    c
                    AS
                        (SELECT p.owner          p_owner,
                                p.table_name     p_table_name,
                                c.owner          c_owner,
                                c.table_name     c_table_name,
                                c.delete_rule,
                                c.constraint_name
                           FROM dba_constraints  p
                                JOIN dba_constraints c
                                    ON (    c.r_owner = p.owner
                                        AND c.r_constraint_name =
                                            p.constraint_name)
                          WHERE     p.constraint_type IN ('P', 'U')
                                AND c.constraint_type = 'R')
                    SELECT c_owner                     owner,
                           constraint_name,
                           c_table_name,
                              CONNECT_BY_ROOT (p_owner || '.' || p_table_name)
                           || SYS_CONNECT_BY_PATH (
                                     DECODE (delete_rule,
                                             'CASCADE', '(cascade delete)',
                                             'SET NULL', '(cascade set null)',
                                             ' ')
                                  || ' '
                                  || c_owner
                                  || '"."'
                                  || c_table_name,
                                  ' referenced by')    foreign_keys
                      FROM c
                     WHERE     LEVEL <= 10
                           AND c_owner = a.c_owner
                           AND c_table_name = a.c_object_name
                CONNECT BY NOCYCLE     p_owner = PRIOR c_owner
                                   AND p_table_name = PRIOR c_table_name
                                   AND (   LEVEL = 1
                                        OR PRIOR delete_rule IN
                                               ('CASCADE', 'SET NULL'))
                START WITH     p_owner = a.p_owner
                           AND p_table_name = a.p_object_name)
        LOOP
            print_all (
                   '--      '
                || 'FK chain: '
                || c.foreign_keys
                || ' ('
                || c.owner
                || '.'
                || c.constraint_name
                || ')'
                || ' unindexed');

            FOR l
                IN (SELECT *
                      FROM dba_cons_columns
                     WHERE     owner = c.owner
                           AND constraint_name = c.constraint_name)
            LOOP
                print_all ('--         FK column ' || l.column_name);
            END LOOP;

            print_ddl (
                   '--      Suggested index: '
                || REGEXP_REPLACE (
                       TRANSLATE (
                           DBMS_METADATA.get_ddl ('REF_CONSTRAINT',
                                                  c.constraint_name,
                                                  c.owner),
                           CHR (10) || CHR (13),
                           '  '),
                       'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*',
                       'CREATE INDEX ON \1 \3;'));

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE     (index_owner, index_name) IN
                                   (SELECT owner, index_name
                                      FROM dba_indexes
                                     WHERE     owner = c.owner
                                           AND table_name = c.c_table_name)
                           AND column_name IN
                                   (SELECT column_name
                                      FROM dba_cons_columns
                                     WHERE     owner = c.owner
                                           AND constraint_name =
                                               c.constraint_name))
            LOOP
                print_ddl ('--      Existing candidate indexes ' || x.ddl);
            END LOOP;

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE (index_owner, index_name) IN
                               (SELECT owner, index_name
                                  FROM dba_indexes
                                 WHERE     owner = c.owner
                                       AND table_name = c.c_table_name))
            LOOP
                print_all ('--       Other existing Indexes: ' || x.ddl);
            END LOOP;
        END LOOP;
    END LOOP;
END;
/


Sample Ouput

— MERGE on hr.addr has locked hr.address in mode 5 for 5 minutes between 09-aug 15:56 and 13-aug 14:50
— blocked statement: MERGE INTO addr C USING SELECT C CNTC ID FROM ac
— FK chain: hr.addr referenced by hr”.”address (hr.add4) unindexed
— FK column CNTC_ID_PAR
— Suggested index: CREATE INDEX ON “hr”.”address” (“CNTC_ID_PAR”);
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE INDEX “hr”.”ITD_address_NDX1″ ON “hr”.”address” (“CNTC_ID”)

No comments yet

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

%d bloggers like this: