1 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.

 

* 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 a.name = 'parse count (total)' and b.name='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)

ORDER BY 2 ;

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,

b.hash_value,

c.execnt,

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;

SHARED_POOL_SIZE & SHARDE_POOL_RESERVED_SIZE

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

select

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,

requests,

request_misses miss,

request_misses/( requests + 0.0001) * 100 "Request misses ratio",

request_failures fail,

request_failures/( requests + 0.0001) * 100 "Request failure ratio"

from

V$SHARED_POOL_RESERVED;

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

Reference

• http://psoug.org/reference/cursor_sharing.html
• https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
• http://dboptimizer.com/2010/06/08/cursor_sharing-a-picture-is-worth-a-1000-words/

V$SHARED_POOL_ADVICE

select * from v$shared_pool_advice;

Show the impact on the performance for the shared pool size

V$DB_OBJECT_CACHE

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",

executions,

sharable_mem "MEMORY USED",

substr(KEPT || ' ',1,4) "KEEP"

from v$db_object_cache

where type in ( 'TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')

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

V$SQL , V$SQLTEXT,V$SQLAREA, V$SQL_PLAN

see here

V$LIBRARYCACHE

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 %",

reloads,invalidations

from v$librarycache

Reference

• Result Cache http://www.oracle-developer.net/display.php?id=503
• Share Pool: http://coskan.wordpress.com/2007/09/14/what-i-learned-about-shared-pool-management/ 
• http://www.kingstone.com.tw/book/book_page.asp?kmcode=2013120140585
• http://docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#i21738