5

My box is RHEL 6.2, and file system BLOCK SIZE is 1024.

# tune2fs -l /dev/sda1
...
Block size:               1024
...

My Oracle is 11g, and database block size is 8192.

SQL> show parameters db_block_size;

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

I create a tablespace, set block size 16K.

CREATE BIGFILE TABLESPACE "C_IDX" DATAFILE '/u01/oradata/weixin/c_idx1.dbf' 
SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO blocksize 16K;

then i got error ora-29339.

ORA-29339: tablespace block size 16384 does not match configured block sizes

It seems the reason is DEFAULT 16K buffer cache is 0.

SQL> select component, current_size from v$sga_dynamic_components;

    COMPONENT       CURRENT_SIZE
    shared pool 402653184
    large pool  16777216
    java pool   16777216
    streams pool    16777216
    DEFAULT buffer cache    1308622848
    KEEP buffer cache   0
    RECYCLE buffer cache    0
    DEFAULT 2K buffer cache 0
    DEFAULT 4K buffer cache 0
    DEFAULT 8K buffer cache 0
    DEFAULT 16K buffer cache    0
    DEFAULT 32K buffer cache    0
    Shared IO Pool  0
    ASM Buffer Cache    0

Now the problem is how large should i set it, or according to what i set the value?

SQL> alter system set db_16k_cache_size= ?

Thanks.

migrated from stackoverflow.com Feb 27 '14 at 14:18

This question came from our site for professional and enthusiast programmers.

7

your db_block_size is 8k and your db_16k_cache_size = 0.

Normally we don't build databases with mulitple block sizes because the optimizer does not take the different block sizes into account. If you still want to do this, start with something like

alter system set db_16k_cache_size = 16m;

and try again. It does not have much to do with the filesystem blocksize but aligning your database storage blocks sizes with the storage blocksizes is performance wise a smart thing to do.

  • Thanks for your reply. I use multiple block sizes databases to solve problem about ORA-01450: maximum key length () exceeded when i create an index.I don't understand your words the optimizer does not take the different block sizes into account, do it means my index on multiple block sizes tablespace is useless? – Ron Feb 14 '14 at 3:19
  • 2
    Ron, the optimizer does al kinds of calculations about the cost of IO. For this the blocksize is a key factor that is ignored. Try to stick to the 1 blocksize db, if possible. – ik_zelf Feb 14 '14 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.