Re: Temporary tables in memory

"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.

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.

Regards
Chris Received on Tue Sep 02 2003 - 04:09:44 CDT