In Oracle : Oracle Instance Tuning: SGA Tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Instance Tuning: SGA Tuning

 

 Oracle Instance Tuning: SGA Tuning

 

 

A “database” (=the files which store the data) is not accessible by itself. In order to access this information, a collection of allocated memory (SGA) and the running processes (like SMON, PMON, LGWR, and DBWR) is called an "instance". So, tuning an instance means to optimize these processes/ SGA memory in order to have a better response time for our database. Tuning the SGA means tuning each component of the SGA (System Global Area).

      Tuning the System Global Area includes:

 


Tuning the Buffer Cache

 

     In Oracle database all the operations ( select, DML, DDL) are not done directly in the physical files, but in an intermediate memory area named Buffer Cache. If a row is modified, first the modification is done in this area. If you want to access a row, first Oracle will take a look in this area to see if the information is here. If the information is here the data is sent to the user or modified in function of the user request. In this case, Oracle name this event a HIT. If the information is not present in this area (in the Buffer Cache) the information is read from the disk and put in this memory zone (in the Buffer Cache). In this case, Oracle name this event a MISS. 

 

     One time modified and written to the disk or sent to the user, data is kept in this area for further use. If new data has to be brought in the Buffer Cache and in no room there, the oldest data is replaced by the newest one. 

 

     A well optimized database mustn't have too many reloads of the data in the Buffer Cache. To evaluate the reload of data The Buffer Cache Hit Ratio is calculated.

 

     This ratio could be calculated for the entire instance (since the database was started):

     

SELECT trunc((P1.value + P2.value - P3.value) / (P1.value + P2.value)*100, 2)  "Buffer Cache Hit Ratio"

   FROM v$sysstat P1, 

              v$sysstat P2, 

              v$sysstat P3

WHERE P1.name = 'db block gets'

     AND P2.name = 'consistent gets'

     AND P3.name = 'physical reads';

 

Oracle Instance / SGA tuning

 

Here is a Buffer Cache Hit Ratio example for a database which started one minute ago. If the Buffer Cache is well tuned (and the SQL statement are coded correctly) the Buffer Cache Hit Ratio will increase. 

    Also, this ratio could be calculated by a specific session (since that session started):    

      


SELECT trunc((P1.value + P2.value - P3.value) / (P1.value + P2.value)*100,2)

   FROM v$sesstat P1, 

              v$statname N1,

              v$sesstat P2,

              v$statname N2,

              v$sesstat P3,

              v$statname N3

WHERE N1.name = 'db block gets'

AND P1.statistic# = N1.statistic#

AND N2.name = 'consistent gets'

AND P2.statistic# = N2.statistic#

AND P2.sid = P1.sid

AND N3.name = 'physical reads'

AND P3.statistic# = N3.statistic#

AND P3.sid = P1.sid

AND P1.sid = <A Specific SID>;

      If the Buffer Cache  is well tuned and no full table scans run in the database, this Ratio must be grater than 90%. 

 

      Until Oracle 8, db_block_lru_extended_statistics parameter was used to tune the Buffer Cache: If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger. 

 

      Now (for 9i, 10g), to size the Buffer Cache V$DB_CACHE_ADVICE view is used. To populate this view DB_CACHE_ADVICE initialization parameter must be set to ON. Here is the information we have in this view:

 

Oracle Instance / SGA tuning

 

     Here is the information we receive from this view:

  • Now the Buffer Cache (BC) is set to 160 Mb (SIZE_FACTOR =1, case A )

  • If the Buffer Cache (BC) will decrease by 50% (will be 80 Mb) we will have 38414 Physical Reads or 13.86 % more Physical Reads (case B)

  • If the Buffer Cache (BC) will increase by 40% (will be 224 Mb) we will have 33583 Physical Reads or 0.46 % less Physical Reads (case C)

     If the BC will be bigger than 224 Mb there is no gain on the database performance, so having a 224 Mb Buffer Cache could be a good solution for this system.  The size of the Buffer Cache is managed by DB_CACHE_SIZE initialization parameter. This is a dynamic initialization parameter.

 

Tuning the Data Dictionary Cache

     Data Dictionary is a part of the Shared Pool and holds information about:

  • database objects;
  • table descriptions;
  • datafile names; 
  • block locations; 
  • information concerning Accounts and their privileges.

     Every time a statement is processed by Oracle, the Dictionary Cache is accessed for the relevant information in order that the statement can be properly executed. For this reason tuning this SGA area is very important. 

     The following statement return the Data Dictionary Cache Hit Ratio:

select round ((1-(sum(getmisses)/(sum(gets) + sum(getmisses)))) * 100,2)

from v$rowcache;

      If Data Dictionary Cache Hit Ratio SHARED_POOL_SIZE initialization parameter should be increased (by small increments). This is a dynamic initialization parameter.

Tuning the Library Cache

      The library cache is the area in the shared pool in which SQL and PL/SQL statements are parsed (One time the SQL statement is parsed, Oracle knows what to do and how to do it, because the execution plan is created). If the SQL (or PL/SQL) statement is found in the Library Cache, the parsing is bypassed. However, to be found in the Library Cache, the statement must be identical (down to the number of spaces, tabs, capital or small letters) with another one which run before. 

     To monitor the performance of the Library Cache, there are 2 ways to do it:     

SELECT round(sum(reloads)/sum(pins)*100, 3)

FROM v$librarycache;

 

(Must be little than 1%)

 

and 

 

SELECT sum(pins)/(sum(pins)+sum(reloads))*100

FROM v$librarycache;

 

(Must be bigger than 95%)

      If these 2 ratios have not good values, SHARED_POOL_SIZE initialization parameter should be increased (by small increments). This is a dynamic initialization parameter.

 

Tuning the Redo Log Buffer

     Before the information is written to the log file, Oracle write first the data in redo log buffer. If the redo buffers are not large enough, the Oracle LGWR process waits for space to become available. This wait time becomes wait time for the end user. Larger Log Buffer Cache values reduce log file I/O, but may increase the time OLTP users have to wait for write 
operations to complete. In general, the Log Buffer Cache is between 1 to 3MB. However, for bulk data loading or to accommodate a system with fast CPUs and slow disks the Log Buffer Cache could be increased.

 

     To tune the value for LOG_BUFFER the Space Request Ratio must be determined:

 

SELECT (r.value/e.value)*100

FROM v$sysstat r,

           v$sysstat e

WHERE r.name='redo buffer allocation retries'

    AND e.name='redo entries';

 

     This ratio must be < 1%. 

 

Tuning the Sort Area

       The sort operations are done normally in the memory. However, if the Sort Memory is not big enough, the sorting will use the disk and the execution time will be longer. To test if the sort operations are done in the memory the following command could be used:

            

SELECT mem.value*100/(disk.value + mem.value)

   FROM v$sysstat disk, v$sysstat mem

WHERE mem.name = 'sorts (memory)'

     AND disk.name = 'sorts (disk)';

   If the select return a value greater than 95% the SORT_AREA_SIZE parameter is well tuned; if the select return a value smaller than 95% the SORT_AREA_SIZE parameter must be increased. 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Instance Tuning: SGA Tuning

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.