The application was upgraded, and simultaneously Oracle was upgraded to 10g and then exhibited TM contention. It had probably been suffering from TM contention while running on Oracle 9i, but we hadn't realised because Oracle9i only reports 'enqueue'.
From 10g, there are no less that 208 different enqueue wait events, that show the type of lock that the process is waiting for, and sometimes additional information. In my case it was event 175. Events can be listed from v$event_name.
SELECT event#, name FROM v$event_name WHERE UPPER(name) LIKE 'ENQ: TM%' / EVENT# NAME ---------- -------------------- 175 enq: TM - contention
With a little help from my friends I came to realise that the cause of this contention was that the system had foreign key constraints on columns that were not indexed. Having found one example of this, I realised that I needed a way to check the entire data model. The result was the following SQL and PL/SQL script.
REM fk_index_check.sql REM 19.10.2007
Uncommenting the following section will produce a test case that should build two indexes.
/*--------------------------------------------------------------
ALTER TABLE EMP_TAB DROP CONSTRAINT MGR_FKEY;
ALTER TABLE EMP_TAB DROP CONSTRAINT DEPT_FKEY;
DROP TABLE Emp_tab;
DROP TABLE DEPT_TAB;
CREATE TABLE Dept_tab (
setid NUMBER(3),
deptno NUMBER(3),
dname VARCHAR2(15),
loc VARCHAR2(15)
--CONSTRAINT dname_ukey UNIQUE (Dname, Loc),
--CONSTRAINT loc_check1
--CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
,CONSTRAINT Dept_pkey PRIMARY KEY (setid,deptno)
)
/
CREATE TABLE Emp_tab (
empno NUMBER(5) CONSTRAINT emp_pkey PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5) CONSTRAINT mgr_fkey REFERENCES emp_tab,
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(5,2),
setid NUMBER(3),
deptno NUMBER(3) NOT NULL,
CONSTRAINT dept_fkey FOREIGN KEY (setid,deptno)
REFERENCES dept_tab (setid,deptno) ON DELETE CASCADE
)
/
/*------------------------------------------------------------*/
set serveroutput on buffer 1000000000
GFC_FK_INDEX_CHECK is a working storage script that is to hold results of the tests on each foreign key.
DROP TABLE gfc_fk_index_check / CREATE TABLE gfc_fk_index_check (owner VARCHAR2(30) NOT NULL ,table_name VARCHAR2(30) NOT NULL ,constraint_name VARCHAR2(30) NOT NULL ,r_owner VARCHAR2(30) NOT NULL ,r_table_name VARCHAR2(30) NOT NULL ,r_constraint_name VARCHAR2(30) NOT NULL ,i_index_owner VARCHAR2(30) ,i_index_name VARCHAR2(30) ,i_status VARCHAR2(30) DEFAULT 'UNKNOWN' ,i_column_list VARCHAR2(300) ,CONSTRAINT gfc_fk_index_check_pk PRIMARY KEY(table_name, constraint_name) ) / TRUNCATE TABLE gfc_fk_index_check /
First the script populates the working storage table with all the referential integrity constraints that reference a primary key constraint.
INSERT INTO gfc_fk_index_check (owner, table_name, constraint_name ,r_owner, r_constraint_name, r_table_name) SELECT c.owner, c.table_name, c.constraint_name , c.r_owner, c.r_constraint_name , r.table_name r_table_name FROM all_constraints c , all_constraints r WHERE c.constraint_Type = 'R' AND r.owner = c.r_owner AND r.constraint_name = c.r_constraint_name AND r.constraint_Type = 'P' AND r.owner = user /
This PL/SQL routine checks each foreign key constraint in the table for each constraint it looks up the referring columns in all_cons_columns and builds a dynamic query that SELECTs the owner and name of an index with the same columns in the same position. The name of that index and the column list is stored on the working storage table. Depending upon how many rows that query returns, a status string is written to the table: No Index/Index Found/Multiple Indexes
DECLARE
l_counter NUMBER;
l_column_list VARCHAR2(200);
l_sql1 VARCHAR2(4000);
l_sql2 VARCHAR2(4000);
l_tmp1 VARCHAR2(20);
l_tmp2 VARCHAR2(20);
l_alias VARCHAR2(3);
l_oldalias VARCHAR2(3);
l_index_owner VARCHAR2(30);
l_index_name VARCHAR2(30);
l_status VARCHAR2(30);
BEGIN
FOR a IN (SELECT * FROM gfc_fk_index_check) LOOP
l_counter := 0;
l_column_list := '';
l_sql1 := 'SELECT i1.index_owner, i1.index_name';
l_sql2 := '';
FOR b IN (SELECT *
FROM all_cons_columns c
WHERE c.owner = a.owner
AND c.constraint_name = a.constraint_name
AND c.table_name = a.table_name
ORDER BY position) LOOP
l_counter := l_counter + 1;
l_oldalias := l_alias;
l_alias := ' i'||TO_CHAR(l_counter);
IF l_counter > 1 THEN
l_sql1 := l_sql1||', ';
l_sql2 := l_sql2
||' AND '||l_oldalias||'.index_owner='
||l_alias ||'.index_owner'
||' AND '||l_oldalias||'.index_name='
||l_alias ||'.index_name'
||' AND ';
l_column_list := l_column_list||',';
ELSE
l_sql1 := l_sql1||' FROM ';
l_sql2 := l_sql2||' WHERE';
END IF;
l_sql1 := l_sql1||'all_ind_columns'||l_alias;
l_sql2 := l_sql2
||l_alias||'.TABLE_OWNER='''||b.owner||''''
||' AND '||l_alias||'.TABLE_NAME='''||b.table_name||''''
||' AND '||l_alias||'.COLUMN_NAME='''||b.column_name||''''
||' AND '||l_alias||'.COLUMN_POSITION='''||b.position||'''';
l_column_list := l_column_list||b.column_name;
END LOOP;
-- dbms_output.put_line(l_sql1);
-- dbms_output.put_line(l_sql2);
-- dbms_output.put_line(l_column_list);
l_status := a.i_status;
l_index_owner := '';
l_index_name := '';
BEGIN
EXECUTE IMMEDIATE l_sql1||l_sql2
INTO l_index_owner, l_index_name;
l_status := 'Index Found';
EXCEPTION
WHEN NO_DATA_FOUND THEN l_status := 'No Index';
WHEN TOO_MANY_ROWS THEN l_status := 'Multiple Indexes';
END;
UPDATE gfc_fk_index_check
SET i_status = l_status
, i_index_owner = l_index_owner
, i_index_name = l_index_name
, i_column_list = l_column_list
WHERE owner = a.owner
AND table_name = a.table_name
AND constraint_name = a.constraint_name;
END LOOP;
COMMIT;
END;
/
This query produces a simple report on each foreign key constraint.
set lines 90 head on feedback on echo on column owner format a20 column table_name format a30 column constraint_name format a30 column r_owner format a20 column r_constraint_name format a30 column r_table_name format a30 column i_index_owner format a20 column i_index_name format a30 column i_status format a30 column i_column_list format a80 spool fk_index_check SELECT g.owner, g.table_name, g.constraint_name , g.r_owner, g.r_table_name, g.r_constraint_name , g.i_index_owner, g.i_index_name, g.i_status , g.i_column_list FROM gfc_fk_index_check g / spool off
This query is similar to the last, but it produces a report of just largest tables that lack indexes on FK constraints. It show tables more than 10000 rows (according to the CBO statistics), or at least the top 20. These are likely to be most severe offenders.
spool fk_index_by_size SELECT * from ( SELECT g.owner, g.table_name, g.constraint_name , g.r_owner, g.r_table_name, g.r_constraint_name , g.i_index_owner, g.i_index_name, g.i_status , /*t.temporary, t.partitioned, */ t.num_rows , g.i_column_list FROM gfc_fk_index_check g, all_tables t WHERE t.table_name = g.table_name AND t.owner = g.owner AND g.i_status = 'No Index' ORDER BY num_rows desc ) WHERE rownum <= 20 or num_rows >= 10000 / spool off
This query generates a script constraint create index DDL statements that will build the missing indexes. The index will have the same name as the foreign key constraint to which it relates.
set head off trimout on trimspool on feedback off verify off timi off echo off lines 200
spool fk_index_build.sql
SELECT 'CREATE INDEX '||g.owner||'.'||g.constraint_name
||' ON '||g.owner||'.'||g.table_name
||' ('||g.i_column_list||');' build_indexes
FROM gfc_fk_index_check g, all_tables t
WHERE t.table_name = g.table_name
AND t.owner = g.owner
AND g.i_status = 'No Index'
ORDER BY t.num_rows
/
spool off
set lines 90 head on feedback on echo on
The test script correctly reports (in fk_index_check.LST) that there are two foreign keys that require supporting indexes
OWNER TABLE_NAME CONSTRAINT_NAME
--------------- -------------------- --------------------
R_OWNER R_TABLE_NAME R_CONSTRAINT_NAME
--------------- -------------------- --------------------
I_INDEX_OWNER I_INDEX_NAME I_STATUS
--------------- -------------------- --------------------
I_COLUMN_LIST
---------------------------------------------------------
SYSADM EMP_TAB MGR_FKEY
SYSADM EMP_TAB EMP_PKEY
No Index
MGR
SYSADM EMP_TAB DEPT_FKEY
SYSADM DEPT_TAB DEPT_PKEY
No Index
SETID,DEPTNO
It produces another script fk_index_build.sql that will build the missing indexes.
CREATE INDEX SYSADM.MGR_FKEY ON SYSADM.EMP_TAB (MGR); CREATE INDEX SYSADM.DEPT_FKEY ON SYSADM.EMP_TAB (SETID,DEPTNO);
When I ran this test script on my problem application, it identified over 200 missing indexes on 900 foreign key constraints, and since building the indexes on tables where I have seen TM locking, I haven't seen any TM locking contention.
The script can be downloaded from the Go-Faster website at http://www2.go-faster.co.uk/scripts.htm#fk_index_check.sql
Caveat: Just because you can index a foreign key, doesn't mean that you should. See
http://www.jlcomp.demon.co.uk/faq/fk_ind.htmlThis query produces a simple report on each foreign key constraint.
@go-faster.co.uk on BlueSky
Posts
