Thanks for the question, Naved.
Asked: April 05, 2024 - 4:33 pm UTC
Last updated: April 08, 2024 - 4:59 am UTC
Version: 11.2.0.4
Viewed 100+ times
You Asked
I am migrating 11g database cross endianness from on-prem to EXACS . On-prem database db_block_size is 4k and all the tablespaces are also of 4k block size .
Since, I cannot provision non-standard block size database in OCI , I am worried about the performance impact caused by different block size. Please help me understand what database block size would be recommended for the below scenario.
-----------------------------------------------------------
Source : ON_PREM
-----------------------------------------------------------
Platform / ID : AIX-Based Systems (64-bit) / 6
Version : 11.2.0.4.0
Size (GB) : 17 TB
db_block_size : 4k
All Tablespaces BLK Size : 4k
-----------------------------------------------------------
Target : OCI - EXACS
-----------------------------------------------------------
Platform / ID : LINUX / 13
Version : 11.2.0.4.0
Size (GB) : 17 TB
db_block_size : 8K
APP Tablespaces BLK Size : 4k
SYSTEM/SYSAUX/TEMP/UNDO : 8K
Phase 1: Migrating from AIX 11g to EXACS 11g
Phase 2: 19c upgrade and Multi tenant {
Due to business requirement we have to split migration and upgrade}
Question : 1. Can we guarantee that there will be no performance impact due to difference in tablespace and database block size if db_4k_cache_size parameter is set adequately to large value .
2. Or Better to go for same 4k block size as source on-premises database.
Off course application regression testing and RAT will be included , but testing both cases is not feasible, hence reaching for expert advice .
and Connor said...
1. Can we guarantee that there will be no performance impact due to difference
There is no such thing as a 100% guarantee, but I'd probably go a high as 99% :-)
You would have to be incredibly unfortunate to hit a slow down going from 4k to 8k. One possible (but very unlikely) circumstance would be two separate 4k blocks that were very busy in your existing system now sit in a single 8k block in the target, thus increasing contention. But the odd of that a very low.
A more likely outcome is no change or perhaps a little bit faster with 8k. This is because is the overhead per block is constant, so you get a slightly better data efficiency with an 8k block. So your tables might be a little smaller, index density a little better, etc. Unlikely to be significant.
2. Or Better to go for same 4k block size as source on-premises database.
I would not go with 4k but not for performance reasons.
(Without having actual data) I would contend that 99.9% of customers running Oracle do so on an 8k blocksize. Which means 0.1% are running on a 2k, 4k or 16k. Which means if you hit an issue that means logging a call with Support, two things come into play when looking at 4k versus 8k
a) It is WAY more likely that someone else has already hit this issue, which means there is more likely already a fix, or at minimum, a better set of knowledge resources around the issue
b) If you hit an issue with a 4k blocksize, it is (by definition) a niche issue if it cannot be reproduced on 8k. You will have hit a far more "extreme" boundary case.
That alone would make me choose 8k every time