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