#!/usr/bin/ksh
# B. Polarski
# Creation : 12-Jan-2008
# History  : 03-Apr-2009   Added option -s, -set. Option -l has been enhanced
#
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

. $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
# ----------------------------------------------------------------
function help {

cat <<EOF

 RESOURCE GROUP MANAGER (drm) usage : 
   
          drm     -c                      #  List consumer groups
          drm     -d                      #  List resource plan directives
          drm     -lg  [-rac]             #  List consumer group stats
          drm     -lh                     #  List resource plan history
          drm     -ls  [-rac]             #  List stats for group sessions
          drm     -la                     #  List active resource plan manager
          drm     -m                      #  List consumer group mapping
          drm     -p                      #  List resource plan
          drm     -sh                     #  List resource stats history
          drm     -u                      #  List users assigned resource consumer groups

          drm     -active  <PLAN_NAME> | -reset                            #  Activate or deactivate a plan
          drm     -cp <plan_name> -cg <CONSUMER_GROUP> [-co <"COMMENT">]   # Create plan
          drm     -cg <CONS_GROUP>  [ -ratio <cpu_mth>] [-co <"COMMENT">]  # Create Consumer group with comment and cpu % allowance 
          drm     -dg <plan_name>                                          # Delete consumer group
          drm     -set <USER> -g <CONSUMER_GRP>                            # set the initial consumer group 

          -g  <CONSUMER_GROUP>
          -v : Verbose
EOF
exit
}
# ----------------------------------------------------------------
if [ -z "$1" ];then
   help
fi
ROWNUM=30

while [ -n "$1" ]
do
  case "$1" in
-active ) req=ACTIVE_PLAN; PLAN=$2; shift ;;
-reset  ) req=RESET_ACTIVE_PLAN;;
     -c ) req=LIST_GRP ;EXECUTE=YES ;;
    -cg ) req=CREATE_CONSUMER_GROUP ; CG=$2; shift ;;
    -co ) COMMENT="$2" ;;
    -cp ) req=CR_PLAN ; PLAN_NAME=$2; shift ;;
    -d ) req=LIST_DIRECTIVES ;EXECUTE=YES ;;
    -dg ) req=DELETE_CONSUMER_GROUP ; CG=$2; shift ;;
     -g ) CONS_GRP=$2; shift ;;
    -la ) req=LIST_ACTIVE ;EXECUTE=YES ;;
    -lh ) req=LIST_HISTORY ;EXECUTE=YES ;;
    -lg ) req=RSG_STAT;EXECUTE=YES;;
    -ls ) req=SESS_STAT;EXECUTE=YES;;
     -m ) req=LIST_MAP ;EXECUTE=YES ;;
     -p ) req=LIST_PLAN ;EXECUTE=YES ;;
   -rac ) INST_ID="INST_ID," ; RAC=G;;
 -ratio ) RATIO=" , cpu_mth=> 'RATIO' " ;;
    -rn ) ROWNUM=$2; shift  ;;
    -set) req=SET_CONS_GRP; F_USER=$2 ; shift ;;
    -sh ) req=STAT_HIST;EXECUTE=YES;;
     -u ) req=LIST_USER ;EXECUTE=YES ;;
     -h ) help ;;
     -x ) EXECUTE=YES ;;
     -v ) VERBOSE=TRUE;;
  esac
  shift
done

if [ "$req" = "LIST_MAP" ];then
TITTLE="List consumer group mapping"
SQL="col value format a30 trunc
col attribute format a20 trunc
col consumer_group format a20 trunc
break on CONSUMER_GROUP skip 1
SELECT CONSUMER_GROUP,ATTRIBUTE, VALUE from DBA_RSRC_GROUP_MAPPINGS order by 1;
"

elif [ "$req" = "STAT_HIST" ];then
TITTLE="List history stats"
SQL="
col max_seq new_value max_seq noprint ;
col REQUESTS head 'Cumulative|number of|requests'
col ACTIVE_SESS_LIMIT_HIT head 'Number|sessions|queued' justify c
col UNDO_LIMIT_HIT head '# Queries|cancelled on| undo limit' justify c
col SESSION_SWITCHES_IN head '# Sess|Switched in|Cons grp'
col SESSION_SWITCHES_out head '# Sess|Switched out|Cons grp'
col ACTIVE_SESS_KILLED head '# Sess kill|on Exceed|Switch_time'
col IDLE_SESS_KILLED head '# Sess kill|on idle exceed' justify c
col IDLE_BLKR_SESS_KILLED head '#Sess kill|on blocking|Too long' justify c
col name for a16 head 'Name'
col QUEUE_TIME_OUTS head '# Session|out due to|long queuing' justify c
col QUEUED_TIME head 'Tot Session|Queue time' justify c
col YIELDS head '#of Sess|Yield CPU'
select max(sequence#) max_seq from ${RAC}V\$RSRC_CONS_GROUP_HISTORY;
select $INST_ID  NAME, REQUESTS , CPU_WAIT_TIME, CPU_WAITS ,
                CONSUMED_CPU_TIME ,YIELDS ,UNDO_LIMIT_HIT ,ACTIVE_SESS_LIMIT_HIT
from ${RAC}V\$RSRC_CONS_GROUP_HISTORY where sequence#=&max_seq order by name;
prompt
select $INST_ID  name,  SESSION_SWITCHES_IN, SESSION_SWITCHES_OUT
  ,SQL_CANCELED,ACTIVE_SESS_KILLED,IDLE_SESS_KILLED,IDLE_BLKR_SESS_KILLED,QUEUED_TIME,QUEUE_TIME_OUTS 
   from ${RAC}V\$RSRC_CONS_GROUP_HISTORY where sequence#=&max_seq ;
"
elif [ "$req" = "RSG_STAT" ];then
    if [ -n "$RAC" ];then  
       ORDER='order by inst_id,2'
    fi
TITTLE="List active consumer group statistics"
SQL="set feed on
col CPU_WAIT_TIME head 'Cpu Wait|Time' justify c
col Queue_length head 'Queue| Length'
col CPU_WAITS head '#Cpu| Waits' justify c
col REQUESTS head 'Cumulative|number of|requests'
col ACTIVE_SESSION_LIMIT_HIT head 'Number|sessions|queued' justify c
col ACTIVE_SESSIONS head 'Active|sessions' justify c
col UNDO_LIMIT_HIT head '# Queries|cancelled on| undo limit' justify c
col SESSION_SWITCHES_IN head '# Sess|Switched in|Cons grp'
col SESSION_SWITCHES_out head '# Sess|Switched out|Cons grp'
col ACTIVE_SESSIONS_KILLED head '#Sess kill|on Exceed|Switch_time'
col IDLE_SESSIONS_KILLED head '# Sess kill|on idle exceed' justify c
col IDLE_BLKR_SESSIONS_KILLED head '#Sess killed|on blocking|Too long' justify c
col name for a16 head 'Name'
col QUEUE_TIME_OUTS head '# Session|out due to|long queuing' justify c
col QUEUED_TIME head 'Total| Session|Queue time' justify c
col YIELDS head '#of Sess|Yield CPU'
col EXECUTION_WAITERS head 'Session Wait|for Exec'
col CONSUMED_CPU_TIME head 'Consumed|Cpu time'
break on inst_id skip 1
SELECT $INST_ID name, active_sessions, queue_length,
  consumed_cpu_time, cpu_waits, cpu_wait_time,EXECUTION_WAITERS,QUEUED_TIME,QUEUE_TIME_OUTS
  FROM ${RAC}v\$rsrc_consumer_group $ORDER;
SELECT $INST_ID name
       YIELDS,ACTIVE_SESSION_LIMIT_HIT,SESSION_SWITCHES_IN,SESSION_SWITCHES_OUT,
       ACTIVE_SESSIONS_KILLED,IDLE_SESSIONS_KILLED,IDLE_BLKR_SESSIONS_KILLED
  FROM ${RAC}v\$rsrc_consumer_group $ORDER;
"
elif [ "$req" = "SESS_STAT" ];then
    if [ -n "$RAC" ];then
         SE='se.' 
         AND_INST_ID=' and se.inst_id = co.inst_id '
         ORDER_INST_ID='se.inst_id,'
    fi
TITTLE="List consumer group Session statistics"
SQL="
col state for a12
break on consumer_group 
SELECT  $SE$INST_ID co.name consumer_group,  se.sid sess_id, 
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM ${RAC}v\$rsrc_session_info se, ${RAC}v\$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id $AND_INST_ID
    order by $ORDER_INST_ID cpu_time desc;
"
elif [ "$req" = "SET_CONS_GRP" ];then
SQL="set head off
prompt Doing : exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('$F_USER','$CONS_GRP') ;;
exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name => '$F_USER', consumer_group => '$CONS_GRP', grant_option => FALSE);
exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('$F_USER','$CONS_GRP') ;
"
elif [ "$req" = "DELETE_CONSUMER_GROUP" ];then
SQL=" set feed on 
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP ( consumer_group => '$CG' ); 
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
"
elif [ "$req" = "CREATE_CONSUMER_GROUP" ];then
SQL=" set feed on 
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( consumer_group => '$CG' ,  comment =>  '$COMMENT' $CPU_MTH ); 
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
"

elif [ "$req" = "CR_PLAN" ];then
SQL=" 
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN ( plan  => '$PLAN_NAME', comment =>  '$COMMENT' $RATIO  ); 
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
"
elif [ "$req" = "LIST_HISTORY" ];then
SQL="select * from (select * from  V\$RSRC_PLAN_HISTORY order by start_time desc ) where rownum <=$ROWNUM ;"

elif [ "$req" = "RESET_ACTIVE_PLAN" ];then
SQL="prompt connect as sys and do :
prompt
prompt ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='' scope=both sid='*';;
prompt"
elif [ "$req" = "ACTIVE_PLAN" ];then
SQL="prompt connect as sys and do :
prompt
prompt ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '$PLAN' scope=both sid='*';;
prompt"

elif [ "$req" = "LIST_ACTIVE" ];then
   TITTLE="List active resource plan manager"
   SQL="col value format a30 head 'Active plan'
set feed on
prompt Use 'drm -d' to see all groups for a plan
prompt
SELECT inst_id, nvl(VALUE,'no plan active') value FROM gV\$PARAMETER WHERE name = 'resource_manager_plan'
   order by 1;"

elif [ "$req" = "LIST_USER" ];then
   TITTLE=" List users assigned resource consumer groups"
   SQL="col username for a30
col initial_group for a7 head 'Initial|Group'
col grant_option form a6 head 'Grant|Option'
SELECT initial_rsrc_consumer_group, username FROM dba_users 
ORDER BY 1,2 ;
prompt
SELECT Granted_group, grantee, grant_option, initial_group 
       FROM dba_rsrc_consumer_group_privs  $WHERE_GROUP
       ORDER BY Granted_group;
"

elif [ "$req" = "LIST_DIRECTIVES" ];then
   TITTLE="List resource plan directives"
   SQL="SELECT plan ,group_or_subplan ,cpu_p1 ,cpu_p2 ,cpu_p3 ,undo_pool ,status, 
          PARALLEL_DEGREE_LIMIT_P1,SWITCH_GROUP,MAX_IDLE_TIME,
          MAX_EST_EXEC_TIME, MAX_IDLE_BLOCKER_TIME, SWITCH_TIME_IN_CALL,MANDATORY,active_sess_pool_p1
        FROM dba_rsrc_plan_directives order by 1 ,2 ;"

elif [ "$req" = "LIST_PLAN" ];then
    TITTLE="List resource plan"
    SQL="SELECT plan ,num_plan_directives ,cpu_method ,active_sess_pool_mth ,parallel_degree_limit_mth ,queueing_mth
    ,status ,mandatory FROM dba_rsrc_plans ;
"

elif [ "$req" = "LIST_GRP" ];then
    TITTLE="List Resource consmner groups"
    SQL=" SELECT consumer_group ,cpu_method ,status ,mandatory ,comments FROM dba_rsrc_consumer_groups ; "
fi
if [ "$VERBOSE" = "TRUE" ];then
   echo "$SQL"
fi

if [ "$EXECUTE" = "YES" ];then
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 80 pause off heading off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  || '  -   $TITTLE ' 
from sys.dual
/
set embedded on heading on feedback off linesize 155 pagesize 66 
COL consumer_group      FORMAT A24  HEADING 'Consumer Group'
COL cpu_method          FORMAT A14  HEADING 'CPU Method'
COL status              FORMAT A10  HEADING 'Status'
COL mandatory           FORMAT A06  HEADING 'Manda-|tory?'
COL comments            FORMAT A82  HEADING 'Comments'
COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL num_plan_directives       FORMAT 9999 HEADING '# of|Plan|Dirs'
COL active_sess_pool_mth      FORMAT A32  HEADING 'Active Session Pool Method' justify c
COL parallel_degree_limit_mth FORMAT A32  HEADING 'Parallel Limit Method' justify c
COL queueing_mth              FORMAT A18  HEADING 'Queueing Method' justify c
COL group_or_subplan          FORMAT A12  HEADING 'Group or|SubPlan'
COL type                      FORMAT A15  HEADING 'Type'
COL initial_rsrc_consumer_group  FORMAT A24  HEADING 'Resource|Consumer|Group'
COL username                     FORMAT A12  HEADING 'User Name'
col SWITCH_GROUP   format a13          head 'Switch|group'
col PARALLEL_DEGREE_LIMIT_P1 format 99 head 'Par|deg|limit' justify c
col cpu_p1 format 999
col cpu_p2 format 999
col cpu_p3 format 999
col cpu_p4 format 999
col MAX_EST_EXEC_TIME format 9999 head 'Max exec|time(s)' justify c
col MAX_IDLE_TIME format 999999 head 'Max idle|time(s)' justify c
col MAX_IDLE_BLOCKER_TIME format 9999 head 'Max block|time(s)' justify c
col SWITCH_TIME_IN_CALL format 9999 head 'switch time|in Call (s)' justify c
col active_sess_pool_p1 format 99999 head 'activ|sess|pool'
col undo_pool for 999999999 head 'Undo|Pool(kb)' justify c
break on plan skip 1
prompt
$SQL
exit
EOF

else

 echo "$SQL"

fi