Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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