From oracle-l-bounce@freelists.org Mon Jun 27 08:15:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5RDFKs3024217 for ; Mon, 27 Jun 2005 08:15:20 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j5RDFBIP024153 for ; Mon, 27 Jun 2005 08:15:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D47281C479E; Mon, 27 Jun 2005 07:11:41 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21648-09; Mon, 27 Jun 2005 07:11:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 50A901C4A87; Mon, 27 Jun 2005 07:11:41 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v622) In-Reply-To: References: Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <15f8c58dc35ebdb23ecd6b37288f7a2b@apple.com> From: Karthik Subject: Re: ORA-600 Deadlock Issues Date: Mon, 27 Jun 2005 17:44:06 +0530 To: oracle-l@freelists.org X-archive-position: 21708 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: karthik@apple.com Precedence: normal Reply-To: karthik@apple.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00,UPPERCASE_25_50 autolearn=no version=2.63 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