Monday, October 22, 2012

0 Shared Pool

Share pool is the memory area that Oracle instance store the recent used SQL statement, Data dictionary …etc.
  • Library Cache: Shared SQL area, PL/SQL procedure and Packages
  • Dictionary Cache: Database information, view, ( V$..) Tables ( DBA..) User information, permission…
  • Result Cache: store the query result , if the same query execute again, oracle would look for the result cache first. new features from 11g.

When SQL statement comes in, the oracle instance will assign one Shared SQL Area and Private SQL Area ( PGA). when next query comes in and oracle can find the query , it will reconstruct the Shared SQL area to save the space and save time to re parse the query. Shared pool is also used LRU algorism to release the memory.

 Description of Figure 14-9 follows

* Image from Oracle.

Shared reserved Pool

Shared reserved pool to reserve for the large object. because after many memory release/change in the shared pool, the shared pool memory could become fragmentation hence it may not be able to satisfy the larger object. The default size for qualify the “large” object is 4400byte. This can be control through the internal parameter _SHARED_POOL_RESERVED_MIN_ALLOC .
Below query can show the default size of _SHARED_POOL_RESERVED_MIN_ALLOC
select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode( bitand(y.ksppstvf,7),1, 'MODIFED',4, 'SYSTEM_MOD','FALSE') ismod, 
decode( bitand(y.ksppstvf,2),2, 'TRUE','FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id= userenv('Instance')
and y.inst_id=  userenv('Instance')
and x.indx= y.indx
and x.ksppinm = '_shared_pool_reserved_min_alloc'
order by translate(x.ksppinm,'_',' ');


Hard Parse & Soft parse

When SQL comes in, the oracle instance would exam if there is no same sql execute before, oracle instance would parse query, create the query plan …etc. This is called Hard Parse. If the query already execute before, then oracle would only check the permission, this is called soft parse.

How to Check the Hard Parse ratio?

select a.value as Total, b.value as "Hard Parse",round( b.value/a.value, 2) as Ratio

from v$sysstat a, v$sysstat b

where = 'parse count (total)' and'parse count (hard)';
The smeller of ration is better.

How to find those query require the hard parse?

This query is from here. however, the V$sqlarea only has the first 1000 char of the query.
SELECT substr(sql_text,1,40) "SQL", 
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)

I got below query from here, this query is using sql_text, which return the full query but the query is break down in multiple lines.
select  d.plan_hash_value plan_hash_value,d.execnt execnt, a.hash_value hash_value, a.sql_text sql_text 
from v$sqltext a, 
( select plan_hash_value,hash_value,execnt
from ( 
select  c.plan_hash_value,
rank() over ( partition by c.plan_hash_value order by b.hash_value) as hashrank
from   v$sql b, 
(   select count(*) as execnt,plan_hash_value
from v$sql
where plan_hash_value <> 0
group by plan_hash_value
having count(*) > 10
order by count(*) desc) c
where b.plan_hash_value = c.plan_hash_value
group by c.plan_hash_value, b.hash_value, c.execnt )
where hashrank <=3) d
where a.hash_value = d.hash_value
order by d.execnt desc , a.hash_value, a.piece;



show parameter shared_pool
The shared_pool_reserved_size is default to 5% of shared_pool_size.The size can be control through SHARED_POOL_RESERVED_SIZE.

Free size of the Shared pool and shared reserved pool

Below query show the free memory in the shared pool
select POOL,NAME, BYTES/1024/1024 "MB" from v$sgastat where name = 'free memory' and POOL='shared pool';
Below query show the free memory in the shared reserved pool
round(free_space/1024/1024,1) "free_space",
round(avg_free_size/1024/1024,1) avg_free,
round(used_space/1024/1024,1) "used space",
round(avg_used_size/1024/1024,1) avg_used,
request_misses miss,
request_misses/( requests + 0.0001) * 100 "Request misses ratio",
request_failures fail,
request_failures/( requests + 0.0001) * 100 "Request failure ratio"

What I don’t get is , base on share parameter shared_pool, the shared_pool_reserved_size is 5% of shared_pool_size (8.3MB) , why the V$SHARED_POOL_RESERVED show 9.3MB?

Shared pool reserved hit ratio

Above query also show the shared reserved pool miss hit ratio and failure ratio. Both should smaller than 1%, if it is larger the 1%, means the shared reserved pool size is too small and can not satisfy the large object. we need to increase the SHARED_POOL_RESERVED_SIZE.

Manually clean up the shared Pool

alter system flush shared_pool;
As you can see, after issue the command, the shared pool free memory increase from 18MB to 95MB.

How to keep the object in the Shared pool

see here

Cursor sharing

Cursor is to determine what kind of query would be treat as the same hence they can share the query plan. Every time , the query comes in, Oracle would assign a cursor to the query. The Cursor sharing is Oracle’s way to resolve the dynamic SQL without using the bind variable.

How to check the current setting


  • EXACT: Default Behavior
  • Similar: Will be removed in 12C per Metalink ID: 1169017.1
  • FORCE: optimizer will replace all the literal with bind



select * from v$shared_pool_advice;

Show the impact on the performance for the shared pool size


show each object use how much memory in the library cache
select substr(owner,1,10) "OWNER",
substr(type,1,12) "TYPE",
substr(name,1,20) "NAME",
sharable_mem "MEMORY USED",
substr(KEPT || ' ',1,4) "KEEP"
from v$db_object_cache
order by executions desc;

Show how many times the object has been load into the memory
select owner,sharable_mem, kept, loads,name
from v$db_object_cache
where loads >1
order by loads desc;

show the memory usage large than 10000 bytes but does not keep in the share pool
select owner,sharable_mem, kept, loads,name
from v$db_object_cache
where sharable_mem > 10000
and kept= 'NO'
order by sharable_mem

show the object type and its memory usage
select type,kept, count(*), sum(sharable_mem)
from v$db_object_cache
group by type,kept



see here


if gethitratio or pinhitratio not bigger enough, that means that there is too much “Hard Pass” may due to low shared pool or not bind variables in the query.
select namespace,gets,gethitratio* 100 "GETS %",pins,pinhitratio * 100 "PINS %",
from v$librarycache




Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates