Database Administration (MOSC)

MOSC Banner

Increasing db_block_size , pros and cons

edited Sep 19, 2020 5:00AM in Database Administration (MOSC) 4 commentsAnswered

Hi everyone,

We have a datawarehouse database which is around 2TB size for now. We came up with the idea of increasing the db_block_size from current 8k to 32K.I just wanted to understand what else needs to tweaked with this change? Will it really help? Please correct me for the below points.

1. For now, I understand SGA needs to be doubled too because it requires more space in buffer cache larger db block.

2. UNDO needs to be doubled too.

3. The main intention for doing this was it would be helpful for a full table scan. However, during testing for 16k, observed that even though I/O has been significantly reduced but the time taken for execution has increased for 16K as compared to 8K DB.The query is simple select * from <huge table_name>; stats was intact during this time.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center