如何确定SGA的大小（7.x中，8.x中，9.x中，10G）
注：本文是为Oracle7和Oracle数据库编写的。对于最新的oracle版本，最好使用Statspack/AWR报表中的建议信息或者是查看视图V$SHARED_POOL_ADVICE。注:255409.1 脚本使用视图V$shared_pool_advice调整共享池大小。注：4061621.8 Bug 4061621共享池建议没有考虑PLSQL对象。以下说明如何接近SGA的大小。
应用范围
根据init.ora参数值去精确计算SGA的大小是非常困难和耗时的。这是困难的，因为在SGA中被分配的数据结构的尺寸是动态变化的。这是费时的因为有这么多参数影响SGA的大小。例如：配置一个资源数量的任何参数都会影响SGA的尺寸，就像PROCESSES和SESSIONS。
本文将集中于：
--一旦连接到正在运行的数据库，显示SGA的大小。
--提出了关于SGA的不同细分简要概述。
--如何根据init.ora参数值估价SGA的大小。
在oracle8i,9i和10g中如何得到SGA的近似尺寸
本节讨论Oracle8，Oracle8i，Oracle9i，和Oracle10g。Oracle7的讨论在本节最后。
显示SGA的大小
----------------------------
数据库启动时显示SGA的大小信息。可以使用svrmgrl或sqlplus命令显示。见下面的例子。
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.
注：从SGA内存结构转移到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.
LOG_BUFFER参数是用来当重做日志缓冲重做条目。这是一个静态的参数，代表了SGA中一个非常小的部分，只有在停止或者重启数据库以从pfile文件读取改变时才能改变。需要注意的是即使你不能改变动态的MAX_SGA_SIZE参数值，你有任何改变依赖于它的三个主要参数选项：DB_CACHE_SIZE，SHARED_POOL_SIZE，LARGE_POOL_SIZE，这些参数使动态内存优化调整。
为了帮助您指定一个最佳缓存值，使用 ALTER SYSTEM...SET 子句启用此参数语句。Oracle9i/Oracle10g 数据库性能指南，参考更多的消息关于使用这个参数。从Oracle 9i开始，有一个创建多个块大小表并指定高速缓存大小每块大小相应概念。SYSTEM表空间使用标准块大小，其它的的表空间最多可以使用4非标准块大小。指定标准块大小由DB_BLOCK_SIZE参数。高速缓存大小是DB_CACHE_SIZE参数指定。非标准块大小由BLOCK SIZE 制定，使用的符号被指定为每个相应非标准块大小高速缓存大小：DB_nK_CACHE_SIZE参数，其中的值是2，4，8，16或32字节。
标准块大小一般设置成和系统块大小一样，或者系统块大小的整数倍。参数DB_CACHE_SIZE设置默认块大小，指定标准块大小的缓存大小系统表空间使用标准块大小作为默认块。无论是标准块还是非标准块，和其关联的高速缓存块尺寸可以用于任何其它表空间。如果你打算在您数据库存储设计时，使用多个块大小，你必须至少指定DB_CACHE_SIZE和一个DB_nK_CACHE_SIZE参数值。您必须指定你打算使用的所有其他非标准块的子缓存大小。此块大小/高速缓存大小调整计划，让您使用了四种不同的非标准你的表空间块大小，让你指定各自高速缓存大小为每个相应块大小。因为DB_BLOCK_SIZE参数值是可以改变的只能通过重新创建数据库，此参数值必须慎重选择和数据库生命保持不变。SGA的近似大小，使用下列公式计算：
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.
附加信息：
- 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 Fixed Size 37704 bytes
Variable Size 4048576 bytes
Database Buffers 409600 bytes
Redo Buffers 8192 bytes ('log buffers')
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2012-3-5 10:33:09 |
赞(
操作
