Apr 30, 2012

aix nmon analyzer

http://www.ibm.com/developerworks/aix/library/au-nmon_analyser/

64bit
http://blog.meyawi.com/?p=81

You'll need a file generated using for example
nmon -f -A -c 100 -s 300 -d -D -K -M -N -P -T -V -^

Apr 27, 2012

check for missing FK indexes

I redid now-unavailable Tom Kyte's unindex.sql script to check for missing FK indexes.
Changes: check all schemas, not just the current one,
generate create index ddl.

select a.owner, 
           --decode( b.table_name, NULL, '****', 'ok' ) Status, 
           a.table_name, a.constraint_name, a.columns, b.columns
       /*
       , 'CREATE INDEX '||a.owner||'.'||index_name(a.table_name,a.columns)
       ||' ON '||a.table_name||' ('||a.columns||') TABLESPACE '||
                nvl((select i.tablespace_name from all_indexes i 
                 where i.owner=a.owner and i.table_name=a.table_name and i.tablespace_name not like 'PK_%' and rownum=1)
                 , 'default_idx_tablespace')
       ||';'
       */
from 
( select a.owner,
                 substr(a.table_name,1,30) table_name, 
                 substr(a.constraint_name,1,30) constraint_name, 
             max(decode(position, 1,      substr(column_name,1,30),NULL)) || 
             max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
             max(decode(position,10,', '||substr(column_name,1,30),NULL)) columns
    from all_cons_columns a, all_constraints b
   where a.constraint_name = b.constraint_name and a.owner = b.owner
     and b.constraint_type = 'R'
   group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( select index_owner owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
             max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
             max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) columns
    from all_ind_columns 
   group by index_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name(+) AND a.owner = b.owner(+) 
  and b.columns (+) like a.columns || '%'
  and b.table_name IS NULL   -- show only missing FKs
order by a.owner, a.table_name, a.constraint_name;

Create index_name function from my previous post and uncomment CREATE INDEX scetion, if you need it.

index name generator


Index name generator. Notes:
1. checks for 30 chars limit;
2. tries to cut vowels first, starting search from the end of the string - so index name will be more readable;
3. doesn't check if index exist.

CREATE OR REPLACE FUNCTION index_name (p_table VARCHAR2, p_suffix VARCHAR2) 
RETURN VARCHAR2 AS
        l_index_name    VARCHAR2(100) := upper(p_table||'_'||p_suffix);
BEGIN
        select reverse(l_index_name) into l_index_name from dual;
        FOR i IN 1..length(l_index_name)-30 LOOP
                l_index_name := regexp_replace(l_index_name, '[EUIOAY]','' , 1,1); 
        END LOOP;
        select reverse(l_index_name) into l_index_name from dual;
        RETURN SUBSTR(l_index_name,1,30);
END;
/

For example, can be called in index_name('table_name','columns_list') context.

Apr 6, 2012

grep+sum run time from Oracle sql sctipts


assuming
SET TIMING ON
was used

grep "Elapsed: " *.log | perl -ne '$f{$1}+=$2*3600+$3*60+$4 if /_(\w+.sql).+:Elapsed: (\d\d):(\d\d):(\d\d)\./; \
END{foreach(sort keys  %f){printf "$_: %s\n",$f{$_}}}' 

aggregates and prints time from all DxLs but for each log file separately.

what columns are used in every view/table..

e.g.
perl -ne 'while(s/\b([a-z]\w+)\.([a-z]\w+)\b//i){$t{uc $1}{uc $2}++} \
END{foreach $t (sort keys %t){printf "$t: %s\n", join(", ",sort keys %{$t{$t}})}}' <CAmview1.sql 

prints something like
CSFV: AMT_CR_CTRL_AREA, AMT_DR_CTRL_AREA, CHART_OF_ACCT_ID, COST_CNTR_ID, COST_ELEM_ID, CTRL_AREA_ID, FISC_PERIOD, FISC_YEAR, FISC_YR_VARIANT_ID_CTRL_AREA, PARTITION_YEAR, RECORD_TYPE_ID, VERSION_ID
FPCA: CALENDAR_MONTH_SHORT, FISC_PERIOD, FISC_YEAR, FISC_YR_VARIANT_ID, START_DATE
...

is good for any type of schema.object or object.column purposes...
e.g. it helps me to understand what columns are used in every table in huge sqls.

Mar 29, 2012

v$object_usage for all schemas

v$object_usage got constraint to show only indexes for schema you are logged in currently.
Here's query for all indexes in the database:

SELECT u.name owner
        , io.name INDEX_NAME
        , t.name table_name
        , DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring
        , DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used
        , ou.start_monitoring
         ,ou.end_monitoring
     FROM  sys.obj$ io
         , sys.obj$ t
         , sys.ind$ i
         , sys.object_usage ou
         , sys.user$ u
    WHERE io.owner# = u.user# AND i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo#;

Mar 22, 2012

what file systems used by which databases

One more variety of the previous post. Let's say you have multiple databases on the same server and a lot of file systems - and you need to understand what filesystems are used by which dbs. Next Perl script will provide the answer to this question.

#!/usr/bin/perl

use strict;

#0. locate oratab file
my $oratab = -e '/etc/oratab' ? '/etc/oratab'       #linux, hp-ux, aix
     : -e '/var/opt/oracle/oratab' ? '/var/opt/oracle/oratab'  #solaris
     : die "Non-Oracle DB server.\n";

#1. parse out active oracle sids
my (%homes, $fh);
open($fh, $oratab) or die "can't open $oratab: $!";
while (<$fh>)
{  chomp; next if /^\s*#/;
  my ($sid,$home,$y) = split /:/;
  next if $y ne 'Y';
  $homes{$sid} = $home;
}
close $fh;
die "No active Oracle homes\n" unless %homes;

#2. get list of file systems
my %fs;
foreach (split "\n", `df`)
{  my ($f,$l) = (split /\s+/)[0,-1];
  if (   $l =~ /^\// && $l !~ /^\/dev/)  #aix, linux, solaris
    {$fs{$l} = {}}
  elsif ($f =~ /^\// && $f !~ /^\/dev/)  #hp-ux
    {$fs{$f} = {}}
}
die "No / file system found" unless $fs{'/'};

#3. for each sid sqlplus instance to find list of dependent file systems
my $path = $ENV{PATH};
delete $ENV{NLS_LANG};    #fixing ORA-12705: invalid or unknown NLS ...
foreach my $sid (sort keys %homes)
{  my $h = $homes{$sid};
  #3.1. oracle instance depends on its oracle home obviously
  depends($sid => $h);

  #3.2. fetch data from instance itself
  $ENV{ORACLE_SID} = $sid;
  $ENV{ORACLE_HOME} = $h;
  $ENV{PATH} = "$h/bin:$path";
  print STDERR "Quering $sid database ($h)...\n";
  my $dirs = `sqlplus -S "/as sysdba" <<EOF
set echo off head off feed off newpage none pages 1000 lines 1000
select distinct dir from (
select substr(fname,1,instr(fname,'/',-1)-1) dir
from (   select name fname  from v\\\$controlfile
 union all select name   from v\\\$datafile
 union all select name   from v\\\$tempfile
 union all select member   from v\\\$logfile
) union all select value   from v\\\$parameter 
    where isdefault='FALSE' and type=2 and not value like '%,%' and value like '/%'
);
exit;
EOF
`;  if ($? || $dirs !~ /^\//)
  {  print STDERR "WARNING: unable to determine dependencies for $sid: $dirs\n";
    next;
  }
  depends($sid => $dirs);
}

#4. now print all the fs -> sids dependencies
foreach my $fs (sort keys %fs)
{  my $sids = join(', ', sort keys %{$fs{$fs}});
  print "$fs: $sids\n"  if $sids;
}

exit 0;
#######

sub depends {
  my ($sid, $dirs) = @_;
  DIR: foreach my $dir (split "\n", $dirs) {
    foreach my $fs (sort {length($b)<=>length($a)} keys %fs)
    {  if ($dir =~ /^$fs/)
      {  $fs{$fs}{$sid}++; 
        next DIR;
      }
    }
    die "Can't match $dir to any fs\n";
  }
}

'Ruslan Dautkhanov  dautkhanov  gmail.com  Mar 2012.
    -----------
 Tested with:  Perl versions - 5.6.0,5.6.1, 5.8.0,5.8.8
     OSes - AIX, HP-UX, Linux (should also be good for Solaris)
     Oracle DBs - 8.1.6+, 9i, 10g, 11g
';


This script is independent in terms that even sysadmin can use it - it doesn't ask for passwords or anything else. Use synopsis: e.g. su - oracle -c ~oracle/orafs.pl

It'll print output like
/u01: dbxx, dbyy
/u02: dbyy, dbzz, dbrr
...

Tested with:
Perl versions - 5.6.0,5.6.1, 5.8.0,5.8.8
OSes - AIX, HP-UX, Linux (should also be good for Solaris)
Oracle DBs - 8.1.6+, 9i, 10g, 11g (known not to work in 8.0.5).

Note: doesn't analyze directory objects, or external libraries... but it was what i wanted to see - patches are welcome )