Memory Tuning
- Shared pool (LC, DDC)
- Database buffer cache (Default, Keep & Recycle)
- 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.
Formula:
Sql> select gethistratio100, pinhitration100 from v$librarycache;
90% – Good
<90% – Problem on LB.
OR
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.
Formula:
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.
- 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’.
- 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;
- 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;
10.2.0.4.0 – it has a bug, sometimes it will hang a DB.
Database buffer cache
V$sysstat this view will give an information.
Columns:
Physical reads, PR direct, PR direct (lob), session logical read. PR – read data from data file by OS cache.
Formula:
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 p.name =’physical reads’ and
d.name =’physical reads direct’ and
l.name =’physical reads direct (lob)’ and
s.name =’session logical reads’;
90 % good and <90% problem.
Solution:
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.
Formula:
Sql> select (r.vaue/e.value)*100 from v$sysstat r, v$sysstat e
where r.name =redo buffer allocation retries’ and
e.name =redo entries’;
<1% – good and > 1 % problem
OR
Sql> select name, value from v$sysstat
where name =’redo log space request’;
0 – Good and >0 – problem
Solution:
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.
OR
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;
Scenario:
Workarea_size_policy = Manual
Sort_area_size=<size> – PGA size.
V$sysstat
Sort(disk) – Temporary tablespace sorting
Sort(memory) – PGA sorting
Sort(rows) – no. of rows sorted.
Formula:
Sql> select (d.vaue/m.value)*100 from v$sysstat d, v$sysstat m
where d.name =sort(disk) and
m.name =’sort (memory)’;
<5% – good and > 5 % problem
Solution:
Check the sort area size.
Recommended:
Sort_area_size = db_block_size*max_sort_blocks
DB_block_size = 8192
Sql> select max_sort_blocks from v$sort_segement;
OR
Sort_area_retained_size =<size>; The sort are size will move and retained here.
