get_vmstat_linux

CREATE TABLE STATSUSER.STATS$TOTALUSERS

(

MEASURED_DATE DATE,

TOTAL_USERS NUMBER,

ACTIVE_USERS NUMBER,

UOCWEBSPHERE_USERS NUMBER,

UOCSCRIPTS_USERS NUMBER

);

CREATE TABLE STATSUSER.STATS$USERSESSIONS

(

START_DATE DATE,

SYSTEM_SESSIONS NUMBER,

ACTIVE_USERS NUMBER,

INACTIVE_USERS NUMBER,

DEDICATED_SERVERS NUMBER,

USER_SESSIONS NUMBER,

TRANSACTIONS NUMBER

);

CREATE INDEX STATSUSER.STATS$USERSESSIONS_STDTE_I ON STATSUSER.STATS$USERSESSIONS

(START_DATE);

/* Following procedure and shell program is for Solaris */

#!/bin/ksh

# This is the Linux version

ORACLE_SID=WEBP18F

export ORACLE_SID

ORACLE_HOME=/oracle/app/oracle/product/10.2

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

SERVER_NAME=`uname -a|awk ‘{print $2}’`

typeset -u SERVER_NAME

export SERVER_NAME

# sample every five minutes (300 seconds) . . . .

SAMPLE_TIME=300

while true

do

vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# run vmstat and direct the output into the Oracle table . . .

cat /tmp/msg$$|sed 1,3d | awk ‘{ printf(“%s %s %s %s %s %s %s %s %s %s %s %s %s %s %s %sn”, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) }’ | while read RUNQUE BUSY_WAITS SWAP FREE BUFF CACHE SWAP_IN SWAP_OUT BLOCKS_IN BLOCKS_OUT DEVICE_INTERRUPT CONTEXT_SWITCHES USER_CPU SYSTEM_CPU IDLE_CPU WAIT_CPU

do

# echo runqueue $RUNQUE

# echo busywaits $BUSY_WAITS

# echo swap $SWAP

# echo free $FREE

# echo buff $BUFF

# echo cache $CACHE

# echo swapin $SWAP_IN

# echo swapout $SWAP_OUT

# echo blockin $BLOCKS_IN

# echo blockout $BLOCKS_OUT

# echo int $DEVICE_INTERRUPT

# echo cs $CONTEXT_SWITCHES

# echo uc $USER_CPU

# echo sc $SYSTEM_CPU

# echo ic $IDLE_CPU

# echo wc $WAIT_CPU

$ORACLE_HOME/bin/sqlplus -s / < execute PERFSTAT.VMSTAT3(SYSDATE, $SAMPLE_TIME, ‘$SERVER_NAME’, $RUNQUE, $BUSY_WAITS, $SWAP, $FREE, $BUFF, $CACHE, $SWAP_IN, $SWAP_OUT, $BLOCKS_IN, $BLOCKS_OUT, $DEVICE_INTERRUPT, $CONTEXT_SWITCHES, $USER_CPU, $SYSTEM_CPU, $IDLE_CPU, $WAIT_CPU);

EXIT

EOF

done

done

rm /tmp/msg$$

create or replace PROCEDURE vmstat3

(START_DATE DATE, DURATION NUMBER, SERVER_NAME VARCHAR2,

RUNQUE NUMBER, BUSY_WAITS NUMBER, SWAP NUMBER,

FREE NUMBER, BUFF NUMBER, CACHE NUMBER,

SWAP_IN NUMBER, SWAP_OUT NUMBER, BLOCKS_IN NUMBER,

BLOCKS_OUT NUMBER, DEVICE_INTERRUPT NUMBER, CONTEXT_SWITCHES NUMBER,

USER_CPU NUMBER, SYSTEM_CPU NUMBER, IDLE_CPU NUMBER,

WAITS_CPU NUMBER)

AS

active_users number;

total_users number;

BEGIN

INSERT INTO PERFSTAT.STATS$VMSTAT2 VALUES

(START_DATE, DURATION , SERVER_NAME ,

RUNQUE, BUSY_WAITS , SWAP ,

FREE, BUFF , CACHE ,

SWAP_IN, SWAP_OUT , BLOCKS_IN ,

BLOCKS_OUT, DEVICE_INTERRUPT , CONTEXT_SWITCHES ,

USER_CPU, SYSTEM_CPU , IDLE_CPU ,

WAITS_CPU );

select count(*) into active_users From v$session where username <> ‘ ‘ and status = ‘ACTIVE’;

select count(*) into total_users From v$session where username <> ‘ ‘ ;

INSERT INTO PERFSTAT.STATS$TOTALUSERS

(measured_date,total_users,active_users)

values

(START_DATE,total_users,active_users);

INSERT INTO PERFSTAT.STATS$USERSESSIONS select sysdate,sum(decode(type,’BACKGROUND’,1,0)) system_sessions,

sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,1,0))) active_users,

sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,0,1))) inactive_users,

sum(decode(type,’BACKGROUND’,0,decode(server,’DEDICATED’,1,0))) dedicated_servers,

sum(decode(type,’BACKGROUND’,0,1)) user_sessions,

sum(decode(taddr,”,0,decode(status,’ACTIVE’,1,0))) transactions

from v$session;

commit;

END;