#!/usr/bin/ksh
# program : smenu_seg.ksh
# author  : B. Polarski
# Date    : 27 Jul 2006
#set -xv
ROWNUM=30
function help
{
      cat <<EOF

       List segment statistics (v\$segment_statistics):

         seg -s                      # System wide segments statistics
         seg -l                      # List statistics name and number. Usefull for seg -n
         seg -n  <stat#>  -rn <nn>   # List values for statistics#
         seg -hot                    # List all host segments
         seg -tch                    # List touch counts on segments blocks in SGA
         seg -w                      # List ITL/buffer busy waits segments
         seg -b                      # List counts of buffer in memory compared to blocks to object
         seg -o  <obj_id>            # List all statistics for object_id=<nn>  ; use 'obj -o' to get obj_id
         set -top  -rn <nn>          # List Top Segment size

        -rn  : limit output to ROWNUM rows
         -u  : retrict selection to <OWNER>


EOF
exit
}
    
if [ -z "$1" ];then
   help
   exit
fi
while [ -n "$1" ]
do
  case "$1" in
        -rn)  ROWNUM=$2 ; shift ;;
        -o ) ACTION=ONEOBJ ; OBJ_ID=$2 ; shift
             TITTLE="List all for object_id=$OBJ_ID" ;;
        -n ) ACTION=FILTER1 ; STATN=$2 ; shift
             TITTLE="List all for statistic#=$STATN" ;;
        -b ) ACTION=BUF
             TITTLE="List counts of buffer in memory compared to blocks to object" ;;
        -l ) ACTION=LIST 
             TITTLE="List statistics name and number";;
        -w ) ACTION=BUSY 
             TITTLE="List ITL and buffer busy wait" ;;
        -s ) ACTION=STATS
             TITTLE="System wide segments statistics" ;;
      -hot ) ACTION=HOT
             TITTLE="ist all host segments" ;;
      -tch ) ACTION=TCH
             TITTLE="Touch counts on segments blocks in SGA" ;;
      -top ) TITTLE="List top segment size" ; ACTION=TOP;;
        -v ) VERBOSE=TRUE;;
        -u ) WHERE_OWNER=" where owner = upper('$2') "; shift ;;
        -h ) help ;;
         * ) help ;;
  esac
  shift
done


. $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
# -----------------------------------------------------
# A nice little query from Tanel Poder
# -----------------------------------------------------
if [ "$ACTION" = "TOP" ];then
SQL="col SEGMENT_NAME for a30
col owner format a24
col segment_type format a18
col tablespace_name format a24
set lines 190
break on tablespace_name on  owner on segment_name
select * from (
	select 
		tablespace_name, 
		owner, 
		segment_name , 
		partition_name,
		segment_type, 
		round(bytes/1048576) MB 
	from dba_segments $WHERE_OWNER
	order by MB desc
)
where rownum <= $ROWNUM;
"
# -----------------------------------------------------
# List counts of buffer in memory compared to blocks to object
# -----------------------------------------------------

elif [ "$ACTION" = "BUF" ];then
#Copyright © 2005 by Rampant TechPress
SQL=" set lines 190 pages 66
break on c0
select t1.owner c0, object_name  c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
   buffer_pool                                       c5,
   sum(bytes)/sum(blocks)                            c6
from (
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v\$bh         bh
where
   o.data_object_id  = bh.objd and
   o.owner not in ('SYS','SYSTEM') and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
) t1,
   dba_segments s
where
   s.segment_name = t1.object_name and
   s.owner = t1.owner and
   s.segment_type = t1.object_type and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   t1.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc;
"
# -----------------------------------------------------
# List ITL and buffer busy wait
# -----------------------------------------------------
elif [ "$ACTION" = "BUSY" ];then
SQL="
col itl head 'ITL Waits'
col rlw head 'Row Lock Waits'
col pr head 'Physical Reads'
col lr head 'Logical Reads'
col bbw head 'Buffer Busy Waits'
col obj head 'Objects'
select * from
    (
       select
          DECODE
          (GROUPING(a.object_name), 1, 'All Objects', a.object_name)
        obj,
    sum(case when a.statistic_name = 'ITL waits' then a.value else null end) itl,
    sum(case
             when a.statistic_name = 'buffer busy waits' then a.value
             when a.statistic_name = 'gc buffer busy waits' then a.value
              else null end) bbw ,
    sum(case when a.statistic_name = 'row lock waits' then a.value else null end) rlw ,
   sum(case when a.statistic_name = 'physical reads' then a.value else null end) pr ,
   sum(case when a.statistic_name = 'logical reads' then a.value else null end) lr
   from v\$segment_statistics a 
  group by rollup(a.object_name)) b where (b.itl>0 or b.bbw >0)
/
"
# -----------------------------------------------------
# Query found on metallink forum from  Andrew Allen.
#  Touch counts on segments blocks in SGA
# -----------------------------------------------------
elif [ "$ACTION" = "TCH" ];then
SQL="set linesize 148 

col owner for a12 
COL tch FOR 9,999 HEAD 'Touch|Count' 
COL file_name FOR a40 
COL dbablk HEAD 'Block Num' 
COL hladdr HEAD 'Cache Buffer|Chain Latch|Address' 
col object_name format a30

PROMPT List the top 100 data blocks by touch counts. A ZERO touch count does not 
PROMPT necessarily mean a cold block because the touch count gets reset to zero 
PROMPT when a block is moved from the cold to the hot end of the LRU list. 
PROMPT . 

SELECT a.hladdr, a.file#, 
  -- f.name AS file_name, 
  a.dbablk, a.tch, a.obj, b.object_type, b.owner, b.object_name 
FROM 
   (select * from (SELECT hladdr, file#, dbablk, tch, obj FROM x\$bh ORDER BY tch DESC ) where rownum < $ROWNUM) a, 
   dba_objects b, 
   v\$datafile f 
WHERE     ( a.obj = b.object_id OR a.obj = b.data_object_id) 
      AND a.file# = f.file# 
ORDER BY 
a.tch  desc ; 
"
# -----------------------------------------------------
# Query found on metallink forum from  Andrew Allen.
#  List all hot segments
# -----------------------------------------------------
elif [ "$ACTION" = "HOT" ];then
SQL="set linesize 150
COL owner FOR a16
COL object_name FOR a28
COL sub FOR a28
COL object_type FOR a20
COL statistic_name FOR a30 
COL value FOR 99,999,999,999,999 HEAD 'Hits' 
col object_type format a18

SELECT 
    owner, object_name, nvl(SUBOBJECT_NAME,'-') sub ,object_type, statistic_name, value 
FROM dba_objects, 
     (SELECT statistic_name, value, obj# FROM 
            (SELECT a.*, MAX(value) OVER (partition by statistic_name) mv 
                   FROM v\$segstat a 
             WHERE statistic_name IN 
                    ( 'logical reads', 'physical reads', 'physical writes', 'physical reads direct', 'physical writes direct' ) )
     WHERE value = mv AND mv <> 0
     ) b 
WHERE dba_objects.object_id = b.obj# order by 1, 2; 
"

# ------------------------------
#  system wide stats
# ------------------------------
elif [ "$ACTION" = "ONEOBJ" ];then
SQL="break on obj_name on tablespace_name
 select owner||'.'||object_name obj_name, tablespace_name, statistic_name,  value
                   from v\$segment_statistics where obj#=$OBJ_ID order by 1  ;"

# ------------------------------
#  system wide stats
# ------------------------------
elif [ "$ACTION" = "FILTER1" ];then
SQL="break on statistic_name on obj_name
select statistic_name, value,obj_name, SUBOBJECT_NAME, tablespace_name
       from (select statistic_name, owner||'.'||object_name obj_name, SUBOBJECT_NAME, value, tablespace_name
                   from v\$segment_statistics where statistic# = $STATN order by value desc) 
     where rownum < $ROWNUM ;"
#SQL="select statistic_name, owner||'.'||object_name obj_name, SUBOBJECT_NAME, value, tablespace_name,
#            rank() over ( order by value desc) rank
#    from v\$segment_statistics where statistic# = $STATN and rownum < $ROWNUM
#    order by value ;"


# ------------------------------
#  system wide stats
# ------------------------------
elif [ "$ACTION" = "LIST" ];then
  SQL="select distinct statistic_name, statistic# from v\$segment_statistics order by 1;"

# ------------------------------
#  system wide stats
# ------------------------------
elif [ "$ACTION" = "STATS" ];then
  SQL="select count(1) cpt , statistic#, statistic_name, sum(value) value 
        from v\$segment_statistics  group by statistic_name , statistic#
       order by statistic_name;"
fi
$SETXV

if [ "$VERBOSE"  = "TRUE" ];then
   echo "$SQL"
fi

sqlplus -s "$CONNECT_STRING" <<EOF

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

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       '$TITTLE (help : seg -h)  ' nline
from sys.dual
/
set linesize 132 head on pagesize 66
col segment_name format A34
col TABLESPACE_NAME format A20
col SEGMENT_TYPE format A8 head "segment|type"
col statistic_name format A27 head "Statistics name"
col cpt format 9999999 head "Count"
col rank noprint
col obj_name format a37
col subobject_name format a27
column c0 heading 'Owner'                        format a16
column Owner heading 'Owner'                        format a16
column c1 heading 'Object|Name'                  format a30
column c2 heading 'Object|Type'                  format a18
column c3 heading 'Number of|Blocks in|Buffer|Cache' format 99,999,999
column c4 heading 'Percentage|of object|blocks in|Buffer' format 999
column c5 heading 'Buffer|Pool'                  format a7
column c6 heading 'Block|Size'                   format 99,999
$BREAK
$SQL
EOF