#!/usr/bin/ksh
# set -xv
# author :  B. Polarski
# 26 October 2007
# program smenu_awr.ksh
# Modified : 17 Jun 2009    Added the get_hash_Value function

ROWNUM=30
# ---------------------------------------------------------------------------
function get_sql_id
{
 if [ -z "${1%%*[a-z]*}" ];then
    # $1 is a mix of digit and alpha
    echo "$1"
    return
 fi
 # $1 is a hash_value made of only digit
 ret=`sqlplus -s "$CONNECT_STRING" <<EOF
 set head off pagesize 0 feed off verify off
 select distinct sql_id from v\\$sql where hash_value = '$1';
EOF`
 echo "$ret"
}
# ---------------------------------------------------------------------------
function help
{
cat <<EOF

Work with AWR:

    aw -l -rn <nn>                           : List last available snap, limit to -rn <ROWS>  default is last 30
    aw -r  [ -html ]                         : Produce AWR report for the last snap period, optionally output in html
    aw -r -b <nn> -e <nn> [ -html ]          : Produce AWR report between -b <nn> and -e <nn> snaps
    aw -s <sqlid> -b <nn> [-e <nn>] [-html]  : Produce AWR report between -b <nn> and -e <nn> snaps for a given sql_sid
    aw -k -b <nn> -e <nn>                    : List table to keep using stats from snapid -b to -e <nn>
    aw -lret                                 : Show retention period and the interval snapshot
    aw -set <retentions> <interval>          : Set the retention period and the interval (both expressed in minutes)
    aw -dif [-b <nn>] [-e <nn>]
            [-b2 <nn>] [-e2 <nn>] [ -html ]  : Produce differential AWR report between -b1 <nn> and -b2 <nn> 
    aw -ash  -sid <sid>                      : produce ASH Spot report in text for the session sid since its logon
    aw -sl -b <nn> -e<nn> -wait -rn <nn>     : List the <n> most instensive sql in worload repository
    aw -sll -b -wait <n>                     : Same as -sl but limit scope to last snap
    aw -tr <sql_id> [-b <nn> -e <nn>]        : Run the sqltune advisor for <sql_id>. if -b is omited then the sql stats are 
                                               taken from v\$sql otherwise they are taken from awr repository between given snap
    aw -ad  [-b <nn> -e <nn>]                : Run the database adivisor over a period. if -b is omited then the sql stats are the last one
    aw -prf  <sqlid> [-b <nn> -e <nn>]       : Generate sql profile for <sql id>. if -b and -e added then use data between these 2 snaps
    aw -lprf  [SQL_PRF]                      : List sql profiles from dba_ADVISOR_TASK whose radical is <SQL_PRF>. Default is SQL_PRF%
    aw -llprf  <TASK>                        : Show report for TASK. use 'aw -lprf' to get the task name
    aw -xpf  [-b <nn> -e <nn>]               : Show descrepancies between AWR and V$SQL for plan_hash_value 
    aw -sp   <plan_hash value> [-b <nn> ]  : Show SQL plan for a given plan_hash_value
    aw -f <sqlid>                            : List snapshots where sqlid appear
    aw -bbw     : List Buffer busy wait for the last day or hour   |        aw -lbw                           : Buffer busy wait for last snap 
    aw -lst -b <nn> -e <nn>    : DB stats out of AWR               |        aw -lsi -b <nn> -e <nn>           : Display metric stats history out of AWR
    aw -gn <Minutes> -id <MID> : Group by metric stat by <Minutes> |        aw -met                           : available metric (useful for aw -id <MID>)
    aw -use    : Show AWR options with licences used               |        aw -purge -b <beg_snap> -e <end_snap>    : Purge AWR snapshots
    aw -xx     : take an awr snap                                  |        aw -lb -rn <nn>                          : List baselines

Notes :   -b  <beg_snap_id> : Start snapid, use aw -l to get snaps id                             | -s <sql_id> : id of an sql as found in  v\$sql
         -e2  <end snap>    : End snap id for second period. default to b2+1                      | -rn  <nn>   : Restrict select to <nn> rows
         -wait              : change the core fields from figures  exec/buff/read to waits figures|          -v : Verbose
         -e  <end_snap_id>  : End   snapid, use aw -l to get snaps id ( if -e <nn> is omited, then it default to -b <nn +1> )
         -b2  <beg snap>    : Begin snap id for second period. Used in comparison. if it is omitted, default 
                              End snapid of first period + 1 
         -html              : Used for report to general html format rather than text format report

Example : aw -dif -html              : Take the difference of the last 2 snap period
          aw -dif -b 1500 -html      : Take the difference of the last 2 snap period
          aw -sll                    : List the most heaviest sql for the last AWR snapshot
          aw -sl -b 155 -e 160 -wait : List the most heaviest sql for period between snap_id 155 and snap_id i60
                                       if -wait is ommited, default output is exec/buff/read otherwise it is about waits
          aw -prf <sql_id>           : Generate sql_profile fro <sql_id>
          aw -s <sql_id>             : Extract SQL plan and stats for <sql_id> using last 2 snaps id
          aw -s <sql_id> -ot         : Extract SQL outlines 
          aw -s <sql_id> -ot  -pl <plan_hash_value>          : Extract SQL outlines for a given plan 
          aw -cr_sql_profile -s <sql_id>  -pl <plan_hash_value>  : Create a profile using given sql_sid an plan_hash_value
                                       this differ from -prf as you provide the set of outlines while -prf takes the one of the optimizer
EOF
exit
}
# ---------------------------------------------------------------------------
function get_snap_beg_end
{
    sqlplus -s "$CONNECT_STRING" <<EOF
column instance_number   new_value instance_number  noprint
select instance_number from   v\$instance ;
set head off verify off feed off pause off 
col beg_id new_value beg_id 
col end_id new_value end_id 

select  beg_id,end_id from (
  select beg_id,end_id,rank() over (order by rownum ) rn from (
select  rownum, snap_id beg_id, lag(snap_id,1) over (order by begin_interval_time desc ) end_id
        from
            wrm\$_snapshot where instance_number=&instance_number
        order by begin_interval_time desc)) where rn=2;
EOF
# the output of 2 rows is the return of this function
}
# ---------------------------------------------------------------------------
if [  -z "$1" ];then
   help
   exit
fi
typeset -u F_USER
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
TITTLE="All about AWR"
ROWNUM=30
EXECUTE=NO
AWR_OUTPUT_TYPE=text
REP_TYPE=DEFAULT
CATEGORY=DEFAULT

while [ -n "$1" ]
do
   case "$1" in
     -ad ) METHOD=AWR_ADV;;
    -ash ) METHOD=ASH  ;;
  -b|-b1 ) SNAP1=$2 ; shift ;;
     -b2 ) SNAP3=$2 ; shift ;;
    -bbw ) METHOD=BBW ; EXECUTE=YES ;;
    -cat ) CATEGORY=$2; shfit ;;
 -cr_sql_profile ) CR_SQL_PROFILE=TRUE ;;
    -dif ) METHOD=AWR_DIF_REPORT; EXECUTE=YES;;
  -e|-e1 ) SNAP2=$2 ; shift ;;
     -e2 ) SNAP4=$2 ; shift ;;
      -f ) METHOD=LIST_SNAP_2 ; SQL_ID=$2 ; shift ;  EXECUTE=YES ;;
     -gn ) METHOD=GN ; MET_ID=$2 ; shift ; EXECUTE=YES ;;
      -h ) help; exit ;;
   -html ) AWR_OUTPUT_TYPE=html ;;
      -k ) METHOD=SHOW_KEEP_FTS ; EXECUTE=YES; TITTLE='full table scans and counts';;
      -l ) METHOD=LIST_SNAP; EXECUTE=YES ;;
     -lb ) METHOD=LIST_BASELINE; EXECUTE=YES ;;
   -lret ) METHOD=RET; EXECUTE=YES ;;
    -lbw ) METHOD=LBW ; EXECUTE=YES ;;
   -lprf ) METHOD=LIST_ADV_PRF;
           if [ -n "$2" ];then
              SQL_PRF=$2; shift
           else 
              SQL_PRF=SQL_PRF
            fi
            EXECUTE=YES ;;
  -llprf ) METHOD=SHOW_TASK; TASK=$2; shift ;EXECUTE=YES ;;
    -lst ) METHOD=LST; EXECUTE=YES ;;
    -lsi ) METHOD=LSI; EXECUTE=YES ;;
    -met ) METHOD=MET; EXECUTE=YES ;;
    -prf ) METHOD=AWR_SQL_PROFILE ; SQL_ID=$2; shift; EXECUTE=YES;;
     -pl ) V_PLAN_HASH_VALUE=$2; shift ;;
  -purge ) METHOD=PURGE ;;
     -ot ) AW_OUTLINES=TRUE;;
      -r ) METHOD=AWR_REPORT; EXECUTE=YES;;
     -rn ) ROWNUM=$2; shift ;;
      -s ) METHOD=AWR_SQL_ID ; SQL_ID=$2; shift; EXECUTE=YES;;
    -set ) METHOD=SET
           if [ -n "$2" ];then
               DURATION=$2 
               shift
               if [ -n "$2" ];then
                   INT_MINUTES=$2
                   shift
               fi
           fi
           ;;
    -sid ) SID=$2 ; shift ;;
     -sl ) METHOD=AWR_SQL_LOAD ; EXECUTE=YES;;
    -sll ) METHOD=AWR_SQL_LOAD ; LAST_SNAP=TRUE; EXECUTE=YES;;
      -t ) METHOD=AWR_SHOW_TEXT ; SQL_ID=$2 ; shift ; EXECUTE=YES ;;
     -tr ) METHOD=SQL_TUNE     ; SQL_ID=$2; shift ; EXECUTE=YES ;;
    -use ) METHOD=AWR_USE      ; EXECUTE=YES;;
     -xx ) METHOD=GET_SNAP     ; EXECUTE=YES;;
    -xpf ) METHOD=DIF_PLAN     ; EXECUTE=YES;;
     -sp ) METHOD=SHOW_PLAN ;  PLAN_HASH_VALUE=$2 ; shift   ; EXECUTE=YES;;
   -wait ) REP_TYPE=WAITS;;
      -u ) OWNER=$2 ; shift ;;
      -x ) EXECUTE=YES ;;
      -v ) VERBOSE=TRUE ;;
       * ) echo "Invalid $1"; help ;;
   esac
   shift
done

# --------------------------------------------------------------------------
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} SYS $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi

# check that SQL_ID is not an hash_value, and if so convert it to SQL_ID
SQL_ID=`get_sql_id $SQL_ID`
# --------------------------------------------------------------------------
# show some metrics names
# --------------------------------------------------------------------------
if [ "$METHOD" = "GN" ];then
SQL="break on metric_name on report
select metric_name, begin_time, end_time, value from (
select metric_name, to_char(begin_time,'YYYY-MM-DD HH24:MI:SS')begin_time,
                         to_char(end_time,'YYYY-MM-DD HH24:MI:SS')end_time,  round(value,1) value from 
            v\$sysmetric_history where metric_id = $MET_ID order by begin_time desc)
where rownum <= $ROWNUM;"

# --------------------------------------------------------------------------
# show some metrics names
# --------------------------------------------------------------------------
elif [ "$METHOD" = "MET" ];then
SQL="col METRIC_ID head 'Metric|Id' justify c
col METRIC_NAME head 'metric name'
col METRIC_UNIT head 'metric unit'
select METRIC_ID, METRIC_NAME,METRIC_UNIT from v\$metricname;"
# --------------------------------------------------------------------------
# show some metrics  stats
# --------------------------------------------------------------------------
elif [ "$METHOD" = "LSI" ];then
  if [ -z "$SNAP1"  -a -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    VAR0=`echo $VVAR | cut -f1 -d' '`
    SNAP1=`expr $VAR0 - 30`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
  fi
    SNAP1=`expr $SNAP1 - 1`
TITTLE="List Metrics history stats from AWR"
SQL="
col begin_snap for a18
col end_snap for a22
col inst forma 9999
col snap_id for 999999
col snap_len for 99999 head 'Snap|len(s)'
col lio for 999990.0 head 'logical|read/s'
col dbc for 999990.0 head 'DB blk|chg/txn'
col respt for 999990.0 head 'Response|Time(s)/txn'
col max_respt for 999990.0 head 'Max|Response|Time(s)/txn' justify c
col phw for 999990.0 head 'Physic.|Write/s' justify c
col phr for 999990.0 head 'Physic.|Read/s' justify c
col redo for 999990.0 head 'Redo |Write/s' justify c
col srv for 999990.0 head 'Sql|Resp/s' justify c
col net for 999990.0 head 'Network|traffic/s' justify c
col execs for 999990.0 head 'Exec/s' justify c
col instance_number new_value instance_number noprint
select instance_number from v\$instance;
select a.snap_id ,
        to_char(s.BEGIN_INTERVAL_TIME,'YY-MM-DD HH24:mi:ss') begin_snap,
        round( extract( day from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME) *24*60*60*60+
                    extract( hour from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME) *60*60+
                    extract( minute from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME )* 60 +
                    extract( second from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME )) snap_len ,
         dbc, lio, respt, max_respt, phw, redo, phr,  srv, net, execs
from
      ( select snap_id, avg(dbc) dbc, avg(lio) lio, avg(respt) respt , 
                        max(respt)max_respt, avg(phw)phw,
                        avg(redo)redo, avg(phr) phr, avg(srv) srv, avg(net)net, max(execs)execs
             from 
               ( select e.snap_id,
                    case 
                        when metric_name = 'DB Block Changes Per Txn' then value
                    end dbc,
                    case 
                       when metric_name = 'Logical Reads Per Txn' then  value
                    end lio,
                    case 
                       when metric_name = 'Response Time Per Txn' then  value
                    end respt,
                    case 
                       when metric_name = 'Executions Per Sec' then  value
                    end execs,
                    case 
                       when metric_name = 'Physical Writes Per Sec' then  value
                    end phw,
                    case 
                       when metric_name = 'Redo Generated Per Sec' then  value
                    end  redo,
                    case 
                       when metric_name = 'Physical Reads Per Sec' then  value
                    end  phr,
                    case 
                       when metric_name = 'SQL Service Response Time' then  value
                    end  srv,
                    case 
                       when metric_name = 'Network Traffic Volume Per Sec' then  value
                    end  net 
                 from DBA_HIST_SYSMETRIC_HISTORY e, DBA_HIST_SNAPSHOT s
                 where s.snap_id = e.snap_id
                     and e.instance_number = s.instance_number
                     and e.instance_number = &instance_number
                     and metric_name  
                         in ( 'DB Block Changes Per Txn','Logical Reads Per Txn','Response Time Per Txn','Executions Per Sec',
                              'Physical Writes Per Sec','Redo Generated Per Sec','Physical Reads Per Sec','SQL Service Response Time',
                              'Network Traffic Volume Per Sec'
                            )
               ) group by snap_id
      )a,
      wrm\$_snapshot s
where  a.snap_id           = s.snap_id            and
       s.instance_number   = &instance_number     and
       a.snap_id between  $SNAP1 and $SNAP2 
order by a.snap_id desc
/
"
# --------------------------------------------------------------------------
# show most important system stats
# --------------------------------------------------------------------------
elif [ "$METHOD" = "LST" ];then
  if [ -z "$SNAP1"  -a -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    VAR0=`echo $VVAR | cut -f1 -d' '`
    SNAP1=`expr $VAR0 - 30`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
  fi
    SNAP1=`expr $SNAP1 - 1`
TITTLE="List system stats from AWR"
SQL="
set pages 66 lines 190
col dbtime for 999,999.99
col begin_snap for a22
col end_snap for a22
col inst forma 9999
col snap_id for 999999
col instance_number new_value instance_number noprint;
col snap_len for 99999 head 'Snap|len(s)' justify c
col bckg for 99999 head 'Background|Time' justify c
col prs for 99999 head 'Parse|Time' justify c
col HardParse for 99999 head 'Hard|Parse|Time' justify c
col PLSQLexec for 99999 head 'PL/SQL|exec time' justify c
col SQLexec for 99999.0 head 'SQL|exec time' justify c
col DBCPU for 99999.0 head 'DB|On cpu' justify c
col DBtime for 99999.0 head 'DB Time' justify c
select instance_number from v\$instance;
select a.snap_id , 
        to_char(s.BEGIN_INTERVAL_TIME,' dd Mon YYYY HH24:mi:ss') begin_snap,
        round( extract( day from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME) *24*60*60*60+
                    extract( hour from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME) *60*60+
                    extract( minute from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME )* 60 +
                    extract( second from s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME )) snap_len ,
       (dbt - lag(dbt) over (order by a.snap_id) )/1000000/60 DBtime ,
       round((dbc - lag(dbc) over (order by a.snap_id) )/1000000/60,1) DBCpu ,
       round((sqlexec - lag(sqlexec) over (order by a.snap_id) )/1000000/60,1) SQLexec ,
       round((plexec - lag(plexec) over (order by a.snap_id) )/1000000/60,1) PLSQLexec ,
       round((prs - lag(prs) over (order by a.snap_id) )/1000000/60,1) prs ,
       round((hardp - lag(hardp) over (order by a.snap_id) )/1000000/60,1) HardParse ,
       round((bckg - lag(bckg) over (order by a.snap_id) )/1000000/60,1) Bckg 
from
      ( select snap_id, max(dbt) dbt,max(dbc) dbc, max(sqlexec) sqlexec , max(plexec)plexec,
                        max(hardp)hardp, max(bckg) bckg, max(prs) prs
             from 
               ( select e.snap_id,
                    case 
                        when stat_name = 'DB time' then value
                    end dbt,
                    case 
                       when stat_name = 'DB CPU' then  value
                    end dbc,
                    case 
                       when stat_name = 'sql execute elapsed time' then  value
                    end sqlexec,
                    case 
                       when stat_name = 'PL/SQL execution elapsed time' then  value
                    end plexec,
                    case 
                       when stat_name = 'hard parse elapsed time' then  value
                    end hardp,
                    case 
                       when stat_name = 'background cpu time' then  value
                    end  bckg,
                    case 
                       when stat_name = 'parse time elapsed' then  value
                    end  prs
                 from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
                 where s.snap_id = e.snap_id
                     and e.instance_number = s.instance_number
                     and e.instance_number = &instance_number
                     and stat_name  
                         in ( 'DB time', 'DB CPU','sql execute elapsed time','PL/SQL execution elapsed time',
                               'hard parse elapsed time','background cpu time','parse time elapsed')
               ) group by snap_id
      )a,
      wrm\$_snapshot s
where  a.snap_id           = s.snap_id            and
             s.instance_number   = &instance_number     and
             a.snap_id between  $SNAP1 and $SNAP2 
order by a.snap_id desc
/
"
# --------------------------------------------------------------------------
# show sql text from dba_hist_sql_text
# --------------------------------------------------------------------------
elif [ "$METHOD" = "AWR_SHOW_TEXT" ];then
   SQL="set pages 0 long 32000
   select sql_text  from dba_hist_sqltext  where   sql_id = '$SQL_ID';"
# --------------------------------------------------------------------------
# Purge snapshots
# --------------------------------------------------------------------------
elif [ "$METHOD" = "PURGE" ];then
  if [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
  fi
SQL="EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>$SNAP1, high_snap_id=>$SNAP2);"


# --------------------------------------------------------------------------
# List Buffer busy wait
# --------------------------------------------------------------------------
elif [ "$METHOD" = "LBW" ];then
  if [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
  fi
SQL="column name format a40
set lines 130
column begin_interval_time format a15
set wrap off
  select ws.begin_interval_time,BUFFER_BUSY_WAITS_DELTA buffer_busy,
         PHYSICAL_WRITES_DELTA writes,
         ROW_LOCK_WAITS_DELTA row_locks,
         owner||'.'||object_name name
   from dba_hist_seg_stat h,
        dba_objects do ,
        sys.WRM\$_SNAPSHOT ws
where      h.SNAP_ID >= $SNAP1  and h.SNAP_ID <= $SNAP2 
      and h.BUFFER_BUSY_WAITS_DELTA>1
      and do.object_id=h.obj#
      and ws.snap_id=h.snap_id
order by BUFFER_BUSY_WAITS_DELTA
/
"

elif [ "$METHOD" = "BBW" ];then
# Adapted to AWR from a query of Tim Gorman  for statspack
NBR_DAYS=${NBR_DAYS:-1}
TITTLE="List Buffer busy wait"
SQL="clear breaks computes
break on day skip 1 on object_type on report
select
   yyyymmdd sort0,
   daily_ranking sort1,
   day,
   object_type,
   owner,
   object_name,
   buffer_busy_waits
from (select to_char(ss.startup_time, 'YYYYMMDD') yyyymmdd, to_char(ss.startup_time, 'DD-MON') day, o.object_type, o.owner,
     o.object_name, sum(s.buffer_busy_waits) buffer_busy_waits,
rank () over (partition by to_char(ss.startup_time, 'YYYYMMDD') order by sum(s.buffer_busy_waits) desc) daily_ranking
from
       ( select dbid, instance_number, dataobj#, obj#, snap_id,
              nvl(decode(greatest(buffer_busy_waits_total,
              nvl(lag(buffer_busy_waits_total) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),0)),
              buffer_busy_waits_total,
              buffer_busy_waits_total - lag(buffer_busy_waits_total) over (partition by dbid, instance_number, dataobj#, obj# order by snap_id),
              buffer_busy_waits_total), 0) buffer_busy_waits
        from DBA_HIST_SEG_STAT
        ) s,
        wrh\$_seg_stat_obj o,
        wrm\$_snapshot ss
where o.dataobj# = s.dataobj#
and o.obj# = s.obj#
and o.dbid = s.dbid
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.startup_time between (sysdate - $NBR_DAYS) and sysdate
    group by
         to_char(ss.startup_time, 'YYYYMMDD'),
         to_char(ss.startup_time, 'DD-MON'),
         o.object_type,
         o.owner,
         o.object_name
order by yyyymmdd, buffer_busy_waits)
where daily_ranking <= 10 order by sort0, sort1
/
"
# --------------------------------------------------------------------------
# List snapshots with sql id
# --------------------------------------------------------------------------
elif [ "$METHOD" = "LIST_SNAP_2" ];then
TITTLE="List snapid which contains SQL_ID=$SQL_ID"
SQL="
col sample_time format a24
select snap_id, sample_time, instance_number from (
select max(sample_time) sample_time, a.snap_id, b.instance_number from 
       WRH\$_ACTIVE_SESSION_HISTORY a,
       ( select distinct  SNAP_ID, instance_number from WRH\$_ACTIVE_SESSION_HISTORY where SQL_ID='$SQL_ID'  order by 1 desc ) b
       where a.snap_id = b.snap_id  group by a.snap_id, b.instance_number order by 1 desc )
where ROWNUM  <= $ROWNUM; "
# --------------------------------------------------------------------------
# SHOW TASK report for an SQL_PROFLE
# --------------------------------------------------------------------------
elif [ "$METHOD" = "SHOW_TASK" ];then
SQL="
SET LONG 10000 longchunksize 1000
set PAGESIZE 333 LINESIZE 1024 head off 
SELECT DBMS_SQLTUNE.report_tuning_task('$TASK') AS recommendations FROM dual;
"
# --------------------------------------------------------------------------
# List  SQL_PROFILE
# --------------------------------------------------------------------------
elif [ "$METHOD" = "LIST_ADV_PRF" ];then
SQL="col DESCRIPTION format a50
select TASK_NAME,DESCRIPTION,STATUS, LAST_MODIFIED from dbA_advisor_tasks where task_name like '${SQL_PRF}%';"

# --------------------------------------------------------------------------
# List WRH and V$SQL_PLAN different plan for same slq_id
# --------------------------------------------------------------------------
elif [ "$METHOD" = "SHOW_PLAN" ];then
if [ -n  "SNAP1" ];then
   AND_SNAP1=" and snap_id = '$SNAP1' "
fi
SQL="
COL id          FORMAT 999
COL parent_id   FORMAT 999 HEADING 'PARENT'
COL operation   FORMAT a45 heading 'Type of |Operations'
cOL object_name FORMAT a22
COL object_node FORMAT a16
COL ACCESS_PREDICATES FORMAT a35
break on snap_id on report
SELECT     snap_id,id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
           options operation, cost ,cardinality,search_columns,object_node,object_name
FROM       (
           SELECT snap_id,time,id, parent_id, operation, options, cost, cardinality,search_columns, object_node,object_name
           FROM   wrh\$_sql_plan
           WHERE  PLAN_HASH_VALUE = '$PLAN_HASH_VALUE'  $AND_SNAP_ID order by snap_id)
START WITH id = 0
CONNECT BY PRIOR id = parent_id and prior snap_id = snap_id;
"
# --------------------------------------------------------------------------
# List WRH and V$SQL_PLAN different plan for same slq_id
# --------------------------------------------------------------------------
elif [ "$METHOD" = "DIF_PLAN" ];then
if [ -n "$OWNER" ];then
   AND_OWNER=" and PARSING_SCHEMA_NAME = upper('$OWNER') "
fi
SQL="
col PARSING_SCHEMA_NAME format a18 head 'Parsed by'
col SQL_TEXT format a70
col OPV head 'Old plan|hash value' justify c
col NPV head 'New plan|hash value' justify c
select first_snap, a.sql_id, opv, npv, PARSING_SCHEMA_NAME,substr(vs.SQL_TEXT,1,70) sql_text
    from (
       select min(snap_id) first_snap ,ws.sql_id, ws.plan_hash_value opv,  s.plan_hash_value  npv,
              case
               when ws.plan_hash_value = s.plan_hash_value then 0
               when ws.plan_hash_value != s.plan_hash_value then 1
               else 2
              end  cpt_type
              from
                   wrh\$_sql_plan ws,
                   (select sql_id,plan_hash_value from v\$sql_plan group by sql_id,plan_hash_value ) s
              where
                    ws.sql_id = s.sql_id
              group by ws.sql_id, ws.plan_hash_value, s.plan_hash_value
      ) a,
      v\$sql vs
      where cpt_type = 1 and vs.sql_id=a.sql_id $AND_OWNER 
     order by sql_id;
"
# --------------------------------------------------------------------------
#  Generate an SQL PROFILE
# --------------------------------------------------------------------------
elif [ "$METHOD" = "AWR_SQL_PROFILE" ];then
  if [ -z "$SQL_ID" ];then
      echo 'No sql_id given'
      exit
  fi
  if [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
  elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
  fi
  TASK=SQL_PRF_${SQL_ID}
  TASK_DESC="SQL profile $SQL_ID snap $SNAP1 --> $SNAP2"
  FOUT=$SBIN/tmp/sql_profile_${SNAP1}_${SNAP2}.txt
  sqlplus -s $CONNECT_STRING<<EOF
set linesize 132 pagesize 333 verify off head off feed off pause off trimspool on
set serveroutput on size unlimited
DECLARE
            ret     varchar2(100);
BEGIN
  ret:=DBMS_SQLTUNE.CREATE_TUNING_TASK($SNAP1,$SNAP2,'$SQL_ID',null,DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,DBMS_SQLTUNE.TIME_LIMIT_DEFAULT, '$TASK', '$TASK_DESC' );
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || ret );
  DBMS_SQLTUNE.execute_tuning_task(task_name => '$TASK');

END;
/
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 190
SELECT DBMS_SQLTUNE.report_tuning_task('$TASK') AS recommendations FROM dual;
EOF
# --------------------------------------------------------------------------
# Run the Database advisor
# --------------------------------------------------------------------------
elif [ "$METHOD" = "AWR_ADV" ];then
  TASK=ADDM_TASK_$$
  FOUT=$SBIN/tmp/aw_addm_task_`date +%Y%m%d%H%M`.txt
  if [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
fi

sqlplus -s $CONNECT_STRING<<EOF
set linesize 132 pagesize 333 verify off head off feed off pause off trimspool on
DECLARE
            task_name VARCHAR2(30) := '$TASK';
            task_desc VARCHAR2(30) := 'ADDM snap $SNAP1 --> $SNAP2';
            task_id NUMBER;
            v_dbid number;
            v_inst number;
BEGIN
         select dbid , i.instance_number into v_dbid,v_inst from v\$database d, v\$instance i;

         dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
         dbms_advisor.set_task_parameter('$TASK', 'START_SNAPSHOT', $SNAP1);
         dbms_advisor.set_task_parameter('$TASK', 'END_SNAPSHOT', $SNAP2);
         dbms_advisor.set_task_parameter('$TASK', 'INSTANCE', v_inst);
         dbms_advisor.set_task_parameter('$TASK', 'DB_ID', v_dbid);
         dbms_advisor.execute_task('$TASK');
END;
/
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT a132
spool $FOUT
SELECT dbms_advisor.get_task_report('$TASK', 'TEXT', 'ALL') FROM   sys.dual;

EOF
 if [ -f $FOUT ];then

    if $SBINS/yesno.sh "to review the report now " DO Y
        then
          vi $FOUT
    fi

  else
     echo " Error : I did not found the report file $FOUT! "
  fi
exit
# --------------------------------------------------------------------------
# Run the AWR sql tuning advisor
# --------------------------------------------------------------------------
elif [ "$METHOD" = "SQL_TUNE" ];then
#  this  is just a wrapper on rdbms/admin/sqlrprt.sql for ease of use
TASK=SQL_${SQL_ID}_$$

sqlplus -s $CONNECT_STRING<<EOF
set serveroutput on size unlimited;
variable task_name varchar2(64);
set linesize 132 feed off
DECLARE
  cnt      NUMBER;
  bid      NUMBER;
  eid      NUMBER;
  report   varchar2(32000);
BEGIN
  -- If it's not in V$SQL we will have to query the workload repository
  select count(*) into cnt from V\$SQLSTATS where sql_id = '$SQL_ID';

  IF (cnt > 0) THEN
    :task_name := dbms_sqltune.create_tuning_task(sql_id => '$SQL_ID', task_name=>'$TASK');
  ELSE
    select min(snap_id) into bid from   dba_hist_sqlstat where  sql_id = '$SQL_ID';
    select max(snap_id) into eid from   dba_hist_sqlstat where  sql_id = '$SQL_ID'; 
    :task_name := dbms_sqltune.create_tuning_task(begin_snap => bid, end_snap => eid, sql_id => '$SQL_ID', task_name=>'$TASK');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF (SQLCODE = -13780) THEN
      dbms_output.put_line ('ERROR: statement is not in the cursor cache ' ||
                            'or the workload repository.');
      dbms_output.put_line('Execute the statement and try again');
    ELSE
      RAISE;
    END IF;
END;
/
set heading off
set long 300000
set longchunksize 1000
set linesize 132
prompt executing taks $TASK
exec dbms_sqltune.execute_tuning_task('$TASK');
prompt reporting taks $TASK
select dbms_sqltune.report_tuning_task('$TASK') from dual ;
prompt drop taks $TASK
exec  dbms_sqltune.drop_tuning_task('$TASK');
EOF
exit
# --------------------------------------------------------------------------
# Set AWR retention
# --------------------------------------------------------------------------
elif [ "$METHOD" = "AWR_SQL_LOAD" ];then
if [ "$LAST_SNAP" = "TRUE" ];then
       VAR=`get_snap_beg_end`
       VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
       SNAP_START=`echo $VVAR | cut -f2 -d' '`
fi
if [ -n "$SNAP1" ];then
    SNAP_START=$SNAP1
    if [ -z "$SNAP2" ];then
       SNAP_END=`expr $SNAP_START + 1`
    else
       SNAP_END=$SNAP2
    fi
fi

if [ -n "$SNAP_START" ];then
    AND_SNAP1=" and stat.SNAP_ID>=$SNAP_START"
    PROMPT="prompt measurement starting from snap_id $SNAP_START"
fi
if [ -n "$SNAP_END" ];then
   AND_SNAP2=" and stat.SNAP_ID<=$SNAP_END "
   PROMPT="$PROMPT and stop at $SNAP_END included"
fi
if [ "$REP_TYPE" = "DEFAULT" ];then
    TITTLE="Most expensive SQL in the workload repository"
    FIELDS_FIGURES="SQL_PROFILE, 
          sum(stat.EXECUTIONS_DELTA) EXECUTIONS_tot, sum(stat.DISK_READS_DELTA)DISK_READS_tot,
          sum(stat.BUFFER_GETS_DELTA)BUFFER_GETS_TOT,
          sum(elapsed_time_delta) / 1000000 as elapsed,
          sum(IOWAIT_DELTA+CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA)/1000000 WAIT_tot,"
    FIELDS="SQL_PROFILE,elapsed, EXECUTIONS_TOT, DISK_READS_tot,BUFFER_GETS_tot,WAIT_tot,"
    ORDER_CLAUSE=elapsed
else
    FIELDS_FIGURES="sum(elapsed_time_delta) / 1000000 as elapsed,
        sum(IOWAIT_DELTA) / 1000000 as iowait,
        sum(CLWAIT_DELTA) / 1000000 as clwait,
        sum(APWAIT_DELTA) / 1000000 as apwait,
        sum(CCWAIT_DELTA) / 1000000 as CCWAIT,
        sum(CCWAIT_DELTA+IOWAIT_DELTA+CLWAIT_DELTA+APWAIT_DELTA) / 1000000 as totwait,"
FIELDS="elapsed,totwait,iowait,clwait,apwait,CCWAIT,"
    ORDER_CLAUSE=totwait

fi
SQL="
col elapsed format 999,999,990.90 head 'Total execution|Time (sec)'
col sql_text_fragment format a55
col EXECUTIONS_TOT head 'Total|Executions' justify c
col DISK_READS_tot head 'Total|Disk reads' justify c
col BUFFER_GETS_TOT head 'Total|Buffer gets' justify c
col WAIT_tot head 'IO Wait' justify c
col APWAIT format 99999990.99 head 'Application|Wait' justify c
col IOWAIT format 99999990.99 head 'IO|Wait' justify c
col CLWAIT format 99999990.99 head 'cluster|Wait' justify c
col CCWAIT format 99999990.99 head 'Concurrency|Wait' justify c
col totwait format 99999990.99 head 'total|Wait' justify c
col SQL_PROFILE format a7 head 'sql|Profile'
set linesize 190 pagesize 66
variable newl varchar2(64);
$PROMPT
prompt Use st -i <sql_id> to see full text
set feed off
prompt 

begin
  :newl := '
';
end;
/
set termout on
select sql_id, $FIELDS
sql_text_fragment from (
 select 
     stat.sql_id as sql_id, $FIELDS_FIGURES
     (select to_char(substr(replace(st.sql_text,:newl,' '),1,55))
             from dba_hist_sqltext st where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment
 from 
     dba_hist_sqlstat stat, 
     dba_hist_sqltext text
 where stat.sql_id = text.sql_id and
       stat.dbid   = text.dbid $AND_SNAP1 $AND_SNAP2
 group by stat.dbid, stat.sql_id,SQL_PROFILE
 order by $ORDER_CLAUSE desc
) where ROWNUM <= $ROWNUM ;
"
# --------------------------------------------------------------------------
# Show AWR retention
# --------------------------------------------------------------------------
elif [ "$METHOD" = "RET" ];then
TITTLE="Retention Period for AWR repository"
SQL="
col a1 head 'Snapshot Interval| (minutes)' justify c
col a2 head 'Retention |(in minutes)' justify c
col a3 head 'Retention |(in days)' justify c
select a1,a2,a2/1440 a3 from (
 select
      extract( day from snap_interval) *24*60+
      extract( hour from snap_interval) *60+
      extract( minute from snap_interval ) a1,
      extract( day from retention) *24*60+
      extract( hour from retention) *60+
      extract( minute from retention ) a2
from dba_hist_wr_control);
"
# --------------------------------------------------------------------------
# Set AWR retention
# --------------------------------------------------------------------------
elif [ "$METHOD" = "SET" ];then
   if [ -z "$DURATION" ];then
         echo "No duration in minutes given"
         exit
   fi
   if [ -z "$INT_MINUTES" ];then
         echo "No interval in minutes given"
         exit
   fi
SQL="exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS($DURATION,$INT_MINUTES) ;"
echo "--> $SQL"
# --------------------------------------------------------------------------
# execute ash report
# --------------------------------------------------------------------------
elif [ "$METHOD" = "ASH" ];then

if [ -z "$SID" ];then
      REP_SID=ALL
else 
      REP_SID=$SID
      VAR_SID=",0,0,$SID"
fi
if [ "$AWR_OUTPUT_TYPE" = "text" ];then
    fn_name=ASH_REPORT_TEXT
    FOUT=$SBIN/tmp/ash_${REP_SID}_${ORACLE_SID}.txt
else
    fn_name=ASH_REPORT_HTML
    FOUT=$SBIN/tmp/ash_${REP_SID}_${ORACLE_SID}.html
fi
if [ -z "MIN" ];then
     MIN=30
fi
DATE1="SYSDATE-30/1440"
DATE2="SYSDATE-1/1440"
   #cat  <<EOF
   sqlplus -s "$CONNECT_STRING" <<EOF
column inst_num  new_value inst_num  noprint ;
column dbid      new_value dbid      noprint ;
column logon_time      new_value logon_time      noprint ;
column now_t      new_value now_t      noprint ;
select dbid , i.instance_number inst_num from v\$database d, v\$instance i;
set linesize 132 pagesize 333 verify off head off feed off pause off 
select logon_time,sysdate now_t from v\$session where sid = '$SID';
spool $FOUT

set lines 130
select * from table(dbms_workload_repository.$fn_name(  &dbid, &inst_num, $DATE1, $DATE2 $VAR_SID));
spool off
EOF
#DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
#   l_dbid          IN NUMBER,
#   l_inst_num      IN NUMBER,
#   l_btime         IN DATE,
#   l_etime         IN DATE,
#   l_options       IN NUMBER    DEFAULT 0,
#   l_slot_width    IN NUMBER    DEFAULT 0,
#   l_sid           IN NUMBER    DEFAULT NULL,
#   l_sql_id        IN VARCHAR2  DEFAULT NULL,
#   l_wait_class    IN VARCHAR2  DEFAULT NULL,
#   l_service_hash  IN NUMBER    DEFAULT NULL,
#   l_module        IN VARCHAR2  DEFAULT NULL,
#   l_action        IN VARCHAR2  DEFAULT NULL,
#   l_client_id     IN VARCHAR2  DEFAULT NULL)
# RETURN awrrpt_text_type_table PIPELINED;

exit
# --------------------------------------------------------------------------
# view outlines and plan for one sqlid
# --------------------------------------------------------------------------
# view outlines and plan for one sqlid
# --------------------------------------------------------------------------
elif [ "$METHOD" = "AWR_SQL_ID" ];then
   # --------------------------------------------------------------------------
   # Author  : Randolf Geist at http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
   # Adapted to Smenu by bpa
   # --------------------------------------------------------------------------
   if [ -n "$CR_SQL_PROFILE" ];then
     if [  -z "$SQL_ID"   ];then
          echo "I need an SQL ID"
          exit
     fi
     if [ -z "$V_PLAN_HASH_VALUE" ];then
            echo "Please provide an plan_hash_value as new plan for SQL_ID $SQL_ID"
            echo "Use: \"aw -s <SQL_ID> -ot\"   to check the available plans"
            exit
     fi 
     SQL="
declare
      ar_profile_hints sys.sqlprof_attr;  
      cl_sql_text      clob;
begin
     select  extractvalue(value(d), '/hint') as outline_hints  bulk collect  into ar_profile_hints
                   from  xmltable('/*/outline_data/hint' passing
                    ( select xmltype(other_xml) as xmlval
                                    from
                                         dba_hist_sql_plan
                                    where
                                         sql_id = '$SQL_ID'
                                    and  plan_hash_value = $V_PLAN_HASH_VALUE
                                    and  other_xml is not null
                            )
                     ) d;
     select sql_text  into cl_sql_text  from dba_hist_sqltext  where sql_id = '$SQL_ID';  
     dbms_sqltune.import_sql_profile(    sql_text     => cl_sql_text, 
                                         profile      => ar_profile_hints, 
                                         category    => '$CATEGORY'  , 
                                         name => 'PROFILE_$SQL_ID'  ,
                                         force_match => true  );     
end;
/
"
    elif [ -n "$AW_OUTLINES"   ];then
         if [ -n "$V_PLAN_HASH_VALUE" ];then
            AND_PLAN_HASH_VALUE=" and plan_hash_value = $V_PLAN_HASH_VALUE "
         fi 
         EXECUTE=YES
         SQL="set linesize 250 pagesize 333 verify off head off feed off pause off 
set serveroutput on size unlimited
          variable id_plan number ;
          declare
             v_id_plan   number ;
             v_cpt_plan  number ;
          begin
          select min(plan_hash_value) , count(1) into v_id_plan , v_cpt_plan
                  from dba_hist_sql_plan where sql_id='$SQL_ID' and id = 0 $AND_PLAN_HASH_VALUE;
          dbms_output.put_line( ' number of plans : ' || to_char(v_cpt_plan)  || ' First :' || to_char(v_id_plan) );
          if  v_cpt_plan > 1 then
              dbms_output.put_line('List of all plan_hash_value:');
              for c in (select plan_hash_value from dba_hist_sql_plan where sql_id='$SQL_ID' and id = 0 )
              loop
                  dbms_output.put_line('.       '||to_char(c.plan_hash_value) );
              end loop;
          end if;
          :id_plan:=v_id_plan ;
          end ;
/
prompt
select  extractvalue(value(d), '/hint') as outline_hints  
                   from  xmltable('/*/outline_data/hint' passing 
                    ( select xmltype(other_xml) as xmlval 
                                    from 
                                         dba_hist_sql_plan 
                                    where 
                                         sql_id = '$SQL_ID'  
                                    and  plan_hash_value = :id_plan
                                    and  other_xml is not null   
                            )          
                     ) d;
"
     else # We want to extract the sql plan and info
        if [ -z "$SNAP1" ];then
            VAR=`get_snap_beg_end`
            VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
            SNAP1=`echo $VVAR | cut -f1 -d' '`
            SNAP2=`echo $VVAR | cut -f2 -d' '`
        elif [ -z "$SNAP2" ];then
            echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
            SNAP2=`expr $SNAP1 + 1`
            echo "Using default -b <nn> + 1> --> $SNAP2"
        fi 

        # at this stage, we have a value for BEG snap and END snap
        if [ "$AWR_OUTPUT_TYPE" = "text" ];then
             fn_name=awr_sql_report_text
             FOUT=$SBIN/tmp/sql_sid_${SQL_ID}_${ORACLE_SID}_${SNAP1}_${SNAP2}.txt
        else
             fn_name=awr_sql_report_html
             FOUT=$SBIN/tmp/sql_sid_${SQL_ID}_${ORACLE_SID}_${SNAP1}_${SNAP2}.html
        fi

       sqlplus -s "$CONNECT_STRING" <<EOF
column inst_num  new_value inst_num  noprint ;
column dbid      new_value dbid      noprint ;
select dbid , i.instance_number inst_num from v\$database d, v\$instance i;
set linesize 132 pagesize 333 verify off head off feed off pause off 

spool $FOUT
select output from table(dbms_workload_repository.$fn_name( &dbid, &inst_num, $SNAP1, $SNAP2, '$SQL_ID', 8 ));
spool off
EOF

        if [ "$AWR_OUTPUT_TYPE" = "text" ];then
            if [ -f $FOUT ];then
               if $SBINS/yesno.sh "to review the report now " DO Y
                   then
                      vi $FOUT
               fi
            else
                echo " Error : I did not found the report file $FOUT! "
            fi
        else
            if [ -f $FOUT ];then
                  echo "Report done :"
                  ls -l $FOUT
            else
                   echo " Error : I did not found the report file $FOUT! "
            fi
        fi
        exit
     fi

# --------------------------------------------------------------------------
# execute one snap
# --------------------------------------------------------------------------
elif [ "$METHOD" = "GET_SNAP" ];then
     echo "Doing : execute dbms_workload_repository.create_snapshot"
     SQL="execute dbms_workload_repository.create_snapshot ;"
elif [ "$METHOD" = "SHOW_KEEP_FTS" ];then
  if [ -z "$SNAP1" ];then
     VAR=`get_snap_beg_end`
     VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
     SNAP1=`echo $VVAR | cut -f1 -d' '`
     SNAP2=`echo $VVAR | cut -f2 -d' '`
elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
fi


SQL="prompt The 'K' indicates that the table is in the KEEP Pool.'
select 
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   p.owner, 
   p.name, 
   t.num_rows,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions_delta) nbr_FTS
from 
   dba_tables   t,
   dba_segments s,
   dba_hist_sqlstat    a,
   dba_hist_snapshot sn,
   (select distinct 
     pl.sql_id,
     object_owner owner, 
     object_name name
   from 
      dba_hist_sql_plan pl
   where 
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
        a.snap_id = sn.snap_id 
   and  a.sql_id = p.sql_id
   and t.owner = s.owner
   and t.table_name = s.segment_name
   and t.table_name = p.name
   and t.owner = p.owner
   and t.owner not in ('SYS','SYSTEM') and sn.snap_id >= $SNAP1 and sn.snap_id <= $SNAP2
having
   sum(a.executions_delta) > 1
group by 
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by 1 asc;
"
elif [ "$METHOD" = "AWR_DIF_REPORT" ];then

if [ -z "$SNAP1" ];then
    VAR0=`get_snap_beg_end`
    VVAR=`echo $VAR0 | sed '/^$/d' |  cut -f1 -d':'`
    VAR=`echo $VVAR | cut -f1 -d' '`         # we take the last 2 and substract 1 for each value to leave room for the next 2
    SNAP1=`expr $VAR - 1`
    SNAP2=`expr $VAR `
    SNAP3=`expr $VAR `
    SNAP4=`expr $VAR + 1`
elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
fi
if [ -z "$SNAP3" ];then
      SNAP3=$SNAP2
fi
if [ -z "$SNAP4" ];then
      SNAP4=`expr $SNAP3 + 1`
fi
# at this stage, we have a value for BEG snap and END snap
if [ "$AWR_OUTPUT_TYPE" = "text" ];then
    FOUT=$SBIN/tmp/awr_diff_report_${ORACLE_SID}_${SNAP1}_${SNAP3}.txt
    fn_name=AWR_DIFF_REPORT_TEXT
else
    FOUT=$SBIN/tmp/awr_diff_report_${ORACLE_SID}_${SNAP1}_${SNAP3}.html
    fn_name=AWR_DIFF_REPORT_HTML 
fi

   #cat <<EOF
   sqlplus -s "$CONNECT_STRING" <<EOF
column inst_num  new_value inst_num  noprint ;
column dbid      new_value dbid      noprint ;
select dbid , i.instance_number inst_num from v\$database d, v\$instance i;
set linesize 250 pagesize 333 verify off head off feed off pause off

spool $FOUT
select output from table(dbms_workload_repository.$fn_name( &dbid, &inst_num, $SNAP1, $SNAP2, &dbid, &inst_num, $SNAP3, $SNAP4 ));
spool off
EOF
exit

if [ -f $FOUT ];then
    if $SBINS/yesno.sh "to review the report now " DO Y
        then
          vi $FOUT
    fi
else
   echo " Error : did not find the report $FOUT"
fi


elif [ "$METHOD" = "AWR_REPORT" ];then

if [ -z "$SNAP1" ];then
    VAR=`get_snap_beg_end`
    VVAR=`echo $VAR | sed '/^$/d' |  cut -f1 -d':'`
    SNAP1=`echo $VVAR | cut -f1 -d' '`
    SNAP2=`echo $VVAR | cut -f2 -d' '`
elif [ -z "$SNAP2" ];then
     echo "Value of SNAP1=$SNAP1, but no value given for end snap. "
     SNAP2=`expr $SNAP1 + 1`
     echo "Using default -b <nn> + 1> --> $SNAP2"
fi 
# at this stage, we have a value for BEG snap and END snap
if [ "$AWR_OUTPUT_TYPE" = "text" ];then
    FOUT=$SBIN/tmp/awr_report_${ORACLE_SID}_${SNAP1}_${SNAP2}.txt
    fn_name=AWR_REPORT_TEXT
else
    FOUT=$SBIN/tmp/awr_report_${ORACLE_SID}_${SNAP1}_${SNAP2}.html
    fn_name=AWR_REPORT_HTML
fi

   sqlplus -s "$CONNECT_STRING" <<EOF
column inst_num  new_value inst_num  noprint ;
column dbid      new_value dbid      noprint ;
select dbid , i.instance_number inst_num from v\$database d, v\$instance i;
set linesize 190 pagesize 333 verify off head off feed off pause off  trimspool on

spool $FOUT
select output from table(dbms_workload_repository.$fn_name( &dbid, &inst_num, $SNAP1, $SNAP2,  8 ));
spool off
EOF


if [ -f $FOUT ];then
    if $SBINS/yesno.sh "to review the report now " DO Y
        then
          vi $FOUT
    fi
else
   echo " Error : did not find the report $FOUT"
fi


elif [ "$METHOD" = "LIST_BASELINE" ];then
SQL="
col BASELINE_NAME format a30
select BASELINE_ID,BASELINE_NAME, START_SNAP_ID, START_SNAP_TIME, END_SNAP_ID, END_SNAP_TIME
  from (
select BASELINE_ID,BASELINE_NAME, START_SNAP_ID, to_char(START_SNAP_TIME,'YYYY-MM-DD HH24:MI:SS')START_SNAP_TIME, 
       END_SNAP_ID, to_char(END_SNAP_TIME,'YYYY-MM-DD HH24:MI:SS')END_SNAP_TIME
       from dba_hist_baseline
) where rownum<=$ROWNUM;
"
elif [ "$METHOD" = "LIST_SNAP" ];then
SQL="
column inst_num   new_value inst_num  noprint
column dbid       new_value dbid      noprint
column dbname     new_value dbname    noprint

select d.dbid   dbid , to_char(i.instance_number) inst_num, name dbname from v\$database d, v\$instance i;
prompt
prompt
prompt Snapshots for &dbname  instance : &inst_num
prompt ==========================================
prompt

select snap_id
     , snap_level            
     , to_char(BEGIN_INTERVAL_TIME,' dd Mon YYYY HH24:mi:ss')    snap_begin
     , to_char(END_INTERVAL_TIME,' dd Mon YYYY HH24:mi:ss')    snap_end 
  from (
select s.snap_id
     , s.snap_level
     , s.BEGIN_INTERVAL_TIME
     , s.END_INTERVAL_TIME
  from wrm\$_snapshot s
  where s.instance_number   = &inst_num and s.dbid=&dbid
  order by   snap_id desc 
) where rownum <=$ROWNUM
/
"
elif [ "$METHOD" = "AWR_USE" ];then
SQL="select NAME, DETECTED_USAGES, to_char(LAST_USAGE_DATE,'DD-MM-YYYY HH24:MI:SS') last_usage, 
     DESCRIPTION from SYS.DBA_FEATURE_USAGE_STATISTICS where DETECTED_USAGES > 0 and name in ('Automatic Database Diagnostic Monitor',
'Automatic Workload Repository','Segment Advisor','SQL Tuning Advisor');"


fi

if [ "$EXECUTE" = "NO" ];then
   echo "\n\nAdd -x to execute. This command wil then be executed:\n"
   echo "$SQL"
   echo
   exit
fi
if [ -n "$VERBOSE" ];then
   echo "$SQL"
fi
# ................................
# we do the job here
# ................................
sqlplus -s "$CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 150 termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline,
       '$TITTLE  ' nline
from sys.dual
/

break on task_name on command
set head on
col owner format a18
col name format a30
col version format a10
col detected_usages format 999999 head "Detected|usages" justify c
col description format a85
$SQL
EOF