#!/usr/bin/ksh 


function help
{
 cat <<EOF

        sqn  -cpt -l <Len>      : Count occurence of same type of SQL (not using bind variable
                                  Len is the length of SQL string to scan. Default is 40
        sqn  -l <num>           : to change sql text length to display
        sqn  -n <username>      : restrict to one username
        sqn  -t                 : sort by text
        sqn  -r <num>           : to limit rows       
        sqn  -e                 : sort by executions  
        sqn  -p                 : sort by parse calls
        sqn  -u                 : sort by user opening   
        sqn  -c                 : sort by cpu
        sqn  -o                 : sort by optimizer
        sqn  -hv                 : show hash_value
EOF
}

SILENCE=N
OWNER=
ROWNUM=30
LEN=60
COUNT_LEN=40

typeset -u UPPVAR
unset HASH_VALUE
while true
      do
      if [ -z "$1" ];then
         break
      fi
      case $1 in
          -hv ) HASH_VALUE=" HASH_VALUE, " ;;
          -e ) ORDER=" ORDER by executions desc"  ;;
          -c ) ORDER=" ORDER by cpu_time desc"  ;;
          -o ) ORDER=" and optimizer_cost is not null ORDER by optimizer_cost desc"  ;;
        -cpt ) COUNT=TRUE ;;
          -l ) LEN=$2; 
               COUNT_LEN=$2
               shift ;;
          -p ) ORDER=" ORDER by parse_calls desc"  ;;
          -u ) ORDER=" ORDER by users_opening desc"  ;;
          -t ) ORDER=" ORDER by text "  ;;
          -r ) ROWNUM=$2 
               shift  ;;
          -n ) UPPVAR=$2
               F_USER=" and u.username = '$UPPVAR' " 
               shift  ;;
          -cpt ) COUNT=TRUE ;;
          -h ) help 
               exit ;;
        esac
        shift
done
if [ -n "$HASH_VALUE" ];then
   LEN=`expr $LEN - 9`
fi
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
SBINS=$SBIN/scripts

. $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

RET=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off feed off pause off
select version from v\\$instance;
EOF
`
VERSION=`echo $RET | awk '{print $1}'| cut -f1 -d'.'`
if [ "$VERSION" = "8" ];then
      FIELD="0"
else
      FIELD="c.cpu_time/1000000"
fi

if [ -z "$COUNT" ];then

sqlplus -s "$CONNECT_STRING" <<EOF

set linesize 80
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   '    Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66
set termout on
set embedded off
set verify off
set heading off pause off

 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Display first 60 char of each SQL ' nline
from sys.dual
/   
set head on pause off feed off
set linesize 132

prompt     sqn -h for Help

col text            FOR a$LEN       HEAD "SQL Text (first $LEN char)"
col HASH_VALUE      FOR 9999999999    HEAD "Hash Value"
col executions      FOR 99999999    HEAD "Exec |Count" justify c
col optcost         FOR 99999       HEAD "Optim|Cost" justify c
col kept            FOR A4          HEAD "Kept|Mem"
col users_opening   FOR 9999        HEAD "user|open"
col parse_calls     FOR 99999999    HEAD "Parse|Calls"
col cpu             FOR 999999      HEAD "CPU|Time"
col usr             FOR A12         HEAD "User Name"
set recsep off

select $SID usr, executions , trunc(cpu_time) cpu, parse_calls, 
         users_opening ,kept, optcost , $HASH_VALUE text
     from (
     select
         $SID u.username usr,
         c.Executions  , $FIELD cpu_time, c.parse_calls,users_opening,
         decode(c.kept_versions,0,' N',' Y') kept,
         c.Optimizer_cost optcost , $HASH_VALUE
         substr(c.SQL_text,1,$LEN) text
      from sys.V_\$SQL c,
        sys.all_users u
where
     c.parsing_user_id = u.user_id $F_USER $ORDER )
     where rownum < $ROWNUM 
/

prompt
EOF
else

sqlplus -s "$CONNECT_STRING" <<EOF

set linesize 80
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   '    Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66
set termout on
set embedded off
set verify off
set heading off pause off

col text  FOR a$COUNT_LEN       HEAD "SQL Text (first $COUNT_LEN char)"
 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Count duplicate SQL in v\$sqlarea ' nline
from sys.dual
/   
set head on pause off feed off
set linesize 132

prompt     sqn -h for Help


select sql, cpt, exec from (
   SELECT substr(sql_text,1,$COUNT_LEN) sql, count(1) cpt , sum(executions) exec
          FROM v\$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,$COUNT_LEN)
          HAVING count(1) > 30 ORDER BY 2 desc)
where rownum < $ROWNUM 
/

EOF

fi