Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary tables in memory

Re: Temporary tables in memory

From: Burton Peltier <burttemp1REMOVE_THIS_at_bellsouth.net>
Date: Sun, 31 Aug 2003 11:53:09 -0500
Message-ID: <UIp4b.1264$mG4.518@bignews5.bellsouth.net>


I wouldn't have thought of that. Good test.

Thanks.

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

> 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 - 11:53:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US