Re: Temporary tables in memory

chris_jack_at_msn.com (Chris Jack) wrote in message news:<648c9f62.0309020109.644fc60d_at_posting.google.com>...
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<buG3b.295685$uu5.64729_at_sccrnsc04>...
> > How does the vendor solve the problem? Can you use global temporary tables?
> > Oracle does allow you to have a temporary tablespace that is a temp file.
> > (similar to Sybase and an efficient way to do it)
> > Jim
>
> I'm not sure what you mean by how does the vendor solve the problem.
> The result sets are returned via global temporary tables - which are
> stored in a temporary tablespace which is stored on disk.
>
> If there is a method of storing the Oracle temporary tablespace in
> memory, then I would agree it was similar to Sybase and similarly
> efficient - however I have been unable to find a way of putting the
> Oracle temporary tablespace in memory in the Oracle manual. Based in
> the replies to date, it would appear it is not possible.

It is not in the Oracle manuals, because it is done in the OS. If you are on Linux, and some other unix, there are built-in devices (perhaps /tmp) that are in-memory disklike devices. On hp-ux, it is used to build the system but not in normal usage, I posted the (untested by me) general instructions for building such a beast a while back. In general, you would have to add the tablespace after bringing up Oracle, and be prepared to deal with the consequences of restarting after a crash. It's not too tough, although it might be for a novice using sybase-think.

There are also hardware devices that substitute memory for disks, which of course have their own idiosyncracies. http://www.desdbx.com/ "The New Rules of Deployment" on oaktable.net http://metalink.oracle.com/cgi-bin/cr/getfile.cgi?p_attid=76413.1:219101

>
> Sybase recommends (and makes it extremely easy) to cache its temporary
> database in memory - but then Sybase development makes much heavier
> use of temporary tables than Oracle does, so it is unfair to criticise
> Oracle (too much) for something that is not a widely used feature.
> Nevertheless for this style of coding, it means Oracle is an order of
> magnitude slower than Sybase.
>
> To explain the Sybase to Oracle port in more detail, and why it
> causing performance problems: if you want to populate a pop up list
> box with all the currencies in the database, in Sybase you do:
> select * from Currency
>
> in a stored procedure. To port this code to Oracle, the vendor uses an
> automated tool which does:
> select * into CurrencyRS from Currency
>
> in PL/SQL and pushes the name CurrencyRS (a global temporary table) on
> to a stack. The application then reads the table names off the stack
> one by one and does a table scan on each table - cleaning out the
> tables as it goes.
>
> Conceptually, this makes it easy to translate the Sybase code to
> Oracle code and have one application handle both back ends (well you
> have a different low level Oracle library, but the rest is the same).
> The downside is it slows performance down by probably a factor of 20
> (or maybe only 10 if logs weren't written for global temporary tables
> - which, unfortunately does not appear to be the case).
>
> In Sybase, diagnostics show a memory cache hit ratio of over 99% - so
> you are basically doing 1 memory page read for every page requested.
>
> Compare this to the Oracle solution: for every page required you need
> to do 1 memory read from the original table (lets assume this is
> cached), 1 disk write for the data page (and probably 1 disk write for
> the log). It is reasonable to assume the data page written to the
> global temporary table is still in memory when it is read by the
> application. If we assume 1 disk write = 9 memory accesses (a bit
> rough and ready I know, but I find it hard and not very useful to find
> actual data out on a specific disk vs memory basis), you can easily do
> the maths to work out why the Oracle solution creaks along.
>
> This is also why I am so keen to see if there is a way of putting the
> temporary table space into memory. We would get an instant speed up of
> a factor of hopefully around 7 (i.e. Oracle would need to do 1 disk
> read for the original data, 1 disk write to the result set table now
> in memory, and 1 disk read to fetch the result set back) - still 3
> times slower than Sybase, but more manageable.
>
> I don't know the exact details of how they have changed this in the
> next version of the product, but, instead of populating temporary
> tables, they are returning ref cursors - so hopefully it is much
> closer to the Sybase performance. As already stated, unfortunately
> this is not a current solution at the clients I am dealing with.

Probably got around to reading the "how to LOV" docs.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/tue/business/news_1b2embraer.html