The following explains how to approximate the size of the SGA (System Global
Area).
SCOPE & APPLICATION
It is very difficult and time consuming to exactly calculate the SGA size
based on values of init.ora parameters. It is difficult because of different
port specific sizes of data structures that are allocated in the SGA. It is
time consuming because there are so many parameters that influence the SGA
size. For example, any parameter that configures a number of resources, such
as PROCESSES and SESSIONS, will have an impact on the SGA size.
This article will concentrate on:
- Showing size of the SGA once connected to a running database.
- Present a brief overview on different sub-divisions of the SGA
- How to ESTIMATE the size of the SGA based on values of init.ora parameters.
How to Approximate the Size of the SGA in in 8.0.X, 8i, and 9i:
===============================================================
This section discusses Oracle8, Oracle8i, and Oracle9i. 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
- 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.
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
In Oracle9i, 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 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:
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
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?
Search words:
============
semaphores, memory, shared pool, calculation, calculate
formula, increase sga , estimate
<>
FOLDER:server.DBA.Architecture
TOPIC:The Shared Pool
DOCUMENT-ID:1008866.6
ALIAS:
SOURCE:
DOCUMENT-TYPE:BULLETIN
TITLE:How to determine SGA Size (7.x, 8.x, 9.x)
IMPACT:LOW
SKILL-LEVEL:CASUAL
07.01.XX
09.XX
STATUS:PUBLISHED
DISTRIBUTION:EXTERNAL
ZCXPUBLIC ZCXREVIEWED
AUTHOR:EPEKELNY.US
ERROR:ORA-07307
KEYWORD:MIDRANGE
KEYWORD:UNIX
KEYWORD:UNIXBASICS
KEYWORD:UNIXOSCONFIG
KEYWORD:VMSSGA
PRODID-5 COMPONENT:RDBMS MINVER:7.1 MAXVER:09.X
PORTID-GENERIC
. http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1008866.6
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51778/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29987/viewspace-51778/