X

Alejandro Vargas' Blog

  • May 16, 2006

Checkup Reserved Size Usage

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.