#!/bin/ksh
# 1. Session Count(S)
loop1()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select substr(comm_name,1,20), 'CNT='||count(*) from v\$session
group by comm_name;
EOF
}
# 2. Statement Count(S)
loop2()
{
is -silent <<EOF
set heading off;
select 'STATEMENT_CNT='||count(*) from v\$statement;
EOF
}
# 3. memtbl
loop3()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select a.max_db_size, b.alloc_size, b.used_size from
(select 'MEM_MAX_DB_SIZE='||MEM_MAX_DB_SIZE/1024/1024||'M' max_db_size from v\$database) a,
(select 'ALLOC_SIZE='||ceil(sum(fixed_alloc_mem+var_alloc_mem)/1024/1024)||'M' AS alloc_size,
'USED_SIZE='||ceil(sum(fixed_used_mem+var_used_mem)/1024/1024)||'M' AS used_size
from v\$memtbl_info) b;
EOF
}
# 4. GC
loop4()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select GC_NAME, ADD_OID_CNT-GC_OID_CNT from v\$memgc
union all
select GC_NAME, ADD_TSS_CNT-GC_TSS_CNT from v\$diskgc;
EOF
}
# 5. replication
loop5()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select rep_name,rep_gap from v\$repgap;
select rep_name,status from v\$repsender;
select rep_name,peer_ip from v\$repreceiver;
EOF
}
# 6. datafile
loop6()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select name, maxsize*8/1024||'M' maxsize, currsize*8/1024||'M' currsize, autoextend, state from v\$datafiles;
EOF
}
# 7. memstat
loop7()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select 'ALLOC_SIZE='||ceil(sum(alloc_size)/1024/1024)||'M' alloc_size_M, 'MAX_SIZE='||ceil(sum(max_total_size)/1024/1024)||'M' max_size_M from v\$memstat ;
select name, ceil(alloc_size/1024)||'K' alloc_size_K, ceil(max_total_size/1024)||'K' max_total_size_K from v\$memstat order by 2 desc;
EOF
}
#vsz
loop8()
{
ps -o vsz -p $PID
}
# 9.tablespace
loop9()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select name
, total||'M' TOTAL
, used||'M' USED
, case2( ROUND(USED/TOTAL,4) * 100 > 100, '99.99', ROUND(USED/TOTAL,4) * 100)||'%' UTIL
from (
select name,c.maxsize*8192/1024/1024 total, used*b.A_EXTENT_PAGE_COUNT*b.PAGE_SIZE/1024/1024 used
from (select space_id, sum(extent_total_count) used
from v\$segment
group by space_id) a, v\$tablespaces b,
(select spaceid,sum(maxsize) maxsize from v\$datafiles
group by spaceid) c
where a.space_id = b.id
and b.id = c.spaceid
)
;
EOF
}
getDisk()
{
UNAME=`uname`
if [ $UNAME = "HP-UX" ]
then
bdf
else
df -k
fi
}
getProp()
{
echo "** Altibase Properties **"
altibase -v
grep "MEM_DB_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "DEFAULT_DISK_DB_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOG_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOGANCHOR_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOGANCHOR_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
echo "ALTIBASE is install ==> `which altibase`"
echo "** Disk arry **"
ls -lrt /
echo ""
}
#PID=`ps -ef|grep $HOME|grep "altibase -p" |grep -v grep|awk '{print $2}'`
PID=`ps -ef|grep altibase | grep "boot from" | grep -v grep | tail -1 | awk '{print $2}'`
PID=${PID:=0}
echo '# VSZ'
loop8
echo ' '
echo ' '
echo '#1 CONNECTION COUNT'
loop1
echo ' '
echo ' '
echo '#2 STATEMENT COUNT'
loop2
echo ' '
echo ' '
echo '#3 sum of memtbl'
loop3
echo ' '
echo ' '
echo '#4 GC'
loop4
echo ' '
echo ' '
echo '#5 replication'
loop5
echo ' '
echo ' '
echo '#6 datafile'
loop6
echo ' '
echo ' '
echo '#7 memstat'
loop7
echo ' '
echo ' '
echo '#8 tablespace'
loop9
echo ' '
echo ' '
echo '#9 disk'
getProp
echo '================================================'
getDisk
echo ' '
echo ' '
echo '#10 vmstat'
echo 'fre*4 = ?K'
vmstat 1 5
echo ' '
echo ' '
#!/bin/ksh
# 1. Session Count(S)
loop1()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select substr(comm_name,1,20), 'CNT='||count(*) from v\$session
group by comm_name;
EOF
}
# 2. Statement Count(S)
loop2()
{
is -silent <<EOF
set heading off;
select 'STATEMENT_CNT='||count(*) from v\$statement;
EOF
}
# 3. memtbl
loop3()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select a.max_db_size, b.alloc_size, b.used_size from
(select 'MEM_MAX_DB_SIZE='||MEM_MAX_DB_SIZE/1024/1024||'M' max_db_size from v\$database) a,
(select 'ALLOC_SIZE='||ceil(sum(fixed_alloc_mem+var_alloc_mem)/1024/1024)||'M' AS alloc_size,
'USED_SIZE='||ceil(sum(fixed_used_mem+var_used_mem)/1024/1024)||'M' AS used_size
from v\$memtbl_info) b;
EOF
}
# 4. GC
loop4()
{
is -silent <<EOF
set heading off;
set linesize 200;
set colsize 35;
select GC_NAME, ADD_OID_CNT-GC_OID_CNT from v\$memgc;
EOF
}
# 5. replication
loop5()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select rep_name,rep_gap from v\$repgap;
select rep_name,status from v\$repsender;
select rep_name,peer_ip from v\$repreceiver;
EOF
}
# 6. datafile
loop6()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select name, maxsize*8/1024||'M' maxsize, currsize*8/1024||'M' currsize, autoextend, state from v\$datafiles;
EOF
}
# 7. memstat
loop7()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select 'ALLOC_SIZE='||ceil(sum(alloc_size)/1024/1024)||'M' alloc_size_M, 'MAX_SIZE='||ceil(sum(max_total_size)/1024/1024)||'M' max_size_M from v\$memstat ;
select name, ceil(alloc_size/1024)||'K' alloc_size_K, ceil(max_total_size/1024)||'K' max_total_size_K from v\$memstat order by 2 desc;
EOF
}
#vsz
loop8()
{
ps -o vsz -p $PID
}
# 9.tablespace
loop9()
{
is -silent <<EOF
set linesize 200;
set colsize 35;
select
rpad(ts.name, 20) as TBS_NAME,
ts.max || 'M' as MAX_SIZE,
ts.used || 'M' as USED_SIZE
from
(
select
ts.name,
trunc(sum(df.maxsize)*8/1024,0) max,
trunc((ts.ALLOCATED_PAGE_COUNT*ts.page_size)/1024/1024,0) used
from
v\$tablespaces TS,
v\$datafiles DF
where
ts.id = df.spaceid
group by ts.name, ts.page_size, ts.total_page_count, ts.ALLOCATED_PAGE_COUNT
) ts
order by name;
EOF
}
getDisk()
{
UNAME=`uname`
if [ $UNAME = "HP-UX" ]
then
bdf
else
df -k
fi
}
getProp()
{
echo "** Altibase Properties **"
altibase -v
grep "MEM_DB_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "DEFAULT_DISK_DB_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOG_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOGANCHOR_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
grep "LOGANCHOR_DIR" $ALTIBASE_HOME/conf/altibase.properties | grep -v "\#"
echo "ALTIBASE is install ==> `which altibase`"
echo "** Disk arry **"
ls -lrt /
echo ""
}
PID=`ps -ef|grep altibase | grep "boot from" | grep -v grep | tail -1 | awk '{print $2}'`
PID=${PID:=0}
echo '# VSZ'
loop8
echo ' '
echo ' '
echo '#1 CONNECTION COUNT'
loop1
echo ' '
echo ' '
echo '#2 STATEMENT COUNT'
loop2
echo ' '
echo ' '
echo '#3 sum of memtbl'
loop3
echo ' '
echo ' '
echo '#4 GC'
loop4
echo ' '
echo ' '
echo '#5 replication'
loop5
echo ' '
echo ' '
echo '#6 datafile'
loop6
echo ' '
echo ' '
echo '#7 memstat'
loop7
echo ' '
echo ' '
echo '#8 tablespace'
loop9
echo ' '
echo ' '
echo '#9 disk'
getProp
echo '================================================'
getDisk
echo ' '
echo ' '
echo '#10 vmstat'
echo 'fre*4 = ?K'
vmstat 1 5
echo ' '
echo ' '
