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

PURPOSE

  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/