1 2 Previous Next 22 Replies Latest reply: Dec 27, 2013 1:29 PM by jgarry Go to original post RSS
      • 15. Re: Tablespace Freespace

        Yikes, So what happen if it needs to extend? I just let it throws error like  "can not extend on size xxx" happened?

         

        Thanks

        • 16. Re: Tablespace Freespace
          sb92075

          when AUTOEXTEND is enabled, then TEMP will grow as long as free disk space exists.

          pick your poison.

          IMO, the TEMP tablespace datafile should NEVER have AUTOEXTEND  enabled.

          • 17. Re: Tablespace Freespace
            Hemant K Chitale

            >IMO, the TEMP tablespace datafile should NEVER have AUTOEXTEND  enabled

            I wouldn't necessary follow your advice. Never say NEVER.

             

            Or are you confusing the TEMP tablespace with the UNDO Tablespace ?

             

            Think about what TEMP is used for.  Queries.  Some of them may be critical. Transactions as well !!

             

             

            Hemant K Chitale

            • 18. Re: Tablespace Freespace
              sb92075

              >Think about what TEMP is used for.  Queries.  Some of them may be critical. Transactions as well !!

              some, many, most software professionals  actually test before deployment into Production.

              If/when some SQL runs throws error in Production, then QA must include this SQL in existing regression test suite.

              I would rather have 1 SQL terminate when the choice is to consume all available free disk space & possible crash the whole application.

              You are free to manage your DBs as you deem appropriate & I will do the same with my DBs.

               

              IMO, the TEMP tablespace datafile should NEVER have AUTOEXTEND  enabled

              • 19. Re: Tablespace Freespace

                Thanks Sb, Hemant and all

                 

                 

                But the fact is, your PROD database if continually increasing its transaction or its masterfiles to be processed every batch time , daily, weekly, or monthly. Hence the support for TEMP space sorting queries will increase too and not stagnant or same as always in the past process.

                 

                The bottom line question is, Do we need to monitor  TEMP  or Not?

                • 20. Re: Tablespace Freespace
                  Hemant K Chitale

                  Yes, TEMP usage is to be monitored.  For example, use V$TEMPSEG_USAGE

                   

                   

                   

                  Hemant K Chitale

                  • 21. Re: Tablespace Freespace
                    Hemant K Chitale

                    >some, many, most software professionals  actually test before deployment into Production.

                    Does your test environment simulate the same level of concurrency of queries that use TEMP as in Production ?  If Production has 50 users concurrently using Temp with usage from 1MB to 500MB, do you simulate the same load in your Test environment ?

                    Does your Test environment have queries with the same volume of data as Production  ? Always ?

                     

                    How do YOU determine the optimal "fixed" size for TEMP in Production ?  from the Development Test size ?  Multiplied by x where x is a number from 1 to n ?

                     

                    Just as data tablespaces may have AUTOEXTEND ON and yet the DBA is expected to monitor their usage and growth, so can temp tablespaces have AUTOEXTEND ON with the DBA doing his job.

                     

                    Hemant K Chitale

                    • 22. Re: Tablespace Freespace
                      jgarry

                      Another thing to consider, the amount of temp space used can depend heavily on what is being done.  In some cases, you discover things like a certain few queries doing multi-pass operations, and simply changing the environment for those sessions (for example, setting manual pga and sort area) may relieve a lot of pressure on temp.

                       

                      So to me, the middle ground is correct:  autoextend, with limits, and deal with overextended temp as an infrequent maintenance activity if needed.  If your environment is really stable, and/or you have enough disk to keep large temp for uses like large loads or things that require index rebuilds, then noautoextend.  This does seem to be the time of year people discover insufficient configurations.

                      1 2 Previous Next