Re: ORA-600 Deadlock Issues

From: Karthik <karthik_at_apple.com>
Date: Mon, 27 Jun 2005 17:44:06 +0530
Message-Id: <15f8c58dc35ebdb23ecd6b37288f7a2b@apple.com>

Tom,

I used the following script to identify FK columns without indexes.

Thanks, Karthik

rem

---

rem Shows the foreign keys without appropiate index rem

---

rem
SET echo off
SET verify off

--
COLUMN OWNER noprint new_value own
COLUMN TABLE_NAME format a24 wrap heading "Table Name"
COLUMN CONSTRAINT_NAME format a24 wrap heading "Constraint Name"
COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
COLUMN COLUMN_NAME format a24 wrap heading "1. Column"
BREAK ON OWNER skip page
--
SET TERMOUT ON
TTITLE  CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
PROMPT
PROMPT Please enter Owner Name and Table Name. Wildcards allowed 
(DEFAULT: %)
PROMPT
PROMPT eg.:  SCOTT, S% OR %
PROMPT eg.:  EMP, E% OR %
PROMPT
--
ACCEPT vOwner prompt "Owner  <%>: " DEFAULT %
ACCEPT vTable prompt "Tables <%>: " DEFAULT %
--
SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
   FROM DBA_CONS_COLUMNS c
   WHERE position=1 AND
    (OWNER, TABLE_NAME, COLUMN_NAME) IN
    (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
       FROM DBA_CONSTRAINTS  c, DBA_CONS_COLUMNS cc
      WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
        AND c.TABLE_NAME      = cc.TABLE_NAME
        AND c.OWNER           = cc.OWNER
        AND c.CONSTRAINT_TYPE = 'R'
        AND cc.POSITION       = 1
        AND c.OWNER           LIKE UPPER('&vOwner')
        AND c.TABLE_NAME      LIKE UPPER('&vTable')
      MINUS
     SELECT table_owner, table_name, column_name
       FROM DBA_IND_COLUMNS
      WHERE COLUMN_POSITION = 1
        AND TABLE_OWNER LIKE UPPER('&vOwner')
        AND TABLE_NAME  LIKE UPPER('&vTable')
   )
   ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
--
ttitle off
SET pause off
COLUMN TABLE_NAME clear
COLUMN CONSTRAINT_NAME clear
COLUMN CONSTRAINT_TYPE clear
COLUMN COLUMN_NAME clear
clear breaks



On 27-Jun-05, at 5:18 PM, Mercadante, Thomas F (LABOR) wrote:



> Karthik,

>

> As you have figured out, the largest cause of Deadlock problems are bad

> coding and missing indexes to support foreign keys.  Below is a sql to

> help you find tables with foreign keys but no indexes to support them.

> The query is not 100% bullet proof, but it is a start.

>

> I use this for Curam applications as they do not believe in supplying

> indexes for foreign keys (their official response is to drop the FK's -

> nice, eh?).

>

> Good Luck!

>

> SELECT 'create index ' || substr(ut.table_name,1,20) ||

> ROUND(sys.dbms_random.value*100)||'IDX ' ||

> 'ON ' || ut.table_name || ' (' || ucc.column_name || ') ' ||

> 'TABLESPACE ' || ut.TABLESPACE_NAME||'PK pctfree 10' ddl_string

> FROM USER_TABLES ut, USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc

> WHERE uc.constraint_type='R'

> AND ucc.constraint_name = uc.constraint_name

> AND ut.table_name = uc.table_name

> AND NVL(position,1) = 1

> AND NOT EXISTS(SELECT 1 FROM USER_IND_COLUMNS uic

> WHERE uic.table_name=ucc.table_name

> AND   ucc.column_name = uic.column_name

> AND   uic.column_position=1)

> ORDER BY 1;

>

> Tom


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 27 2005 - 08:15:20 CDT