Checkup Reserved Size Usage
This check require 3 steps:1) create a table to gather the reserved size statistics:
drop table v_reserved_size_history;
create table v_reserved_size_history(
TIMESTAMC DATE,
FREE_SPACE NUMBER,
AVG_FREE_SIZE NUMBER,
FREE_COUNT NUMBER,
MAX_FREE_SIZE NUMBER,
USED_SPACE NUMBER,
AVG_USED_SIZE NUMBER,
USED_COUNT NUMBER,
MAX_USED_SIZE NUMBER,
REQUESTS NUMBER,
REQUEST_MISSES NUMBER,
LAST_MISS_SIZE NUMBER,
MAX_MISS_SIZE NUMBER,
REQUEST_FAILURES NUMBER,
LAST_FAILURE_SIZE NUMBER,
ABORTED_REQUEST_THRESHOLD NUMBER,
ABORTED_REQUESTS NUMBER,
LAST_ABORTED_SIZE NUMBER
)
tablespace tbs_monitor
/
2) Execute a script to populate it every 5 minutes:
#!/usr/bin/ksh
# set -x
# chkressize
# This script insert records to check for reserved size variations
# and generate a report
# Alejandro
. /jinn/app01/oracle/scripts/cshrc/817/.profile
td=/jinn/app01/oracle/scripts/av/freezedb/chkressizes
to=statgather
ts=`date +%d%m%Y%H%M`
cd $td
export ts
sid=$1
newsid=`echo $sid | tr '[a-z]' '[A-Z]'`
sqlplus -s /@dbaprod <<eof1
insert into $to.V_RESERVED_SIZE_HISTORIES
select
SYSDATE ,
'$sid' ,
FREE_SPACE ,
AVG_FREE_SIZE ,
FREE_COUNT ,
MAX_FREE_SIZE ,
USED_SPACE ,
AVG_USED_SIZE ,
USED_COUNT ,
MAX_USED_SIZE ,
REQUESTS ,
REQUEST_MISSES ,
LAST_MISS_SIZE ,
MAX_MISS_SIZE ,
REQUEST_FAILURES ,
LAST_FAILURE_SIZE ,
ABORTED_REQUEST_THRESHOLD ,
ABORTED_REQUESTS ,
LAST_ABORTED_SIZE
from v$shared_pool_reserved@$sid
/
col freespc for 999999999
col avgfree for 9999999
col maxfree for 9999999
col used for 999999999
col avgused for 9999999
col requests for 99999999
col failures for 99999999
col lstfailsz for 999999
col misses for 999999
col lstmisssz for 99999
col maxmisssz for 9999
set pages 50000 lines 100 echo off veri off
spool $td/$newsid.reserved_size.log
SELECT
to_char(timestamc,'dd/mm hh24:mi') collect_date,
free_space freespc,
-- avg_free_size avgfree,
max_free_size maxfree,
used_space used,
avg_used_size avgused,
requests,
request_failures failures,
last_failure_size lstfailsz,
request_misses misses,
last_miss_size lstmisssz
-- max_miss_size maxmisssz
FROM $to.v_reserved_size_histories -- v$shared_pool_reserved
where timestamc >= trunc(sysdate)
and dbname='$sid'
/
spool off
exit
eof1
v_chk=`cat $newsid.reserved_size.log | grep : | tail -1 | awk '{print $8}'`
if [ $v_chk -gt 1 ]; then
echo Failures to allocate segments on reserved area = $v_chk, Please check.
msg='$newsid $v_chk Failures to allocate segments on reserved area'
sendsms $msg
fi
# eof chkressize
3) use a script to check the statistics when required:
#!/usr/bin/ksh
# set -x
# chkreserved
# This script check reserved size behaviour
# Alejandro
. /jinn/app01/oracle/scripts/cshrc/817/.profile
cd /jinn/app01/oracle/scripts/av/freezedb/reserved_sizes
x1=`cat ../.151762`
ts=`date +%d%m%Y%H%M`
export ts
sid=$1
newsid=`echo $sid | tr '[a-z]' '[A-Z]'`
sqlplus -s sys/$x1@$sid <<eof1
set pages 100 lines 100
col freespc for 999999999
col avgfree for 9999999
col maxfree for 9999999
col used for 999999999
col avgused for 9999999
col requests for 99999999
col failures for 99999999
col lstfailsz for 999999
col misses for 999999
col lstmisssz for 99999
col maxmisssz for 9999
spool $newsid.reserved_size.$ts
spool $newsid.reserved_size.$ts
select x.ksppinm||'='||y.ksppstvl PARAMETERS
from x$ksppi x,
x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '%reserved%'
/
SELECT
to_char(timestamc,'dd/mm hh24:mi') collect_date,
free_space freespc,
-- avg_free_size avgfree,
max_free_size maxfree,
used_space used,
avg_used_size avgused,
requests,
request_failures failures,
last_failure_size lstfailsz,
request_misses misses,
last_miss_size lstmisssz
-- max_miss_size maxmisssz
FROM ops$oracle.v_reserved_size_history -- v$shared_pool_reserved
where timestamc >= sysdate -7 -- trunc(sysdate)
/
select STARTUP_TIME from v$instance
/
spool off
exit
eof1
# eof chkreserved
