Oracle, Timesten and PL/SQL support

Tanel Poder

2009-03-07

I thought to post about another new interest of mine, TimestTen, as I’ve worked with it in past and I have become a fan of it, especially after Oracle bought the company.

Oracle has announced that TimesTen in-memory database will support PL/SQL in the upcoming release. That’s in 11gR2, where TimesTen is named the “in-memory database cache”.

I’m happy to see the deep level of integration Oracle is doing with it. It looks like both classic Oracle RDBMS and the TimesTen based code will have a (partially) shared PL/SQL code base.

There’s already a package called UTL_IDENT in Oracle 11.1.0.7 which stores couple variables used for conditional compilation depedent on the database product the packages are installed:

SQL> select dbms_metadata.get_ddl('PACKAGE', 'UTL_IDENT') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','UTL_IDENT')
--------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYS"."UTL_IDENT" is

  /* A typical usage of these boolean constants is

         $if utl_ident.is_oracle_server $then
           code supported for Oracle Database
         $elsif utl_ident.is_timesten $then
           code supported for TimesTen Database
         $end
   */

  is_oracle_server     constant boolean := TRUE;
  is_oracle_client     constant boolean := FALSE;
  is_timesten          constant boolean := FALSE;

end utl_ident;
/

It looks that there will be at least some utility packages which share common code base with classic Oracle RDBMS and just use different code internally if needed, based on the UTL_IDENT variables.

This was a nice surprise as such approach promises to have full PL/SQL utilities in TimesTen cache and no discrepancy between the PL/SQL utility capabilities available in different platforms. So (I hope) there will be just one PL/SQL, not one PL/SQL version for Oracle and other for the cache.

Back before 9i days, it was quite annoying to hit the SQL interface discrepancies between PL/SQL and native SQL engines. So there’s hope that there are gonna be less porting issues between Oracle classic RDBMS and TimesTen cache (both for application developers and TimesTen coders as only low level interfaces need to be changed).

Well, at least in theory it’s like that… But I hope Oracle/TimesTen coders will do a good job with this as I’ve worked with some algorithmic trading apps doing extreme transaction processing and in this world you don’t even like wasted microseconds as this directly translates to lost trading opportunities and lost potential revenue. The legacy solutions for such apps are usually some in-memory data stores, often built in house and lots of C++ code around it for procedural work. The data is then loaded / dumped to some disk-based RDBMS asynchronously (as the multi-millisecond response times of a classic RDBMS take ages!!!)

I’m waiting to get my hands on the 11gR2 (as everyone who’s reading this blog I suspect :), but especially the integrated in-memory database cache engine as it promises to make the life easier for a lot of algorithmic trading systems developers (and also the ability adapt quicker to ever-changing markets and growing complexity of products for investment banks and trading houses).


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS