Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 10 February 2013

Tuning Oracle 9i/10g SGA Parameters

Oracle 9i database administrator can fully allocate the oracle server's RAM memory upto 80 percent of the total RAM on the oracle server. Oracle recommends that 20 percent of the RAM memory on a database server be reserved for operating system tasks. Dynamic nature of this parameters help to grow and shrink the existing SGA parameters. Here in this article my concentration is to focus the performance tuning on existing SGA parameters. Check my other post for more details about SGA parameters:
SGA Sizing in Oracle 9i
Checking SGA Parameters:
Select pool, m_bytes
   from (select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
    from v$sgastat
    where pool is not null group by pool
    union
 select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
    from  v$sgastat
    where pool is null order by 2 desc
    )
    UNION ALL
    select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;
Tuning BUFFER CACHE:
It includes tuning the Initialization parameters DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE
Select name, value from v$parameter where name in ('db_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size') order by name;
This is the the most crucial parameter in Oracle database for performance tuning. If the DB_CACHE_SIZE is set too low, Oracle won’t have enough memory to operate efficiently and the system may run poorly, and If DB_CACHE_SIZE is too high, your system may begin to swap and may come to a halt. To have enough memory allocation, to store data in memory depends on the value used for DB_CACHE_SIZE. From the below script, you can check the current data cache hit ratio
Select name, value from v$parameter where name = 'db_cache_advice';
Initialization parameter DB_CACHE_ADVICE must be either ON, OFF, READY.
Select * from v$db_cache_advice;
A data cache hit ratio of 95 percent or greater should be achievable for a well-tuned transactional application with the appropriate amount of memory. Because there is such a performance difference between some disk devices and memory access, improving the data cache hit ratio from 90 to 95 percent can nearly double system performance when reading disk devices that are extremely slow. Improving the cache hit ratio from 90 to 98 percent could yield nearly a 500 percent improvement where disks are extremely slow and under the right architectural setup.
Select sum(decode(name,'physical reads',value,0)) phys, sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets', value,0)) con_gets, (1 - (sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))))) * 100 hitratio
from v$sysstat;

Select trunc( 1 - (phy.value - lob.value - dir.value)/ses.value ,4) as cache_hit_ratio
from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
where    ses.name = 'session logical reads' and    dir.name = 'physical reads direct'
and    lob.name = 'physical reads direct (lob)' and    phy.name = 'physical reads';
 
Select  name, trunc( 1 - (physical_reads / (db_block_gets + consistent_gets)) ,4) as hit_ratio
from v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0;
* The Cache Hit Ratio should be greater than 0.9 (> 90%). If it is less than 0.9 and the shared pool hit ratio is good consider increase DB_CACHE_SIZE init parameter.
Select name, value  from v$sysstat
where name = 'free buffer inspected';
* Consider increase the buffer cache size if there are high or increasing values for the statistic that shows the number of buffers skipped to find a free buffer.
* Check wait in some buffers in the buffer cache that multiple processes are attempting to access concurrently:
Select event, total_waits, time_waited
from v$system_event
where event in ('buffer busy waits');
* If the contention is on 'data block' check for:
SQL statements using unselective indexes.
Consider using automatic segment-space management or increase free-lists.
* If the contention is on 'undo header'
   Consider using automatic segment-space management or add more rollback segments.
* If the contention is on 'undo block'
   Consider using automatic segment-space management or make rollback segment sizes larger.
Select class, count, time
from  v$waitstat
where class in ('data block', 'undo header', 'undo block', 'segment header');
* If the contention is on 'segment header'
   Look for the segment and consider increase free-lists.
Select s.segment_name, s.segment_type, s.freelists,  w.wait_time,w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event = 'buffer busy waits'
and w.p1 = s.header_file and w.p2 = s.header_block;
* Check for waits to find a free buffer in the buffer cache:
 Check if the I/O system is slow. Consider increase the size of the buffer cache if it is so small.
 Consider increase the number of DBWR process if the buffer cache is properly sized.
Select event, total_waits, time_waited
from v$system_event
where event in ('free buffer waits');
Caution: Poor joins and poor indexing can also yield very high hit ratios due to reading many index blocks, so make sure that your hit ratio isn’t high for a reason other than a well-tuned system. An unusually high hit ratio may indicate the introduction of code that is poorly indexed or includes join issues.
Tuning SHARED POOL:
Tuning the shared pool is priority than tuning database buffer cache and while tuning this parameter concentrate the tuning of library cache than dictionary cache.
--Check the size of shared pool parameter 
Select value from v$parameter where name = 'shared_pool_size';
* The Library Cache GET HIT RATIO should be greater than 0.9 (> 90%). If it is less than 0.9 consider increase SHARED_POOL_SIZE init parameter.
      Select gethits, gets, trunc(gethitratio,4) as gethitratio
from  v$librarycache
where  namespace = 'SQL AREA';
* The Library Cache Reloads should be less than 1% of the pins. If it is greater than 0.1 consider increase SHARED_POOL_SIZE init parameter.
 Select sum(pins) as Executions, sum(reloads) as Cache_Misses,
trunc(sum(reloads)/sum(pins),4) as reload_pin_hitratio
from v$librarycache;
* The number of times objects of the namespace were marked invalid, causing reloads:
Select namespace, pins, reloads, invalidations
from v$librarycache
where invalidations > 0;
Library Cache Reserved Space:
For this parameter Generally, Set the initial value to 10% of the SHARED_POOL_SIZE.
--Check the size of shared_pool_reserved_size
Select value from v$parameter where name = 'shared_pool_reserved_size';
* pct_free_memory should be greater than 0.5 (> 50%) and pct_req_misses should be zero or near zero and not increasing; request_failures shoud be zero.
If the above conditions not match, consider increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
 Select free_space, trunc(free_space/to_number(p.value),4) as pct_free_space,request_misses, requests, trunc(request_misses/requests,4) as pct_req_misses, request_failures
  from v$parameter p, v$shared_pool_reserved
  where p.name = 'shared_pool_reserved_size';
Some other Parameters that affecting the LIBRARY CACHE:
--Check parameter OPEN_CURSORS.
  Default value is 50.
  Select value from v$parameter where name = 'open_cursors';
--Check the Current and required value for open_cursor
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'and p.name= 'open_cursors'

group by p.value;
--Check parameter CURSOR_SPACE_FOR_TIME.
* Do not set to TRUE if the RELOADS of v$librarycache (select above) is greater than zero.
Select value from v$parameter where name = 'cursor_space_for_time';
--Check parameter CURSOR_SHARING.
  Possible values: EXACT, SIMILAR, FORCE.
Select value from v$parameter where name = 'cursor_sharing';
DICTIONARY CACHE:
* The Dictionary Cache percent misses should be less than 0.15 (< 15%). If it is greater than 0.15 consider increase SHARED_POOL_SIZE init parameter.
Take this value some time after startup, not first time after startup.
Select sum(getmisses) as getmisses, sum(gets) as gets,
trunc(sum(getmisses)/sum(gets),4) as miss_ratio
from v$rowcache;
UGA inside Shared Pool:
When usnig oracle Shared server, Consider increase this memory size value in the SHARED_POOL_SIZE init parameter.
Select v.name, to_number(sum(value)) as Total_memory_in_Bytes
from v$sesstat st, v$statname v
where v.name in ('session uga memory', 'session uga memory max')
and st.statistic# = v.statistic#
group by v.name;
Tuning REDO LOG BUFFER:
There should be no Log Buffer Space waits. Consider making the log buffer bigger if it is small. Consider moving the log files to faster disks such as striped disks.
Select event, total_waits, total_timeouts, time_waited, average_wait from v$system_event
where event = 'log buffer space';
Select sid, event, seconds_in_wait, state
from v$session_wait
where event = 'log buffer space';
column  name format a40
Select name, value from  v$sysstat
where name in ('redo log space requests');
* The pct_buff_alloc_retries should be zero or less than 0.01 (< 1%). If it is greater consider making the log buffer bigger if it is small.  If it is greater consider moving the log files to faster disks such as striped disks.
Select v1.value as redo_buff_alloc_retries, v2.value as redo_entries,trunc(v1.value/v2.value,4) as pct_buff_alloc_retries
from  v$sysstat v1, v$sysstat v2
where v1.name = 'redo buffer allocation retries'
and v2.name = 'redo entries';
* If there are log file switch waits because of log switches, it indicates disk I/O contention. Check that the redo log files are stored on separated and fast devices. Consider increase the size of the redo log files.
Select event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch completion';
* Check the number of checkpoints in completed. Check the frequency of checkpoints in LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, FAST_START_MTTR_TARGET. Check the size, number and location of the redo log groups.
grep "CHECKPOINT NOT COMPLETED" alert.log
Select  event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch (checkpoint incomplete)';
* If  log buffer switches waits for archive processes. Consider move archived log to faster devices and use of multiple archiver processes with LOG_ARCHIVE_MAX_PROCESSES initialization parameter.
Select event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch (archiving needed)';
* DB_BLOCK_CHECKSUM is set to TRUE adds performance overhead.
Select value from v$parameter where name = 'db_block_checksum';
Tuning ROLLBACK SEGMENT:
Transactions should never wait for access to rollback segments. Rollback segments should not be extended during normal running. Avoid dynamic space management.
* The sum of waits shoud be less than 0.01 (< 1%). Consdier increase the number of rollback segments.
Select sum(waits) as waits, sum(gets) as gets, trunc(sum(waits)/sum(gets),4) as ratio
from  v$rollstat;
* There should be no waits for transaction slots on rollback segment headers. Consdier increase the number of rollback segments.
Select event, total_waits, time_waited
from v$system_event
where event in ('undo segment tx slot');
* There should be no waits on header blocks and data blocks of rollback segment headers or the wait ratio should be less than 0.01 (< 1%). Check if this values is lower and not increasing. Consdier increase the number of rollback segments.
Select ws.class, ws.count as waits, ws.time as wait_time, 
        ss.gets, trunc(ws.count/ss.gets,4) as wait_ratio
from  v$waitstat ws,
(select sum(value) as gets
from  v$sysstat
    where name in ('db block gets', 'consistent gets')) ss
        where ws.class in ('undo header', 'undo block');
Optimizing SORT Operations:
* The number of disk sorts to memory sorts should be less than 0.05 (< 5%).
Select d.value as disk_sorts, m.value as mem_sorts, trunc(d.value/m.value,4) as ratio
from v$sysstat m, v$sysstat d
where m.name = 'sorts (memory)' and d.name = 'sorts (disk)';
Consider increase the vaule of SORT_AREA_SIZE initialization parameter.  The default value of SORT_AREA_RETAINED_SIZE initialization parameter is equal to SORT_AREA_SIZE.
Using Oracle Shared Server the value of SORT_AREA_RETAINED_SIZE should initialy set to 10% of SORT_AREA_SIZE.Consider set the WORKAREA_SIZE_POLICY initialization parameter to AUTO;
Set the PGA_AGGREGATE_TARGET initialization parameter to the total memory destinated to Server Processes in MB. Check the application SQL statements to avoid sort operations.

1 comments:

  1. Bluehost is ultimately one of the best hosting company for any hosting plans you require.

    ReplyDelete