Tuning Shared Pool & Tuning Large Pool


Shared pool includes cursors, sql statements, plsql blocks which are parsed/executed and stored/cached in memory. It is important to optimize the plsql blocks and/or sql commands so sessions can use the same commands without parsing but only executing the statements. This improves the application response times. Hints to optimize your sql code will be given in the following sections.

There are also some system paramters that you can decide to set (or not to set) to improve your instance performance. These parameters are covered below.

--
-- system wide statistics for parsing and execution of statements
--
-- the statement sent for parsing and found in cursor cache, this means the
statement is not really parsed
select * from v$sysstat where name like 'session cursor cache hits';
-- the count for sql parses
select * from v$sysstat where name like 'parse count (total)';
-- the count for sql executions
select * from v$sysstat where name like 'execute count';
--
-- the real parse count for executions
-- desired result should be as close as to zero
--
select total_parse/execute_count from
(
select (t2.value - t1.value) total_parse from v$sysstat t1, v$sysstat t2
where t1.name='session cursor cache hits'
and t2.name='parse count (total)'
) t1,
(
select value execute_count from v$sysstat where name='execute count'
) t2;

NOTE:

If the result of the above query is far from zero this means oracle instance loses some time for parsing the statement instead of executing them. First action should be reviewing sql statements of the application. Bind variables should be a proper approach. We will look at database wide tuning actions.

--
-- session wide (user level) statistics for parsing and executin of
statements
--
-- session wide statistics are same as system wide. but there is only
-- a SID column to fetch only the desired session statistics
select
t1.value,
t2.name,
t3.sid,
t3.username,
t3.machine
from
v$sesstat t1,
v$statname t2,
v$session t3
where
t1.statistic# = t2.STATISTIC# and
t1.sid = t3.sid and
t2.name in ('session cursor cache hits','parse count (total)','execute
count') and
username is not null
order by t3.sid, t1.value desc;
--
-- look for any cursors cached for a session, and compare with system
parameter
--
select
t1.value curr_cached,
t4.value max_cached,
t3.username,
t3.sid,
t3.serial#
from
v$sesstat t1,
v$statname t2,
v$session t3,
v$parameter2 t4
where
t1.statistic# = t2.statistic# and
t1.sid=t3.sid
and t4.name='session_cached_cursors'
and t2.name = 'session cursor cache count';
--
-- obtaining cache hit ratios of the sqlarea and some other memory
allocations
--
select * from v$librarycache;
/*
Desired value for gethitratio is over %90
gets: number of lookups (parse)
pins: number of reads (execution)
reloads: the number of library cache misses
invalidations: if an object is modified
*/
--
-- comparison for executions and misses from library cache
--
-- desired value of course should be as small as possible like < %1
select
sum(pins) "executions" ,
sum(reloads) "cache misses" ,
sum(reloads) / sum(pins)
from
v$librarycache;
-- examine the sqlarea
--
select
t1.sql_text,
t1.users_executing,
t1.executions,
t1.disk_reads,
t1.buffer_gets,
t1.first_load_time,
t2.username
from
v$sqlarea t1,
dba_users t2
where
t1.parsing_user_id = t2.user_id
order by disk_reads desc;
--
-- you can examine execution plans of the queries that are currently in
memory
-- and who are running then
--
select
t1.hash_value,
t1.operation,
t1.options,
t1.optimizer,
t1.object_owner || '.' || t1.object_name,
t2.sql_text,
t2.users_executing,
t3.username
from
v$sql_plan t1,
v$sqlarea t2,
v$session t3
where
t1.hash_value = t2.hash_value and
t2.address = t3.sql_address;
--
--
--
select * from v$rowcache
/*
Gets : numberof requests on objects
Getmisses : number of requests in resulting misses
*/

NOTES

1- You can find out the queries currently in sqlarea. But tuning the sql statements will be covered later in this document. Nowly we are concantrated on tuning shared_pool tuning.

2- If you can’t find any cached cursors, you should check for session_cached_cursors system parameter. Fore xample in my test database the following query was returning ‘0’ which means no cursor (statement) is cached in memory. As a result all the queries of a session were reparsed by oracle.

--
-- session_cached_cursors system parameter
--
select value from v$parameter where name='session_cached_cursors';
alter system set session_cached_cursors=50 scope=spfile;
-- changes will take effect after instance restart
3- Another parameter is cursor_sharing which effects the sql performance in
an instance.
--
-- cursor_sharing init parameter
--
select name, value from v$parameter where name='cursor_sharing';
alter system set cursor_sharing='SIMILAR';
-- alter system set cursor_sharing=''[EXACT|SIMILAR|FORCE]';
4- If you dont have a plenty of memory then you can set the following
parameter for faster execution of some cursors
--
-- cursor_space_for_time
--
select * from v$parameter where name='cursor_space_for_time';
alter system set cursor_space_for_time=TRUE scope=spfile;
-- changes will take effect after instance restart
-- available values: [TRUE|FALSE]
5- Dont forget, sometimes you should decrease the value of the
shared_pool_size, check the following;
if
       Request_miss = 0 or not increasing
       Free_memory >= %50 of the shared_pool_reserved_size
Then
       Decrease the size of the shared_pool
       End if
list of views for shared_pool management
v$sysstat
v$sesstat
v$statname
v$session
v$sga
v$sgastat
v$librarycache
v$sql                //contains one row for each child of the original
                     sql text same as v$sqlarea but group by clause
v$sqlarea           //full statistics about shared cursors
v$sqltext           //full sql text without truncation
dba_users
v$db_object_cache
v$rowcache
parameters
          session_cached_cursors
          cursor_sharing
          shared_pool_size
          shared_pool_reserved_size
          cursor_space_for_time
          open_cursors
Tuning Large_pool
 
Used for I/O Processes
Backup and restore operatins
If set in shared server then this will be used for users instead of PGA.

--
-- changing large_pool_size parameter
--
 
select * from v$parameter where name like '%large_pool_size%';
alter system set large_pool_size=50M;
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: