Home  >  Oracle Database Administration (DBA)  >  Database Tuning  >  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

• Tuning the Data Dictionary Cache (First component of Shared Pool)

• Tuning the Library Cache (Second Component of Shared Pool)

• Tuning the Redo Log Buffer

• Tuning the Sort Area

 

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';

 

 

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%. 

 

     

      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:

 

 

     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.

 

Tuning the Sort Area

       More information about  this subject ( Instance Tuning: SGA Tuning ) you can get from  www.in-oracle.com