DB_nK_cache_size研究

DB_nK_cache_size

众所周知，db_block_size在建库后并不能修改，但是我们面对一些特殊需求的时候，总是想把db_block_size调大一点或者调小一点。我们可以通过db_nk_cache_size去实现我们这些需求。

SQL> show parameter cache_size;

NAME                                 TYPE        VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size                    big integer 4M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> alter system set db_16k_cache_size=1m;

System altered

SQL> show parameter cache_size;

NAME                                 TYPE VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size                    big integer 4M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0



SQL> show parameter cpu;

NAME                                  TYPE        VALUE
------------------------------------ -----------------------------------------
cpu_count                             integer       1
parallel_threads_per_cpu              integer       2

SQL> select t.NAME,t.BYTES/1024/1024 as "size(M)"from v$sgainfo t where t.name='Granule Size';

NAME                              size(M)
-------------------------------- ----------
GranuleSize                          4

SQL> create tablespace cache_size datafile'cache_size_001.dbf' size 16M BLOCKSIZE 16K;

Tablespace created.

SQL> select t1.FILE_NAME,
t1.USER_BYTES/t1.USER_BLOCKS/1024 as "per_block_size(K)"
from dba_data_files t1
where t1.TABLESPACE_NAME in(upper('users'),upper('cache_size'));

FILE_NAME                                                         per_block_size(K)
-----------------------------------------------------------------------------------
/u01/app/oracle/oradata/xiaoyao/dfile/users01.dbf                   8
/u01/app/oracle/product/10.2.0/dbhome_1/dbs/cache_size_001.dbf      16

SQL> create table tmp_test (id int,image clob)tablespace cache_size;

Table created

SQL> create table tmp_czx (id int,image clob);

Table created

SQL> select distinct(t.segment_name),
t.BYTES/t.BLOCKS/1024 as "per_block_size(K)"
from dba_extents t
where t.segment_name in ('TMP_TEST','TMP_CZX') andt.owner=upper('system');

SEGMENT_NAME                     per_block_size(K)
------------------              -----------------
TMP_TEST                          16
TMP_CZX                            8


SQL> alter system set db_16k_cache_size=0;

System altered

SQL> show parameter cache_size;

NAME                                 TYPE VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> alter system set db_16k_cache_size=0;

System altered

insert into tmp_test(id) values(1);

SQL> insert into tmp_test(id) values(1);

insert into tmp_test(id) values(1)

ORA-00379: no free buffers available in buffer pool DEFAULT forblock size 16K

SQL> alter system set db_16k_cache_size=1m;

System altered

SQL> show parameter cache_size;

NAME                                 TYPE VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size                    big integer 4M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> insert into tmp_test(id) values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> show parameter db_block

NAME                                 TYPE VALUE
------------------------------------ -----------------------------------------
db_block_buffers                      integer 0
db_block_checking                     string FALSE
db_block_checksum                     string TRUE
db_block_size                         integer 8192

SQL> alter system set db_8k_cache_size=1m;

alter system set db_8k_cache_size=1m

ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standardblock size

SQL> show parameter sga

NAME                                TYPE VALUE
------------------------------------ -----------------------------------------
lock_sga                            boolean FALSE
pre_page_sga                        boolean FALSE
sga_max_size                        big integer 300M
sga_target                          big integer 300M

SQL> alter system set db_16k_cache_size=136m;

System altered

SQL> alter system set db_16k_cache_size=140m;

alter system set db_16k_cache_size=140m

ORA-02097: parameter cannot be modified because specified value isinvalid
ORA-00384: Insufficient memory to grow cache



SQL> alter system set db_16k_cache_size=7m;

System altered

SQL> show parameter cache_size;

NAME                                 TYPE VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size                    big integer 8M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0

ps：
db_nk_cache_size其实就是设置一个处理一些特殊blocksize的表空间。如果这个值为0，那么就无法使用这些表空间了。

转自：http://xiaoyao1107.itpub.net/post/43518/527664