Monday, October 22, 2012

9 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




  1. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Oracle course in chennai

  2. Share poles are generally those people telescopic a fishing rod calibrating any where from while small while a few foot for you to providing thirty foot. These kind of damages poles most often have sometimes a world wide web or possibly a comb that come with one particular stop, nevertheless you should purchase various other equipment also. telescopic pool pole

  3. Les montres Rolex fausses rolex montress ont fabriquées à partir des meilleures matières premières et fausses rolex cellini montres sont assemblées avec une attention particulière aux détails. Chaque composant est conçu, développé et produit en interne selon les normes les plus strictes.

  4. There's a zig-zag stripe design running down the buy cheap soccer jerseys side of the England Euro 2020 kit, cheap national team jerseys just like the one on the Netherlands jersey. It's dark blue with a red-blue center stripe - a detail that's repeated on the crew-neck collar.Just as always with Nike, the Euro 2020 kits, including England's, will be released in March. The England 2020 away kit will be launched together with the home, just like 2016 and 2018.

  5. Alexander McQueen serves up a collection for men and replica shoes alexander mcqueen women of elevated everyday styles, tailoring rich in tradition and singular pieces with a gothic-chic sensibility. Dramatic accessories – think leather and chains and skull-motif embellishments – top off the line that's a hit with all the critics.

  6. From cool glitter trainers to decadent velvet sandals, Alexander McQueen shoes for new season ALEXANDER MCQUEEN Bags For Sale are fit for a dark historical romance. cheap ALEXANDER MCQUEEN boot womens We have selected the key styles of the season to fall for here.

  7. This is a staggering site; I was endeavoring to figure out some approach to keep in contact with your blog until I found a decent information about tools. I genuinely wish you to have a fair day and keep with the incredible work! cloud training in bangalore

  8. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. Poolleiter

  9. Thanks for sharing nice information with us. i like your post and all you share with us is uptodate and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job. Stahlwandpool



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