Oracle Timesten is an in-memory database that is either available as a standalone database system or comes bundled with Oracle Exalytics systems. The objective of Timesten is to hold the database in the server’s Main Memory so that data retrieval and processing are extremely fast, when compared to conventional disk-optimized database systems. We have recently implemented a migration of OBIEE reports from Oracle based data warehouse to Timesten on Exalytics and have observed an average of 17 times faster data retrieval times, as compared to Oracle 11g database and that too across 92 reports!

Timesten Installation

Timesten comes pre-installed on Oracle Exalytics systems along with a test datastore. Exalytics is a powerful system with 2 Terabytes of RAM and is engineered for high performance with in-memory analytics. Timesten can also be downloaded and installed from Oracle’s website as a standalone database. I will skip elaborating on the installation steps in this post as the installation steps are quite straightforward. You can read it on the installation guide available from Oracle. 

Once the installation is complete, the user needs to initialize the environment for Timesten every time he logs in. This is done by running the ttenv.sh file from the ‘bin’ folder, as follows:

Once the LD_LIBRARY_PATH, PATH and CLASSPATH are set using the above, the user can start using the Timesten commands. It is ideal to include to the above in the .bash_profile of the user so that the initialization of the environment automatically happens when the user logs in.

The user may now use the following commands to check Timesten’s status and version:

• ttstatus
• ttversion

Creating a Datastore / Database

For creating a datastore, the user needs to define the datastore in the sys.odbc.ini file in the following location: $TIMESTEN_HOME/<ttexa_prod_domain >/info/sys.odbc.ini

Considering the datastore’s name is TT_AGGR_STORE, the datastore may be defined as follows:

Step 1: Define the Name of the ODBC data source

Step 2: Define the properties for the timesten data store. 

Some of the key parameters that are dependent on your environment are: 

1. Permsize: It is the total size allocated to the datastore/database. In this case, it is 500GB.
2. Tempsize: It is the temporary space allocated to the datastore. In this case, it is 200GB

-linuxLargePageAlignment 2

[Here, we assume that the prerequisites for Hugepages are already met. The user may check the output of the following command 'cat /etc/sysctl.conf ' to see the number of hugepages set.

Step 4: Restart the timesten daemon and connect to the data store to create the empty data store.

After the above configurations are in place, the user must restart the Timesten daemon for the changes to take effect. The Timesten datastore gets created and loaded into the memory when the user connects to the datastore for the first time, using the ttisql command:

After connecting to the datastore, the user may check the size of the datastore using the dssize command:

The user will be able to verify the output against the sizes specified in the sys.odbc.ini file when defining the datastore.

Creating Users

After the data store has been created, users need to be created and privileges should be assigned. Use the following commands

Create user <username> identified by <password;

Grant create session, create table, create view to <user name>;

After this, the user may perform the DDL and DML operations. Oracle syntax may be used for doing the same:

- Create Tables, Views

- Insert into Tables

- Truncate Tables

- Create Indexes

- Drop Tables, View, Indexes

- Others

Some of my initiatl observations are

1. Truncate in Timesten works in the same way as Delete in Oracle and provides no performance gain. It is better to drop and create tables in some cases where applicable, instead of using Truncate.
2. The user may use the commands tables and indexes to see a listing of all the tables or indexes present in the database.Users also have the option to connect to Oracle from Timesten, and load/cache Oracle tables into Timesten. This topic will be taken up in the forthcoming 

In my next post I will spend some time to run through some of the common scenarios like refreshing timesten tables from Oracle, common system admin functions and integration of timesten with DAC for BI Apps deployments. 

Want to know more about doing analysis at the speed of thought.