Shared Pool Checkup

#!/usr/bin/ksh
# set -x
# chkshared
# This script perform shared memory checks
# Alejandro

. .myprofile
cd /oracle/scripts/av/freezedb/chkshareds
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
spool $newsid.chkshared.$ts

set pages 50000 lines 120;

select  to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Report Time"
from    dual
/

prompt
prompt * Library Cache
prompt

column gethitratio format 999.99 heading "GHR"
column pinhitratio format 999.99 heading "PHR"
column namespace format a10

select  namespace, gets, gethits, gethitratio, pins, pinhits,
        pinhitratio, reloads, invalidations
from    v$librarycache
/

prompt
prompt * Free Memory
prompt

select  *
from    v$sgastat
where   name like '%free%'
/

prompt
prompt * v$sqlarea statistics
prompt

select  max(version_count), max(invalidations), max(loaded_versions),
        max(sharable_mem)
from    v$sqlarea
/

prompt
prompt * Rate of flushing
prompt

select  ksmlrcom, ksmlrsiz, ksmlrnum, ksmlrhon, ksmlrohv, ksmlrses
from    x$ksmlru where ksmlrnum > 0
/

prompt
prompt * Sessions Waiting (wait_time=0) for non idle events
prompt

column p1 format 999999999999
column p2 format 999999999999
column event format a35

select  sid,event,p1,p2,p3,seq#
from    v$session_wait
where   wait_time = 0 and event not in
        ('smon timer', 'pmon timer', 'rdbms ipc reply', 'rdbms ipc message',
        'SQL*Net message from client')
order by sid
/

prompt
prompt * Memory consumed by objects
prompt

select  max(sharable_mem), max(loads)
from    v$db_object_cache
/

prompt
prompt * lru_stats
prompt

column kghlurcr heading "RECURRENT|CHUNKS"
column kghlutrn heading "TRANSIENT|CHUNKS"
column kghlufsh heading "FLUSHED|CHUNKS"
column kghluops heading "PINS AND|RELEASES"
column kghlunfu heading "ORA-4031|ERRORS"
column kghlunfs heading "LAST ERROR|SIZE"

select  kghlurcr,
        kghlutrn,
        kghlufsh,
        kghluops,
        kghlunfu,
        kghlunfs
from    sys.x$kghlu
where   inst_id = userenv('Instance')
/


SELECT  DECODE (SIGN (ksmchsiz - 812),
        -1, (ksmchsiz - 16) / 4,
        DECODE (SIGN (ksmchsiz - 4012),
        -1, TRUNC ((ksmchsiz + 11924) / 64),
        DECODE (SIGN (ksmchsiz - 65548),
        -1, TRUNC (1 / LOG (ksmchsiz - 11, 2)) + 238,
        254
        )
        )
        ) bucket,
        SUM (ksmchsiz) free_space, COUNT (*) free_chunks,
        TRUNC (AVG (ksmchsiz)) average_size, MAX (ksmchsiz) biggest
FROM    SYS.x$ksmsp
WHERE   inst_id = USERENV ('Instance') AND ksmchcls = 'free'
GROUP   BY DECODE (SIGN (ksmchsiz - 812),
        -1, (ksmchsiz - 16) / 4,
        DECODE (SIGN (ksmchsiz - 4012),
        -1, TRUNC ((ksmchsiz + 11924) / 64),
        DECODE (SIGN (ksmchsiz - 65548),
        -1, TRUNC (1 / LOG (ksmchsiz - 11, 2)) + 238,
        254
        )
        )
        )
/


prompt
prompt * ksmsp 1
prompt

select  ksmchcom contents,
        count(*) chunks,
        sum(decode(ksmchcls, 'recr', ksmchsiz)) recreatable,
        sum(decode(ksmchcls, 'freeabl', ksmchsiz)) freeable,
        sum(ksmchsiz) total
from    x$ksmsp
where   inst_id = userenv('Instance') and
        ksmchcls not like 'R%'
group   by ksmchcom
/


select  ksmchcom ChunkComment, ksmchcls Status, sum(ksmchsiz) Bytes
from    sys.x$ksmsp
group   by ksmchcom, ksmchcls
/


SELECT  free_space, avg_free_size, used_space,
        avg_used_size, request_failures, last_failure_size
FROM    v$shared_pool_reserved
/

prompt
prompt * Open cursors per session (more than 500)
prompt

select  sid,count(*)
from    v$open_cursor
group   by sid having count(*) >500
/

prompt
prompt * Total Open Cursors
prompt

col "Total Open Cursors" for 999,999,999,999
select  count(*) "Total Open Cursors"
from    v$open_cursor
/

prompt
prompt * Check if we have any memory leak in State Object chunks
prompt

select  *
from    v$sgastat -- (I need to check if we have any memory leak in State Object chunks)
/

prompt
prompt * Permanent memory allocations SGA Heap - x$ksmsp
prompt

select  ksmchsiz        "Chunk Size",
        count(ksmchsiz) "Chunks",
        sum(ksmchsiz)   "Total"
from    x$ksmsp
where   ksmchcls like '%perm'
group by ksmchsiz
order by ksmchsiz
/

prompt
prompt * Count(*) from x$kgllk - Library Cache Object Lock
prompt

select  count(*)
from    x$kgllk
/

prompt
prompt * Count(*) from x$kglpn - Library Cache Object Pins
prompt

select  count(*)
from    x$kglpn
/

spool off
exit
eof1
exit

# eof chkshared