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 */
CREATE OR REPLACE PROCEDURE STATSUSER.vmstat3
(START_DATE DATE,
DURATION NUMBER,
SERVER_NAME VARCHAR2,
RUNQUE NUMBER,
BUSY_WAITS NUMBER,
RUNNABLE_WAITS NUMBER,
SWAP NUMBER,
FREE NUMBER,
PAGE_RECLAIMS NUMBER,
MINOR_FAULTS NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER,
PAGE_FREE NUMBER,
DESTROYED NUMBER,
SCAN_RATE NUMBER,
DEVICE_INTERRUPT NUMBER,
SYSTEM_CALLS NUMBER,
CONTEXT_SWITCHES NUMBER,
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
IDLE_CPU NUMBER,
WAITS_CPU NUMBER)
AS
active_users NUMBER;
total_users NUMBER;
uocwebsphere_users NUMBER;
uocscripts_users NUMBER;
BEGIN
INSERT INTO STATSUSER.STATS$VMSTAT2 VALUES(START_DATE,DURATION,SERVER_NAME,RUNQUE,BUSY_WAITS,RUNNABLE_WAITS, SWAP, FREE, PAGE_RECLAIMS, MINOR_FAULTS, PAGE_IN, PAGE_OUT, PAGE_FREE, DESTROYED, SCAN_RATE, DEVICE_INTERRUPT, SYSTEM_CALLS, 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 <> ‘ ‘ ;
SELECT COUNT(*) INTO uocwebsphere_users FROM v$session WHERE username <> ‘ ‘ AND status = ‘ACTIVE’ AND username like ‘UOC_UOCAPPQA%’;
SELECT COUNT(*) INTO uocscripts_users FROM v$session WHERE username <> ‘ ‘ AND status = ‘ACTIVE’ AND username= ‘UOCSCRIPTS’;
INSERT INTO STATSUSER.STATS$TOTALUSERS
(measured_date,total_users,active_users,uocwebsphere_users,uocscripts_users)
VALUES
(START_DATE,total_users,active_users,uocwebsphere_users,uocscripts_users);
INSERT INTO STATSUSER.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;
/
/* get_vmstat_solaris.ksh */
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=UOC
export ORACLE_SID
ORACLE_HOME=/oracle/app/oracle/product/10.1
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$$
# Note that Solaris does not have a wait CPU column
cat /tmp/msg$$|sed 1,3d | awk ‘{ printf(“%s %s %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, $17, $18, $19, $20, $21, $22) }’ | while read RUNQUE BUSY_WAITS RUNNABLE_WAITS SWAP FREE PAGE_RECLAIMS MINOR_FAULTS PAGE_IN PAGE_OUT PAGE_FREE DESTROYED SCAN_RATE DEVICE_INTERRUPT SYSTEM_CALLS CONTEXT_SWITCHES USER_CPU SYSTEM_CPU IDLE_CPU
do
echo $RUNQUE
echo $BUSY_WAITS
echo $RUNNABLE_WAITS
echo $SWAP
echo $FREE
echo $PAGE_RECLAIMS
echo $MINOR_FAULTS
echo $PAGE_IN
echo $PAGE_OUT
echo $PAGE_FREE
echo $DESTROYED
echo $SCAN_RATE
echo $DEVICE_INTERRUPT
echo $SYSTEM_CALLS
echo $CONTEXT_SWITCHES
echo $USER_CPU
echo $SYSTEM_CPU
echo $IDLE_CPU
$ORACLE_HOME/bin/sqlplus -s statsuser/statsuser <
EXIT
EOF
done
done
rm /tmp/msg$$