How to determine SGA Size (7.x, 8.x, 9.x, 10g)

How to Approximate the Size of the SGA in in 8.0.X, 8i, 9i, and 10g:
===============================================================

This section discusses Oracle8, Oracle8i, Oracle9i, and Oracle 10g. Oracle7 is discussed
at the end of this note.

Showing size of the SGA
———————–

SGA size information are displayed upon startup of the database. It can also be
displayed using svrmgrl or sqlplus. See examples below.

8.0.X
– svrmgrl
connect internal
show sga

8.1.X
– svrmgrl or sqlplus /nolog
connect internal
show sga

9.X / 10g
– sqlplus

SQL*Plus: Release 9.0.1.0.0 – Production on Thu Aug 23 15:40:29 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 – Production
With the Partitioning option
JServer Release 9.0.1.0.0 – Production

SQL> show sga
Total System Global Area 72123504 bytes
Fixed Size 279664 bytes
Variable Size 67108864 bytes
Database Buffers 4194304 bytes
Redo Buffers 540672 bytes

Different sub-divisions of the SGA
———————————-

Sample from svrmgrl SHOW SGA:

Total System Global Area 23460696 bytes
Fixed Size 72536 bytes
Variable Size 22900736 bytes
Database Buffers 409600 bytes
Redo Buffers 77824 bytes

Total System Global Area
– Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size
– Contains general information about the state of the database and the
instance, which the background processes need to access.
– No user data is stored here.
– This area is usually less than 100k in size.

Variable Size
– This section is influenced by the following init.ora parameters
shared_pool_size
large_pool_size
java_pool_size
– See ‘Approximating Size of the SGA’ section of this article for version
specific information.

Database Buffers
– Holds copies of data blocks read from datafiles.
size = db_block_buffers * block size

Redo Buffers
– A circular buffer in the SGA that holds information about changes made to
the database.
– Enforced mininum is set to 4 times the maximum database block size for the
host operating system.

NOTE: Memory structures were moved from the SGA to the shared pool in 10g.
Reference: Note 270935.1 Shared pool sizing in 10g

Approximating size of the SGA
—————————–

8.0.X

To approximate size of the SGA (Shared Global Area), use the following
formula:

((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + log_buffers) + 1MB

8.1.X

To approximate size of the SGA (Shared Global Area), use the following
formula:

((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

9.X/10g

In Oracle9i/Oracle 10g, the SGA can be configured as in prior releases to be static,
or can now be dynamically configured.

The size of the dynamic SGA is determined by the values of the following
database initialization parameters: DB_BLOCK_SIZE, DB_CACHE_SIZE,
SHARED_POOL_SIZE, and LOG_BUFFER.

Beginning with Oracle9i, the SGA infrastructure is dynamic. This means that
the following primary parameters used to size the SGA can be changed while
the instance is running:

Buffer cache ( DB_CACHE_SIZE) — the size in bytes of the cache of
standard blocks

Shared pool ( SHARED _POOL_SIZE) — the size in bytes of the area devoted
to shared SQL and PL/SQL statements

Large pool (LARGE_POOL_SIZE) (default is 0 bytes) — the size in bytes of
the large pool used in shared server systems
for session memory, parallel execution for
message buffers, and by backup and restore
processes for disk I/O buffers.

The LOG_BUFFER parameter is used when buffering redo entries to a redo log.
It is a static parameter and represents a very small portion of the SGA and
can be changed only by stopping and restarting the database to read the
changed value for this parameter from the initialization parameter file
(init.ora).

Note that even though you cannot change the MAX_SGA_SIZE parameter value
dynamically, you do have the option of changing any of its three dependent
primary parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE to
make memory tuning adjustments on the fly.

(NOTE: LARGE_POOL_SIZE cannot be dynamically changed in Oracle 9.0.1, it is
anticipated to be made dynamic in the next release).

To help you specify an optimal cache value, you can use the dynamic
DB_CACHE_ADVICE parameter with statistics gathering enabled to predict
behavior with different cache sizes through the V$DB_CACHE_ADVICE performance
view. Use the ALTER SYSTEM…SET clause… statement to enable this parameter.
See Oracle9i/Oracle10g Database Performance Guide and Reference for more information
about using this parameter.

Beginning with Oracle9i, there is a concept of creating tablespaces with
multiple block sizes and specifying cache sizes corresponding with each block
size. The SYSTEM tablespace uses a standard block size and additional
tablespaces can use up to four non-standard block sizes.

The standard block size is specified by the DB_BLOCK_SIZE parameter. Its
cache size is specified by the DB_CACHE_SIZE parameter. Non-standard block
sizes are specified by the BLOCKSIZE clause of the CREATE TABLESPACE
statement. The cache size for each corresponding non-standard block size is
specified using the notation: DB_nK_CACHE_SIZE parameter, where the value n
is 2, 4, 8, 16, or 32 Kbytes.

The standard block size, known as the default block size, is usually set to
the same size in bytes as the operating system block size, or a multiple of
this size. The DB_CACHE_SIZE parameter, known as the DEFAULT cache size,
specifies the size of the cache of standard block size (default is 48M bytes).
The system tablespace uses the standard block size and the DEFAULT cache size.

Either the standard block size or any of the non-standard block sizes and
their associated cache sizes can be used for any of the other tablespaces. If
you intend to use multiple block sizes in your database storage design, you
must specify at least the DB_CACHE_SIZE and one DB_nK_CACHE_SIZE parameter
value. You must specify all sub-caches for all the other non-standard block
sizes that you intend to use. This block size/cache sizing scheme lets you
use up to four different non-standard block sizes for your tablespaces and
lets you specify respective cache sizes for each corresponding block size.

Because the DB_BLOCK_SIZE parameter value can be changed only by re-creating
the database, the value for this parameter must be chosen carefully and
remain unchanged for the life of the database.

To approximate size of the SGA (Shared Global Area), use following formula:

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB

NOTE: ADD IN EACH DB_nk_CACHE_SIZE. THERE CAN BE UP TO 4 DB_nk_CACHE_SIZE
(2, 4, 8, 16, 32k) DEFINED. ONE OF THE BLOCK SIZES IS THE DEFAULT
BLOCK SIZE AND ITS CACHE SIZE IS DEFINED BY DB_CACHE_SIZE.

Additional Information:
———————–

– Redo Buffers in SHOW SGA does not match init.ora:log_buffer parameter
setting.
– Enforced mininum is set to 4 times the maximum database block size for the
host operating system. For more details, see:
Note 30753.1 Init.ora Parameter “LOG_BUFFER” Reference Note

– Java_pool_size not accounted for in SHOW SGA or v$sga.
This is a bug that is fixed in 8.1.6.
– Java_pool_size restrictions in 8.1.5.
The default is 20000K.
If specifying in the init.ora, must it must be greater than 1000K, or you
will receive an ORA-01078 “failure in processing initialization parameters”
error on startup.

– Java_pool_size restrictions in 8.1.6.
The default is 20000K.
This parameter can be set in the init.ora, but the enforced mininum is
32768.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Approximating SGA Size and Showing Existing SGA in Oracle7:
===========================================================

To approximate the size of the SGA (Shared Global Area), use the following
formula:

( (db_block_buffers * block size) + shared_pool_size + log_buffers) /.9

Example (from 7.0.16 on PORT 2 HP-UX 9000):

From the init.ora
DB_BLOCK_BUFFERS = 200
LOG_BUFFERS = 8192
SHARED_POOL_SIZE = 3500000

Default Block Size = 2048 bytes
The Block Size is an Operating System specific default.

db_block_buffers * block size + shared_pool_size + log_buffers
( (200 * 2048) + 3500000 + 8192 ) / .9

409600 + 3500000 + 8192 = 3917792 bytes

dividing by 0.9 = 4,353,102 bytes or 4M

The division by .9 is used to take into account the variable portion of
the SGA — this is only an approximation of the actual value.

Our calculations come up to 4353102 but the actual value is 4504072(see below).

To check the actual size of the SGA, issue these commands using either
sqldba or svrmgrl:

7.0.X – 7.2.X

% sqldba lmode=y
SQLDBA> connect internal
SQLDBA> show sga

7.1.X – 7.3.X

% svrmgrl
SVRMGR> connect internal
SVRMGR> show sga

Example of Output:

Total System Global Area 4504072 bytes <– total size loaded into memory
Fixed Size 37704 bytes
Variable Size 4048576 bytes
Database Buffers 409600 bytes
Redo Buffers 8192 bytes ('log buffers')

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

References:
===========

Note 30753.1 PARAMETER: INIT.ORA: LOG_BUFFER
Note 1058897.6 WHAT DO V$SGASTAT AND V$SGA INDICATE AND DO THEY RELATE?
Note 270935.1 Shared pool sizing in 10g

Search words:
============

semaphores, memory, shared pool, calculation, calculate
formula, increase sga , estimate

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em SGA. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s