# TLperf.ctl: Collect and Manage Performance Reports
# $Id: TLperf.ctl,v 1.6 2015/11/16 16:04:39 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/TLperf.ctl,v 1.6 2015/11/16 16:04:39 RDA Exp $
#
# Change History
# 20151113  JJU  Add OSW command.

=head1 NAME

DB:TLperf - Collects and Manages Performance Reports

=cut

use Limit
use Message
use Timing

options HI*P*R:i*l:p*r:t:

section begin

# Initialisation
var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'

define %CMD = (collect => 'collect',\
               list    => 'list',\
               osw     => 'osw',\
               purge   => 'purge',\
               query   => 'query')
define %FMT = (d => '^([0-3]\d\-[A-Za-z]{3}\-[12]\d{3}_[0-2]\d:[0-5]\d)$',\
               l => '^([BG])$',\
               n => '^(\d+)$')
define %MON = (jan => 1,feb => 2,mar => 3,apr => 4,may => 5,jun => 6,\
               jul => 7,aug => 8,sep => 9,oct =>10,nov =>11,dec =>12)
define %TYP = (addm => [{req => 0,\
                         val => {i => ['n','n'],\
                                 p => ['d','d*'],\
                                 r => 'd'}}],\
               ash  => [{req => 0,\
                         val => {p => ['d','d*'],\
                                 r => 'd'}}],\
               awr  => [{req => 0,\
                         val => {i => ['n','n'],\
                                 p => ['d','d*'],\
                                 r => 'd'}}],\
               awrd => [{key => 'B',\
                         req => 0,\
                         val => {i => ['n','n'],\
                                 p => ['d','d*'],\
                                 r => 'd'}},\
                        {key => 'G',\
                         req => 1,\
                         val => {I => ['n','n'],\
                                 P => ['d','d*'],\
                                 R => 'd'}}],\
               osw  => [{req => 1,\
                         val => {l => 'l',\
                                 p => ['d','d']}}])

# Set the abbreviation
call setAbbr('DB_PER_')

# Check the database access
macro check_db
{define ($tgt) = @arg

 # Set the current target
 if findItem('SQ','.',$tgt)
  call setCurrent(addTarget(last))
 elsif ${SET.DB.DB.I_DB}
  call setCurrent(last)
 elsif ?testDir('d',${ENV.ORACLE_HOME})
  call setCurrent(addTarget('SQ_PERF',\
    {B_LOCAL      =>true,\
     D_ORACLE_HOME=>last,\
     T_ORACLE_SID =>${ENV.ORACLE_SID}}))
 else
  call setCurrent(addTarget('SQ_PERF',{T_ORACLE_SID=>${ENV.ORACLE_SID}}))
 else
  die 'Missing database setup'
 call ${CUR.O_TARGET}->set_trace(setTrace())

 # Check the database access
 if testSql()
  die 'No database access'
}

# Compact a date
macro compact_date
{define ($str) = @arg
 import %MON
 keep %MON

 var @tbl = split('[\-\_\:\s]',nvl($str,${RDA.T_LOCALTIME}))
 return sprintf('%02d%02d%02d%02d%02d',\
   expr('%',$tbl[2], 100),$MON{lc($tbl[1])},$tbl[0],$tbl[3],$tbl[4])
}

# Validate the options a specified tool
macro validate_options
{define (\%opt,$typ) = @arg
 import %TYP
 keep %TYP

 # Reject unknown tool
 if missing($TYP{$typ})
  return undef

 # Extract option groups
 define $req = {}
 loop $grp (@{$TYP{$typ}})
 {define @opt = ()
  loop <OPT> $opt (keys($tbl = $grp->{'val'}))
  {next <OPT> missing($opt{$opt})
   if ref($tbl->{$opt})
   {define (@src,@dst) = @{$opt{$opt}}
    loop $fmt (@{$tbl->{$opt}})
    {if !?shift(@src)
      break substr($fmt,1)
     elsif ?validate_value(substr($fmt,0,1),last)
      next push(@dst,last)
     debug 'Rejecting invalid option ',$opt,' for ',$typ
     next <OPT>
    }
    call push(@opt, lc($opt), [@dst])
   }
   else
   {if ?validate_value($tbl->{$opt},$opt{$opt})
     call push(@opt, lc($opt), last)
    else
     debug 'Rejecting invalid option ',$opt,' for ',$typ
   }
  }
  if and($grp->{'req'},not(@opt))
   die 'Missing options for ',$typ
  if missing($grp->{'key'})
   return {@opt}
  var $req->{$grp->{'key'}} = {@opt}
 }
 return $req
}

macro validate_value
{define ($typ,$val) = @arg
 import %FMT
 keep %FMT

 if exists($FMT{$typ})
  return first(match($val,$FMT{$typ}))
 return undef
}

section tool

# Execute the corresponding section
if missing($CMD{$cmd = shift(@arg)})
 die 'Invalid command: ',$cmd
var @{CUR.W_NEXT} = ($CMD{$cmd})

=head1 COLLECT COMMAND

 <rda> -XRda run DB.perf <switches> collect [tool...]

 <sdci> run DB.perf <switches> collect [tool...]

Executes the specified tools, all by default. Supported tools are C<addm>,
C<ash>, C<awr>, and C<awrd>. It supports the following switches:

=over 14

=item B<  -H>

Generates the HTML report also (applicable to  C<ash>, C<awr>, and C<awrd>
tools).

=item B<  -i num,num>

Specifies the begin and end snapshot identifiers (not applicable for ASH tool).

=item B<  -p tim,tim>

Specifies the begin and end times of the period.

=item B<  -p tim>

Specifies the begin time of the period.

=item B<  -r tim>

Specifies the reference time.

=back

You must use the C<DD-Mon-YYYY_HH24:MI> to specify the times.

The C<awrd> tool requires two periods. The mandatory period is specified by
uppercase options (C<I>, C<P>, or C<R>).

=cut

# Report names :
#   DB_PER_I<n>T<n>_<typ>
#   DB_PER_P<tim>T<tim>_<typ>  tim=YYMMDDHHMI
#   DB_PER_R<tim>_<typ>        tim=YYMMDDHHMI
#   DB_PER_S<tim>_<typ>        tim=YYMMDDHHMI (current time)

section collect

# Test the database connection and determine its version
call check_db($opt->{'t'})
if !?$ORACLE_HOME = ${SET.DB.DB.D_ORACLE_HOME/P:\
                      nvl(testDir('d',${SYS.ORACLE_HOME}),\
                          testDir('d',${ENV.ORACLE_HOME}))}
 die 'Missing Oracle home'
run DB:DBinfo()
var $VER = get_db_version()

# Generate an ADDM report
macro generate_addm_report
{var ($flg,$txt,$beg,$end) = @arg
 import $ORACLE_HOME

 debug ' Inside DB.perf tool, generating the ADDM report'
 output E,addm
 var $fil = catFile($ORACLE_HOME,'rdbms','admin','addmrpt.sql')
 if ?testFile('r',$fil)
 {if $flg
  {set $sql
   {SET define on
   "DEFINE REPORT_NAME = :1
   "DEFINE BEGIN_SNAP  = :2;
   "DEFINE END_SNAP    = :3;
   "@@:4
   }
   var $tmp = getTemp('addm')
   call loadSql(bindSql($sql,$tmp,$beg,$end,catNative($fil)),false,4)
   if grepLastSql('^ORA-65040:')
   {write 'Requesting ADDM report is not allowed from within a pluggable \
           database.'
   }
   elsif ?testFile('r',$tmp)
    call writeFile($tmp,[])
   elsif getSqlMessage()
    write last
   else
   {write 'Requested time interval for the ADDM report:'
    write $txt
    write '%BR%Check for entered date or snapshots do not exist in database.'
    write '<verbatim>'
    call writeLastSql(-12)
    write '</verbatim>'
   }
   call unlinkTemp('addm')
  }
  else
  {write 'Requested time interval for the ADDM report:'
   write $txt
   write '%BR%Two snapshots are not available for that period of time.'
  }
 }
 if isCreated(true)
  call renderReport()
}

# Generate an Active Session History report
macro generate_ash_report
{var ($htm,$req) = @arg
 import $ORACLE_HOME

 debug ' Inside DB.perf tool, generating the ASH report'
 output E,ash
 var $fil = catFile($ORACLE_HOME,'rdbms','admin','ashrpt.sql')
 if ?testFile('r',$fil)
 {set $sql
  {SET define on
  "DEFINE ASH_Begin_Time=''
  "COL ASH_Begin_Time NEW_VALUE ASH_Begin_Time
  "DEFINE ASH_Duration=0
  "COL ASH_Duration NEW_VALUE ASH_Duration
  "
  "SELECT TO_CHAR(:1,'MM/DD/YY HH24:MI:SS') ASH_Begin_Time
  " FROM sys.dual;
  "
  "SELECT :2 ASH_Duration
  " FROM sys.dual;
  "
  "DEFINE REPORT_NAME = :3;
  "DEFINE BEGIN_TIME  = '&&ASH_Begin_Time';
  "DEFINE DURATION    = &&ASH_Duration;
  "DEFINE REPORT_TYPE = :4;
  "
  "@@:5
  }
  if exists($req->{'p'})
  {var $sdt = $req->{'p'}->[0]
   var $beg = concat("TO_DATE('",$sdt,"','DD-Mon-YYYY_HH24:MI')")
   var $txt = concat('   * Entered start time is ``',$sdt,'``')
   if ?$edt = $req->{'p'}->[1]
   {var $dur = concat("GREATEST(1,\
      (TO_DATE('",last,"','DD-Mon-YYYY_HH24:MI') - \
       TO_DATE('",$sdt,"','DD-Mon-YYYY_HH24:MI')) * 1440)")
    var $txt = concat($txt,"\012   * Entered end time is ``",$edt,'``')
   }
   else
   {var $dur = concat("GREATEST(1,\
      (SYSDATE - TO_DATE('",$sdt,"','DD-Mon-YYYY_HH24:MI')) * 1440)")
    var $txt = concat($txt,"\012   * Current time taken as end time")
   }
  }
  elsif exists($req->{'r'})
  {var $rdt = $req->{'r'}
   var $beg = concat("TO_DATE('",$rdt,"','DD-Mon-YYYY_HH24:MI') - 14/1440")
   var $dur = 15
   var $txt = concat('   * Entered event local time is ``',$rdt,'``')
  }
  else
  {var $beg = 'SYSDATE - 15/1440'
   var $dur = 15
   var $txt = '   * Current time taken for generating the report'
  }
  var $tmp = getTemp('ash')
  call loadSql(bindSql($sql,$beg,$dur,$tmp,'text',catNative($fil)),false,4)
  if ?testFile('rs',$tmp)
  {call writeFile($tmp,[])

   # Generate the HTML report when requested
   if $htm
   {debug ' Inside DB.perf tool, generating the ASH HTML report'
    var $tmp = getTemp('ash_rpt','.html')
    call loadSql(bindSql($sql,$beg,$dur,$tmp,'html',catNative($fil)),false,4)
    if ?testFile('rs',$tmp)
    {var $zip = concat(${CUR.W_PREFIX},'ash.zip')
     if !createArchive(catFile(${OUT.E},$zip),'',parsePath($tmp))
      write '%BR%[[',$zip,'][_blank][ASH in HTML format (zipped)]]'
    }
    call unlinkTemp('ash_rpt')
   }
  }
  elsif getSqlMessage()
   write last
  else
  {write 'Requested time interval for the ASH report:'
   write $txt
   write '<verbatim>'
   call writeLastSql(-12)
   write '</verbatim>'
  }
  call unlinkTemp('ash')
 }
 if isCreated(true)
  call renderReport()
}

# Generate a AWR report
macro generate_awr_report
{var ($htm,$flg,$txt,$beg,$end) = @arg
 import $ORACLE_HOME

 debug ' Inside DB.perf tool, generating the AWR report'
 output E,awr
 var $fil = catFile($ORACLE_HOME,'rdbms','admin','awrrpt.sql')
 if ?testFile('r',$fil)
 {if $flg
  {set $sql
   {SET define on
   "DEFINE REPORT_NAME = :1;
   "DEFINE BEGIN_SNAP  = :2;
   "DEFINE END_SNAP    = :3;
   "DEFINE NUM_DAYS    = 3;
   "DEFINE REPORT_TYPE = ':4';
   "
   "@@:5
   }
   var $tmp = getTemp('awr')
   call loadSql(bindSql($sql,$tmp,$beg,$end,'text',catNative($fil)),false,4)
   if ?testFile('rs',$tmp)
   {call writeFile($tmp,[])

    # Generate the HTML report when requested
    if $htm
    {debug ' Inside DB.perf tool, generating the AWR HTML report'
     var $tmp = getTemp('awr_rpt','.html')
     call loadSql(bindSql($sql,$tmp,$beg,$end,'html',catNative($fil)),false,4)
     if ?testFile('rs',$tmp)
     {var $zip = concat(${CUR.W_PREFIX},'awr.zip')
      if !createArchive(catFile(${OUT.E},$zip),'',parsePath($tmp))
       write '%BR%[[',$zip,'][_blank][AWR in HTML format (zipped)]]'
     }
     call unlinkTemp('awr_rpt')
    }
   }
   elsif getSqlMessage()
    write last
   else
   {write 'Requested time interval for the AWR report:'
    write $txt
    write 'Check for entered date or snapshot does not exist in database.%BR%'
    write '<verbatim>'
    call writeLastSql(-12)
    write '</verbatim>'
   }
   call unlinkTemp('awr')
  }
  else
  {write 'Requested time interval for the AWR report:'
   write $txt
   write '%BR%Two snapshots are not available for that period of time.'
  }
 }
 if isCreated(true)
  call renderReport()
}

# Generate a AWR Diff report
macro generate_awrd_report
{var ($htm,$flg,$t_g,$b_g,$e_g,$t_b,$b_b,$e_b) = @arg
 import $ORACLE_HOME

 debug ' Inside DC.perf tool, generating the AWR Diff report'
 debug ' - Good period:'
 debug $t_g
 debug ' - Bad period:'
 debug $t_b
 output E,awrd

 var $fil = catFile($ORACLE_HOME,'rdbms','admin','awrddrpi.sql')
 if testFile('r',$fil)
 {if $flg
  {set $sql
   {SET define on
   "DEFINE inst_num  = 0
   "DEFINE inst_num2 = 0
   "DEFINE inst_name = 0
   "DEFINE db_name   = 0
   "DEFINE dbid      = 0
   "DEFINE dbid2     = 0
   "COL inst_num  NEW_VALUE inst_num
   "COL inst_num2 NEW_VALUE inst_num2
   "COL inst_name NEW_VALUE inst_name
   "COL db_name   NEW_VALUE db_name
   "COL dbid      NEW_VALUE dbid
   "COL dbid2     NEW_VALUE dbid2
   "SELECT d.dbid            dbid,
   "       d.dbid            dbid2,
   "       d.name            db_name,
   "       i.instance_number inst_num,
   "       i.instance_number inst_num2,
   "       i.instance_name   inst_name
   " FROM v$database d,
   "      v$instance i;
   "DEFINE REPORT_NAME = :1;
   "DEFINE BEGIN_SNAP  = :2;
   "DEFINE END_SNAP    = :3;
   "DEFINE BEGIN_SNAP2 = :4;
   "DEFINE END_SNAP2   = :5;
   "DEFINE NUM_DAYS    = 1;
   "DEFINE NUM_DAYS2   = 1;
   "DEFINE REPORT_TYPE = ':6';
   "
   "@@:7
   }
   var $tmp = getTemp('awrd')
   call loadSql(bindSql($sql,$tmp,$b_g,$e_g,$b_b,$e_b,'text',\
                             catNative($fil)),false,4)
   if ?testFile('rs',$tmp)
   {call writeFile($tmp,[])

    # Generate the HTML report when requested
    if $htm
    {debug ' Inside DC.perf tool, generating the AWR Diff HTML report'
     var $tmp = getTemp('awrd_rpt','.html')
     call loadSql(bindSql($sql,$tmp,$b_g,$e_g,$b_b,$e_b,'html',\
                               catNative($fil)),false,4)
     if ?testFile('rs',$tmp)
     {var $zip = concat(${CUR.W_PREFIX},'awrd.zip')
      if !createArchive(catFile(${OUT.E},$zip),'',parsePath($tmp))
       write '%BR%[[',$zip,'][_blank][AWR in HTML format (zipped)]]'
     }
     call unlinkTemp('awr_rpt')
    }
   }
   elsif getSqlMessage()
    write last
   else
   {write 'Requested time interval for the AWR report:'
    write $t_g
    write 'Check for entered date or snapshot does not exist in database.%BR%'
    write '<verbatim>'
    call writeLastSql(-12)
    write '</verbatim>'
   }
   call unlinkTemp('awr')
  }
  else
  {write 'Requested time interval for the AWR report:'
   write $t_g
   write '%BR%Two snapshots are not available for that period of time.'
  }
 }
 if isCreated(true)
  call renderReport()
}

# Set report prefix
macro set_report_prefix
{var ($req) = @arg

 if and(exists($req->{'B','.'}),exists($req->{'G','.'}))
  call setPrefix(concat('I',join('T',@{$req->{'G','.'}}),'_I',\
                            join('T',@{$req->{'B','.'}}),'_'))
 else
 {if exists($req->{'G'})
   var $req = $req->{'G'}
  if exists($req->{'i'})
   call setPrefix(concat('I',join('T',@{$req->{'i'}}),'_'))
  elsif exists($req->{'p'})
   call setPrefix(concat('P',\
     join('T',map($req->{'p'},code(compact_date(last)))),'_'))
  elsif exists($req->{'r'})
   call setPrefix(concat('R',compact_date($req->{'r'}),'_'))
  else
   call setPrefix(concat('S',compact_date(),'_'))
 }
 debug ' Inside DB.perf tool, set report prefix to ',${CUR.W_PREFIX}
}

# Retrieve two snapshots
macro retrieve_snapshots
{var ($req) = @arg
 import $VER
 keep $VER

 # Validate provided snapshots
 if exists($req->{'i'})
 {var $beg = $req->{'i'}->[0]
  var $end = $req->{'i'}->[1]
  debug ' - Specified snapshots are ',$beg,' and ',$end
  if and(expr('>',$beg,0),expr('>',$end,$beg))
  {var $req->{'.'} = [$beg,$end]
   return (true,$beg,$end,\
     concat('   * Specified snapshots are ``',$beg,'`` and ``',$end,'``'))
  }
  return ()
 }

 # Retrieve the ADDM and AWR snapshots
 debug ' Inside DB.perf tool, retrieving snapshots for ADDM and AWR reports'
 set $sql
 {SET DEFINE ON
 "DEFINE ADDM_Time_Interval=0
 "COL ADDM_Time_Interval NEW_VALUE ADDM_Time_Interval
 "
 }
 if match($VER,'^12')
 {append $sql
  {SELECT w.snapint_num / (60 * 60) ADDM_Time_Interval
  " FROM sys.wrm$_wr_control w,v$database d
  " WHERE d.CON_DBID = w.DBID;
  }
 }
 else
 {append $sql
  {SELECT snapint_num / (60 * 60) ADDM_Time_Interval
  " FROM sys.wrm$_wr_control
  " WHERE ROWNUM < 2;
  }
 }
 append $sql
 {
 "SELECT NVL(MIN(s.snap_id), 0) || '|' ||
 "       NVL(MAX(s.snap_id), 0)
 " FROM dba_hist_snapshot s,
 "      dba_hist_database_instance di
 " WHERE di.dbid            = s.dbid
 "   AND di.instance_number = s.instance_number
 "   AND di.startup_time    = s.startup_time
 "   AND s.end_interval_time BETWEEN
 "        :1 - (&&ADDM_Time_Interval / 24) AND
 "        :2 + (&&ADDM_Time_Interval / 24);
 }
 if exists($req->{'p'})
 {var $sdt = $req->{'p'}->[0]
  var $beg = concat("TO_DATE('",$sdt,"','DD-Mon-YYYY_HH24:MI')")
  var $txt = concat('   * Entered start time is ``',$sdt,'``')
  if $edt = $req->{'p'}->[1]
  {var $end = concat("TO_DATE('",$edt,"','DD-Mon-YYYY_HH24:MI')")
   var $txt = concat($txt,"\012   * Entered end time is ``",$edt,'``')
   debug ' - From ',$sdt,' to ',$edt
  }
  else
  {var $end = 'SYSDATE - (&&ADDM_Time_Interval / 24)'
   var $txt = concat($txt,"\012   * Current time taken as end time")
   debug ' - From ',$sdt
  }
 }
 elsif exists($req->{'r'})
 {var $rdt = $req->{'r'}
  var $beg = concat("TO_DATE('",$rdt,"','DD-Mon-YYYY_HH24:MI')")
  var $end = $beg
  var $txt = concat('   * Entered event local time is ``',$rdt,'``')
  debug ' - Around ',$rdt
 }
 else
 {var $beg = 'SYSDATE - (&&ADDM_Time_Interval / 24)'
  var $end = $beg
  var $txt = '   * Current time taken for generating the report'
 }
 var ($lin) = grepSql(bindSql($sql,$beg,$end),'\d+\|\d+','f')
 var ($beg,$end) = split('\|',$lin,2)

 # Check if two snapshots are available
 if and(expr('>',$beg,0),expr('>',$end,$beg))
 {debug ' - Retrieved snapshots are ',$beg,' and ',$end
  var $req->{'.'} = [$beg,$end]
  return (true,$txt,$beg,$end)
 }
 return (false,$txt)
}

# Collect the tool results
if !@arg
 var @arg = keys(%TYP)
loop $typ (@arg)
{next !?$req = validate_options(\%opt,$typ = lc($typ))
 if compare('EQ',$typ,'awrd')
 {var ($flg,$t_b,$b_b,$e_b) = retrieve_snapshots($req->{'B'})
  var ($flg,$t_g,$b_g,$e_g) = retrieve_snapshots($req->{'G'})
  call set_report_prefix($req)
  call generate_awrd_report($opt{'H'},$flg,$t_g,$b_g,$e_g,$t_b,$b_b,$e_b)
 }
 else
 {call set_report_prefix($req)
  if compare('EQ',$typ,'addm')
   call generate_addm_report(retrieve_snapshots($req))
  elsif compare('EQ',$typ,'ash')
   call generate_ash_report($opt{'H'},$req)
  elsif compare('EQ',$typ,'awr')
   call generate_awr_report($opt{'H'},retrieve_snapshots($req))
 }
}
call setPrefix('')

=head1 LIST COMMAND

 <rda> -XRda run DB.perf list [<tool>...]

 <sdci> run DB.perf list [<tool>...]

Lists the available reports for the specified tools, all by default.

=cut

section list

if !@arg
 var @arg = keys(%TYP)
loop $typ (@arg)
{next missing($TYP{$typ = lc($typ)})
 echo "\012Available ",uc($typ),' Results:'
 var $pat = concat('^',${CUR.W_ABBR},'.*_',$typ,'(_html)?\.txt$')
 loop $fil (grepDir([${OUT.E}],$pat,'i'))
  dump '  ',substr($fil,0,-4)
}

=head1 OSW COMMAND

 <rda> -XRda run DB.perf <switches> osw

 <sdci> run DB.perf <switches> osw

Packages OSWatcher files related to a specified time period. It supports the
following switches:

=over 14

=item B<  -l label>

Specifies the packaging label (C<G> for a period where the performances were
good, C<B> for a period where the performances were bad).

=item B<  -p tim,tim>

Specifies the begin and end times of the period.

=back

=cut

section osw

macro collect_osw
{var ($req) = @arg

 # Validate the period
 if missing($req->{'p'})
 {debug 'A time period must be specified for OSW packaging.'
  return
 }
 call suspendTiming(true)
 var $beg = $[TIM]->convert_db(concat(substr($req->{'p'}->[0],0,11),' ',\
   substr($req->{'p'}->[0],12,2),':00:00'))
 var $end = $[TIM]->convert_db(concat(substr($req->{'p'}->[1],0,11),' ',\
   substr($req->{'p'}->[1],12,5),':00'))
 if setPeriod($beg,$end)
 {call resumeTiming()
  debug 'Invalid OSW packaging period: ',$beg,'-',$end
  return
 }

 # Package the related files
 call suspendLimit('p')
 var ${LIM.DB.PERF.OSW.T_PF} = {\
        pth => {fmt => 'chk_localtime=YY/MM/DD/hh/mi',\
                pat => '_(\d+)\.(\d+)\.(\d+)\.(\d{2})(\d{2})\.dat$'}}
 call setPrefix(concat($req->{'l'},'P',\
   join('T',map($req->{'p'},code(compact_date(last))))))
 debug ' Inside DB.perf tool, packaging OSWatcher files'
 debug ' * From ',getBeginTime(true),' to ',getEndTime(true)
 output E,osw
 title '---+ OSWatcher Files'
 title '   * Available from ',getBeginTime(true),' to ',getEndTime(true)
 title '|*Files*|'
 if loadFile('/tmp/osw.hb')
 {var @loc = getLines(0,1)
  if ?$osw = nvl(testDir('d',$loc[1]),\
                 testDir('d',catDir($loc[0],'archive')))
  {var $tgt = concat(${CUR.W_PREFIX},'osw')
   var @fil = ()
   loop $nam (grepDir({bas=>$osw,ctx=>'DB.PERF.OSW'},'\.dat$','dr'))
   {if ?testFile('f',catFile($osw,$nam))
     call push(@fil,$nam)
     write '|',$nam,' |'
   }
   if @fil
   {var $rsp = $[COL]->submit('.','PACKAGE.FILES',\
      directory => $osw,\
      files     => [@fil],\
      location  => ${OUT.E},\
      name      => $tgt,\
      verbose   => false)
    if ?testFile('s',$rsp->get_first('archive'))
     debug ' * ', scalar(@fil),' OSWatcher files packaged in ',basename(last)
   }
  }
 }
 if isCreated(true)
  call renderReport()
 call resumeLimit()
 call resumeTiming()
 call setPrefix('')
}

# Treat the command
if ?$req = validate_options(\%opt,'osw')
 call collect_osw($req)

section purge

=head1 QUERY COMMAND

 <rda> -XRda run DB.perf query

 <sdci> run DB.perf query

Lists the snapshots available in the database.

=cut

section query

call check_db($opt{'t'})
echo 'Available snapshots:'
set $sql
{SELECT '  ' || snap_id ||
"       '  ' || TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24:MI:SS') ||
"       '  ' || TO_CHAR(end_interval_time,'DD-MON-YYYY HH24:MI:SS')
" FROM dba_hist_snapshot
" ORDER BY snap_id DESC;
}
if loadSql($sql,false,4)
{loop $lin (grepLastSql('.'))
  dump $lin
}
elsif getSqlMessage()
 dump last

=head1 SEE ALSO

L<DB:DCdb|collect::DB:DCdb>,
L<DB:LDperf|collect::DB:LDperf>,
L<OS:DCperf|collect::OS:DCperf>

=begin credits

=over 10

=item RDA 8.10: Richard Powell

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut