OraFAQ Forum: Performance Tuning » DB_BLOCK_SIZE

Home » RDBMS Server » Performance Tuning » DB_BLOCK_SIZE (ORACLE 11GR2 SOLARIS)

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

DB_BLOCK_SIZE [message #559654]

Thu, 05 July 2012 05:59

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear All,

I have created the database on 11gr2 and forgot to set the db_block_size 16k, can i do it once the installation is done.

If the tablespace is created with 16k will it help to improve the performance.

Please clarify me for larger table full table scan , if i have block size of 16 it will be faster compare to block size of 8k.

does block size of 8k improves performance when writing the data into the table.

Please help me to understand

Rajesh

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559656 is a reply to message #559654]

Thu, 05 July 2012 06:30

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I have created the database on 11gr2 and forgot to set the db_block_size 16k, can i do it once the installation is done.

No.

Database Performance Tuning Guide
Chapter 8 I/O Configuration and Design
Section 8.2.6 Choosing Data Block Size

Regards
Michel

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559657 is a reply to message #559656]

Thu, 05 July 2012 06:36

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Michel,

Wonderfull, i read it and read the advantage and disadvantage as well, I want to have 16k block size. can i go for multiple block size parameter. how can i create multiple blocksize and can i have tablespace created with 16k eventhough the db_block_size is 8k

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559658 is a reply to message #559657]

Thu, 05 July 2012 06:38

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

No. You cannot have multiple block sizes within a database. Also, as Michel has indicated, you need to recreate the database with the required block size.

HTH
-g

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559659 is a reply to message #559658]

Thu, 05 July 2012 06:49

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

You cannot have multiple block sizes within a database

Yes, you can have multiple block sizes in a database but it is not recommended.
See Database Concepts http://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm#sthref61

Quote:

...Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to five other block sizes....

and http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm#sthref529

Quote:

Oracle supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. Legitimate values are from 2K to 32K.[...] You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces.

Regards
Michel

[Updated on: Thu, 05 July 2012 06:50]

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559661 is a reply to message #559659]

Thu, 05 July 2012 07:16

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

I stand corrected. Thank you Michel.

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559671 is a reply to message #559654]

Thu, 05 July 2012 08:04

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

The Oracle Uni Performance Tuning course manual includes this sentence:

"In recent years all the TPC performance tests have used an 8 KB block size. Larger and smaller block sizes have not given significant performance benefits."

That tells me that I can choose block size purely for administration convenience, ie, if I want datafiles bigger than 32G, then use 16K or 32K blocks. But I would like some confirmation of that statement. Anyone seen anything more official than just a note in a training manual?

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559675 is a reply to message #559671]

Thu, 05 July 2012 08:43

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

All,

What is the command to create multiple db_block_size.

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559677 is a reply to message #559675]

Thu, 05 July 2012 08:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What is the command to create multiple db_block_size.
if you don't know this command and are unwilling or incapable to RTFM yourself,
you are not competent to use it.

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559681 is a reply to message #559677]

Thu, 05 July 2012 09:21

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Very well said sir, i am not competent at dba level, but i know that why i want to use db_block_size 16k which has been set to 8k now and don't wat to recreate database. the only option looks to me is to use multiple block size. i am not the DBA as i am datawarehouse developer. Since in the absence of DBA i am the dba here and want to use it.

Today i might not be knowing it but once i know it i will be able to utilize the skill and can teach to many people like you sir.

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559682 is a reply to message #559681]

Thu, 05 July 2012 09:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.oracle.com/technetwork/indexes/documentation/index.html

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559684 is a reply to message #559681]

Thu, 05 July 2012 09:26

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

If you want the 16K block size the best bet is really to drop and recreate the database. In fact, you could have done that in the time you have spent arguing here Smile

Smile

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559693 is a reply to message #559681]

Thu, 05 July 2012 10:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

only option looks to me is to use multiple block size. i am not the DBA as i am datawarehouse developer. Since in the absence of DBA i am the dba here and want to use it.

So
1/ Wait for the DBA to recreate the database
2/ In the meantime use it with the current block size

Regards
Michel

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559784 is a reply to message #559671]

Fri, 06 July 2012 02:26

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Quote:

That tells me that I can choose block size purely for administration convenience

Principally you are right. But in some situations the different block size could help to reduce latch contention for "cache buffers chains", because blocks of different size will be managed in the separate buffer cache with separate set of these latches.

Report message to a moderator

Re: DB_BLOCK_SIZE [message #559787 is a reply to message #559784]

Fri, 06 July 2012 03:06

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

... assuming you have no limit in server RAM.

Regards
Michel

Report message to a moderator

Previous Topic:	Query running slow
Next Topic:	DB file sequential read (9 merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:15:08 CST 2025