Re: Mystique: Locks, Constraints and Indexes
Date: Wed, 8 Mar 1995 21:45:31 GMT
Message-ID: <D555rv.E00_at_world.std.com>
Pauli Salmu <p.salmu_at_mn.medstroms.se> writes:
>We were getting strange deadlocks in our production DB and finally
>happened to discover that they had something to do with constraints.
>Then we got some help from the local Oracle representatives who said
>that you probably must index every column that has a foreign key
>constraint.
Here is an oraperl script that warns you if you have a foreign key constraint, but no index exists for the columns being constrained: (The man page follows the script)
#!/usr/local/bin/oraperl
($progname) = ($0 =~ m!([^/]*$)! ) ;
require "getopts.pl" ;
sub usage {
print STDERR "$usage\n" ;
exit 1;
}
$usage="Usage: $progname [-fcivh] [-t table_name] [-u username/password] owner" ;
&usage unless &Getopts('fchvit:u:');
&usage unless $owner = shift _at_ARGV ; $owner="\U$owner"; # CONVERT TO UPPERCASE
&usage if shift _at_ARGV;
# And now for some oraperl stuff
sub out_and_die {
$ora_error=$ora_errstr ;
&ora_close($csr);
&ora_logoff($lda) ;
die "_at__ $ora_error" ;
}
if ($opt_u) {
($uid,$upass)=split('/',$opt_u,2);
if (! $upass) {
system 'stty', '-echo';
print STDERR "Enter password for $uid: ";
chop($upass = <>);
print STDERR " Thank you.\n";
system 'stty', 'echo';
}
}
$ora_cache=20000;
$lda = &ora_login('',$uid || '/',$upass) || die "Cannot log in\n$ora_errstr" ;
$table_clause=($opt_t?" and table_name = '\U$opt_t'":"");
$csr = &ora_open($lda,<<
select constraint_name,table_name,decode (constraint_type,'P','PRIMARY','U',' UNIQUE','R','FOREIGN','V','CHECK_OPT','C','CHECK',constraint_type) , r_constrain t_name , search_condition, delete_rule, status from dba_constraints
where owner = '$owner' $table_clause
order by 2,3 desc
) || &out_and_die("Line",__LINE__) ;
$csr_cols = &ora_open($lda,<<
select constraint_name,table_name,column_name,position from dba_cons_columns where owner = '$owner'
) || &out_and_die("Line",__LINE__);
write if $opt_h;
if ($opt_f || $opt_i) {
$csr_ind = &ora_open($lda,<<
select index_name,table_name, column_name,column_position from dba_ind_column
s where table_owner = '$owner' $table_clause order by table_name,index_name,colu
mn_position
) || &out_and_die("Line",__LINE__);
while (($iname,$tname,$cname,$cpos) = &ora_fetch($csr_ind)) {
$index{$tname,$cpos,$cname}.="$iname,";
if ($opt_i) {
if ($oiname ne $iname) {
print "\n" if $not_first++;
print "\n" if $not_first_also++ && $otname ne $tname;
print "*TABLE*: $tname\n" if $opt_v || $otname ne $tname;
print "INDEX: $iname\n";
$oiname = $iname;
$otname = $tname;
}
print "COLUMN: $cpos $cname\n";
}
}
&out_and_die("Line",__LINE__) unless $ora_errno == 0; &ora_close($csr_ind);
print "\n\nCONSTRAINT SECTION ******\n\n" if $opt_i; }
while (($name,$table,$column,$pos) = &ora_fetch($csr_cols)) {
# I no longer think the .= is neccessary, but I'll leave it
$columns{$name,$pos} .= "$table:$column";
}
&out_and_die("Line",__LINE__) unless $ora_errno == 0;
&ora_close($csr_cols) ;
while (($name,$table,$type,$r_name,$search,$rule,$status) = &ora_fetch($csr)) {
next if $type eq 'CHECK' && !$opt_c;
if ($otable ne $table) {
print "\n" if $otable;
print "*TABLE*: $table\n";
$otable=$table;
}
else {
print "*TABLE*: $table\n" if $opt_v; }
print "CONSTRAINT: $name\n"; print "TYPE: $type\n"; print "CHECK_COND: $search\n" if $search && $opt_c; print "RULE: $rule\n" if $rule =~ /cascade/i; print "STATUS: $status\n" if $status !~ /enabled/i;$index=1;
if ($r_name) {
print "REF_INDEX: $r_name\n";
while ($columns{$r_name,$index}) {
($rtable,$rcolumn)=split(/:/,$columns{$r_name,$index});
print "RTABLE: $rtable\n" unless $index > 1;
print "RCOLUMN: $index $rcolumn\n";
($table,$column)=split(/:/,$columns{$name,$index});
print "COLUMN: $index $column\n";
if ($opt_f) {
(_at_indexes)=split(/,/,$index{$table,$index,$column});
if ($index == 1) {
_at_intersection=_at_indexes;
}
else {
local(%mark);
grep($mark{$_}++,_at_indexes);
_at_intersection=grep($mark{$_},_at_intersection);
}
}
$index++;
}
# There should be an index for this foreign key
# Find it and display it's name
if ($opt_f) {
if (_at_intersection) {
for $index (_at_intersection) {
print "FK_INDEX: $index\n";
}
}
else {
print "********: WARNING- NO FK_INDEX FOUND *****\n";
}
}
}
else {
while ($columns{$name,$index}) {
($table,$column)=split(/:/,$columns{$name,$index});
print "COLUMN: $index $column\n";
$index++;
}
}
print "\n";
}
&out_and_die("Line",__LINE__) unless $ora_errno == 0; &ora_close($csr) ;
&ora_logoff($lda) ;
format top =
_at_<<<<<< CONSTRAINT REPORT @>>>>>>>>>>>>>>>>>>
$ENV{'ORACLE_SID'}, `/bin/date`
OWNER=_at_<<<<<<<<<<<<<<<<<<<<<<<<<<
$owner
.
format =
.
.TH consl 1WG "WGC Applications"
.SH NAME
consl \- Displays constraint info
.SH SYNOPSIS .B "consl [-fihcv] [-t table_name] [-u username[/password]] schema" .br .SH DESCRIPTION
consl displays a schema's constraints, including the columns doing the constraining and the columns being constrained, and the tables being referenced.
Use -t to limit the output to a specific table.
Use -f to show the foreign key index on the constrained table. With this option, a warning is printed if there is no foreign key index.
The -h (header) option prints a header one time only.
The -v (verbose) option prints the table for each constraint instead of just printing it once for all the table's constraints.
The -i (index) option prints the indexes and their columns prior to printing the constraint report. Look for CONSTRAINT SECTION*** to find the constraints.
The -c (check) option prints check constraint information.
The -u option logs you into oracle with the specified username. The password, if not supplied, will be prompted for invisibly. If no -u, the '/' is the username used.
The schema is the oracle account on which you would like information. It is
required.
.SH SEE_ALSO
cons(1wg)
.SH AUTHOR
Brad Howerter, bhower_at_wgc.woodward.com
Received on Wed Mar 08 1995 - 22:45:31 CET