Forum OpenACS Q&A: Oracle temp tablespace is huge!

Posted by Andrew Piskorski on
I just noticed that my Oracle temp tablespace datafile is huge, 12.7 GB. Now, my next-largest database file is only 445 MB, and when I do a full database export and then gzip it, it's only 130 MB. So 12.7 GB for the temp tablespace seems a little excessive...

I've started reading more docs about tablespace fragmentation and the like, but has anyone here encountered this problem before, and come up with a simple fix for it??

Also, the reason I noticed this in the first place is that I've been fooling with hotbackups using RMAN, and my RMAN backup of the whole database was running absurdly slowly. Turned out it was because RMAN was including the whole 12.7 GB temp tablespace in the backup. Am I correct that there is no reason to every make a physical backup of my temp tablespace?

Posted by Sebastiano Pilla on
What's the version of Oracle you're using? If >= 8.1.5, you could use a locally-managed temporary tablespace with tempfiles, and you could avoid making physical backups of it.

It's possible that this particular datafile was created with the default pctincrease 50 and with autoextend on, but without a maxsize limitation. I suggest to drop and recreate the temporary tablespace, but before doing anything make sure that no permanent object has been created by accident in this tablespace.

To create a brand new temporary tablespace, use something like:
create temporary tablespace TEMP
  tempfile '/ora8/data/temp01.dbf'
  size 50M
  autoextend on
  next 10M
  maxsize 300M
  extent management local
  uniform size 64K;
A couple of notes: you have to use tempfile rather than datafile (and this file won't ever show up in dba_data_files), and the value of the uniform size should be equal to the value of the sort_area_size initialization parameter.
Posted by Andrew Piskorski on
I'm using Oracle (and plan to upgrade to soon). Sebastiano, thanks for your suggestion! I decided to create a new tablespace named TMP, and then drop my old huge TEMP tablespace. Here's how I did it:

My Oracle instance creation script "$ORACLE_HOME/assistants/dbca/jlib/" had this in it, so presumably that's how my old TEMP tablespace was created:

REM ********** TABLESPACE FOR TEMPORARY **********
CREATE TABLESPACE TEMP DATAFILE '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' SIZE 150M REUSE

First I used these queries to check some settings:

-- List all database files and their tablespaces:
select  file_name, tablespace_name, status
  ,bytes   /1000000  as MB
  ,maxbytes/1000000  as MB_max
from dba_data_files ;
-- What temporary tablespace is each user using?:
select username, temporary_tablespace, default_tablespace from dba_users ;
-- List all tablespaces and some settings:
select tablespace_name, status, contents, extent_management
from dba_tablespaces ;

------------------------------ --------- ---------- ---------
TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:
select count(*)  from dba_all_tables
where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get
-- NO rows for this:
column owner        format a20
column object_type  format a30
column object_name  format a40
  o.owner  ,o.object_name
from sys_objects s
  ,dba_objects o
  ,dba_data_files df
where df.file_id = s.header_file
  and o.object_id = s.object_id
  and df.tablespace_name = 'TEMP' ;

Ok, that all looked good, so then I went and created my new TMP tablespace, changed all my users to use TMP instead of TEMP, and took TMP and its datafile offline:

create temporary tablespace TMP
tempfile '/ora8/m01/app/oracle/oradata/ora8/tmp01.dbf'
size 100M  autoextend on  next 10M  maxsize 500M
extent management local
uniform size 64K  -- should be same as sort_area_size init param

alter user USERNAME  temporary tablespace TMP;

alter tablespace TEMP offline normal ;
alter database datafile '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' offline;

Note that I didn't actually drop the tablespace yet - more on that later.

There isn't much information out there what the uniform size should be. Tom Kyte mentions in passing in his Expert One on One Oracle, and explains a bit more thoroughly on asktom that yes, it should be the same as your sort_area_size. (Sebastiano, if you hadn't pointed it out above, I'd probably never have found it!)

One interesting thing that wasn't immediately clear to me from the docs, is that while it's storage contents were definitely "tempory", this is definitely different using a "tempfile". I think only locally managed tablespaces can use "tempfiles", and perhaps being a "temporary tablespace" means the same thing as using a "tempfile".

Before I created my new TMP tablespace, I had no locally managed tablespaces in this database, and both these queries returned 0:

select count(*) from dba_temp_files ;
select count(*) from v$temp_space_header ;
Once I created my locally managed TMP tablespace, both of the above queries returned 1.

At any rate, RMAN's "backup database" command still insisted on backing up TEMP, even though its contents were temporary. But export definitely did not export anything from it - which makes sense, since there should be no permanent objects in there to export. The new locally managed temporary tablespace TMP, on the other hand, did not is now ignored completely by RMAN's "backup database" command.

Out of paranoia, I waited a couple days before finally dropping the huge TEMP tablespace with:

drop tablespace TEMP ;
Note that I didn't add the including contents or cascade constraints clauses to the drop tablespace, because I figured if there are any objects or constraints in there, then something must be wrong.

I also did not first do a "alter database datafile '/ora8/m01/app/oracle/oradata/ora8/temp01.dbf' offline drop;", but from what I've since read, that should probably make no difference whatsoever either way.

Here's the annoying part: As I write this, my drop tablespace TEMP command is still running!

Apparently, since it's a dictionary managed tablespace, Oracle needs to delete (in my case) 170k or so rows from the system fet$ table, which has 1 row for each free extent. And Oracle is absurdly slow in doing so. Why Oracle is so slow, I haven't found any explanation for. But it is. And the recomended fix is just to wait, and let Oracle suck up 50% CPU for several days if necessary.

From what I've read, you should be able to kill the drop tablespace command at any time in order to stop using up CPU, and then restart it later to continue the process of deleting rows from fet$ without any trouble, but I haven't tried that.

Here's a query to show you the number of rows in fet$ for the TEMP tablespace - it should slowly keep going down:

select, count(*)
from fet$ fet, ts$ ts
where fet.ts# = ts.ts# 
  and = 'TEMP'
group by ;

Here are some links directly related to this slow drop tablespace problem: Metalink: one, two; Ask Tom, OraFAQ. And a few more links that are sort-of related: Metalink: three, four, five.

(Thanks to Michael Bryzek for pointing out several of those links to me, and giving me the fet$ query above.)

Btw, 170k extents in a tablespace is ridiculously many. Apparently it's only ever a problem for drop and coalesce operations, but some of the links above basically say that anything over 1000 or so extents is bad, it means you messed up in how you defined the tablespace in the first place.

Posted by Andrew Piskorski on
Oh, also, this might be mildly usefull: While your drop tablespace command seems to be hanging, if you run the query below you should see that Oracle user SYS is executing this SQL: "delete from fet$ where file#=:1 and block#=:2 and ts#=:3".
set linesize 180 
   p.spid  as os_pid 
  ,s.sid  ,s.serial#  
  --,p.username  as os_user 
  ,s.username  ,s.status 
  --,p.terminal  ,p.program 
from   v$session s, v$sqltext sql 
  ,v$process p 
where  sql.address    = s.sql_address 
  and  sql.hash_value = s.sql_hash_value 
  and  p.addr = s.paddr 
  --and p.spid in (4656) 
  and upper(s.username) = 'SYS' 
order by s.username ,s.sid ,s.serial# ,sql.piece ;

And the os_pid will be the PID that top shows sucking up your CPU.

Posted by Andrew Piskorski on
FYI, it took about 12 hours or so for my drop tablespace TEMP to complete.