7 Replies Latest reply on Jun 17, 2008 12:36 AM by 247514

    one single tablespace for the entire db of 3.3T

    641640
      One of the db i am supporting has about 3.3 Terabytes capacity and the application is using only 1 huge tablespace with one big file.

      the system is linux 4 , 32 bit.
      oracle version is 10.2.0.4

      Is there a limit of space for a tablespace when you consider insert/delete/query performance?

      Thanks,
      Chau
        • 1. Re: one single tablespace for the entire db of 3.3T
          Alvaro Buitrago
          IMHO a only big file is an error
          how can you backup it ?
          if you have many files, you could put one by one in backup mode, but how to do it in only one file ?
          • 2. Re: one single tablespace for the entire db of 3.3T
            F.Munoz Alvarez
            Dear User638637,

            Are you using ASM?
            You can use one tablespace, but is not a good idea because you complicate your administration process. Divide the Data in organized tablespaces like: HR_DATA, HR_INDEX,FIN_DATA, FIN_INDEX...etc, will help you with the admininstration of the Data.

            What is better (thinking in all your personal properties in your house):

            Have all your stuff in a big container? or have all organized in small ones, that when you need to find something you know where to find it, and because is small it's easy to find it?

            Regards,

            Francisco Munoz Alvarez
            • 3. Re: one single tablespace for the entire db of 3.3T
              584650
              How is the database being backed up? If you're using RMAN that means only 1 channel can backup the datafile at a time as opposed to the datafile being split up in which case RMAN could backup the datafiles using multiple channels (if you're using Enterprise Edition) meaning backup time will be less (in theory).
              • 4. Re: one single tablespace for the entire db of 3.3T
                amardeep.sidhu
                Whosoever designed this database, deserves pat on the back ! :)
                • 5. Re: one single tablespace for the entire db of 3.3T
                  641640
                  Could someone analyze the performance impact in this case?
                  with one single tablespace, does it need more time to insert/query than using multple tablespaces?

                  Thanks,
                  Chau
                  • 6. Re: one single tablespace for the entire db of 3.3T
                    F.Munoz Alvarez
                    Dear user638637,

                    It's almost imposible to give the information you request with the information you post. They are so many variables: Number of datafiles, Storage, ASM?, Hot objects, Database parameters,etc. I recommend you to read some material regarding concepts, performance tuning,etc on oracle tahiti.

                    http://tahiti.oracle.com/


                    Best Regards,

                    Francisco Munoz Alvarez
                    www.oraclenz.com
                    • 7. Re: one single tablespace for the entire db of 3.3T
                      247514
                      It's really depends on how your storage been setup. Besides the lack of parallel backup ability like other user pointed out. There shouldn't be any other major performance impact solely because of big tablespace. Or bigfile tablespace alone doesn't cause performance problem. It's only a problem if you setup is wrong. For example setup this file on system that doesn't support striping.

                      Since you have 3.3 TB size tablespace with one single datafile, that means you must have a big file tablespace which only support one datafile.
                      Performance of database opens, checkpoints, and DBWR 
                      processes should improve if data is stored in bigfile
                      tablespaces instead of traditional tablespaces.
                      However, increasing the datafile size might increase
                      time to restore a corrupted file or create a new
                      datafile.
                      That is in the event of media crash, that could be only affect one or two small files in traditional setup, but in your case, you need to restore whole big file.

                      Some more information about big file tablespace here,
                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN01102

                      and here
                      Considerations with Bigfile Tablespaces
                      http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/physical.htm#sthref489