>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
>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
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?
>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
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.