Re: Temporary tables in memory

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 1 Sep 2003 21:51:11 +0100
Message-ID: <bj0bep$pg6$1$8300dec7@news.demon.co.uk>

You can refine this test by checking v$tempstat as the test proceeds. Although an extent has been allocated, that doesn't mean that anything has been written to the tempfile.

In fact, temporary tables have an extra I/O benefit over normal tables - if you truncate a normal table, any dirty blocks are flushed to disc first. If you truncate a temporary table (e.g. by commit for an 'on commit delete rows' table), the blocks are not flushed to disk.

So, although the temporary table blocks populate the buffer cache, they don't necessarily get written to disc.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:3f51d7ef$0$6529$afc38c87_at_news.optusnet.com.au...


> In session 2:

>

> SQL> select * from v$sort_usage;

>

> USERNAME                       USER

SESSION_


> ------------------------------ ------------------------------ ------

--


> SESSION_NUM SQLADDR     SQLHASH TABLESPACE

CONTENTS


> ----------- -------- ---------- ------------------------------- ----

-----


> SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#

> --------- ---------- ---------- ---------- ---------- ----------

> SCOTT                          SCOTT

682102D8


>       24583 66EBAD24 3093019500 TEMP

TEMPORARY


> DATA             201          9          1        128          1

>

> Whoops! The insertion of an extremely tiny row causes a sort extent

to hit


> disk. (Incdidentally, I have a sort_area_size of 512KB, and you can

see that


> the row length is only a matter of a few tens of bytes)

>

> Conclusion: temporary tables do not get cached in the PGA

(sort_area_sized)


> but, regardless of size, are written directly to disk. They don't

generate


> redo, however.

>

> Regards

> HJR

>

Received on Mon Sep 01 2003 - 15:51:11 CDT