Oracle Memory Tuning-18


Memory Tuning

  1. Shared pool (LC, DDC)
  2. Database buffer cache (Default, Keep & Recycle)
  3. Redo buffer cache

Library cache:

To find out the issue we can use the following methods.

V$librarycache , AWR & OEM

V$librarycache Columns:

Namespace – Object Name

Gets – No. of parsing

Gethistratio – Ratio of Phrasing

Pins – No.of execution plan

Pinhistratio – Ratio of execution plan

Reloads – No. of rephrasing

Invalidation – No. of rephrasing for DDL alter table, Column invalidation.


Sql> select gethistratio100, pinhitration100 from v$librarycache;

90% – Good

<90% – Problem on LB.


Sql> select [sum(reloads)/sum(pins)] from v$librarycache;

<5% – Good

5% – Problem on LB.

Data Dictionary cache

V$rowcache, AWR, OEM


V$rowcache Columns:

Gets – Object definition taken from DDC.

Get misses – Object definition taken from system tablespace, since it could not get it from DDC.



Sql> select 1-[sum(getmisses)/sum(gets)]*100 from v$rowcache;

<15% – Good

15% – Problem on DDC.

We need to increase the shared pool size.

Increasing shared pool size:

We have an advisory.

Sql> select * from v$shared_pool_advice;

1.0 – Current size and after some value constant one is recommended.

How to minimize the workload on shared pool.

We can configure a reserved area/pinning object area for most frequent query. Non – frequent query will use shared pool.

  1. Pinning object area configuration:

Sql> Shared_pool_reserved_area_size = <size>; – 10% of shared pool size.

Sql> exec dbms_shared_pool.keep(‘payroll_pkg); – Package

We need to get the packages from application team. For remove ‘unkeep’.

  1. Bind Variable

We can give suggestion to developers to use it.

Ex: Sql> select * from emp where eno = 101/102/ … etc It has to phrase and create plan.

We can make it all in one, instead of each statement.

Sql> exec :a=101

Sql> select * from emp where eno=:a;

Sql> exec :a=102

Sql> select * from emp where eno=:a;


  1. Cursor options

Open_cursor = <300>. We can change that to 1000.

In OS there is a contextual area/cache memory. So the cursor will go to the OS cache instead of shared pool, if we have good memory on OS side.

Cursor_sharing = exact / similar / force

Exact- Will share more than one SQL query & execution plan

Similar- Will share more than one SQL query

Force- Will share more than one execution plan

Session_cached_cursors = 50;

We can change this to 100. To save a code for sessions.

Cursor_space_for_time = false / True;

“True” – will set zero and will not allow for reloads.

In worst case, Nothing works flush the shared pool.

Sql> alter system flush shared_pool; – it has a bug, sometimes it will hang a DB.


Database buffer cache

V$sysstat this view will give an information.


Physical reads, PR direct, PR direct (lob), session logical read. PR – read data from data file by OS cache.


Sql> select 1-[(p.vaue-d.value-l.value)/(s.value)]*100 from v$sysstat p, v$sysstat d, v$sysstat l, v$sysstat s where =’physical reads’ and =’physical reads direct’ and =’physical reads direct (lob)’ and =’session logical reads’;

90 % good and <90% problem.


Sql> alter system db_cache_advice = on;

Sql> select * from v$db_cache_advice;

Get the recommended size.

If it still not solved. Configure multiple buffer pool size.

Default –

Keep – Configure most frequently used data here

Parameter: Db_keep_cache_size =<size>

Recycle – Configure bulk data here. 2* default size

Parameter: Db_recycle_cache_size =<2*default>

Sql> alter table emp storage (buffer_pool keep/recycle/default);

Query to find out the tables are stored on keep.

Sql> select * from v$buffer_pool;

Query to find out the issue on keep and recycle

Sql> select 1-[(pr/ (dbblockgets+consistengets))]*100 from v$buffer_pool_statistics;

90% good & <90% problem.

If we have a problem here, we need to move data from DBC to OS cache.

Sql> alter table emp cache;

What are the tables are stored in cache.

Sql> select * from dba_table where cache=’y’;

Sql> alter table emp nocache; — To move back to DBC.

Use always advisory.


Redo log buffer cache

V$sysstat, AWR & OEM

V$sysstat columns:

Redo entries – New entry

Redo buffer allocation retries- Total no. of retries.

Redo log space request- The query hit on redo to write data, no space left.



Sql> select (r.vaue/e.value)*100 from v$sysstat r, v$sysstat e

where =redo buffer allocation retries’ and =redo entries’;

<1% – good and > 1 % problem



Sql> select name, value from v$sysstat

where name =’redo log space request’;

0 – Good and >0 – problem



Check the current redo log buffer size and increase by 20MB and 20MB so on, Monitor it by adding this, if the issue solves then leave it.

Another solution:

Sql> alter table emp nologging;

Nologging will stop going to redo log file.

Note: In case of disaster we cannot recover the data.


PGA Tuning

Workarea_size_policy = auto

Pga_aggregate_target=<size> – PGA size.

Sql> select [(onpass_execution100)/(total_execution)], [(optimal_execution100)/(total_execution)], [(multipass_execution*100)/(total_execution)] From v$sql_wrokarea_history;

We need to run this command more than one time. Like 5, 6 times. On the result if the multipass_execution increasing subsequently, we need to increase the PGA size.


Sql> select * from v$pgastat where name=’cache hit percentage’;

90% good & <90 – problem.


Solution: Run PGA advisory

Sql> select * from v$pg_target_advice;



Workarea_size_policy = Manual

Sort_area_size=<size> – PGA size.



Sort(disk) – Temporary tablespace sorting

Sort(memory) – PGA sorting

Sort(rows) – no. of rows sorted.


Sql> select (d.vaue/m.value)*100 from v$sysstat d, v$sysstat m

where =sort(disk) and =’sort (memory)’;

<5% – good and > 5 % problem



Check the sort area size.


Sort_area_size = db_block_size*max_sort_blocks

DB_block_size = 8192

Sql> select max_sort_blocks from v$sort_segement;


Sort_area_retained_size =<size>; The sort are size will move and retained here.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: