Oracle中的SGA是实例的重要组成部分，对Oracle数据的操作都放在SGA中完成，而SGA也由多个组件构成，其中9I后支持多个块大小，可以 通过设置db_nk_cache_size来配置nk大小的块缓冲区，只有在设置了db_nk_cache_size参数后才能创建相应块大小的表空间。

创建数据库时批定的block_size所对应的db_nk_cache_size是不可以修改的，比如db_block_size 为8M,db_8k_cache_size不可以指定大小,其它的可以修改但递增方式还是要注意一下，下面做个实验，先查看当前的db_block_size,

os: rhel 5  linux

oracle version :10.2.01

by :zhang weizhao www.anbob.com

————————begiin——————————

SQL> show parameter db_block_size

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
db_block_size                        integer                8192

SQL> alter system set db_8k_cache_size=4m;
alter system set db_8k_cache_size=4m
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size

SQL> desc v$parameter;
Name                                      Null?    Type
—————————————– ——– —————————-
NUM                                                NUMBER
NAME                                               VARCHAR2(80)
TYPE                                               NUMBER
VALUE                                              VARCHAR2(512)
DISPLAY_VALUE                                      VARCHAR2(512)
ISDEFAULT                                          VARCHAR2(9)
ISSES_MODIFIABLE                                   VARCHAR2(5)
ISSYS_MODIFIABLE                                   VARCHAR2(9)
ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
ISMODIFIED                                         VARCHAR2(10)
ISADJUSTED                                         VARCHAR2(5)
ISDEPRECATED                                       VARCHAR2(5)
DESCRIPTION                                        VARCHAR2(255)
UPDATE_COMMENT                                     VARCHAR2(255)
HASH                                               NUMBER

SQL> set pagesize 150
SQL> col name for a30
SQL> select name,issys_modifiable from v$parameter where name like ‘db_4k%’;

NAME                           ISSYS_MODIFIABLE
—————————— ——————
db_4k_cache_size               IMMEDIATE

SQL> alter system set db_4k_cache_size=4m scope=both;

System altered.

SQL> show parameter db_4k

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
db_4k_cache_size                     big integer            8M

note:

明明修改db_4k_cache_size=4m,(大小单位可以为k、M、G)，但还是8M，而且这个参数是IMMEDIATE立及生效的，所以可以断定是有最小限制的,网上查询一下(我非常支持查询资源去官方oracle.com上找，这样也最权威，当然itpub、各种oug也可以，只是你没法辨别真实性)，贴上官方说法

DB_nK_CACHE_SIZE

DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE).

Do not set this parameter to zero if there are any online tablespaces with an nK block size.

Operating system-specific block size restrictions apply. For example, you cannot set DB_32K_CACHE_SIZE if the operating system’s maximum block size is less than 32 KB. Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.

———————-

这里涉及到了SGA中内存分配的粒度问题，粒度是连续虚拟内存分配的单位,在9I版本引入了一个粒度（granule）的概念，如果SGA小于128M，则粒组大小为4M，否则为16M；在10G版本中，如果SGA小于1G，则粒组为4M，否则大于4M。粒度大小受内部隐含参数_ksmg_granule_size或sga_max_target的控制

10g r2官方

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger
SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the
granule size is 8 M for SGAs larger than 1 GB.

在不同的版本、不同的平台也有可能不同，查看当前数据库的granule大小

SQL> show parameter sga_max

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
sga_max_size                         big integer            536M

SQL> select * from v$sgainfo where name=’Granule Size’;

NAME                                BYTES RESIZE
—————————— ———- ——
Granule Size                      4194304 No

我把我的机器sga_max_target调 成大于1g试试

SQL> alter system set sga_max_size=1200m scope=spfile;

System altered.

SQL> shutdwon immediate
SP2-0734: unknown command beginning “shutdwon i…” – rest of line ignored.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1219184 bytes
Variable Size             855639440 bytes
Database Buffers          385875968 bytes
Redo Buffers               15556608 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo where name=’Granule Size’;

NAME                                BYTES RESIZE
—————————— ———- ——
Granule Size                     16777216 No

SQL> alter system set db_4k_cache_size=2m;

System altered.

SQL> show parameter db_4k

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
db_4k_cache_size                     big integer            16M

我的机器是双核的 cpu,在一开始粒度为4m时，所以我觉的db_nk_cache_size最小是8M的原因应该是4M*cpu2，而随着后来把 sga_max_target调大而改变了粒度为16M时，db_nk_cache_size最小为16M的原因时，它取了粒度的大小.

而且db_nk_catch_size的大小指定是自动向上取整为粒度的倍数,粒度为4M时，如下

SQL> alter system set db_4k_cache_size=9m;

System altered.

SQL> show parameter db_4k

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
db_4k_cache_size                     big integer            12M
SQL> alter system set db_4k_cache_size=13m;

System altered.

SQL> show parameter db_4k

NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
db_4k_cache_size                     big integer            16M

以上仅供参考

打赏

微信扫一扫，打赏作者吧～