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 -^

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.

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.

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#;

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 )