Home » RDBMS Server » Performance Tuning » DB_BLOCK_SIZE (ORACLE 11GR2 SOLARIS)
DB_BLOCK_SIZE [message #559654] Thu, 05 July 2012 05:59 Go to next message
guddu_12
Messages: 166
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
Re: DB_BLOCK_SIZE [message #559656 is a reply to message #559654] Thu, 05 July 2012 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: DB_BLOCK_SIZE [message #559657 is a reply to message #559656] Thu, 05 July 2012 06:36 Go to previous messageGo to next message
guddu_12
Messages: 166
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
Re: DB_BLOCK_SIZE [message #559658 is a reply to message #559657] Thu, 05 July 2012 06:38 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, 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
Re: DB_BLOCK_SIZE [message #559659 is a reply to message #559658] Thu, 05 July 2012 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, UK
Senior Member
I stand corrected. Thank you Michel.
Re: DB_BLOCK_SIZE [message #559671 is a reply to message #559654] Thu, 05 July 2012 08:04 Go to previous messageGo to next message
John Watson
Messages: 4082
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?
Re: DB_BLOCK_SIZE [message #559675 is a reply to message #559671] Thu, 05 July 2012 08:43 Go to previous messageGo to next message
guddu_12
Messages: 166
Registered: April 2012
Location: UK
Senior Member
All,

What is the command to create multiple db_block_size.
Re: DB_BLOCK_SIZE [message #559677 is a reply to message #559675] Thu, 05 July 2012 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 21948
Registered: January 2009
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.
Re: DB_BLOCK_SIZE [message #559681 is a reply to message #559677] Thu, 05 July 2012 09:21 Go to previous messageGo to next message
guddu_12
Messages: 166
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.

Re: DB_BLOCK_SIZE [message #559682 is a reply to message #559681] Thu, 05 July 2012 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 21948
Registered: January 2009
Senior Member
http://www.oracle.com/technetwork/indexes/documentation/index.html
Re: DB_BLOCK_SIZE [message #559684 is a reply to message #559681] Thu, 05 July 2012 09:26 Go to previous messageGo to next message
gazzag
Messages: 267
Registered: November 2010
Location: Bristol, 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
Re: DB_BLOCK_SIZE [message #559693 is a reply to message #559681] Thu, 05 July 2012 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: DB_BLOCK_SIZE [message #559784 is a reply to message #559671] Fri, 06 July 2012 02:26 Go to previous messageGo to next message
LNossov
Messages: 283
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.
Re: DB_BLOCK_SIZE [message #559787 is a reply to message #559784] Fri, 06 July 2012 03:06 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... assuming you have no limit in server RAM.

Regards
Michel
Previous Topic: Query running slow
Next Topic: DB file sequential read (9 merged)
Goto Forum:
  


Current Time: Sat Apr 19 15:26:20 CDT 2014

Total time taken to generate the page: 0.09757 seconds