OraFAQ Forum: Performance Tuning » Can I change the DB_BLOCK

Home » RDBMS Server » Performance Tuning » Can I change the DB_BLOCK_SIZE in Oracle 9i?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136748]

Fri, 09 September 2005 16:29

merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia

Junior Member

Hi
I need some help about changing the block size.
the actual block size is 8192 and i want change to 32768.

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136776 is a reply to message #136748]

Sat, 10 September 2005 01:41

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

U can not change the default block size of the database but Oracle9i allows u to create separate tablespaces with non-default block sizes.

So if it is for some specific purpose then u can create a new tablespace with a non default block size in the same database.

here is the process if u need, suppose i want to create a new tablespace with 16K block size & default is 8K.

Quote:

SQL> select name,block_size,resize_state, current_size, buffers from v$buffer_pool;

NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
-------------------- ---------- ---------- ------------ ----------
DEFAULT 8192 STATIC 24 6000

SQL> alter system set db_16K_cache_size=24M;

System altered.

SQL> create tablespace tb1_bigblock datafile
2 'c:\tbsp_bigblock.dbf' size 2M blocksize 16K;

Tablespace created.

regards,
tarun

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136969 is a reply to message #136776]

Mon, 12 September 2005 08:51

merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia

Junior Member

ok I did this and i got this errors, what am i doing wrong?

Quote:

SQL> select name,block_size,resize_state, current_size, buffers from v$buffer_pool;

NAME BLOCK_SIZE RESIZE_STA CURRENT_SIZE BUFFERS
------- ---------- ---------- ------------ --------
DEFAULT 8192 STATIC 88 11011

SQL> alter system set db_16K_cache_size=24M;
alter system set db_16K_cache_size=24M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

[Updated on: Mon, 12 September 2005 08:53]

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136986 is a reply to message #136748]

Mon, 12 September 2005 10:27

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

For testing purposes, why don't you try 2 m instead of 24 m and see what happens?

That error message is fairly explicit...You might want to read the concepts guide chapter on oracle memory before implementing anything in production.

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136989 is a reply to message #136986]

Mon, 12 September 2005 11:12

merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia

Junior Member

ok igot the same problem and i did what you tell me:

Quote:

SQL> alter system set db_16k_cache_size=2m;
alter system set db_16k_cache_size=2m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

ahh and look this :

SQL> show parameter cache;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_cache_advice                      string      ON
db_cache_size                        big integer 92274688
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
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
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400

NAME                                 TYPE        VALUE
------------------------------------- ----------- -------------
session_cached_cursors               integer     0

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136991 is a reply to message #136748]

Mon, 12 September 2005 11:53

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

What version? What is your sga_max_size and sga_target?

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

MYDBA@ORCL >
MYDBA@ORCL >
MYDBA@ORCL > alter system set db_16k_cache_size = 24m;

System altered.

MYDBA@ORCL > show parameter cache;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size                      big integer 40M
db_16k_cache_size                    big integer 24M
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_advice                      string      ON
db_cache_size                        big integer 4M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
session_cached_cursors               integer     10
MYDBA@ORCL > alter system set db_16k_cache_size=0;

System altered.

MYDBA@ORCL > show parameter cache;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
__db_cache_size                      big integer 64M
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_advice                      string      ON
db_cache_size                        big integer 4M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
session_cached_cursors               integer     10
MYDBA@ORCL >
MYDBA@ORCL > alter system set sga_target=0 scope=memory;

System altered.

MYDBA@ORCL > alter system set db_cache_size=32m scope=memory;

System altered.

MYDBA@ORCL > alter system set db_16k_cache_size=16m scope=memory;

System altered.

MYDBA@ORCL > show parameter cache;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
__db_cache_size                      big integer 32M
db_16k_cache_size                    big integer 16M
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_advice                      string      ON
db_cache_size                        big integer 32M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
session_cached_cursors               integer     10
MYDBA@ORCL > show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
lock_sga                             boolean     TRUE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 152M
sga_target                           big integer 0

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #136993 is a reply to message #136748]

Mon, 12 September 2005 12:00

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Sorry, didn't notice the 9i in subject. Check your sga_max_size.

Also a warning, the above steps that I just pasted caused oracle cpu to jump to 100% and put this in alert file, I'm rebooting my pc now Smile

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0xAC36DF] [] [] [] []

Report message to a moderator

Re: Can I change the DB_BLOCK_SIZE in Oracle 9i? [message #137008 is a reply to message #136993]

Mon, 12 September 2005 14:07

merovingio
Messages: 4
Registered: September 2005
Location: Santa Cruz - Bolivia

Junior Member

ok i did it. the problem was that my SGA_MAX_SIZE was small so i had to encrease it.
i did this:

SQL>ALTER SYSTEM SET SGA_MAX_SIZE=450M;
System altered.

SQL>ALTER SYSTEM SET DB_16K_CACHE_SIZE=[NUMBER][M|K];
System altered.

SQL>create tablespace PRUEBA1 DATAFILE
  2 'F:\ORACLE\ORADATA\CENTRAL\PRUEBA1.DBF'SIZE 10M blocksize 16K;

Tablespace created.

Report message to a moderator

Previous Topic:	Resolving "latch free" problems
Next Topic:	different performance results in Oracle9i vs. MSSQL Server 2000

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 10:00:28 CDT 2024