#!/bin/ksh

#---------- LOCAL VALUE ----------#
DBID="sys"
DBPW="tibero"
TOOL_HOME=`pwd`
LOG_HOME=$TOOL_HOME/log
SQL_HOME=$TOOL_HOME/sql


#----------  function   ----------#
function DML_FIND
{
clear
echo "------------------------------------------------------------------"
echo "search date input....."
echo "(input date format -> YYYYMMDDHH24MISS)"
echo 
echo -e "start date : \c "
read i_sdate
echo -e "end date   : \c "
read i_edate 
echo "------------------------------------------------------------------"

tbsql -s $DBID/$DBPW <<EOF 
SET linesize 32768
SET pagesize 32768
COL FIRST_LOAD_TIME FOR A25
COL LAST_ACTIVE_TIME  FOR A25

alter session set nls_date_format='YYYYMMDDHH24MISS';
select B.plan_hash_value
     , B.sql_id
     , B.command_type
     , B.CHILD_NUMBER
     , C.first_load_time
     , C.last_active_time
     , C.elapsed_time
from
(
select plan_hash_value, sql_id,CHILD_NUMBER from v\$sql_plan
group by plan_hash_value, sql_id,CHILD_NUMBER
) A
,
(
select plan_hash_value, sql_id, command_type,CHILD_NUMBER from v\$sqltext
group by plan_hash_value, sql_id, command_type,CHILD_NUMBER
) B
,
(
select sql_id, plan_hash_value, first_load_time, last_active_time, elapsed_time from V\$SQLAREA
) C
where A.plan_hash_value = B.plan_hash_value
and   A.sql_id      = B.sql_id
and   A.CHILD_NUMBER= B.CHILD_NUMBER
and   A.plan_hash_value = C.plan_hash_value
and   A.sql_id      = C.sql_id
and   C.first_load_time >= '$i_sdate'
and   C.last_active_time <= '$i_edate'
;
EOF

read tmp
}

#----------    main     ----------#
while true
do

clear

echo
echo "------------------------------------------------------------------"
echo "1. DML Search"
echo "2. DML Query View"
echo "x. EXIT"
echo "------------------------------------------------------------------"
echo -e " Choose the Number or Command : \c "
read i_number

case $i_number in
1)
DML_FIND;
;;

2)
clear
echo "=========="
echo " SQL PLAN "
echo "=========="
tbsql -s $DBID/$DBPW @$SQL_HOME/8_sql_plan_7.sql
read tmp 
;;

x|X)
clear
echo "Good bye..."
echo
exit
;;

esac

done
