select a.measured_date, a.total_users, a.active_users, b.user_cpu, b.system_cpu, b.idle_cpu, b.wait_cpu from stats$totalusers a, stats$vmstat2 b
where a.measured_date = b.start_date
and to_char(measured_date,’DD-MON-YY’)=’22-NOV-05′
and to_char(measured_date,’HH24′) > ’14’
and to_char(measured_date,’HH24′) < '19' order by measured_date desc
Script that populates stats$vmstat2 table is dbblkabva01:/export/home/oracle/oraprocs/get_vmstat2.ksh
——– vmstats.ksh ————-
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=BLINKT
export ORACLE_SID
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d’:’`
ORACLE_HOME=/oracle/app/oracle/product/8.1.7
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 DESTROYE
D 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 / <
PAGE_OUT,$PAGE_FREE,$DESTROYED,$SCAN_RATE,$DEVICE_INTERRUPT,$SYSTEM_CALLS,$CONTEXT_SWITCHES,$USER_CPU,$SYSTEM_CPU,$IDLE_CPU,0);
EXIT
EOF
done
done
rm /tmp/msg$$