Category: SQL

Indexes on Foreign Keys

Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
 ||'['||acc.position||'])'||' ***** Missing Index'
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 AND (acc.owner, acc.table_name, acc.column_name, acc.position)
 IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 MINUS
 SELECT table_owner, table_name, column_name, column_position
 FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
 acc.column_name, acc.position;

By creating an index on the foreign key of the child table,  “table-level” locks can be avoided.

Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.

Source / Reference links:

Quick TKPROF

1. Find the session to be monitored

SELECT username, sid, serial#, program
FROM v$session
WHERE username = <User_Name>;

2. Enable SQL Tracing

a, Current Session:

ALTER SESSION SET sql_trace = TRUE;
or
execute dbms_session.set_sql_trace(true);

b, Different Session (as SYSDBA):

execute dbms_system.set_sql_trace_in_session(sid, serial#, sql_trace);
e.g.
execute dbms_system.set_sql_trace_in_session(114, 4667, TRUE);

3. Enable Oracle database to gather statistics

ALTER SYSTEM SET timed_statistics = true;  -- at system level
ALTER SESSION SET timed_statistics = true; -- at session level

4. Formatting the output with TKPROF 

TKPROF inputfile outputfile [OPTIONS]
e.g.
tkprof mydb_ora_11915.trc /tmp/tkprof1.txt SYS=NO

5. Find directory where trace file is generated

SELECT value
FROM v$parameter
WHERE name='user_dump_dest';

6. Identify trace file generated

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid
FROM v$session s,
 v$process p
WHERE p.addr = s.paddr and s.username = <User_Name>;

7. Disable tracing for the session

ALTER SESSION SET sql_trace = TRUE;
OR
EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);

Big Tables for Testing

An «ALL_OBJECTS» Table with 1’000’000 Rows

The following Code is from Tom Kyte (http://asktom.oracle.com)

— Create Table with same structure as ALL_TABLES from Oracle Dictionary
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;

— Fill 1’000’000 Rows into the Table
declare
l_cnt number;
l_rows number := 1000000;
begin
— Copy ALL_OBJECTS
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;

— Generate Rows
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table bigtab add constraint
bigtab_pk primary key(id);

A Table with Random Data and same Size as ALL_OBJECTS

CREATE TABLE bigtab (
id NUMBER,
weight NUMBER,
adate DATE
);

INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
51502 rows created.

A Table which can be used for Partition Tests

The ID of the table can be used for Range Partitioning

create table bigtab (
id number(12,6),
v1 varchar2(10),
padding varchar2(50)
)
nologging — just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad(‘x’,50,’x’)
from
all_objects s1, — you’ll need the privilege
all_objects s2
where
rownum <= 1000000
/
commit;

ID V1 PADDING
———- ———- ————————————————–
3000 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.002 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.004 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.006 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.008 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.01 6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.012 7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.014 8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.016 9 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

A Table with Date’s which can be used for Partition Tests

This code is from http://www.oracle-base.com

CREATE TABLE bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);

DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;

INSERT INTO bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, ‘This is some data for ‘ || i);
END LOOP;
COMMIT;
END;
/

Verify:

SQL> select id,to_char(created_date,’DD.MM.YYYY’),
lookup_id, data
from bigtab where rownum < 10;

ID TO_CHAR(CR LOOKUP_ID DATA
———- ———- ———- —————————–
1 21.08.2007 3 This is some data for 1
2 21.08.2006 1 This is some data for 2
3 21.08.2005 2 This is some data for 3
4 21.08.2006 1 This is some data for 4
5 21.08.2007 3 This is some data for 5
6 21.08.2005 2 This is some data for 6
7 21.08.2007 3 This is some data for 7
8 21.08.2006 1 This is some data for 8
9 21.08.2005 2 This is some data for 9

Source:

Akadia

Find Foreign Key Relationships

The below query lists all of the foreign keys and the parent table and columns to which they relate.

SELECT a.owner , a.table_name , c.column_name ,
 b.owner , b.table_name , d.column_name 
FROM dba_constraints a, dba_constraints b,
 dba_cons_columns c, dba_cons_columns d
WHERE a.r_constraint_name = b.constraint_name
 AND a.constraint_type = 'R'
 AND b.constraint_type = 'P'
 AND a.r_owner=b.owner
 AND a.constraint_name = c.constraint_name
 AND b.constraint_name=d.constraint_name
 AND a.owner = c.owner
 AND a.table_name=c.table_name
 AND b.owner = d.owner
 AND b.table_name=d.table_name;

Source