#!/usr/bin/ksh
# set -xv
# author :  B. Polarski
# 02 Sept 2005
# Modified 08 Jun 2006 : Added dbms_xplan
#          28 Jun 2006 : Added vi, vh, -sb
#          27 May 2008 : Added option -vx
#          16 Jun 2009 : Added automatic conversion from sql_id to hash_value and reverse
#          09 Dec 2010 : Added iggy Fernandez 'show execution steps plan'
#          23 Dec 2010 : renamed '-sb' to '-lb'
ROWNUM=30
# ---------------------------------------------------------------------------
# to update an outlines : update outln.ol$hints set hint_text='INDEX_RS_ASC(@"SEL$1" "TXN"@"SEL$1" ("TXN"."EXT_TXN_ID" "TXN"."END_TIME"))'
#          where ol_name = 'SYS_OUTLINE_07120313083698728' and hint#=1
# get the value of ol_name and hint# with 'sx -stl' followed by 'sx -tn <ol_name>'

# ----------------------------------------------------------------------------------------------------------------
function check_HV_or_exit
{
  if [ -z "$HASH_VALUE" ];then
       echo "corresponding hash_value is not found in memory anymore"
       exit
  fi
}
# ----------------------------------------------------------------------------------------------------------------
function get_sql_id_first_child {
   ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select min(child_number) from v\\$sql where sql_id='$1' ;
EOF`
echo "$ret"
}
# ----------------------------------------------------------------------------------------------------------------
function get_sql_id
{
 if [ -z "${1%%*[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 = '$1';
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 help1
{
cat <<EOF

#   .................................................................
"         Show options for Stored outlines:
#   .................................................................

    sx -cro <hash_value> -c <n> -cat <CATEGORY_NAME> # Create stored outlines for a given hash_value
    sx -crf <sql file> -cat <CATEGORY_NAME> [-ot <OUTLINE_NAME>] -u <OWNER> [-outln <schema>]
                                                     # Create stored outlines for SQL in file
    sx -ch <OUTLINE_NAME> -hv <HASH_VALUE>           # Set hash value for OUTLINE_NAME
    sx -lso                                          # list sql from V\$SQL which uses an Outline
    sx -lc [-cat <CATEGORY>]                         # list stored outlines for category <CATEGORY>
    sx -ln [ OUTLINE_NAME ] [-outln <schema>]        # List hints for a given outline name. 'sx -lc' list existing outlines
                                                     # default outln schema is OUTLN. use this if it is another schema
    sx -drc <category>                               # Drop an outlines category
    sx -dr  <OUTLINE_NAME>                           # Drop an outline
    sx -clone <outlname> <new_outlname> [-cat <category>]  # for cloning, default category is DEFAULT
    sx -rfo <OUTLINE_NAME>                           # Resync the outline edited with memory
    sx -cl  <OUTLINE_NAME>                           # reset used column in 'sx -lc' (dba_outlines.used) for an outline name
    sx -tr  <SRC_OL> <TARGET_OL> ][-outln <schema>]  # Transfer all outlines hints from <SRC_OL> to <TARGET_OL>
    sx -pos  OUTLINE_NAME OLD_POS NEW_POS            # Move a hint line from one position to another
    sx -exp ][-outln <schema>]                       # export the 3 outlines tables : OL\$ OL\$NODES OL\$HINTS in $SBIN/tmp
    sx -sp                                           # show SQL Profiles
    sx -vs  <hash_value>                             # Add stats from average execution (v\$sql_plan_statistic  / v\$sql.executions )
    sx -opt <hash_value>                             # Add optimizer environment for the given hash_value

SQL plan statbilisation -> Stored Outlines/SQL profiles:

    sx -str <STRING>  [-u <USER>]                    # List hash values which strings in the plan
    sx -stu                                          # List operations present in v\$sql_plan

Notes:
  -outln : use this only if the owner of the OL$ is not schema OUTLN

Example :
       sx -crf F.SQL -cat MYCAT -ot F_OT -u LILI # Creates stored outlines for the sql in F.SQl, that addess tables visible
                                                 # by schema LILI  the stored outline will be named F_OT and be in category
                                                 # MYCAT. if F.SQL is not in the current dir, then give full path


EOF
exit
}
# ----------------------------------------------------------------------------------------------------------------
function help
{
cat <<EOF

#   .................................................................
#      Type : 'sx -h1'   for help on Stored Outlines
#   .................................................................
 Show Xplan  (Note: you can use indiferently sql_id or hash_value)
                                             ------   ----------
    sx <hash_value> -a -c <nn>             # Show plan
    sx -pl <PLAN_HASH_VALUE>               # list hash_value, sql_id for a given plan_hash_value
    sx -l -u <OWNER>  -tim -gets -cost     # list plans, show multiples plans/sql_id; sort -tim:active time; -gets -cost
    sx -s <sql_id> -o <n> -c <nn>          # use sql_id 10g+
    sx -vl  <hash_value>                   # Add stats from last execution (v\$plan_statistic )
    sx -vx  <hash_value> [-c <n> ] -p      # another mix of v\$sql_plan and v\$sql_plan_statistics.   -p : show partitions start/stop
    sx -lb <sql_id>                        # show bind variable sample (10g)+
    sx -f <file> -ev <nnn> -level <nn>     # set events (10046|53 etc..) before executing file
    sx -ed <process_numm>                  # edit tracefile whose number was given by '-f'
    sx -stp <sql_id> -c <n>                # Show execution plan steps
    sx -vh <hash_value>                    # Conversion : return sql_id    for a given hash_value
    sx -vi <sql_id>                        # Conversion : return hash_value for a given sql_id


Notes:
   -len  : set the len of the sql_text column               -v  : Verbose : output sql text that the 'sx' command will use. Default is silent
     -f  : execute sql in file.                             -c  : SQL Child number (used in v$sql) default is 0
     -s  : Show plain with dbms_xplan.display_cursor    -level  : event level, default is 12, relevant only with -f
     -l  : List all SQL present in v\$sql_plan              -a  : List access path and filter 
             -u  : restric plan to <schema>             -dup <nn>    : show sqlid with <nn> or more duplicate plan. default is 1
             -rn : Limit number or rows to display, default is 30
    -ev  : set event <nnnnn> in session  while executing sql given by -f, default is 10053
     -o  : where <n> is a choice of format
                   1 :  Basic    Displays the minimum information
                   2 :  Typical  Displays Partition pruning, parallelism and predicates if available
                   3 :  Serial   Like TYPICAL except that the parallel information is not displayed
                   4 :  All      Display all levels
                   5 :  Runstats_last  Displays the runtime statistics for the last execution of the cursor.
                        (requires init.ora statistics_level=ALL)
                   6 :  Runstats_TOT Displays the total aggregated runtime statistics for all executions (req. init.ora statistics_level=ALL)
                   7 :  Outlines  Display outlines used by the sql
                   8 :  Advanced Display all available data about this sql
               default FORMAT is Typical


  For explain plans, 'sx' will first count the number of plans for a given SQL and display the number of the lowest child
  It will then try to display the plan for child 0, which is default. If there is no plan use the value of min to see
  the first one and query v\$sql_plan for other childs number. if there are multple childs, use sx <HV> -c <CHILD> to see each child plan

EOF
exit
}
# ---------------------------------------------------------------------------
if [  "$1" = "-h" ];then
   help
fi
if [ -z "$1" ];then
  help
fi
METHOD=PLAN
FORMAT=TYPICAL
EV_NUM=10053
EV_LEVEL=12
typeset -u F_USER
FLEN=50
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
unset TITTLE
unset_presql=FALSE
OLSCHEMA=OUTLN
while [ -n "$1" ]
do
   case "$1" in
    -a ) METHOD=AC1 ;;
   -lb ) METHOD=BIND; SQL_ID=$2; shift;;
    -c ) CHILD=$2;shift;;
  -cat ) CATEGORY=`echo $2|awk '{print toupper ($1) }'`; shift ;;          # too much compat problems with typset -u
   -cl ) METHOD=CLEAN_USED; OT_NAME=`echo $2|awk '{print toupper($1)}'`; shift; TITTLE="Clear used col for $CATEGORY" ;;
   -ch ) METHOD=CH_HV ; OT_NAME=$2 ; shift ;;
-clone ) METHOD=CLONE; OLD=$2 ; NEW=$3;shift; shift ;;
  -cro ) METHOD=CR_OUTLN; HASH_VALUE=$2; shift ;;
  -crf ) METHOD=CRF_OUTLN; SQLFILE=$2; shift ;;
  -drc ) METHOD=DROPCAT ; CATEGORY=`echo $2|awk '{print toupper($1)}'`; shift ;TITTLE="drop outlines category $CATEGORY" ;;
   -dr ) METHOD=DROPOL ; OT_NAME=`echo $2|awk '{print toupper($1)}'`; shift ;TITTLE="drop outlines category $CATEGORY" ;;
  -dup ) MAX_PLAN=$2 ; shift ;;
   -ed ) METHOD=TRC; TRC=$2; shift ;;
   -ev ) EV_NUM=$2; shift ;;
  -exp ) METHOD=EXP_STO ;;
    -f ) FFILE=$2;shift
         if [ ! -f "$FFILE" ];then
            echo "Cannot find $FFILE"
            exit
         fi
         METHOD=FILE;;
  -gets) ORDER=gets;;
  -tim ) ORDER=time;;
  -cost) ORDER=cost;;
    -h ) help ;;
   -hv ) HASH_VALUE=$2 ; shift ;;
    -i ) METHOD=PLAN_SQL_ID; SQL_ID=$2; shift ;;
  -len ) FLEN=$2 ; shift ;;
    -l ) METHOD=LIST_PLAN;;
   -lc ) METHOD=LIST_OUTLN ; TITTLE="List outlines" ;;
-level ) EV_LEVEL=$2; shift ;;
   -ln ) METHOD=LIST_HINT; OT_NAME=$2 ; shift ;;
  -lso ) METHOD=LIST_SQL_OUTLN  ;;
   -ot ) OT_NAME=`echo $2|awk '{print toupper($1)}'`; shift ;;
  -opt ) METHOD=OPTIM_ENV ; HASH_VALUE=$2 ; shift ;;
    -p ) PART_START_STOP=TRUE;;
   -pl ) METHOD=PL; PL_HV=$2; shift ;;
  -pos ) METHOD=CHG_POS; OT_NAME=$2; OLD_POS=$3 ; NEW_POS=$4; shift; shift;shift ;;
  -rfo ) METHOD=REFRESH_OL ; OT_NAME=$2; shift;;
   -rn ) ROWNUM=$2; shift;;
    -s ) METHOD=DBMS; SQL_ID=$2; shift ;;
   -sp ) METHOD=SQL_PROFILE;;
  -stp ) METHOD=STEPS ; SQL_ID="$2" ; shift ;;
  -str ) METHOD=STR ; STRING="$2" ; shift ;;
 -stru ) METHOD=STRU ;;
   -tr ) METHOD=CP_OL; SOURCE=$2;TARGET=$3;shift ; shift ;;
    -v ) VERBOSE=TRUE ;;
   -vh ) METHOD=VH; HASH_VALUE=$2 ; shift;;
   -vi ) METHOD=VI; SQL_ID=$2 ;  TITTLE="List bind variable value for sql_id"; shift;;
   -vl ) METHOD=VLAST; HASH_VALUE=$2;shift;;
   -vs ) METHOD=VSTAT; HASH_VALUE=$2;shift;;
   -vx ) METHOD=XMS ;  HASH_VALUE=$2
         if [ "$3" = "-c" ];then
              if [ -n "$4" ];then
                    SQL_CHILD=$4; shift; shift ;
              fi
         fi
         shift;;
    -u ) F_USER=$2; AND_OWNER=" and u.username='$F_USER'"; shift;;
    -o ) VAR=$2;
          case $VAR in
              1) FORMAT=BASIC;;
              2) FORMAT=TYPICAL;;
              3) FORMAT=SERIAL;;
              4) FORMAT='ALL +PEEKED_BINDS';;
              5) FORMAT=RUNSTATS_LAST;;
              6) FORMAT=RUNSTATS_TOT;;
              7) FORMAT=OUTLINE ;;
              8) FORMAT=ADVANCED ;;
          esac
         shift ;;
   -outln) OLSCHEMA=$2 ; shift ;;
   -h1   ) help1; exit ;;
     * ) HASH_VALUE=$1;;
   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
HASH_VALUE=`get_hash_value $HASH_VALUE`

# --------------------------------------------------------------------------
if [ -n "$CHILD" ];then
   AND_CHILD_NUMBER=" and child_number = $CHILD"
   AND_A_CHILD_NUMBER=" and a.child_number = $CHILD"
fi
if [ -z "$TITTLE" ];then
    TITTLE="Explain plan for query hash_value"
fi

# --------------------------------------------------------------------------
# Show SQL optimizer enviromentment
# --------------------------------------------------------------------------
if [ "$METHOD" = "STEPS" ];then

# a very interresting attempt to demonstrate executions steps. this takes in a account
# the last discussion on bushy tree plan whichd do not follow the deep left trees rule.
# Copyright 2010 Iggy Fernandez : http://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/
SQL_ID=`get_sql_id $SQL_ID`
if [ -z "$CHILD" ];then
   echo "No child given, retrieving from DB first child"
   RET=`get_sql_id_first_child $SQL_ID`
   CHILD=`echo "$RET" | tr -d '\n' | tr -d '\r' | awk '{print $1}'`
fi
CHILD=${CHILD:-0}
SQL="
SET linesize 1000 pagesize 0 echo off feedback off  verify off
SET time off timing off sqlblanklines on
col EXECUTION_SEQUENCE#  for 999 head 'Step'
col FID form a5 head 'Id in|plan' justify c
col line form a140
--------------------------------------------------------------------------------
-- First retrieve the basic data from V$SQL_PLAN_STATISTICS_ALL.
-- Modify this subquery if you want data from a different source.
--------------------------------------------------------------------------------
WITH plan_table AS
(
  SELECT
    id, parent_id, object_name, operation, options, last_starts,
    last_elapsed_time / 1000000 AS last_elapsed_time, cardinality, 
    last_output_rows, last_cr_buffer_gets + last_cu_buffer_gets AS last_buffer_gets,
    last_disk_reads
  FROM
    v\$sql_plan_statistics_all
  WHERE
    sql_id = '$SQL_ID'
    AND child_number = '$CHILD'
),
--------------------------------------------------------------------------------
-- Determine the order in which steps are actually executed
--------------------------------------------------------------------------------
execution_sequence AS
(
  SELECT
    id,
    ROWNUM AS execution_sequence#
  FROM
    plan_table pt1
  START WITH
    -- Start with the leaf nodes
    NOT EXISTS (
      SELECT *
      FROM plan_table pt2
      WHERE pt2.parent_id = pt1.id
    )
  CONNECT BY
    -- Connect to the parent node
    pt1.id = PRIOR pt1.parent_id
    -- if the prior node was the oldest sibling
    AND PRIOR pt1.id >= ALL(
      SELECT pt2.id
      FROM plan_table pt2
      WHERE pt2.parent_id = pt1.id
    )
  -- Process the leaf nodes from left to right
  ORDER SIBLINGS BY pt1.id
),
--------------------------------------------------------------------------------
-- Calculate deltas for elapsed time, buffer gets, and disk reads
--------------------------------------------------------------------------------
deltas AS
(
  SELECT
    t1.id,
    t1.last_elapsed_time - NVL(SUM(t2.last_elapsed_time),0) AS delta_elapsed_time,
    t1.last_buffer_gets - NVL(SUM(t2.last_buffer_gets),0) AS delta_buffer_gets,
    t1.last_disk_reads - NVL(SUM(t2.last_disk_reads),0) AS delta_disk_reads
  FROM
    plan_table t1
    LEFT OUTER JOIN plan_table t2
    ON t1.id = t2.parent_id
  GROUP BY
    t1.id,
    t1.last_elapsed_time,
    t1.last_buffer_gets,
    t1.last_disk_reads
),
--------------------------------------------------------------------------------
-- Join the results of the previous subqueries
--------------------------------------------------------------------------------
enhanced_plan_table AS
(
  SELECT -- Items from the plan_table subquery
    plan_table.id,
    plan_table.parent_id,
    plan_table.object_name,
    plan_table.operation,
    plan_table.options,
    plan_table.last_starts,
    plan_table.last_elapsed_time,
    plan_table.cardinality,
    plan_table.last_output_rows,
    plan_table.last_buffer_gets,
    plan_table.last_disk_reads,
    -- Items from the execution_sequence subquery
    execution_sequence.execution_sequence#,
    -- Items from the deltas subquery
    deltas.delta_elapsed_time,
    deltas.delta_buffer_gets,
    deltas.delta_disk_reads,
    -- Computed percentages
    CASE
      WHEN (SUM(deltas.delta_elapsed_time) OVER () = 0)
      THEN (100)
      ELSE (100 * deltas.delta_elapsed_time / SUM(deltas.delta_elapsed_time) OVER ())
    END AS delta_percentage_elapsed_time,
    CASE
      WHEN (SUM(deltas.delta_buffer_gets) OVER () = 0)
      THEN (100)
      ELSE (100 * deltas.delta_buffer_gets / SUM(deltas.delta_buffer_gets) OVER ())
    END AS delta_percentage_buffer_gets,
    CASE
      WHEN (SUM(deltas.delta_disk_reads) OVER () = 0)
      THEN (100)
      ELSE (100 * deltas.delta_disk_reads / SUM(deltas.delta_disk_reads) OVER ())
    END AS delta_percentage_disk_reads,
    CASE
      WHEN (SUM(deltas.delta_elapsed_time) OVER () = 0)
      THEN (100)
      ELSE (100 * plan_table.last_elapsed_time / SUM(deltas.delta_elapsed_time) OVER ())
    END AS last_percentage_elapsed_time,
    CASE
      WHEN (SUM(deltas.delta_buffer_gets) OVER () = 0)
      THEN (100)
      ELSE (100 * plan_table.last_buffer_gets / SUM(deltas.delta_buffer_gets) OVER ())
    END AS last_percentage_buffer_gets,
    CASE
      WHEN (SUM(deltas.delta_disk_reads) OVER () = 0)
      THEN (100)
      ELSE (100 * plan_table.last_disk_reads / SUM(deltas.delta_disk_reads) OVER ())
    END AS last_percentage_disk_reads
  FROM
    plan_table,
    execution_sequence,
    deltas
  WHERE
    plan_table.id = execution_sequence.id
    AND plan_table.id = deltas.id
  -- Order the results for cosmetic purposes
  ORDER BY plan_table.id
)
--------------------------------------------------------------------------------
-- Begin THE graph
--------------------------------------------------------------------------------
SELECT distinct
   execution_sequence#,'('||id||')' fid, ' '
  -- Line 2: Operations, options, object name, and starts
  || operation
  || CASE
       WHEN (options IS NULL)
       THEN ('')
       ELSE (' ' || options)
     END
  || CASE
       WHEN (object_name IS NULL)
       THEN ('')
       ELSE (' ' || object_name)
     END
  || CASE
       WHEN (last_starts > 1)
       THEN (' (Starts= ' || last_starts || ')')
       ELSE ('')
     END
  -- Line 3: Delta elapsed time and cumulative elapsed time
  || ' Ela= '
  || CASE
       WHEN (delta_elapsed_time IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_elapsed_time, '999,999,990.00')) || 's')
     END
  || ' ('
  || CASE
       WHEN (delta_percentage_elapsed_time IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_percentage_elapsed_time, '990')) || '%')
     END
  || ')'
  || ' C.Ela='
  || CASE
       WHEN (last_elapsed_time IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(last_elapsed_time, '999,999,990.00')) || 's')
     END
  || ' ('
  || CASE
       WHEN (last_percentage_elapsed_time IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(last_percentage_elapsed_time, '990')) || '%')
     END
  || ')'
  -- Line 4: Delta buffer gets and cumulative buffer gets
  || ' Gets='
  || CASE
       WHEN (delta_buffer_gets IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_buffer_gets, '999,999,999,999,990')))
     END
  || ' ('
  || CASE
       WHEN (delta_percentage_buffer_gets IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_percentage_buffer_gets, '990')) || '%')
     END
  || ')'
  || ' C.Gets='
  || CASE
       WHEN (last_buffer_gets IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(last_buffer_gets, '999,999,999,999,990')))
     END
  || ' ('
  || CASE
       WHEN (last_percentage_buffer_gets IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(last_percentage_buffer_gets, '990')) || '%')
     END
  || ')'
  -- Line 5: Delta disk reads and cumulative disk reads
  || ' Dsk Reads='
  || CASE
       WHEN (delta_disk_reads IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_disk_reads, '999,999,999,999,990')))
     END
  || ' ('
  || CASE
       WHEN (delta_percentage_disk_reads IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(delta_percentage_disk_reads, '990')) || '%')
     END
  || ')'
  || ' C.Dsk Reads='
  || CASE
       WHEN (last_disk_reads IS NULL)
       THEN ('0')
       ELSE (TRIM(TO_CHAR(last_disk_reads, '999,999,999,999,990')))
      END
  || ' ('
  || CASE
       WHEN (last_percentage_disk_reads IS NULL)
        THEN ('0')
       ELSE (TRIM(TO_CHAR(last_percentage_disk_reads, '990')) || '%')
     END
  || ')'
  -- Line 6: Estimated rows and actual rows
  || 'E Rows='
  || CASE
       WHEN (cardinality IS NULL)
       THEN '0'
       ELSE (TRIM(TO_CHAR(cardinality, '999,999,999,999,990')))
     END
  || ' A Rows='
  || CASE
       WHEN (last_output_rows IS NULL)
       THEN '0'
       ELSE (TRIM(TO_CHAR(last_output_rows, '999,999,999,999,990')))
     END
   AS line
FROM enhanced_plan_table
-- START WITH parent_id = 0
-- CONNECT BY parent_id = PRIOR id
order  by execution_sequence# 
/
"
# --------------------------------------------------------------------------
# Show SQL optimizer enviromentment
# --------------------------------------------------------------------------
elif [ "$METHOD" = "OPTIM_ENV" ];then
unset_presql=TRUE
SQL_ID=`get_sql_id $HASH_VALUE`
SQL="COLUMN CN FORMAT 99
col name for a45
col value for a25
SET PAGESIZE 1000 lines 190
break on sql_id on report

SELECT
  SQL_ID, CHILD_NUMBER CN, SUBSTR(NAME,1,45) NAME, SUBSTR(VALUE,1,25) VALUE, ISDEFAULT DEF
FROM
  V\$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='$SQL_ID' AND CHILD_NUMBER=0
ORDER BY
  NAME;
"
# --------------------------------------------------------------------------
# Explain a SQL statements  with execution profile from library cache
# --------------------------------------------------------------------------
elif [ "$METHOD" = "XMS" ];then
   if  [ -z "$SQL_CHILD" ];then
       SQL_CHILD=%
   fi
   if [ "$SQL_CHILD" = "%" ];then
      AND_SQL_CHILD="  and to_char(p.child_number) like '%' "
   else
      AND_SQL_CHILD="  and p.child_number = $SQL_CHILD "
   fi
   if [ "$PART_START_STOP" = "TRUE" ];then
       P_START="p.PARTITION_START, p.partition_stop"
   else
      P_COST="p.io_cost io_cost,
             case
                  when ps.last_disk_writes > 1048576 then lpad(to_char(round(ps.last_disk_writes/1048576,1)),6,' ')||'m'
                  else lpad(to_char(ps.last_disk_writes),7,' ')
             end  last_disk_writes"
   fi
#-------------------------------------------------------------------------------------
#-- File name:   xmsh (eXplain from Memory with Statistics lookup by Hash value)
#-- Author:      Tanel Poder
# Copyright of Mr. Tanel suppressed for this query is identical to Breitling's one
# found in this is same script but comes 3 years later (see sx -vl) . The output is though more candy
# adapted to smenu by bpa
#-------------------------------------------------------------------------------------

SQL="
set verify off heading off feedback off linesize 190 pagesize 5000 tab off heading on
column child_number     heading 'Ch|ld' format 99
break on child_number   skip 1

column id           heading Op|ID format 999
column id2          heading Op|ID format a6
column pred         heading Pr|ed format a2
column optimizer        heading Optimizer|Mode format a10
column plan_step        heading Operation for a38
column object_name      heading Object|Name for a20
column opt_cost     heading Optim|Cost for 999999
column opt_card     heading 'Estim.|rows' for a8 justify c
column cpu_cost             heading CPU|Cost for 999999
column io_cost              heading IO|Cost for 999999
column last_output_rows     heading ' Last exec|#rows|returned' for a10 justify c
column last_cr_buffer_gets  heading 'Consist|  gets' for a8
column last_cu_buffer_gets  heading 'Current|  gets' for a8
column last_disk_reads      heading 'Physic| reads' for a8
column last_disk_writes     heading ' Physic| writes' for a8
column last_elapsed_time_ms heading 'ms spent' for 9999990.99
col partition_stop head 'Part|Stop' for a5
col partition_start head 'Part|Start' for a5

select  --+ ordered use_nl(p ps)
    p.child_number      child_number,
    case when p.access_predicates is not null then 'A' else ' ' end ||
    case when p.filter_predicates is not null then 'F' else ' ' end pred,
    p.id        id,
    lpad(' ',p.depth*1,' ')|| p.operation || ' ' || p.options plan_step,
    p.object_name   object_name,
    round(ps.last_elapsed_time/1000,2) last_elapsed_time_ms,
    p.cost  opt_cost,
    case
        when  p.cardinality > 1048576 then lpad(to_char(round(p.cardinality/1048576,1)),6,' ')||'m'
        else lpad(to_char(p.cardinality),7,' ')
        end opt_card,
    case
        when  ps.last_output_rows > 1048576 then lpad(to_char(round(ps.last_output_rows/1048576,1)),6,' ')||'m'
        else lpad(to_char(ps.last_output_rows),7,' ')
        end last_output_rows,
    case
        when ps.last_cr_buffer_gets > 1048576 then lpad(to_char(round(ps.last_cr_buffer_gets/1048576,1)),6,' ')||'m'
        else lpad(to_char(ps.last_cr_buffer_gets),7,' ')
    end last_cr_buffer_gets,
    case
        when ps.last_cu_buffer_gets > 1048576 then lpad(to_char(round(ps.last_cu_buffer_gets/1048576,1)),6,' ')||'m'
        else lpad(to_char(ps.last_cu_buffer_gets),7,' ')
    end last_cu_buffer_gets,
    case
        when ps.last_disk_reads > 1048576 then lpad(to_char(round(ps.last_disk_reads/1048576,1)),6,' ')||'m'
        else lpad(to_char(ps.last_disk_reads),7,' ')
    end  last_disk_reads , $P_COST $P_START
from
    v\$sql_plan p,
    v\$sql_plan_statistics ps
where
    p.address     = ps.address(+)
and p.hash_value  = ps.hash_value(+)
and p.id          = ps.operation_id(+)
and p.hash_value  = $HASH_VALUE $AND_SQL_CHILD
and p.child_number= ps.child_number (+)
order by
    p.child_number asc,
    p.id asc
/
prompt
set feedback on
"
# --------------------------------------------------------------------------
# List type of o$eration in v$sql_plan
# --------------------------------------------------------------------------

elif [ "$METHOD" = "STRU" ];then
unset_presql=TRUE
SQL="
col ops format a40
select distinct ops, count(*) cpt from (select operation||' '||options ops  from v\$sql_plan) group by ops ; "

# --------------------------------------------------------------------------
# List hash_value with $STRING in plan
# --------------------------------------------------------------------------

elif [ "$METHOD" = "STR" ];then
unset_presql=TRUE
SQL="
col operation format a40
set pagesize 66
select distinct a.hash_value, c.cost final_cost,a.id,  a.operation ||' '|| a.options operation , b.id, b.operation||' '|| b.options operation
  from v\$sql_plan a , v\$sql_plan b, v\$sql_plan c
    where a.operation||' '||a.options like upper('%$STRING%') and
    a.hash_value=b.hash_value and
       a.child_number = b.child_number and
    c.hash_value=b.hash_value and
       c.child_number = b.child_number
       and c.id=0 and
       b.id = (a.id + 1)
;
"
# --------------------------------------------------------------------------
#  change HASH_VALUE for a given OT
# --------------------------------------------------------------------------
elif [ "$METHOD" = "CH_HV" ];then
unset_presql=TRUE
SQL="prompt Doing update outln.ol\$ set HASH_VALUE=$HASH_VALUE where ol_name = upper('$OT_NAME');;
update outln.ol\$ set HASH_VALUE=$HASH_VALUE where ol_name = upper('$OT_NAME');"

# --------------------------------------------------------------------------
# List hash_value when a plan_hash_value is given
# --------------------------------------------------------------------------

elif [ "$METHOD" = "PL" ];then
unset_presql=TRUE
SQL="col sql_text format a80
select a.hash_value, a.child_number, sql_id ,
   (select executions from v\$sql where hash_value=a.hash_value and child_number=a.child_number) executions,
   (select sql_text from v\$sql where hash_value=a.hash_value and child_number=a.child_number) sql_text
    from v\$sql_plan a where a.plan_hash_value = $PL_HV and id = 0;
"
# --------------------------------------------------------------------------
# List SQL from v$sql using outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CRF_OUTLN" ];then
   if [ ! -f "$SQLFILE" ];then
         echo " I need an sql file"
         exit
   fi
   if [ -n "$CATEGORY" ];then
      AND_CAT=" for category $CATEGORY "
   fi
   FTMP=$SBIN/tmp/cr_outln_$$.sql
   echo "create or replace outline $OT_NAME $AND_CAT on " > $FTMP
   cat $SQLFILE |sed '/^$/d' >> $FTMP
   tail -2 $FTMP | grep '/'
   if [ $? -eq 1 ];then
      echo "/" >> $FTMP
   fi
   echo "exit" >> $FTMP
   if [ -n "$F_USER" ];then
   S_USER=`echo $F_USER| tr '[a-z]' '[A-Z]'`
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
   fi

   sqlplus -s "$CONNECT_STRING" @$FTMP
   rm -f $FTMP
   exit


# --------------------------------------------------------------------------
# List SQL from v$sql using outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "LIST_SQL_OUTLN" ];then
unset_presql=TRUE
SQL="col SQL_TEXT format a40
col OPTIMIZER_COST format 999999 head 'Opt|Cost'
col EXECUTIONS  head 'Execs'
col old_hash_value head 'Old hash|Value' justify c
col plan_hash_value head 'Plan hash|Value' justify c
select a.sql_id,a.hash_value,a.child_number,a.plan_hash_value, a.old_hash_value,a.EXECUTIONS,OPTIMIZER_COST,a.LAST_ACTIVE_TIME,substr(a.SQL_TEXT,1,40) sql_text
from v\$sql a
 where outline_category is not null ;
"

# --------------------------------------------------------------------------
# Change outlines position
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CHG_POS" ];then
unset_presql=TRUE
SQL="
prompt doing : DBMS_OUTLN_EDIT.CHANGE_JOIN_POS ( '$OT_NAME', $OLD_POS , $NEW_POS);
exec DBMS_OUTLN_EDIT.CHANGE_JOIN_POS( '$OT_NAME', $OLD_POS , $NEW_POS);
"


# --------------------------------------------------------------------------
# Transfer outlines hints from one OTL to another
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CP_OL" ];then
unset_presql=TRUE
    if [ -z "$SOURCE" ];then
       echo "I need a source outline name"
       exit
    else
       var=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off feed off pause off
select count(*) from $OLSCHEMA.OL\\$ where OL_NAME='$SOURCE';
exit
EOF`
       ret=`echo $var|awk '{print $1}'`
       if [ ! $ret -eq 1 ];then
           echo "I do not find Source $SOURCE in $OLSCHEMA.OL\$"
           exit
       fi
    fi
    if [ -z "$TARGET" ];then
       echo "I need a target outline name"
       exit
    else
       var=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off feed off pause off
select count(*) from $OLSCHEMA.OL\\$ where OL_NAME='$TARGET';
exit
EOF`
       ret=`echo $var|awk '{print $1}'`
       if [ ! $ret -eq 1 ];then
           echo "I do not find Target $TARGET in $OLSCHEMA.OL\$"
           exit
       fi
    fi
# a exchange of OL name is simpler but this destroy our source
# we want to transfer our source onto the target witout destroying the source
    sqlplus -s "$CONNECT_STRING" <<EOF
    set serveroutput on size unlimited
    declare
     rec $OLSCHEMA.ol\$hints%rowtype ;
     cat varchar2(30);
    begin
       select CATEGORY into cat from $OLSCHEMA.OL\$ where OL_NAME='$SOURCE';
       --dbms_output.put_line('cat=' || rec.category);
       delete from $OLSCHEMA.ol\$hints where ol_name = '$TARGET' ;
       for r in (select * from $OLSCHEMA.OL\$HINTS where OL_NAME='$SOURCE')
       loop
          null ;
          rec:=r ;
          rec.ol_name:='$TARGET' ;
          rec.category:=cat ;
 dbms_output.put_line('ol_name='||rec.ol_name ||'   cat=' || rec.category);

          insert into $OLSCHEMA.ol\$hints values rec ;
       end loop ;
       commit ;
    end;
/

EOF
exit

# --------------------------------------------------------------------------
# Clear used outlines column in DBA_OUTLINES
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CLEAN_USED" ];then
unset_presql=TRUE
   if [ -z "$OT_NAME" ];then
      echo "I need an outline name"
      exit
   fi
if $SBINS/yesno.sh "to clean USED column in DBA_OUTLINES for Outline $OT_NAME"
then
SQL="prompt doing exec dbms_outln.clear_used('$OT_NAME');
exec dbms_outln.clear_used('$OT_NAME');"
else
SQL='prompt clean cancelled'
fi

# --------------------------------------------------------------------------
# export outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "EXP_STO" ];then
unset_presql=TRUE
SQL="exp / file=$SBIN/tmp/OL.dmp tables=('$OLSCHEMA.OL\$HINTS','$OLSCHEMA.OL\$NODES' ,'$OLSCHEMA.OL\$')"
$SQL
exit

# --------------------------------------------------------------------------
# Refresh outlines after changing a hints
# --------------------------------------------------------------------------

elif [ "$METHOD" = "REFRESH_OL" ];then
unset_presql=TRUE
SQL="
Prompt Doing : exec DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE ('$OT_NAME');
exec DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE ('$OT_NAME');
"

# --------------------------------------------------------------------------
# Clone outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CLONE" ];then
unset_presql=TRUE
  if [ -z "$OLD" ];then
       echo "I need the outlines names to clone"
       exit
  fi
  if [ -z "$NEW" ];then
       echo "I need a new outlines names for the  clone"
       exit
  fi
  if [ -n "$CATEGORY" ];then
       CAT=" alter session set create_stored_outlines = '$CATEGORY' "
  else
       unset CAT
  fi
  OLD=`echo $OLD | awk '{print toupper($1)}'`   # More porable than typset -u as this work also on cygwin
  NEW=`echo $NEW | awk '{print toupper($1)}'`   # More porable than typset -u as this work also on cygwin
SQL="$CAT
create or replace outline $NEW from $OLD ;
"

# --------------------------------------------------------------------------
# Drop one outline
# --------------------------------------------------------------------------

elif [ "$METHOD" = "DROPOL" ];then
unset_presql=TRUE
SQL="prompt doing drop outline $OT_NAME ;
drop outline $OT_NAME ;
"

# --------------------------------------------------------------------------
# Delete outlines category
# --------------------------------------------------------------------------

elif [ "$METHOD" = "DROPCAT" ];then
unset_presql=TRUE
echo
if $SBINS/yesno.sh "to drop all outlines for category : $CATEGORY"
then
SQL="
prompt exec dbms_outln.drop_by_cat('$CATEGORY');
exec dbms_outln.drop_by_cat('$CATEGORY');
"
else
SQL="prompt drop cancelled"
fi

# --------------------------------------------------------------------------
# List sql profiles
# --------------------------------------------------------------------------

elif [ "$METHOD" = "SQL_PROFILE" ];then
unset_presql=TRUE
SQL="set long $FLEN
select NAME, LAST_MODIFIED,TYPE, STATUS, FORCE_MATCHING, SQL_TEXT from dba_sql_profiles;
"

# --------------------------------------------------------------------------
# List stored outlines hints
# --------------------------------------------------------------------------

elif [ "$METHOD" = "LIST_HINT" ];then
unset_presql=TRUE
  if [ -z "$OT_NAME" ];then
       echo "I need an Outlines name. Use sx -stl [-cat <CATEGORY> ] "
       exit
  fi
SQL="
 set linesize 190
 col hint_text format a50
col hint# format 9999
col stage# format 9999
col Table_pos format 9999 head 'Table|pos' justify c
col table_name format a24
 set long 32000
 select   HINT#, HINT_TEXT, STAGE# ,TABLE_NAME, TABLE_POS,round(COST,2) cost,CARDINALITY,bytes
           from $OLSCHEMA.ol\$HINTS where OL_NAME = '$OT_NAME' ;

"

# --------------------------------------------------------------------------
# List stored outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "LIST_OUTLN" ];then
unset_presql=TRUE
  if [ -n "$CATEGORY" ];then
     AND_CAT=" AND category = upper('$CATEGORY') "
  fi
SQL="COLUMN name FORMAT A30
COLUMN category FORMAT A20
col owner format a14
set long 32000 linesize 150
set long 40
SELECT a.owner,a.name, a.category, a.enabled,a.used,b.hash_value, a.sql_text FROM dba_outlines a , $OLSCHEMA.ol\$ b
 where a.name = b.ol_name $AND_CAT;
"
# --------------------------------------------------------------------------
# Create stored outlines
# --------------------------------------------------------------------------

elif [ "$METHOD" = "CR_OUTLN" ];then
unset_presql=TRUE
SQL="
prompt exec  DBMS_OUTLN.create_outline( hash_value => $HASH_VALUE, child_number  => $CHILD, category => '$CATEGORY');

exec DBMS_OUTLN.create_outline( hash_value => $HASH_VALUE, child_number  => $CHILD, category => '$CATEGORY');
"

# --------------------------------------------------------------------------
# Performance v$sql_plan_statistics
# --------------------------------------------------------------------------

elif [ "$METHOD" = "VSTAT" ];then
ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off head off pagesize 0 termout off
select count(*) from v\\$sql where hash_value=$HASH_VALUE;
EOF`
ret=`echo "$ret" |sed 's/[^0-9]*//g'`
echo "CHILD=$CHILD"
if [ -z "$ret" ];then
  echo "Currently, there is no entry in v\$sql for $HASH_VALUE"
  exit
elif [ "$ret" -eq "0" ];then
  echo "Currently, there is no entry in v\$sql for $HASH_VALUE"
  exit
elif [ "$ret" -eq "1" ];then
   #  there is only one child, we retrieve its number
   CHILD=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select min(child_number) from v\\$sql where hash_value=$HASH_VALUE ;
EOF`
elif [ "$ret" -gt "0"  -a -z "$CHILD" ];then
        echo " there are many sql child for this hash_values:"
        echo " Use  'sx -vs $HASH_VALUE -c <nn>'  to view stats and plan for one child\n"
        sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 66 head on
select child_number, executions from v\$sql where hash_value=$HASH_VALUE ;
EOF
        exit
fi
TITTLE="Explain plan and stats for $HASH_VALUE child:$CHILD"
    SQL="
set verify off echo off feed off
set linesize 190 pagesize 3000
col operation format a50
col card for 999,999,990 head 'Estimated|Card from|sql_plan'
col FROWS for 99,999,990 head '|Real avg|rows/exec|gotten '
col ELAPSED for 99,990.999
col CPU for 99990.999
col CR_GETS for 99,999,990
col CU_GETS for 99,999,990
col READS for 9,999,990
col WRITES for 99,990
col starts format 999
col fexecs new_value execs  noprint
-- whenever  sqlerror exit 1
select executions fexecs from v\$sql where hash_value = $HASH_VALUE and child_number=$CHILD;
prompt
-- at this stage exes may be null. we will use decode to avoid zero divisions
SELECT
        to_char(p.id,'990')||decode(access_predicates,null,null,'A') ||decode(filter_predicates,null,null,'F') id
       , P.COST cost
       , LPAD(' ',depth)||P.OPERATION||' '|| P.OPTIONS||' '|| P.OBJECT_NAME|| DECODE(P.PARTITION_START,NULL,' ',':')||
         TRANSLATE(P.PARTITION_START,'(NRUMBE','(NR')|| DECODE(P.PARTITION_STOP,NULL,' ','-')|| TRANSLATE(P.PARTITION_STOP,'(NRUMBE','(NR') operation
       , ( SELECT decode(&execs,0,0,(S.STARTS/&execs))
                  FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                        and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) Starts
       , P.CARDINALITY card
       , ( SELECT S.OUTPUT_ROWS/&execs FROM V\$SQL_PLAN_STATISTICS S
                      WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) FROWS
       , ( SELECT decode(&execs,0,0,ROUND(S.ELAPSED_TIME/&execs/1000000,2)) FROM V\$SQL_PLAN_STATISTICS S
           WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) ELAPSED
       , (SELECT S.CR_BUFFER_GETS/&execs FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                 and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) CR_GETS
       , (SELECT decode(&execs,0,0,S.CU_BUFFER_GETS/&execs) FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) CU_GETS
       , (SELECT decode(&execs,0,0,S.DISK_READS/&execs) FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
          and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) READS
       , (SELECT decode(&execs,0,0,S.DISK_WRITES/&execs) FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                 and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) WRITES
FROM
    V\$SQL_PLAN P
where p.hash_value = $HASH_VALUE and p.child_number=$CHILD order by p.id
/
"
elif [ "$METHOD" = "VLAST" ];then
     check_HV_or_exit
#-------------------------------------------------------------------------------
#-- Purpose: format the plan and execution statistics from the dynamic
#-- performance views v$sql_plan and v$sql_plan_statistics
#--
#-- Copyright: (c)1996-2006 Centrex Consulting Corporation
#-- Author: Wolfgang Breitling
#--
#-- Usage One parameter: sql_hash_value
#--
#-------------------------------------------------------------------------------
     sqlplus -s "$CONNECT_STRING" <<EOF
set verify off echo off feed off
set linesize 190 pagesize 3000
col "cn" for 90 print
col operation format a50
col "card" for 999,999,990
col "ROWS" for 99,999,990
col "ELAPSED" for 99,990.999
col "CPU" for 99990.999
col CR_GETS for 99,999,990
col CU_GETS for 99,999,990
col READS for 9,999,990
col WRITES for 99,990
col pos format 999
break on "cn" skip 0
SELECT
        P.CHILD_NUMBER "cn"
       , to_char(p.id,'990')||decode(access_predicates,null,null,'A') ||decode(filter_predicates,null,null,'F') id
       , P.COST "cost"
       , P.CARDINALITY "card"
       , LPAD(' ',depth)||P.OPERATION||' '|| P.OPTIONS||' '|| P.OBJECT_NAME|| DECODE(P.PARTITION_START,NULL,' ',':')||
         TRANSLATE(P.PARTITION_START,'(NRUMBE','(NR')|| DECODE(P.PARTITION_STOP,NULL,' ','-')|| TRANSLATE(P.PARTITION_STOP,'(NRUMBE','(NR') "operation"
       , P.POSITION "pos"
       , ( SELECT S.LAST_OUTPUT_ROWS FROM V\$SQL_PLAN_STATISTICS S
                  WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                        and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "ROWS"
       , ( SELECT ROUND(S.LAST_ELAPSED_TIME/1000000,2) FROM V\$SQL_PLAN_STATISTICS S
           WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "ELAPSED"
       , (SELECT S.LAST_CR_BUFFER_GETS FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                 and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "CR_GETS"
       , (SELECT S.LAST_CU_BUFFER_GETS FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "CU_GETS"
       , (SELECT S.LAST_DISK_READS FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
          and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "READS"
       , (SELECT S.LAST_DISK_WRITES FROM V\$SQL_PLAN_STATISTICS S WHERE S.ADDRESS=P.ADDRESS and s.hash_value=p.hash_value
                 and s.child_number=p.child_number AND S.OPERATION_ID=P.ID) "WRITES"
FROM
    V\$SQL_PLAN P
where p.hash_value = $HASH_VALUE
order by P.CHILD_NUMBER, p.id
/
EOF
exit
# --------------------------------------------------------------------------
# getting trace file
# --------------------------------------------------------------------------

elif [ "$METHOD" = "TRC" ];then
     var=`sqlplus -s "$CONNECT_STRING" <<EOF
set head off feed off pause off
select value from v\\$parameter where name='user_dump_dest' ;
exit
EOF`
     FILE=$var/${ORACLE_SID}_ora_${TRC}.trc
     vi $FILE

# --------------------------------------------------------------------------
# set event and execute the file
# --------------------------------------------------------------------------

elif [ "$METHOD" = "FILE" ];then
     unset_presql=TRUE
     EV_ON="alter session set events '$EV_NUM trace name context forever, level $EV_LEVEL';"
     EV_OFF="alter session set events '$EV_NUM trace name context off';"
     SQL="$EV_ON
@$FFILE ;
$EV_OFF
-- set head off
-- select 'Session process is : ' ||b.spid from v\$session a, v\$process b
--        where a.sid =  sys_context('USERENV','SID') and a.paddr = b.addr;
"

# --------------------------------------------------------------------------
# Return the hash_value for a given sql_id
# --------------------------------------------------------------------------

elif [ "$METHOD" = "VI" ];then
   SQL="select hash_Value,child_number child from v\$sql where sql_id = '$SQL_ID';"

# --------------------------------------------------------------------------
# Return the sql_id for a given hash_value
# --------------------------------------------------------------------------

elif [ "$METHOD" = "VH" ];then
   TITTLE="List bind variable value for hash_value $HASH_VALUE";
   SQL="select sql_id, hash_Value,child_number child from v\$sql where hash_value = $HASH_VALUE;"

# --------------------------------------------------------------------------
# Return the binds associated to a given sql_id
# --------------------------------------------------------------------------

elif [ "$METHOD" = "BIND" ];then
  if [ -z "$SQL_ID" ];then
     SQL_ID=`get_sql_id $HASH_VALUE`
  fi
  SQL="col value_string format a30
      col name format A25 head 'Bind name'
      select child_number child,name, position, datatype, value_string,
             to_char(last_captured,'DD-MM HH24:MI:SS') capture_date 
      from v\$sql_bind_capture
      where SQL_ID = '$SQL_ID' order by child_number,position
/
"

# --------------------------------------------------------------------------
# Explain plan using 10g dbms_xplan
# --------------------------------------------------------------------------

elif [ "$METHOD" = "DBMS" ];then
  if [ -n "${SQL_ID%%*[a-z]*}" ];then
     SQL_ID=`get_sql_id $SQL_ID`
  fi
  if [ -z "$CHILD" ];then
      echo "no child given, using default 0"
      CHILD=0
  fi
  SQL="SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('$SQL_ID',$CHILD,'$FORMAT'));"
# --------------------------------------------------------------------------
# List plans
# --------------------------------------------------------------------------

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

    unset_presql=TRUE
    MAX_PLAN=${MAX_PLAN:-0}
    if [ -n "$AND_OWNER" ];then
        unset VAR_FIELD1
        VAR_FIELD2="s.rows_processed,s.elapsed_time/1000 elt,"
        FLEN=${FLEN:-45}
   else
        VAR_FIELD1="u.username, "
        FLEN=${FLEN:-50}
   fi
   if [ "$ORDER" = "time" ];then
       ORDER="  llt desc,"
       TITTLE="List plan by Last active query"
   elif [ "$ORDER" = "cost" ];then
       ORDER=" cost desc,"
       AND_COST=" and cost is not null"
       TITTLE="List plan by Cost"
   elif [ "$ORDER" = "gets" ];then
       ORDER=" buffer_gets desc,"
       TITTLE="List plan by Buffer gets"
   fi
   SQL="set lines 190 pages 66
       break on username on phv on report
       col child head Ch for 99
       col cost for 99999 head 'Cost'
       col plan# head 'Nbr|plan' for 9999
       col phv head 'Plan | hash value' justify c for 9999999999
       col exec for 999999
       col buffer_gets head 'gets' for 999999990
       col rows_processed head 'rows' for 999999990
       col exc head 'Execs' for 9999990
       col elt head 'elapsed|time(ms)' for 9999990
       col llt head 'last active|time' justify c for a16
       col txt head 'Sql Text' for a${FLEN}

  select * from (
      select $VAR_FIELD1
             p.plan_hash_value phv ,
             substr(last_LOAD_TIME,6,11) llt,p.sql_id, p.child_number child, 
            p.cost, s.executions exc, s.buffer_gets, $VAR_FIELD2
            count(p.sql_id) over ( partition by p.sql_id ) plan#,
             substr(sql_text,1,$FLEN) txt
       from v\$sql_plan p,v\$sql s,dba_users u
       where p.sql_id = s.sql_id and p.child_number = s.child_number
             and p.id=0  
             and s.parsing_user_id = u.user_id $AND_OWNER  $AND_COST
     order by $ORDER p.sql_id, p.child_number 
   )
    where plan# > $MAX_PLAN and rownum <$ROWNUM  ;"
# --------------------------------------------------------------------------
# plan
# --------------------------------------------------------------------------
elif [ "$METHOD" = "PLAN" ];then

SQL="col child_number noprint
SELECT    distinct id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
           options operation, cost ,cardinality,search_columns,object_node,object_name, child_number
FROM       (
           SELECT id, parent_id, operation, options, a.cost, cardinality,
                  search_columns, object_node, object_name, child_number
           FROM   v\$sql_plan a
           WHERE  a.HASH_VALUE = '$HASH_VALUE' $AND_A_CHILD_NUMBER)
START WITH id = 0
CONNECT BY PRIOR id = parent_id 
order by child_number,id;
"
# --------------------------------------------------------------------------
elif [ "$METHOD" = "PLAN_SQL_ID" ];then

  if [ -n "${SQL_ID%%*[a-z]*}" ];then
     SQL_ID=`get_sql_id $SQL_ID`
  fi
SQL="SELECT   distinct  id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
           options operation, cost ,cardinality,search_columns,object_node,object_name
FROM       (
           SELECT id, parent_id, operation, options, cost, cardinality,search_columns, object_node,object_name
           FROM   v\$sql_plan
           WHERE  SQL_ID = '$SQL_ID' $AND_CHILD_NUMBER)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
order by child_number;
"
# --------------------------------------------------------------------------
# predicates values
# --------------------------------------------------------------------------

elif [ "$METHOD" = "AC1" ];then
  SQL="select id,  object_name, operation ops,  'Access' type ,ACCESS_PREDICATES condition,cardinality,cost
            from v\$sql_plan
            where access_predicates is not null and object_name is not null and hash_value = $HASH_VALUE
    union
     select id, object_name, operation ops , 'Filter' type, FILTER_PREDICATES condition, cardinality, cost
            from v\$sql_plan
            where filter_predicates is not null and object_name is not null and hash_value = $HASH_VALUE $AND_CHILD_NUMBER
   order by id desc; "

fi
# --------------------------------------------------------------------------
#   End of if $METHOD
# --------------------------------------------------------------------------
if [ -n "$VERBOSE" ];then
   echo "$SQL"
fi

if [ "$unset_presql" = "FALSE" ];then
   if [ -z "${HASH_VALUE%%*[a-z]*}" ];then
     HASH_VALUE=`get_hash_value $SQL_ID`
   fi
   PRESQL="set head off
   select 'Total plans among childs: '|| count(1)|| '  First child with plan: '||min(child_number) from v\$sql_plan where hash_value=$HASH_VALUE and id=0;
   set head on"
fi
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 132 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 task_name       FORMAT  A20 heading 'Task'
COL command         FORMAT  A19 heading 'Command'
COL tt              FORMAT  A80   HEADING 'Type'
COL rank noprint
COL id          FORMAT 999
COL parent_id   FORMAT 999 HEADING "PARENT"
COL operation   FORMAT a45 heading "Type of |Operations"
COL username   FORMAT a18 heading "Username"
COL SQL_Text   FORMAT a50 heading "Sql Text"
COL ops   FORMAT a12 head "Operation"
COL object_name FORMAT a22
COL ACCESS_PREDICATES FORMAT a35
COL FILTER_PREDICATES FORMAT a35
COL condition FORMAT a35
COL child_number FORMAT 99 heading "C|h|i|l|d"
COL object_node FORMAT a16
COL search_columns FORMAT 9999 head "Search| Cols"
col FIRST_LOAD_TIME format a19
col txt format a60
set pagesize 0
$PRESQL
$SQL
EOF
echo
#echo "ret=$?"