X

Alejandro Vargas' Blog

  • December 6, 2006

Shared Pool Checkups II

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

set echo on pagesize 150
spool shared_pool_check.lis

rem Needed Queries to get the Shared pool status
rem ============================================

rem Is there a reload problem ?
rem ===========================
select namespace,pins,reloads from v$librarycache;
show parameters shared_pool
select bytes/1024/1024 from v$sgastat where pool='shared pool' and name='free memory';

rem Find the large queries in the shared pool library cache
rem (using more than 4mb each)
rem ===========================================================

SELECT sql_text "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY sql_text
HAVING sum(sharable_mem) > 4096000;

rem
rem Objects that we should consider pining into the shared pool
rem ============================================================
column name format a40
column owner format a15

select owner,name,executions,locks,pins,loads,kept
from v$db_object_cache
where loads > 10;

rem
rem
Rem LRU work in the shared pool
rem KSMLRNUM stores the number of objects that were flushed to load
rem the large object. KSMLRISZ stores the size of the object that was
rem loaded (contiguous memory allocated)
rem ==================================================================
column ksmlrcom format a20
column username format a5
select
username,sid,KSMLRCOM,KSMLRSIZ,KSMLRNUM,
KSMLRHON,KSMLROHV,KSMLRSES
from x$ksmlru , v$session
where KSMLRSES=SADDR and
KSMLRNUM >2 ;


rem How much are waiting for Library Cache Latch
rem ============================================
select count(*),event from v$session_wait
where event not like '%SQL%' and event not like '%ipc%' and event not like '%timer%'
GROUP BY EVENT;

select count(*),wait_time from v$session_wait
where event='latch free' and p2=106  group by wait_time;

select sid,wait_time,seconds_in_wait from v$session_wait
where event='latch free' and p2=106 and  WAIT_TIME>1;

rem
rem
rem Find the queries which are identical but aren't shared
rem We should want this query to return 0 rows.
rem ========================================================


SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,240) "SQL"
FROM v$sqlarea
WHERE version_count > 10;


rem
rem See the biggest chunk of free memory and how many chuncks we have
rem ========================================================
select sysdate,
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
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 ))) ;



rem
rem See the shared pool parameters
rem =================================
column name format a30
select name,value from v$parameter where name like '%shared_pool%' ;

select x.ksppinm, y.ksppstvl from x$ksppi x , x$ksppcv y
where x.indx = y.indx and lower(x.ksppinm) like '%spin%';

SELECT count(*) FROM v$latch_children WHERE NAME = 'library cache';  

rem
rem Check the shared pool reserved size status
rem ===================================

SELECT free_space, avg_free_size, used_space,
avg_used_size, REQUEST_MISSES,request_failures, last_miss_size
FROM v$shared_pool_reserved;


Rem
rem When having multiple subheaps:
rem =================================
select KSMCHIDX,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',
3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k',
9,'9-10k',
'> 10K'), count(*),
ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp
where KSMCHCOM = 'free memory'
group by KSMCHIDX,ksmchcom, ksmchcls,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',
3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k',
9,'9-10k','> 10K');

spool off

Join the discussion

Comments ( 2 )
  • Vivek Sharma Thursday, December 7, 2006
    Good One..
    For the queries that are identical, one can make use of v$sql_shared_cursor from Oracle 9i and above. This view enables the dba to know the reason for this mismatch.
  • Alejandro Vargas Thursday, December 7, 2006
    Vivek, even on 8i you may identify statements having many versions and match their address on v$sql_shared_cursor to investigate the cause of the mismatch.
    The structure of v$sql_shared_cursor is the same on 8i and 9i. On 10g instead of KGLHDPAR thal held the parent address on 8i-9i we have ADDRESS, and 8i-9i ADDRESS is on 10g CHILD_ADDRESS.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.