How to step up performance with Oracle in-memory database options
A comprehensive collection of articles, videos and more, hand-picked by our editors
More on in-memory databases
Oracle Exalytics includes TimesTen
Is in-memory technology becoming a commodity?
Read about Oracle’s acquisition of TimesTen
This is the second part of a two-part series comparing the in-memory database technologies of Oracle TimesTen and Sybase Adaptive Server Enterprise (ASE). This part takes an in-depth look at Sybase ASE, while the first part examined TimesTen.
Sybase ASE in-memory database structure
ASE introduced its own in-memory database (IMDB) as part of its 15.5 release. The origin of ASE-IMDB goes back to days when Sybase was working on its Real Analytics Platform (RAP), but this was the first time that ASE has a licensed IMDB product.
Like TimesTen, ASE-IMDB is a high-performance database. ASE-IMDB is fully integrated within ASE itself. This is in contrast to TimesTen, which is basically a standalone database. ASE-IMDB can read and write data to other databases on the same ASE, and can receive data from other ASE or non-ASE databases. ASE-IMDB also uses replication to receive data from all these sources.
ASE classic databases are designed for applications that need to strictly adhere to ACID (atomicity, consistency, isolation, durability) transaction semantics. These ACID properties are implemented by means of a write-ahead transaction log, located on persistent storage (i.e. disk). In this respect, ASE-IMDB allows the durability and atomicity aspects of transactional behaviour to be relaxed in exchange for lower response times and higher throughput. This is in contrast to TimesTen, which adheres fully to ACID properties.
To have an ASE-IMDB, you will need enough cache to hold the entire database in memory. Once this dedicated cache is created, it will act as placeholder for devices for the IMDB and a database can be created on these in-memory devices. ASE-IMDB is created based on an available template database. A template database is a classic ASE database. At start-up ASE-IMDB will inherit all objects and data from its template database. A typical syntax for creating an ASE-IMDB would be:
create inmemory database ASEIMDB
use ASEIMDB_template as template
log on ASEIMDB_log01='1000M'
with durability = no_recovery
The approach is neat and requires very little new learning for database administrators. Note that in the above syntax there is an explicit reference to a template database - ASEIMDB_template in this case. Also durability has to be no recovery, which means that ASE-IMDB databases are not recoverable. All contents of an ASE-IMDB are therefore lost after cycling the ASE server or an unexpected shut down, because of the absence of persistent storage. On the other hand, this has allowed Sybase to optimize transaction logging (which still happens, but fully in-memory); since an IMDB never needs to be recovered from its transaction log when ASE reboots, you can get better transactional scalability and performance.
There has been no change in the index algorithms of ASE-IMDB. In other words, there has been no attempt to reduce storage space consumption when the database is in-memory. When dealing with large amounts of data, this can mean significant overhead and additional memory for processing in-memory. Since you can dump and load a normal ASE database into ASE-IMDB, you can then use the existing indexes common to ASE classic databases, thus allowing total compatibility.
Application of ASE-IMDB
If you already use ASE, then having an ASE-IMDB is a matter of obtaining the license for it. ASE-IMDB is aimed at write-intensive applications where the persistence of the data is of secondary nature. I think one must state the intended usage to say if an IMDB should be recoverable for that stated application. There are certain applications where recovery does not matter. These applications can deploy ASE-IMDB for the sake of much better performance.
Since ASE-IMDB is completely integrated within a hybrid classic server, it can fully deploy the SQL dialect, security and encryption aspects of ASE itself. E-commerce, shopping carts, certain trading systems, staging/intermediate databases where data is cleaned and ready before posting to a classic database are prime examples of where ASE-IMDB will come very useful. In addition, its ability to receive data from other sources via common replication methods makes ASE-IMDB an ideal workbench where the business demands a faster response. There is currently work in progress to enable fast replication of an ASE-IMDB to another. However, if the recovery is paramount for your application, then ASE-IMDB may not be suitable for that purpose.
Both ASE-IMDB and Oracle TimesTen provide excellent in-memory capabilities. Although they are both in-memory databases, they tend to serve different purpose. But in their respective application areas they are industry leaders with proven track records for delivery and reliability.
Mich Talebzadeh is a consultant and technical architect who has worked with database management systems since his student days at Imperial College, University of London, where he obtained his PhD in Experimental Particle Physics. He specializes in the strategic use of Oracle and Sybase and is the author of several database books and articles.