#!/usr/bin/ksh
# Program  :  smenu_get_heavy_figures.ksh
# author   :  B. Polarski
# date     :  09 September 2005
# Modified :  21 September 2005 : Added join to V$process to enforce display of only active SQL with option -x
#             25 september 2006 : added -load option extracted from some work of Tim Gorman
#             04 October   2006 : added option -sp to load and the notion of family sql for sql without bind variables
#             28 May       2009 : Added option -n
#             29 November  2010 : add GV, -x renamed to -ses, help revisited. addded sqlid_to_hv
# set -x
# default conditional fields
ROWNUM="where rownum <31"
ELP="ELAPSED_TIME/1000000 elp, "
ELP0="elp,"
ORDER=" "
TITLE='SQL Work area Usefull figures (Help: sq -h)'
HASH_OR_TEXT="hash_value,child_number,parsing_schema_name"
HT_PARSING=",parsing_schema_name"
HT_HEADER="hash_value,child_number"

F_TIME=" substr(LAST_LOAD_TIME,9) ltl"
#F_TIME=" LAST_LOAD_TIME ltl"
F_TIME_TITLE="Last time| Loaded"
# ----------------------------------------------------------------------------------------------------------------
function sqlid_to_hv
{
. $SBIN/scripts/passwd.env
. ${GET_PASSWD}
if [  -z "$CONNECT_STRING"  ];then
      echo "could no get a the password of $S_USER"
      exit 0
fi
 ret=`sqlplus -s "$CONNECT_STRING" <<EOF
 set head off pagesize 0 feed off verify off
 select trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('$SQL_ID')),level,1))-1)
        *power(32,length(trim('$SQL_ID'))-level)),power(2,32))) hash_value
     from dual connect by level <= length(trim('$SQL_ID'));
EOF`
 echo "$ret"
}
# ----------------------------------------------------------------------------------------------------------------
function get_sql_id
{
 if [ -z "$1" ] ;then
     # if sql_id is empty then try hash_value
     if [ -z "$HASH_VALUE" ];then
         VAR=$HASH_VALUE
     else
         echo "I need an SQL_ID or HASH_VALUE"
         exit
     fi
 else
   VAR="$1"
 fi
 if [ -z "${VAR%%*[a-z]*}" ];then
    # $1 is a mix
    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 = '$VAR';
EOF`
 echo "$ret"
}
# ----------------------------------------------------------------------------------------------------------------
function get_hash_value
{
 if [ -n "${1%%*[a-z]*}" ];then
    # $1 is only digit
    echo "$1"
    return
 fi
 SQL_ID=$1
 ret=`sqlid_to_hv $SQL_ID`
 echo "$ret"
}
# ----------------------------------------------------------------------------------------------------------------
function help
{
  cat <<EOF

    Miscelaneous SQL stats :


            sq -m [seconds] [-u <owner>][io]         #  list SQL that were active within last <seconds>
            sq -top <nn> [-ngets <nn>]               #  List top active sql for the last <nn> secs, default sort, gets &  ngets=100
            sq -ses                                  #  List SQL that can be joined to sessions

            sq -hv <SQL_HASH_VALUE>                  #  see only stat for this sql, usefull if you are given only HV

            sq -pl                                   #  show sql with difference execution time
            sq -pv  [ -day <nn>]                     #  Show sql with execution speed variations, limit to last nn days, default is 1

            sq -ph <sqlid> [-d <DBID>]               #  List sql history executions plan and perfs  
            sq -pb <sqlid> [-d <DBID>]               #  List binds for SQL

            sq -hd                                   #  List sql load sort by disk reads   :   Sql load from Ixora
            sq -hg                                   #  List sql load sort by disk gets    :   Sql load from Ixora
            sq -load -sp len <nn> -rn <nn>           #  SQL summary stats from v\$sqlarea 
                                                          -sp  : display a sample of this family hash_value 
                                                          -len : length of text used to build family

                                             -------------------
                                             |      sq -l      |
                                             -------------------

   sort by :      -b  : buffer_gets         -c  : cpu               -d : disk_reads    -e  : elapsed     -inv : invalidations
                -pars :  parse calls        -t  : last time loaded  -x : executions    -w  : rows   

    -g   : figures for all execs, not per execution                 -rn : Limit display to <nn> rows                      
    -ses : Limit to SQL that may be linked to active sessions        -v : Verbose
    -pk  : list session running PL/SQL package                    -text : Show sql_text rather than hash value
   -min  : limit to <nnn> disk reads/gets, default is 10 000       -len : <len text> 

                               example : sq -l | sq -l -ses   | sq -t -ses |  sq -d  |  sq -l -g
                                         sq -top -x -ngets 1000   # top sql with at least 1000 gets sorted by executions

EOF
exit
}
# ----------------------------------------------------------------------------------------------------------------
VARFIELD=SQL_TEXT
LEN_TEXT=50
if [ -z "$1" ];then
   help
fi
ACTION=DEFAULT
typeset -u fowner
while [ -n "$1" ]
do
      case $1 in
          -b ) ORDER=" ORDER by buff desc"  ;;
          -c ) ORDER=" ORDER by cpu desc" ; SORT_COL=elapsed;;
          -d ) ORDER=" ORDER by disk_reads desc" ; SORT_COL=reads ;;
          -x ) ORDER=" ORDER by executions desc" ; SORT_COL=execs;;
          -e ) ORDER=" ORDER by elapsed_time desc" ; SORT_COL=elapsed;;
       -dbid ) DBID=$2 ; shift  ; AND_DBID=" and s.dbid='$DBID' " ;;
        -day ) days_ago=$2 ; shift ;;
          -g ) TOT_G=TRUE ;;
         -hd ) ACTION=HEAVY ; FIELD=disk_reads; FIELD1=buffer_gets;;
         -hg ) ACTION=HEAVY ; FIELD=buffer_gets ; FIELD1=disk_reads;;
         -hv ) WHERE=" where 1=1 " ; HV=`get_hash_value $2`
               FILTER1="  and hash_value = $HV " ; unset ELP ; unset ELP0 ;;
        -inv ) ORDER=" ORDER by invalidations desc" ; unset ELP ; unset ELP0;;
         -io ) SHOW_IO=TRUE;;
          -l ) ACTION=DEFAULT ;;
        -len ) LEN_TEXT=$2; shift ; GROUP_BY="substr(sql_text, 1, $LEN_TEXT)" ;;
       -load ) ACTION=LOAD ; GROUP_BY="substr(sql_text, 1, $LEN_TEXT)" ;;
        -min ) MIN_PRES=$2;shift;;
          -m ) ACTION=LAST_SQL;
               if [ -n "$2" -a ! "$2" = "-u" -a ! "$2" = "-io" ];then
                    LAST_SEC=$2; shift
                fi;;
       -ngets) NGETS=$2; shift ;;
       -pars ) ORDER=" ORDER by parse_calls desc" ; unset ELP ; unset ELP0;;
         -pl ) ACTION=UNSTABLE ;;
         -pv ) ACTION=EXEC_VAR_SPEED ;;
         -pb ) ACTION=MIS_BIND;
               if [ -z "$2" ];then
                   echo "I need an sql id"
                   exit
               fi
               SQL_ID=$2; shift ;;
         -ph ) ACTION=PLH ;
               if [ -z "$2" ];then
                   echo "I need an sql id"
                   exit
               fi
               SQL_ID=$2; shift;;
         -pk ) ACTION=PLSQL ;;
         -rac) G=g; INST_ID=inst_id, ;;
         -rn ) ROWNUM="where rownum <=$2" ; NROWNUM=$2 ; shift ;;
       -text ) HASH_OR_TEXT=sql_text ; HT_HEADER=SQL_TEXT; unset HT_PARSING;;
          -t ) ORDER=" ORDER by last_load_time desc" ; FILTER=" and LAST_LOAD_TIME is not null" 
               WHERE=" where 1=1 " ; TITLE="Sort by last time loaded" ;;
        -top ) ACTION=TOP ; NBR_SECS=$2; shift ;;
        -ses ) JOIN_TO_SESS=TRUE;;
         -sp ) FAMILLY=TRUE;;
          -w ) ORDER=" ORDER by rows_processed desc" ;;
          -u ) fowner=$2 ; FILTERN=" and PARSING_SCHEMA_NAME = upper('$2') " 
               shift ; WHERE=" where 1=1 " ;;
          -h ) help ;;
          -v ) SETXV="set -xv";;
            *) SINGLE_SID=" and s.sid = '$1' " ;;
        esac
        shift
done
if [ -z "$ACTION" ];then
      ACTION="DEFAULT"
fi
if [ "$JOIN_TO_SESS" = "TRUE" ];then
         JOIN_TO_SESS="  , sys.${G}v_\$session b, sys.${G}v_\$process c " ; WHERE=" where 1=1 " ;
         FILTER1=" and (hash_value = sql_hash_value or hash_value = prev_hash_value) and b.paddr = c.addr" ; SID="sid,"
fi
if [ "$HASH_OR_TEXT" = "sql_text" ];then
      HASH_OR_TEXT="substr(sql_text, 1, $LEN_TEXT) sql_text"
fi
if [ "$FAMILLY" = "TRUE" ];then
     VARFIELD="upper(substr(replace(replace(replace(replace(sql_text,' ',''),',',''),'\"',''),'*',''),1,$LEN_TEXT)) family,family_hv"
     GROUP_BY="upper(substr(replace(replace(replace(replace(sql_text,' ',''),',',''),'\"',''),'*',''),1,$LEN_TEXT)),substr(sql_text,1,$LEN_TEXT)"
fi
if [ -n "$WHERE" ];then
   if [ -n "$FILTER" -a -n "$FILTER1" ];then
      if [ -n "$JOIN_TO_SESS" ];then
         # if we linked -x and -t then we want the sql active and use last_call_et from v$session
         F_TIME="to_char(b.last_call_et) ltl"
         F_TIME_TITLE="Sql run|Since(sec) "
      fi
   fi
   ADD_WHERE="${WHERE}$FILTER$FILTERN"
fi
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
SBINS=$SBIN/scripts

if [ "$TOT_G" = TRUE ];then
        TITLE="sum gets and rows for all executions"
        BUF_GET="BUFFER_GETS buff, "
        ROW_PROC="ROWS_PROCESSED rp,"
        ROW_PROC_TITLE="Total |Rows"
        BUF_GET_TITLE=" Total|Buff Gets"
else
        TITLE="Sql stats per execution"
        BUF_GET="BUFFER_GETS/decode(executions,0,1,executions) buff, "
        ROW_PROC="ROWS_PROCESSED/decode(executions,0,1,executions) rp,"
        ROW_PROC_TITLE="Rows per|Exec"
        BUF_GET_TITLE="Buff Gets|Per Exec"
fi

. $SBIN/scripts/passwd.env
. ${GET_PASSWD}
if [  -z "$CONNECT_STRING"  ];then
      echo "could no get a the password of $S_USER"
      exit 0
fi
# .........................
# Top sql for last n seconds
# default sorting on gets
# .........................
if [ "$ACTION" = "TOP" ];then
FSQL_LEN=${LEN_TEXT:-50}
NBR_SECS=${NBR_SECS:-1}
SORT_COL=${SORT_COL:-gets}
NROWNUM=${NROWNUM:-24}
NGETS=${NGETS:-100}
TITLE=" Top $NROWNUM sql sampled $NBR_SECS seconds, sort by $SORT_COL"
SQL="
   set linesize 190 pagesize 333 feed off head off
   set serveroutput on size 999999

declare

   type  rec_type is record (
         name       varchar2(18),
         hash_value number,
         child      number ,  
         execs      number,
         gets       number,
         reads      number,
         writes     number,
         elapsed    number,
         app_wait   number,
         io_wait    number,
         conc_wait  number,
         fsql        varchar2($FSQL_LEN)
    );
   type  rec_sort is record (
         hash_key varchar2(30),
         value   number ) ;

    type TC is table of REC_TYPE index by  varchar2(30);
    type TC_sort is table of REC_SORT index by  binary_integer ;

    thv1 TC ; 
    thv2 TC ; 
    th_sort TC_sort;
    th_res  TC;
    v_var   varchar2(30);
    v_int   number ;
    v_res   rec_type ; 
    v       rec_type ; 
    v0      rec_sort;
    cpt     number:=0;
    rownum  number:=0;
    ------------------------------------------------------------------------------------------------------
    procedure load_data (p_thv IN OUT tc) is
       v_rec   rec_type ; 
    begin
       for c1 in ( select substr(parsing_schema_name,1,18) name,hash_value, child_number,
                          executions, buffer_gets, disk_reads, direct_writes, 
                          round(decode(elapsed_time,0,0,elapsed_time/1000)) elapsed_time,
                          round(decode(application_wait_time,0,0,application_wait_time/1000)) application_wait_time, 
                          round(decode(user_io_wait_time,0,0,user_io_wait_time/1000)) user_io_wait_time, 
                          round(decode(concurrency_wait_time,0,0,concurrency_wait_time/1000))concurrency_wait_time, 
                          substr(sql_text,1,$FSQL_LEN) fsql
                  from sys.${G}v_\$sql where buffer_gets > $NGETS )
      loop
          -- dbms_output.put_line('hash_value=' ||to_char(c1.hash_value)|| ' c=' 
          --      ||to_char(c1.child_number) || ' execs=' ||to_char(c1.executions)  );
          v_rec.name:=c1.name;
          v_rec.hash_value:=c1.hash_value ;
          v_rec.child:=c1.child_number ;
          v_rec.execs:=c1.executions ;
          v_rec.gets:=c1.buffer_gets;
          v_rec.reads:=c1.disk_reads;
          v_rec.writes:=c1.direct_writes;
          v_rec.elapsed:=c1.elapsed_time;
          v_rec.app_wait:=c1.application_wait_time;
          v_rec.io_wait:=c1.user_io_wait_time;
          v_rec.conc_wait:=c1.concurrency_wait_time;
          v_rec.fsql:=c1.fsql;
          p_thv(to_char(c1.hash_value)||'_'||to_char(c1.child_number) ):=v_rec;
       end loop ;
    end ;
    ------------------------------------------------------------------------------------------------------
    FUNCTION  transfer_delta(p1 IN rec_type, p2 IN rec_type )   RETURN rec_type IS
            res rec_type;
    BEGIN
            res.hash_value:=p2.hash_value;
            res.child:=p2.child;
            res.name:=p2.name;
            res.fsql:=p2.fsql;
          if p2.execs is not null and p1.execs is not null  and p2.execs > p1.execs then
             res.execs:=p2.execs - p1.execs ;
          else
             res.execs:=0;
          end if;
          if p2.gets is not null   and p1.gets is not null   and p2.gets > p1.gets then
             res.gets:=p2.gets - p1.gets ;
          else
             res.gets:=0;
          end if;
          if p2.reads is not null   and p1.reads is not null   and p2.reads > p1.reads then
             res.reads:=p2.reads - p1.reads ;
          else
             res.reads:=0;
          end if;
          if p2.writes is not null   and p1.writes is not null   and p2.writes > p1.writes then
             res.writes:=p2.writes - p1.writes ;
          else
             res.writes:=0;
          end if;
          if p2.elapsed is not null   and p1.elapsed is not null   and p2.elapsed > p1.elapsed then
             res.elapsed:=p2.elapsed - p1.elapsed ;
          else
             res.elapsed:=0;
          end if;
          if p2.app_wait is not null   and p1.app_wait is not null   and p2.app_wait > p1.app_wait then
             res.app_wait:=p2.app_wait - p1.app_wait ;
          else
             res.app_wait:=0;
          end if;
          if p2.io_wait is not null   and p1.io_wait is not null   and p2.io_wait > p1.io_wait then
             res.io_wait:=p2.io_wait - p1.io_wait ;
          else
             res.io_wait:=0;
          end if;
          if p2.conc_wait is not null   and p1.conc_wait is not null   and p2.conc_wait > p1.conc_wait then
             res.conc_wait:=p2.conc_wait - p1.conc_wait ;
          else
             res.conc_wait:=0;
          end if;
          return res;
    END ;
    ------------------------------------------------------------------------------------------------------

begin
   load_data(thv1);
   dbms_lock.sleep($NBR_SECS);
   load_data(thv2);

   -- read and compare the 2 datasets. At first cpt is still :=0
   v_var:=thv2.FIRST ; 
   if  thv1.exists(v_var) then
        v:=transfer_delta(thv1(v_var), thv2(v_var) );
        if v.gets > 0  then
           th_sort(cpt).hash_key:=v_var ;
           th_sort(cpt).value:=v.$SORT_COL ;
           th_res(v_var):=v;                           -- we store the delta
        end if;
   else
        dbms_output.put_line( 'Did not find :=' ||v_var ||'  in thv1 ! ') ; 
   end if;
   while v_var is not null
   LOOP
        v_var:=thv2.next(v_var) ;  
        if v_var is not null then 
           if thv1.exists(v_var) then
              -- dbms_output.put_line( 'hash_value exists in thv1 : ' ||v_var) ; 
              v:=transfer_delta(thv1(v_var), thv2(v_var) );
              if v.gets > 0  then
                 cpt:=cpt+1;
                 th_sort(cpt).hash_key:=v_var ;
                 th_sort(cpt).value:=v.$SORT_COL ;           -- this will define the sort order 
                 th_res(v_var):=v;                           -- we store the delta
             end if;
          else
              dbms_output.put_line( 'Did not find ' ||v_var ||'  in thv1 ! ') ; 
          end if;
       end if ;
   end loop;
   dbms_output.put_line('Total number of SQL considered : ' ||to_char(thv1.count)  || ' --> actives : ' ||to_char(th_res.count) );
   v_int:=th_sort.count ;
   if v_int > 0 then
      -- dbms_output.put_line('there is ' || to_Char(v_int) || ' elements in th_sort' );
      -- good old buble. one day should be less lazy an improve this
      for i in 0..th_sort.last
      loop
        if th_sort.exists(i) then
           for j in 1..th_sort.last
           loop
              if th_sort.exists(j) then
                 if th_sort(j).value < th_sort(i).value then
                    v0:=th_sort(i);
                    th_sort(i):=th_sort(j);
                    th_sort(j):=v0;
                  end if;
               end if;
               rownum:=rownum+1;
               exit when rownum = $NROWNUM;
            end loop;
        end if;
      end loop;
   else
      dbms_output.put_line('No relevant activity found in the elapsed time.' );
   end if;
   DBMS_OUTPUT.PUT_LINE('.                                                                      Elapse App/w   IO/w   Conc/w');
   DBMS_OUTPUT.PUT_LINE(' Owner               Hash_value  C#   execs    Gets    reads   d.Writes  (ms)   (ms)   (ms)    (ms)   SQL text');
   DBMS_OUTPUT.PUT_LINE(' ------------------- ----------- ---  ------ -------- -------- -------- ------ ------ ------- ------'||rpad(' ',$FSQL_LEN+1,'-') );
 
   v_int:=th_sort.FIRST ; 
   if  th_sort.exists(v_int) then
       v_var:=th_sort(v_int).hash_key ;
        v:=th_res(v_var) ;
        dbms_output.put_line( rpad(v.name,19)|| ' '|| to_char(rpad(v.hash_value,12))|| ' '|| to_char(rpad(v.child,3))|| ' ' || 
            to_char(lpad(v.execs,6))|| ' ' || to_char(lpad(v.gets,8) ) || ' ' || to_char(lpad(v.reads,8)) || ' '
            || to_char(lpad(v.writes,8))|| to_char(lpad(v.elapsed,7))||
            to_char(lpad(v.app_wait,7))|| ' ' || to_char(lpad(v.io_wait,7))|| to_char(lpad(v.conc_wait,7))||' '|| v.fsql );
   end if ;
   while v_int is not null
   loop
      v_int:=th_sort.next(v_int);
      if (v_int) is not null then
          v_var:=th_sort(v_int).hash_key ;
          v:=th_res(v_var) ;
          dbms_output.put_line( rpad(v.name,19)|| ' '|| to_char(rpad(v.hash_value,12))|| ' '|| to_char(rpad(v.child,3))|| ' ' || 
            to_char(lpad(v.execs,6))|| ' ' || to_char(lpad(v.gets,8) ) || ' ' || to_char(lpad(v.reads,8)) || ' '
            || to_char(lpad(v.writes,8))|| to_char(lpad(v.elapsed,7))||
            to_char(lpad(v.app_wait,7))|| ' ' || to_char(lpad(v.io_wait,7))|| to_char(lpad(v.conc_wait,7))||' '|| v.fsql );
      end if; 
   end loop;
end;
/
"
#EOF

# .........................
# last run sql
# .........................
elif [ "$ACTION" = "LAST_SQL" ];then
   if [ -n "$fowner" ];then
       AND_OWNER=" and PARSING_SCHEMA_NAME = '$fowner' "
   fi
   LAST_SEC=${LAST_SEC:-60}
   if [ -n "$SHOW_IO" ];then
      FIELDS="executions to_execs,DISK_READS,DIRECT_WRITES,USER_IO_WAIT_TIME,"
      FLEN=65
   else
      FIELDS="executions to_execs, decode (nvl(executions,0),0,0,BUFFER_GETS/executions)  avg_gets,"
      FLEN=75
   fi

TITLE=" List  SQL run during the last $LAST_SEC seconds"
SQL="set lines 190 pages 66
col sql_text for a$FLEN
col hash_value justify c
col owner for a16 justify c head 'Owner'
col avg_gets for 999999990.9 justify c
col last_active_time for a22 justify c
col DISK_READS for 999999999 head 'disk reads' justify c
col DIRECT_WRITES for 999999999 head 'Direct|Write' justify c
col USER_IO_WAIT_TIME for 999999999 head 'User Io|Wait time' justify c
col last_active_time for a12 head 'Last active| time' justify c
break on last_active_time on report
 select $INST_ID to_char(last_active_time,'HH24:MI:SS')last_active_time, hash_value,  $FIELDS
          PARSING_SCHEMA_NAME owner,
          substr(SQL_TEXT,1,$FLEN) sql_text
        from sys.${G}v_\$sql where last_active_time > sysdate-$LAST_SEC/86400 $AND_OWNER order by last_active_time desc;
"

elif [ "$ACTION" = "MIS_BIND" ];then

# .........................
#  SQL bind mistmatch
# .........................
#- Modified version of Dion Cho's script - http://dioncho.wordpress.com/?s=v%24sql_shared_cursor
#--
#-- Modified by Kerry Osborne
#-- I just changed the output columns (got rid of sql_text and address columns and added last_load_time)
#-- I also ordered the output by last_load_time.
SQL_ID=`get_sql_id $SQL_ID`
TITLE=" Show bind mismmatch reason"
SQL="
set serveroutput on size unlimited
declare
  c         number;
  col_cnt   number;
  col_rec   dbms_sql.desc_tab;
  col_value varchar2(4000);
  ret_val    number;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
      'select q.sql_text, q.last_load_time, s.*
      from sys.${G}v_\$sql_shared_cursor s, sys.${G}v_\$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_id like ''$SQL_ID''
      order by last_load_time',
      dbms_sql.native);
  dbms_sql.describe_columns(c, col_cnt, col_rec);

  for idx in 1 .. col_cnt loop
    dbms_sql.define_column(c, idx, col_value, 4000);
  end loop;

  ret_val := dbms_sql.execute(c);

  while(dbms_sql.fetch_rows(c) > 0) loop
    for idx in 1 .. col_cnt loop
      dbms_sql.column_value(c, idx, col_value);
      if col_rec(idx).col_name in ('SQL_ID', 'CHILD_NUMBER','LAST_LOAD_TIME') then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
      elsif col_value = 'Y' then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
      end if;
    end loop;
    dbms_output.put_line('--------------------------------------------------');
   end loop;
  dbms_sql.close_cursor(c);
end;
/
"

# .........................
#  History of plan performances
# .........................

elif [ "$ACTION" = "PLH" ];then
#----------------------------------------------------------------------------------------
#-- Author:      Kerry Osborne
#----------------------------------------------------------------------------------------
SQL_ID=`get_sql_id $SQL_ID`
   if [ -z "$SQL_ID" ];then
        echo "I need an sql id"
        exit
   fi
SQL="
set lines 155
col execs for 999999999
col avg_etime for 99999999.9 head 'Avg|exec|Time(ms)'
col etime for 999999999.9 head 'Total exec|Time(s)' justify c
col avg_lio for 999999999 head 'Avg Gets'
col begin_interval_time for a22 head 'Begin interval| time' justify c
col snap_id form 9999999 head 'Snap'
col node for 9999 head 'Inst'
col plan_hash_value head 'Plan hash| Value'
col Execs for 9999999 head 'Execs'
col OPTIMIZER_COST head 'Cost' for 99999
col dreads head 'Disk|Reads' format 99999999
col twaits head 'Wait|time(ms|per exec)' format 9999999

break on plan_hash_value on startup_time skip 1
select
      ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') begin_interval_time,
      sql_id, plan_hash_value, OPTIMIZER_COST,
        nvl(executions_delta,0) execs, 
      round( elapsed_time_delta/1000000,1) etime,
     round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000,1) avg_etime,
     (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
     (DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta)) dreads,
     round((IOWAIT_DELTA+CLWAIT_DELTA+APWAIT_DELTA+CCWAIT_DELTA)/
                decode(executions_delta,0,1,executions_delta)/1000,1) twaits
from
     DBA_HIST_SQLSTAT S,
     DBA_HIST_SNAPSHOT SS
where sql_id = '$SQL_ID'  and S.dbid=SS.dbid $AND_DBID
  and ss.snap_id = S.snap_id
  and ss.instance_number = S.instance_number
  and executions_delta > 0
order by 3 desc, 1 , 2 ;
"

# .........................
#  Attempts to find SQL statements with plan instability
# .........................

elif [ "$ACTION" = "EXEC_VAR_SPEED" ];then
#----------------------------------------------------------------------------------------
#-- File name:   whats_changed.sql
#-- Purpose:     Find statements that have significantly different elapsed time than before.
#-- Author:      Kerry Osborne
#-- Usage:       This scripts prompts for four values.
#--
#--              days_ago: how long ago was the change made that you wish to evaluate
#--                        (this could easily be changed to a snap_id for more precision)
#--              min_stddev: the minimum "normalized" standard deviation between plans
#--                          (the default is 2 - which means twice as fast/slow)
#--              min_etime:  only include statements that have an avg. etime > this value
#--                          (the default is .1 second)
#--              faster_slower: a flag to indicate if you want only Faster or Slower SQL
#--                             (the default is both - use S% for slower and F% for faster)
#--
#-- Description: This scripts attempts to find statements with significantly different
#--              average elapsed times per execution. It uses AWR data and computes a
#--              normalized standard deviation between the average elapsed time per
#--              execution before and after the date specified by the days_ago parameter.
#--              The ouput includes the following:
#--              SQL_ID - the sql_id of a statement that is in the shared pool (v$sqlarea)
#--              EXECS - the total number of executions in the AWR tables
#--              AVG_ETIME_BEFORE - the average elapsed time per execution before the REFERENCE_TIME
#--              AVG_ETIME_AFTER - the average elapsed time per execution after  the REFERENCE_TIME
#--              NORM_STDDEV - this is a normalized standard deviation (i.e. how many times slower/faster is it now)
#-- See http://kerryosborne.oracleguy.com for additional information.
#----------------------------------------------------------------------------------------
DAYS=${days_ago:-1}
TITLE="Show SQL with execution speed variations"
SQL="
-- accept days_ago -
--        prompt 'Enter Days ago: ' -
--        default '1'
define days_ago=$DAYS ;
define min_stddev=2;
define min_etime=.1;
define faster_slower='%';
set lines 155
col execs for 999,999,999
col before_etime for 999,990.99
col after_etime for 999,990.99
col before_avg_etime for 999,990.99 head AVG_ETIME_BEFORE
col after_avg_etime for 999,990.99 head AVG_ETIME_AFTER
col min_etime for 999,990.99
col max_etime for 999,990.99
col avg_etime for 999,990.999
col avg_lio for 999,999,990.9
col norm_stddev for 999,990.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev,
       case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower' else 'Faster' end result
-- select *
from (
select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs,
       sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime,
       min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev,
       case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse
from (
select sql_id,
       period_flag,
       execs,
       avg_etime,
       stddev_etime,
       case when period_flag = 'Before' then execs else 0 end before_execs,
       case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime,
       case when period_flag = 'After' then execs else 0 end after_execs,
       case when period_flag = 'After' then avg_etime else 0 end after_avg_etime
from ( select
        sql_id, period_flag, execs, avg_etime,
        stddev(avg_etime) over (partition by sql_id) stddev_etime
     from (
           select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from (
              select sql_id, 'Before' period_flag,
              nvl(executions_delta,0) execs,
             (elapsed_time_delta)/1000000 etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
             from 
                DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
             where 
                 ss.snap_id = S.snap_id and s.dbid = ss.dbid $AND_DBID
             and ss.instance_number = S.instance_number
             and executions_delta > 0
             and elapsed_time_delta > 0
             and ss.begin_interval_time <= sysdate-&&days_ago
           union
           select 
                sql_id, 'After' period_flag, nvl(executions_delta,0) execs, (elapsed_time_delta)/1000000 etime
                -- (elapsed_time_delta)/decode(nvl(executions_delta,0),0,1,executions_delta)/1000000 avg_etime
                -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
               from 
                   DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
               where   
                    ss.snap_id = S.snap_id and s.dbid = ss.dbid $AND_DBID
                and ss.instance_number = S.instance_number
                and executions_delta > 0
                and elapsed_time_delta > 0
                and ss.begin_interval_time > sysdate-&&days_ago
              )
         group by sql_id, period_flag
      )
    )
    )
   group by sql_id, stddev_etime
   )
     where 
            norm_stddev > nvl(to_number('&min_stddev'),2)
        and max_etime > nvl(to_number('&min_etime'),.1)
)
where result like nvl('&Faster_Slower',result)
order by norm_stddev
/
"
elif [ "$ACTION" = "UNSTABLE" ];then
#----------------------------------------------------------------------------------------
#-- Purpose:     Attempts to find SQL statements with plan instability.
#-- Author:      Kerry Osborne
#-- Usage:       This scripts prompts for two values, both of which can be left blank.
#--              min_stddev: the minimum "normalized" standard deviation between plans
#--                          (the default is 2)
#--              min_etime:  only include statements that have an avg. etime > this value
#--                          (the default is .1 second)
#-- See http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/ for more info.
#---------------------------------------------------------------------------------------
SQL="
set lines 155
col execs for 999,999,999
col min_etime for 999,999.99 head 'Min execution|Time' justify c
col max_etime for 999,999.99 head 'Max execution|Time' justify c
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
prompt
prompt Use 'sx' and 'aw' to further research
select * from (
                select sql_id, sum(execs) execs, min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
                       from (
                               select
                                       sql_id, plan_hash_value, execs, avg_etime,
                                       stddev(avg_etime) over (partition by sql_id) stddev_etime
                                from (
                                         select
                                            sql_id, plan_hash_value, sum(nvl(executions_delta,0)) execs,
                                            (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime,
                                             sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
                                        from
                                            DBA_HIST_SQLSTAT S,
                                            DBA_HIST_SNAPSHOT SS
                                        where
                                             ss.snap_id = S.snap_id and s.dbid = ss.dbid $AND_DBID
                                         and ss.instance_number = S.instance_number
                                         and executions_delta > 0
                                         group by sql_id, plan_hash_value
                                      )
                             ) group by sql_id, stddev_etime
               )
         where
                norm_stddev > 2
            and max_etime > 0.1
        order by norm_stddev ;
"

# .........................
#  PL/SQL
# .........................

elif [ "$ACTION" = "PLSQL" ];then
SQL="set feed on
col type format a15
col owner format a25
col name format a24
col sid format 9999
col serial format 999999
set linesize 124 pagesize 33
   SELECT
      substr(DECODE(o.kglobtyp, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13, 'CLASS'),1,15)  "TYPE",
      substr(o.kglnaown,1,30)  "OWNER",
      substr(o.kglnaobj,1,30)  "NAME",
      s.indx  "SID",
     s.ksuseser  "SERIAL"
   FROM
     sys.X\$KGLOB  o, sys.X\$KGLPN  p, sys.X\$KSUSE  s
   WHERE
     o.inst_id = USERENV('Instance') AND
     p.inst_id = USERENV('Instance') AND
     s.inst_id = USERENV('Instance') AND
     o.kglhdpmd = 2 AND
     o.kglobtyp IN (7, 8, 9, 12, 13) AND
     p.kglpnhdl = o.kglhdadr AND
     s.addr = p.kglpnses
  ORDER BY 1, 2, 3;"

# .........................
#   expensive sql
# .........................

elif [ "$ACTION" = "HEAVY" ];then

#-------------------------------------------------------------------------------
#--
#-- Script:     expensive_sql.sql
#-- Purpose:    to find expensive sql that may need tuning
#-- For:                8.1.6 and above
#--
#-- Copyright:  (c) Ixora Pty Ltd
#-- Author:     Steve Adams
#-- Adapted to smenu by B. Polarski
#-------------------------------------------------------------------------------
NROWNUM=${NROWNUM:-5}
SQL="set linesize 190 pagesize 66
column load format a6 justify right
column executes format 9999999 head 'Execs'
column sql_text format a65 head 'Sql Text'
column child_number head 'c#' for 999
colum buffer_gets head 'gets'

break on load on $FIELD on $FIELD1 on executes on hash_value on child_number on report

select
  substr(to_char(s.pct, '99.00'), 2) || '%'  load, $FIELD,
  s.executions  executes, $FIELD1,
  p.hash_Value, s.child_number,
  p.sql_text|| chr(10) sql_text
from
  (
    select
      address,
      $FIELD, $FIELD1,
      executions, hash_value,child_number,
      pct,
      rank() over (order by $FIELD desc)  ranking
    from
      (
        select address, $FIELD, executions, $FIELD1, 100 * ratio_to_report($FIELD) over ()  pct,
               hash_value, child_number
        from sys.${G}v_\$sql where command_type != 47
      )
    where
      $FIELD > 50 * executions
  )  s,
  sys.${G}v_\$sqltext  p
where
  s.ranking <= $NROWNUM and
  p.address = s.address
order by
  1 desc, s.address, p.piece;
"
 # ...............................................................................
elif [ "$ACTION" = "LOAD" ];then
    MIN_PRES=${MIN_PRES:-10000}

SQL="col cnt format 99999
col family format a52
col load head 'relative|load|on system' for 999999999
col cpu head 'cpu(ms)' for 99999999
select sql_id,$VARFIELD, cpu,disk_reads,buffer_gets buff,sorts,executions,loads,cnt, load from (
select           sql_id,
                 substr(sql_text, 1, $LEN_TEXT) sql_text,
                 sum(abs(disk_reads)) disk_reads,
                 sum(abs(buffer_gets)) buffer_gets,
                 sum(abs(sorts)) sorts,
                 sum(abs(executions)) executions,
                 sum(abs(loads)) loads,
                 sum(abs(cpu_time/1000)) cpu,
                 max(hash_value) family_hv,
                 count(*) cnt,
                 ((sum(abs(disk_reads))*100)+sum(abs(buffer_gets)))/1000 load
        from     sys.${G}v_\$sqlarea
        group by $GROUP_BY,sql_id
        having   sum(abs(disk_reads)) > $MIN_PRES
        and      sum(abs(buffer_gets)) > $MIN_PRES
        order by  load desc) $ROWNUM;
"
 # ...............................................................................
 #     default
 # ...............................................................................
elif [ "$ACTION" = "DEFAULT" ];then
SQL="
col PARSING_SCHEMA_NAME head 'Parsing|Schema name' for a22
col child_number form 99 head 'c#'
SELECT $SID executions, loads, invalidations, parse_calls,
         DISK_READS, buff, rp, cpu, $ELP0 ltl, $HT_HEADER $HT_PARSING
from (
SELECT  $SID invalidations, parse_calls, executions, loads, 
         DISK_READS, $BUF_GET $ROW_PROC CPU_TIME/1000000 cpu, $ELP $F_TIME
         , $HASH_OR_TEXT from sys.${G}v_\$sql $JOIN_TO_SESS $ADD_WHERE $FILTER1 $ORDER
  ) $ROWNUM ;"
fi
if [ -n "$SETXV" ];then
   echo "$SQL"
fi
echo $NN "MACHINE $HOST - ORACLE_SID : $ORACLE_SID $NC"
sqlplus -s "$CONNECT_STRING" <<EOF

set linesize 80
column nline newline
set pagesize 66 termout on embedded off verify off heading off pause off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  || '       $TITLE' from sys.dual
/
set head on pause off feed off linesize 190
column buff format 99999999 head "$BUF_GET_TITLE" justify c
column disk_reads format 99999999 head "Disk|reads" justify c
column User_opening format 9999 head "Nbr of|Users" justify c
column rp format 99999990 head "$ROW_PROC_TITLE" justify c
column cpu format 99999 head "cpu |Time(s)" justify c
column elp format 99999 head "elapse |Time(s)" justify c
column invalidations format 99999999 head "invali-|dations" justify c
column parse_calls format 99999999 head "parse|calls" justify c
column loads format 99999 head "loads" justify c
column executions format 99999999 head "Execs"
column sid format 9999 head "Sid"
column hasv_value format 999999999 head "  SQL|Hash_value"  justify c
column ltl format A11 head "$F_TIME_TITLE" justify c
column sqltype format 9999 head "SQL|Type" justify c
column sorts format 99999 head "Sorts" justify c
col cnt head "nbr|in|area" format 9999 justify c
col sql_text format a$LEN_TEXT

$SQL
prompt
EOF