Oracle Point, Oracle Life.

In Oracle,

“The buffer cache manages blocks read from disk into buffers in memory. It also holds information on the most recently used buffers and those modified in normal database operation. To get best query performance, a user query accesses all required data blocks within the buffer cache, thus satisfying the query from memory. However, this might not always happen, because the database is many multiples the size of the buffer cache. With this in mind, it is easy to see that the buffer cache requires management and tuning.

The objective in tuning the buffer cache is to get acceptable user query time by having as many of the required blocks in the cache as possible. Also, eliminate time consuming I/Os without inducing any serialization points or performance spikes as old blocks are aged out of the cache. This process requires a working knowledge of the buffer cache mechanism, the database writer, and the checkpointing mechanism. Most information can be extracted from the V$SYSSTAT table.”

To tune the size of data buffers, we need to calculate the hit ratio of data buffers. The script used for that is listed as following.

/***********************************************
This script is used to calculate hit ratio of data buffer.
If the hit ratio is less than 80%-90%, increase the db_buffer_buffers by using
alter system set db_cache_size=”bigger size” scope=both;

************************************************/

column xn1 format a50
column xn2 format a50
column xn3 format a50
column xv1 new_value xxv1 noprint
column xv2 new_value xxv2 noprint
column xv3 new_value xxv3 noprint
column d1 format a50
column d2 format a50

prompt HIT RATIO:
prompt
prompt Values Hit Ratio is calculated against:
prompt

select lpad(name,20,’ ‘)||’ = ‘||value xn1, value xv1
from v$sysstat
where name = ‘db block gets’
/

select lpad(name,20,’ ‘)||’ = ‘||value xn2, value xv2
from v$sysstat
where name = ‘consistent gets’
/

select lpad(name,20,’ ‘)||’ = ‘||value xn3, value xv3
from v$sysstat b
where name = ‘physical reads’
/

set pages 60

select ‘Logical reads = db block gets + consistent gets ‘,
lpad (’Logical Reads = ‘,24,’ ‘)||to_char(&xxv1+&xxv2) d1
from dual
/

select ‘Hit Ratio = (logical reads - physical reads) / logical reads’,
lpad(’Hit Ratio = ‘,24,’ ‘)||
round( (((&xxv2+&xxv1) - &xxv3) / (&xxv2+&xxv1))*100,2 )||’%’ d2
from dual
/

prompt If the hit ratio is less than 80%-90%, increase the initialization
prompt parameter DB_BLOCK_BUFFERS. ** NOTE: Increasing this parameter will
prompt increase the SGA size.
prompt
prompt————————————————————————

(download script from data_buffer_hit_ratio.sql) 

If the hit ratio is less than 80%-90%, you may need to increase the size of data buffers.

Note: Comparing to oracle 9i, the significant changes on init parameter with Oracle 10g is the introduction of “SGA_TARGET”. “SGA_TARGET” specifies the total size of all SGA components. If the SGA_TARGET is set to a non-zero value and the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL, then the following memory pools are automatically sized:

· Buffer cache (DB_CACHE_SIZE)

· Shared pool (SHARED_POOL_SIZE)

· Large pool (LARGE_POOL_SIZE)

· Java pool (JAVA_POOL_SIZE)

· Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory. That means, you as DBA need to set customized minimum values if your application has experienced value about SGA components. Therefore, the above scripts are also helpful for DBAs to set the experienced value about SGA components.

Reference: Oracle Official Statistics Description

Popularity: 17% [?]

By reading this AskTom entry, I got a suggestion from Tom in calculating size of SGA.

Tom’s formula is:

SGA size = ( (db_block_buffers * block size) + sum( *_pool_size ) + log_buffers) /.9

where, *_pool_size is the java_pool_size, large_pool_size,and shared_pool_size. (java_pool_size is for 8.1 and up only)

I then calculate size of SGA by using this formula both on Oracle 9ir2 and Oracle 10gr1.

Oracle 9ir2:

SQL> show sga

Total System Global Area 2400687560 bytes
Fixed Size 734664 bytes
Variable Size 1795162112 bytes
Database Buffers 603979776 bytes
Redo Buffers 811008 bytes

Sum of bytes from v$sgastat:

SQL> select sum(bytes) from v$sgastat;

SUM(BYTES)
———-
2400664008

Oracle 10Gr1:

SQL> show sga

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes

Sum of bytes from v$sgastat:

SQL> select sum(bytes) from v$sgastat;

SUM(BYTES)
———-
169084436

The sum of bytes from v$sgastat is size summary of Database Buffers, Shared Pool, Large Pool, Java Pool, Redo Buffers, and Fixed Size. We noticed that the sum is little bit less than SGA size by issuing command “show sga”, both in oracle 9ir2 and Oracle 10Gr1.

This difference comes from Log Buffer (Redo BUffers) for Oracle 9ir2 as following.

Oracle 9ir2:

SQL> select name, bytes from v$sgastat where name=’log_buffer’;

NAME BYTES
————————– ———-
log_buffer 787456 (less than 811008 by issuing “show sga”

This difference comes from inconsistent size of Variable Size from Oracle 10Gr1 as following.

Oracle 10Gr1:

SQL> select sum(bytes) from v$sgastat where pool in (’shared pool’,'java pool’,'large pool’);

SUM(BYTES)
———-
142606336 (less than 145488364 bytes by issuing “show sga”)

By examining the results by using Tom’s formula, we found that that formula doesn’t work well any more. Both for oracle 9ir2 and oracle 10Gr1, the size of SGA is not exactly equal to the sum of Database Buffers, Shared Pool, Large Pool, Redo Buffers, and Fixed Size. Therefore, to get summary size of SGA, we couldn’t rely on view v$sgastat. Instead, the only way to get the exact size is “show sga”.

Popularity: 3% [?]