Re: Questions about using BIGFILE vs SMALLFILE

Thanks to everyone for your feedback. Since our largest tablespace currently has less than 200 datafiles and we use ASM, datafile count/management isn't really an issue. Size of the SGA and controlfiles also are not not an issue for us. As I mentioned, we are migrating off of Oracle and have begun the process of moving our spatial data and images to postgres. The application will be redirected to the postgres database once the data migration is complete, so very little growth expected for our largest database over the next 1-2 years. I had not considered this issues around backup/restore, so great information on that aspect.

Regarding the question of the benefits my colleague says we will see are easier file management (already a non-issue) and better performance. Two things about the performance part:

1. We are not experiencing any kind of performance issues in production, only in a test environment (OS/server config) that was not sized appropriately for the kind of testing they are doing.
2. I read something from Tom Kyte that says you get a small benefit on startup, but not so much after that. I cannot find anyone who took his challenge to prove otherwise.

Great information and I feel more confident in my decision going forward.

Sandy

On Sat, Feb 17, 2024 at 6:55 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On Sat, 2024-02-17 at 08:23 +0100, Ilmar Kerm wrote:
>
> Daniel Overby Hansen just recently wrote about this topic:
>
> https://dohdatabase.com/2023/10/24/why-are-you-not-using-bigfile-tablespaces/
>
> To me it was a major surprise, that people still debate it, I really would
> not want to manage hundreds or thousands of data files for a single
> tablespace.
> But a good link from that thread was that there might still be a case for
> smallfiles due to Doc ID 2089689.1
>
> PS I don't really think in your case the migration effort is worth it.
>
>
> On Fri, 16 Feb 2024 at 23:50, Sandra Becker <sbecker6925_at_gmail.com> wrote:
>
> 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*.
>
> 1. Does BIGFILE support BLOB, CLOB and Geometry (spatial) data? We
> have a lot of spatial data as well as BLOBs and CLOBs.
> 2. Will moving tablespaces from smallfile to bigfile require downtime?
> 3. 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?
> 4. 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.
>
>
>
> --
> Ilmar Kerm
>
>
> Hi Ilmar,
> Small file tablespace allows me to move a portion of it to another storage
> unit. I cannot do partial move with the BIGFILE tablespace. Also, I am not
> "managing" thousands of data files, they don't need any management. The
> pertinent information would be what Oracle uses for their tests. Are they
> using bigfile tablespaces or not?
> Regards
>
> --
>
> Mladen Gogala
> Database SME
> https://dbwhisperer.wordpress.com
>
>

-- 
Sandy B.

--
http://www.freelists.org/webpage/oracle-l