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
