#!/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
# 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,parsing_schema_name"
HT_PARSING=",parsing_schema_name"
HT_HEADER=hash_value

F_TIME=" substr(LAST_LOAD_TIME,9,2)||'/'|| substr(LAST_LOAD_TIME,6,2)|| ' ' || substr(LAST_LOAD_TIME,12,4) ltl"
F_TIME_TITLE="Last time| Loaded"
# ----------------------------------------------------------------------------------------------------------------
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
 ret=`sqlplus -s "$CONNECT_STRING" <<EOF
 set head off pagesize 0 feed off verify off
 select distinct hash_Value from v\\$sql where sql_id = '$1';
EOF`
 echo "$ret"
}
# ----------------------------------------------------------------------------------------------------------------
function help
{
  cat <<EOF

    SQL area and history :
       
            sq -l -b -d -e -f -inv -pars -t -w -n <USER> -x -hd -hg
            sq -load -sp -rn <nn> 
            sq -text -len <len text> -min <nnn>
            sq -hv <SQL_HASH_VALUE>                  #  see only stat for this sql
            sq -pl  | -ph <sqlid>  | -pb <SQLID>     #  show sql with plan difference and perf hits

   -b  : to sort by buffer_gets        -c : to sort by cpu             -d : to sort by disk_reads         -e : to sort by executions
   -f  : to sort by fetches          -inv : to sort by invalidations   -w : sort by rows processed        -g : show global stats, not per execution
   -t  : sort by last time loaded or elapsed sec for SQL with -x      -rn : limit display to <nn> rows    -v : Verbose                                         

             -x     : Limit selection to SQL that can be linked to sessions |     -n : restric to Parsing_schema <USER>
            -pk     : list session running PL/SQL package           |          -pars : to sort by parse calls
            -text   : show sql_text rather than hash value          |   -pb <SQL_ID> : Show bind mistmatch reason    
             -pl    : show sql with plan difference and perf hits   |   -ph <SQL_ID> : Show history of plan performances 

Notes:            -sp   : display a sample of this family hash_value 
                  -len  : length of text used to build family
                  -min  : consider only query with at least <nnn> disk reads and <nnn> gets. default is 10 000

Option for SQL and their weigth on the system:   sq -hd : List sql with disk reads
                                                 sq -hg : List sql with gets

EOF
exit
}
# ----------------------------------------------------------------------------------------------------------------
VARFIELD=SQL_TEXT
LEN_TEXT=50
if [ -z "$1" ];then
   help
fi
while [ -n "$1" ]
do
      case $1 in
         -sp ) FAMILLY=TRUE;;
        -min ) MIN_PRES=$2;shift;;
         -len ) LEN_TEXT=$2; shift ; GROUP_BY="substr(sql_text, 1, $LEN_TEXT)" ;;
          -e ) ORDER=" ORDER by executions desc"  ;;
          -f ) ORDER=" ORDER by fetches desc" ;;
          -w ) ORDER=" ORDER by rows_processed desc" ;;
          -d ) ORDER=" ORDER by disk_reads desc" ;;
          -b ) ORDER=" ORDER by buff desc" ;;
          -c ) ORDER=" ORDER by cpu desc" ;;
          -l ) ACTION=DEFAULT ;;
        -inv ) ORDER=" ORDER by invalidations desc" ; unset ELP ; unset ELP0;;
       -pars ) ORDER=" ORDER by parse_calls desc" ; unset ELP ; unset ELP0;;
         -pl ) ACTION=UNSTABLE ;;
         -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;;
         -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 ltl desc" ; FILTER=" and LAST_LOAD_TIME is not null" ; WHERE=" where 1=1 "  ;;
          -x ) JOIN_TO_SESS="  , v\$session b, 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,";;
          -g ) TOT_G=TRUE ;;
          -n ) FILTERN=" and PARSING_SCHEMA_NAME = upper('$2') " ; shift ; WHERE=" where 1=1 " ;;
         -pk ) ACTION=PLSQL ;;
         -hd ) ACTION=HEAVY ; FIELD=disk_reads;;
         -hg ) ACTION=HEAVY ; FIELD=buffer_gets;;
       -load ) ACTION=LOAD ; GROUP_BY="substr(sql_text, 1, $LEN_TEXT)" ;;
         -hv ) WHERE=" where 1=1 " ; FILTER1="  hash_value = $2 " ; unset ELP ; unset ELP0 ;;
          -h ) help ;;
          -v ) SETXV="set -xv";;
            *) SINGLE_SID=" and s.sid = '$1' " ;;
        esac
        shift
done

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
        BUF_GET="BUFFER_GETS buff, "
        ROW_PROC="ROWS_PROCESSED rp,"
        ROW_PROC_TITLE="Total |Rows"
        BUF_GET_TITLE=" Total|Buff Gets"
else
        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 [  "x-$CONNECT_STRING" = "x-" ];then
      echo "could no get a the password of $S_USER"
      exit 0
fi
if [ "$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 v\$sql_shared_cursor s, 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.999 head 'Avg|Time(ms)'
col etime for 999999999.9 head 'Total exec|Time(s)' justify c
col avg_lio for 999999999.9 head 'Avg Gets'
col begin_interval_time for a30
col node for 99999
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, nvl(executions_delta,0) execs, elapsed_time_delta/1000000 etime,
     (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime,
     (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 sql_id = '$SQL_ID'
  and ss.snap_id = S.snap_id
  and ss.instance_number = S.instance_number
  and executions_delta > 0
order by 1, 2, 3 ;
"

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

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 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 124 pagesize 66
column load format a6 justify right
column executes format 9999999
column sql_text format a90 head 'Sql Text'

break on load on executes on hash_value

select
  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
  s.executions  executes,
  p.hash_Value,
  p.sql_text|| chr(10) sql_text
from
  ( 
    select
      address,
      $FIELD,
      executions,
      pct,
      rank() over (order by $FIELD desc)  ranking
    from
      (
	select address, $FIELD, executions, 100 * ratio_to_report($FIELD) over ()  pct
	from sys.v_\$sql where command_type != 47
      )
    where
      $FIELD > 50 * executions
  )  s,
  sys.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 load format 9999990.9 head load
col family format a52
select $VARFIELD, disk_reads,buffer_gets buff,sorts,executions,loads,cnt,load from (
select  /*+ rule */
                 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,
                 max(hash_value) family_hv,
                 count(*) cnt,
                 ((sum(abs(disk_reads))*100)+sum(abs(buffer_gets)))/1000 load
        from     v\$sqlarea
        group by $GROUP_BY
        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
SELECT $SID executions, loads, invalidations, parse_calls,
        fetches, DISK_READS, buff, rp, cpu, $ELP0 ltl, $HT_HEADER $HT_PARSING
from (
SELECT  $SID invalidations, parse_calls,fetches, executions, loads,
         DISK_READS, $BUF_GET $ROW_PROC CPU_TIME/1000000 cpu, $ELP $F_TIME
         , $HASH_OR_TEXT from 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 A10 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