- 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 PoolShared 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 parseWhen 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?
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",
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
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
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
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 poolBelow 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(used_space/1024/1024,1) "used space",
request_misses/( requests + 0.0001) * 100 "Request misses ratio",
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 ratioAbove 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 PoolAs 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
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;
V$DB_OBJECT_CACHEshow each object use how much memory in the library cache
select substr(owner,1,10) "OWNER",
sharable_mem "MEMORY USED",
substr(KEPT || ' ',1,4) "KEEP"
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
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
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_PLANsee here
V$LIBRARYCACHEif 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 %",
- 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/