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
WikiDBA 