Re: Temporary tables in memory

A quick test:

In session 1:

SQL> create global temporary table X (col1 number); Table created.

In session 2:

SQL> select * from v$sort_usage;
no rows selected

Good news: the creation of a global temporary table does NOT hit the disk (ie, the temporary tablespace), otherwise v$sort_usage would know about it.

Continuing:

In session 1:

SQL> insert into X values (34);
1 row created.

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 "Burton Peltier" <burttemp1REMOVE_THIS_at_bellsouth.net> wrote in message news:Ufi4b.1426$L6.826_at_bignews6.bellsouth.net...
> One opinion/thought with some questions.... further testing needed...
>
> Global tempoary tables are stored in the temporary tablespace, but I see
> your point in that you assume this is disk I/O...
>
> I have looked at the docs and it is not clear if a global temporary table
> would use the sort_area_size memory area of a session if it were large
> enough?
>
> Since other operations use the sort_area_size of memory before going to
the
> temporary tablespace, it would be nice if this worked that way too.
>
> Then, assuming you have the memory, you could just allocate more memory to
> each session's sort_area_size .
>
>
> --
> "Chris Jack" <chris_jack_at_msn.com> wrote in message
> news:648c9f62.0308280512.38a39b62_at_posting.google.com...
> > Is there a way in Oracle 8i (or later, but would prefer 8i solution)
> > of avoiding the writing to disk of temporary tables: i.e. tables
> > created with 'create global temporary table'...? Alternatively, can
> > someone confirm it is not possible so I can stop worrying about it?
> >
> > For instance, it seems to me you are required to store temporary
> > tables in a tablespace, and a tablespace must be stored on disk. It
> > appears that that disk cannot be a memory mapped device - but maybe
> > someone knows a way around this.
> >
> > I have a rule of thumb that, in the absence of other information,
> > disks are nine times as slow as memory. As the application makes
> > significant use of temporary tables, the overhead is significant. I
> > cannot do anything about the application and if I told you it was a
> > Sybase to Oracle port, you might guess at why the temporary tables are
> > used.
> >
> > Also, do temporary tables get logged and, if so, is there a way of
> > turning this off?
> >
> > Thanks in advance
> > Chris
>
>
Received on Sun Aug 31 2003 - 06:10:05 CDT