- 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 poolselect 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;

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 cacheselect 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 hereV$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
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
ReplyDeleteOracle course in chennai
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
ReplyDelete