Re: Questions about using BIGFILE vs SMALLFILE
Date: Mon, 19 Feb 2024 16:02:56 +0000
Message-ID: <SJ0P221MB075588C65B93DA0BC2AFA106A6512_at_SJ0P221MB0755.NAMP221.PROD.OUTLOOK.COM>
I can't see a valid reason for you to take the effort to move to bigfile tablespaces. Especially since you are on RHEL 6.6 and EE 12.2 and migrating to a different platform. I would have a change freeze on db changes.
I wonder what type of "performance" gains they are talking about. Seems like a solution in search of a problem.
Mike
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sandra Becker <sbecker6925_at_gmail.com> Sent: February 16, 2024 5:48 PM
To: oracle-l <oracle-l_at_freelists.org> Subject: Questions about using BIGFILE vs SMALLFILE
OS - RHEL6
Oracle EE - 12.2
We have a new DBA that is insisting using BIGFILE has been the industry standard for the past 10 years. Not something I was aware of since my current and last 3 employers chose not to use it. As team lead, I'm trying to find more information to decide if it's really worth the effort to move all our tablespaces to BIGFILE format before I make any recommendations to my management.
First some background on our environment:
- We have an incredibly bad database design that was done some 20 years ago and management will not approve a redesign.
- We do use ASM, which does simplify datafile management for us.
- About 80 percent of the application tables belong to a single schema. Roughly 90 percent of all application tables reside in the same tablespace.
- Thankfully, most indexes are in a separate tablespace.
- Many of the tables have a BLOB, CLOB or spatial data, sometimes all three in the same table.
- We are in the middle of a project to rewrite applications and move all data off of on-prem Oracle databases to AWS hosted postgres databases.
- New management has expressed the need to accelerate the move off of Oracle (still expect it to take another 2-3 years even if accelerated).
- There will be no more upgrades or patches to the Oracle environment. (We also no longer have Oracle Support. Financial decision.).
- Any changes cannot incur downtime for the production databases.
- Moving to BIGFILE also assumes we'll actually get new ASM storage, not a given due to some budget cuts.
I have some questions regarding what's involved and supported.
- Does BIGFILE support BLOB, CLOB and Geometry (spatial) data? We have a lot of spatial data as well as BLOBs and CLOBs.
- Will moving tablespaces from smallfile to bigfile require downtime?
- None of our tablespaces are over 175 datafiles currently, most are under 20 with minimal growth expected. Would we really gain anything by making the change?
- Would BIGFILE on ASM really be any better than our current SMALLFILE on ASM given the project to move off of Oracle completely?
Since the major projects are to get us off of Oracle sooner rather than later, is this really where we should be focusing our time and energy? My inclination is to say no, the team should be focusing on learning to administer and monitor the AWS postgres, as well as the MSSQL servers/databases that will not be migrated to postgres. I've been reading some articles, but wanted to get the feedback from people who have done the move from smallfile to bigfile.
Thank you for your feedback.
-- Sandy B. -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 19 2024 - 17:02:56 CET