This is a quick note to document some things I have learned when investigating temporary tables.

Global Temporary Table = Temporary Table

In Oracle, you often see references to global temporary tables and temporary tables. These are both different names for the same thing.

Data in Temporary Tables goes through the Buffer Cache

Temporary table are very like normal tables. When you insert, delete, update or select from them, the data all passes through the buffer cache.

The buffer cache typically serves two purposes:

1. Buffer inserts, deletes and updates in memory to make these operations faster, leaving DBWR to flush the dirty buffers to disk.

2. Buffer data in memory that may be required by other queries or sessions in the future, reducing the chances of having to reread it from disk.

For temporary tables the data inserted is never accessed by any other session other than the one that created it, so the data in the cache will tend to be short lived. If an application makes heavy use of temp tables, it may need a bigger buffer cache to prevent other useful reusable data getting flushed out.

Temp Table changes result in UNDO, which generates REDO

Data written into temp tables is never logged in the REDO log stream. However, adding or changing data does create undo records, which are logged in redo. Therefore manipulating data in temp tables does indirectly create some redo, but nowhere near as much as a permanent table will. There are good examples of this in Tom Kyte's book - Expert Oracle Architecture.

Using Temp Tables Start Transactions

I recently encountered an application that uses a connection pool to do seemingly read only queries from a database.

All the queries were performed via stored procedures, and internally these procedures use temporary tables to stage the result data.

At the start of each procedure all data in the temporary table is deleted before inserting new data. All these changes to the table created UNDO records that could not be freed until the application commits or performs a rollback.

As the application believed these queries to be read only, it never committed. Over time the UNDO usage on the database grew slowly until it ran out out space, causing an outage on the application.

The lesson here is that if you use Temporary tables in an otherwise readonly application, the application needs to commit to free up the UNDO created by changes in the temporary tables.

How much space are my temp tables using?

By querying v$sort_usage, all temporary table segments can be located with a SEGTYPE = DATA. Alternatively use the following query:

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 )     size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Right now, I cannot find a way to link the temporary extents back to the temporary table they are part of.

How much of my Buffer Cache is Temp?

By querying v$bh (which should not be done on a production system) with the following query, all the blocks in the buffer cache dedicated to temp tables can be located:

select * from v$bh where temp = 'Y';

Normally in v$bh, the column OBJD contains the dataobjectid for the object stored in dba_objects. However, for temporary tables, the OBJD does not link to the table name.

According to Jonathan Lewis, the OBJD for temporary blocks is defined as:

power(2,22) * tempfile id + block number

Where:

• block number is the block number used as the start of the extent in the tablespace. It can be found in SEGBLK# in v$sort_usage.
• Tempfile id is the tempfile number found in v$tempfile for the tablespace.

I have verified this does indeed seem to be the case.