We’re Gonna Need a Bigger TEMP Tablespace

17 Comments

1. 

   David Fitzjarrell 12 years ago Reply

   There is no ‘magic’ number for temp space size as it depends upon the type of system, the user load, the transaction volume, the size of the data and how well the queries/statements are tuned:

   http://dfitzjarrell.wordpress.com/2008/02/21/temporary-tablespace-insanity/

2. 

   Kellyn Pot'Vin 13 years ago Reply

   Ironic, but had issue today where waits on temp read/write across SSD was a performance hit, (sequential reads were first seen as hit, then upon investigation, the IO hit wait was from the temp tablespace groups!) The performance difference between two processes, same CTAS, same explain plan, one only using 6MB of TEMP, the second 13GB of TEMP was over 32 fold.
   My goal is to figure out a way to redesign the code or find a way to guarantee more PGA towards the process. Fix the temp usage, fix the performance… 🙂

3. 

   Gary 13 years ago Reply

   You got my mind chewing on this.
   Another aspect to consider is how many sessions/transactions are currently using temp space. The contents are private so it is inherently ‘selfish’ (That is MY space). Multiple sessions using temp space may be a sign of a problem (which is an extension of OLTP apps shouldn’t need temp).

4. 

   Kellyn Pot'Vin 13 years ago Reply

   I have many adhoc users and due to the demands on the business, a query can utilize huge amounts of temp from poorly written SQL and designed objects. A couple of the analysts/developers thought it was a competition, with the worst being 3TB of temp tablespace used for one PROCESS!! Understanding how temp is used vs. the size of the data set is incredibly important and how and when to use “step through tables” during an ETL process can save 100’s GB in TEMP. This can not only save wasted disk space but also enhance performance, often taking processes that sorted for hours down to mere minutes. Sorting and hashing to temp should be seen no different than swapping to disk when you are out of memory. This is an expensive process as you are no longer in fast memory, but slow disk. Faster disk, such as SSD can render some of this obsolete in our current configurations, but we still need to investigate the costs. All explain plans for DSS systems especially, should show the temp usage and IO cost, not just the CPU cost, cardinality and object information.

5. 

   Gary 13 years ago Reply

   Another thought.
   TEMP can be a trade-off.

   If an occasional report requires sorted data, that sorting can be done at the time, requiring temp, or perhaps through an index. The latter incurs the performance penalty on writes, the former on reads.

   And the other obvious trade off is doing a sort in memory (sucking up PGA and potentially SGA depending on how your memory limits are set) against a spill to disk. The latter is lower for that process/query but might be friendlier to the rest of the application.

   Different users can be set up with different temp tablespaces (and hence limits) to isolate them according to what is expected of them

6. 

   Martin Berger 13 years ago Reply

   I personally have only limited monitoring enabled. But I limit the resources in Test.
   With some luck no ‘bad’ query can escape into the real world.

7. 

   Kellyn Pot'Vin 13 years ago Reply

   #!/usr/bin/ksh
   #############################################################################
   # chk_tmp_undo.ksh
   # Kellyn Pot'Vin
   # Usage: ./chk_tmp_undo.ksh   
   # Good Base for Thresholds is 50% of Temp/Undo tablespace size. That will give you the warning when something large is running
   # Threshold is in MB- not GB!!
   #############################################################################
   
   #----------------------------------------------------------------------------
   # Verify that the ORACLE_SID has been specified on the UNIX command-line...
   #----------------------------------------------------------------------------
   if (( $# != 3 ))
   then
   echo "usage: $0 SID tmp_pct undo_pct"
   exit 1
   fi
   #
   #----------------------------------------------------------------------------
   # Set up Oracle environment...
   #----------------------------------------------------------------------------
   
   export ORACLE_SID=$1
   export TMP_THRSHLD=$2
   export UNDO_THRSHLD=$3
   echo "Oracle SID: "${ORACLE_SID}
   export ORACLE_BASE=/u01/app/oracle
   export ORACLE_HOME=`grep -i ^"$ORACLE_SID" /etc/oratab | awk -F: '{print $2}'`
   export PATH=$PATH:${ORACLE_HOME}/bin
   
   
   export EXEC_DIR=/common/admin
   export SQL_DIR=/common/sql
   export LOG_DIR=/common/logs
   export ML_LIST=""
   
   export UNDO_FL=${LOG_DIR}/${ORACLE_SID}_undo.log
   export TMP_FL=${LOG_DIR}/${ORACLE_SID}_tmp.log
   export OU_FL=${LOG_DIR}/${ORACLE_SID}_u.out
   export OT_FL=${LOG_DIR}/${ORACLE_SID}_t.out
   
   echo "UNDO_Threshold: $UNDO_THRSHLD"
   sqlplus -s << EOF | read GET_UNDO
   / as sysdba
   set feedback off
   set head off
   set pagesize 0
   select sum(round((vu.undoblks*32768)/(1024*1024))) "MB of UNDO"
   from v$undostat vu
   where ((vu.undoblks*32768)/(1024*1024)) > 0;
   EOF
   export $GET_UNDO
   
   sqlplus -s << EOF > $OU_FL
   / as sysdba
   set head on
   set feedback on
   set serveroutput on
   select distinct vt.sql_text, vs.sid,vs. osuser, vs.sql_id, sum(round((vu.undoblks*32768)/(1024*1024)))MB
   from v$sqltext vt, v$undostat vu, v$session vs
   where vu.maxqueryid in vs.sql_id
   and vs.sql_id=vt.sql_id
   and vt.piece=0
   and ((vu.undoblks*32768)/(1024*1024)) > 0
   group by vt.sql_text,vs.sid,vs. osuser,vs.sql_id, vu.undoblks
   order by MB desc;
   
   select sum(round((vu.undoblks*32768)/(1024*1024))) "MB of UNDO"
   from v$undostat vu
   where ((vu.undoblks*32768)/(1024*1024)) > 0;
   
   EOF
   
   if [[ $GET_UNDO -gt $UNDO_THRSHLD ]]
   then
   echo|mail -s "${ORACLE_SID}: $UNDO_THRSHLD MB Threshold of Undo Usage Surpassed" $ML_LIST < $OU_FL
   fi
   
   echo "TEMP_Threshold: $TMP_THRSHLD"
   sqlplus -s << EOF | read GET_TMP
   / as sysdba
   set head off
   set pagesize 0
   select sum(trunc(swa.tempseg_size/1024/1024))"TEMP TOTAL MB"
   from v$sql_workarea_active swa;
   EOF
   export $GET_TMP
   
   sqlplus -s << EOF > $OT_FL
   / as sysdba
   set head on
   set serveroutput on
   col sid format 9999990 heading 'SID'
   col osuser format 999,999,999,999 heading 'OSUSER'
   col tmpseg_size format 999999990 heading 'TEMP TOTAL MB'
   col sql_id format 999,999,999,999,999,999 heading 'Active SQL_ID'
   select swa.sid, vs.process, vs.osuser, vs.machine,vst.sql_text, vs.sql_id "Session SQL_ID", 
   swa.sql_id "Active SQL_ID", trunc(swa.tempseg_size/1024/1024)"TEMP TOTAL MB"
   from v$sql_workarea_active swa, v$session vs, v$sqltext vst
   where swa.sid=vs.sid
   and vs.sql_id=vst.sql_id
   and piece=0
   and swa.tempseg_size is not null
   order by "TEMP TOTAL MB" desc;
   
   select sum(trunc(swa.tempseg_size/1024/1024))"TEMP TOTAL MB"
   from v$sql_workarea_active swa;
   EOF
   
   if [[ $GET_TMP -gt $TMP_THRSHLD ]]
   then
   echo|mail -s "${ORACLE_SID}: $TMP_THRSHLD MB Threshold of TEMP Usage Surpassed" $ML_LIST < $OT_FL
   fi
   
   rm -f $OU_FL
   rm -f $OT_FL
   exit 0
   

   • 

     JeffS Post Author 13 years ago

     ewwwww, Unix and SQL*Plus?

     Just kidding, thanks for sharing, we all appreciate it!

   • 

     JeffS Post Author 13 years ago

     In case you’re having problems getting the script to copy correctly, try here too

8. 

   Kellyn Pot'Vin 13 years ago Reply

   Now having a tempDB quite large in SQL Server is a harsh issue, but I don’t consider this a feature that can be compared apples to apples with Oracle in the way “temp” space is utilized. Nothing shows poor performance like a huge TEMP tablespace in Oracle, where in SQL Server, it will show itself in a large transaction log. Tune the queries and in Oracle, TEMP tablespace usage shrinks as PGA is able to aborb the sorting and hash work that would have “spilled over” to the TEMP tablespace and in SQL Server, you can shrink the tran log and it will actually stay a reasonable size!
   In Oracle, if you want to know how to size it or even better yet, what needs to be tuned in an environment BEFORE it goes production, monitor the memory work area and see what is spilling over from PGA to temp, along with the majority of temp usage. Checking the PGA cache hit ratio will also tell you if you are utilizing TEMP too often for spill over and need to resize it to stop the TEMP work.

9. 

   Martin Berger 13 years ago Reply

   You should distinguish between OLTP and DSS systems (or at least queries):
   In a perfect OLTP system, no TEMP should be needed at all: If any statement does so much sort which can not be handled in memory, the statement might be worth tuning (or worse: the design is inappropriate). In a real world you should be able to know these few statements and estimate how many of these might run in parallel.
   In a DSS system you will need TEMP for sure. But not only TEMP, also CPU, IO (also for TEMP) and probably other resources for the statements. Again you should know at least those statements, which need most of them. As it makes no sense to run more statements than resources are available, the first saturated resource limits the total required resources – also TEMP.

   • 

     JeffS Post Author 13 years ago

     How many perfect OLTP systems have you encountered? Plus you’re assuming there’s plenty of memory…I’m thinking there’s a lot of AD HOC/reporting happening inside the OLTP database. That’s a much bigger ‘problem’ I suppose.

     As a DBA how do you track/ID queries using TEMP? Is that a report you run on a regular basis, or just something you look for when you get a performance ticket?

   • 

     Gary 13 years ago

     While the OLTP application might not need TEMP tablespace, some DBA operations may. The obvious example is building a new index.

     So a rough and ready solution is, your initial data load (eg some migrated data from an old system) will probably keep failing until you have enough TEMP space. Then you leave it there and your OLTP shouldn’t have a problem.

10. 

    Erin Stellato 13 years ago Reply

    For new customers, I try to understand how many users they will have, how busy the system will be, and size from there *to start*. It’s a best guess…and can anywhere from 2GB to 20GB, it just depends. Then, I tell them how to monitor it and explain that the size may need to increase at some point as they bring on more users or add more functionality.

    Yes, when you bounce the instance it will come up with tempdb sized at 8 MB UNTIL you increase the size. Once you do that, it will recreate tempdb at that size when you restart.

    • 

      JeffS Post Author 13 years ago

      Your best guess is really based on years of understanding your users and your app. So it’s not really a guess anymore 🙂

      Thanks for the clarification on the restart. That’s actually a very nice feature!

11. 

    Erin Stellato 13 years ago Reply

    There is no magic number for tempdb size in SQL Server 🙂 just as there’s no magic size for the transaction log. Here’s a couple MS articles on tempdb:

    http://msdn.microsoft.com/en-us/library/ms345368.aspx
    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

    The general recommendation is to run the system under normal production load (if you can recreate this in advance of go-live in a TEST/DEV environment, that’s great…but it really doesn’t happen too often) and see how large tempdb grows. Then, size it accordingly in Production. In SQL Server, when you restart the service, tempdb goes back to it’s default size (8 MB). For a Production instance, this is usually way too small. In addition, you can create multiple tempdb file to enhance concurrency, which is an entirely separate discussion 🙂

    • 

      JeffS Post Author 13 years ago

      I don’t see – in general – folks running Test/Dev with the same amount of data or users. So while that’s great advice, I’m betting it won’t do much for 80-90% of the users out there. When you deploy a new customer site, how do you size their TEMPDB? Have you come up with a Goldilocks, not too cold, not too hot number? Or do you run in test dev onsite and then tune the production instance as appropriate?

      Are you saying after a server bounces it will always come up with a 8MB tempDB in SQL Server? Or is there a way to keep it at a higher low-water-mark?