TimesTen

Following a visit from an Oracle consultant yesterday, I have successfully installed and got working the Windows version of Timesten. I did some speed tests which, given the hardware I was running on, were a bit inconclusive. I also got a couple of 'Timetens Oracle database Cache groups' working. One in 'readonly autorefresh' mode and the other in 'asynchronous writethrough' mode.

My objectives in installing were to :-
1) To familiarize myself with the product.
3) Learn about the set up and install of the product.
2) Run some tests to demonstrate how much quicker queries, updates ,insert and deletes were in Timesten is when compared to a standard Oracle database
3) Investigate the processes involved in setting up and running Timesten as an Oracle database Cache.

I chose the Windows version of Timeten to save the hassle of finding a suitable machine to run it on. My PC has plenty of memory (2Gb) and is reasonably quick.

I downloaded and installed the Windows version of Timetens from the Oracle website
http://www.oracle.com/technology/software/products/timesten/index.html


After a straight forward installation I was was left with a Timeten menu group and a number of new ODBC entries.

Of the newly created menu entries, ttisql proved to be the most useful, that said it is easier to run it from the DOS command prompt since you can specify which ODBC source you want to use.
e.g. ttisql wiscdata_tt70_32

This will log you straight in using your windows username as the Timeten user.
Typing 'help' gets you a long list of commands including 'tables' which show which tables are available to you. The interface is reminiscent of MySql, so up arrow gets you the previous line of the previous command etc. Pretty much all the SQL syntax is Oracle SQL (DECODE is even there). I created a large 1 million row table in Timesten and a similar table in Oracle 10g. It was at this point I hit a problem which meant I had to increase the value of the PERMSIZE parameter. It was easy enough, I just went to the ODBC source and upped the value of PERMSIZE by hitting the configure button and changing the value of PERMSIZE from 16 to 100. I ran various queries against both databases; using TTisql to access TimesTen and SQlplus to get at 10g.... the result timings were...well...similar. Not a very scientific test, I'll admit, especially since one was running on a local PC and the other on a remote Server. Where I did notice a difference was when I used Excel to pull data out of both databases using ODBC. Again not very scientific, remote server Vs local PC etc.... but there was a significant difference between the two, Timesten was much faster.


I then turned my attention to setting up TimesTen as a Oracle database cache.

Useful documents are
http://download-uk.oracle.com/otn_hosted_doc/timesten/603/TimesTen-Documentation/operations.pdf
and
http://download.oracle.com/otn_hosted_doc/timesten/703/TimesTen-Documentation/cacheconnect.pdf

Once you get it all working you can configure most things from
http://localhost:17004/cache/
but before you get there, there are a number of other steps.

The first thing you need to do is create a new ODBC source. The TimesTen database HAS to have the same character set as the Oracle database.

Set it as per the cacheconnect.pdf document. I had some trouble getting logged in via
http://localhost:17004/cache but once I had create the Timesten user. Granted it admin and ddl priviledges (which I had to do using ttisql when logged in as my Windows user) and got the character sets right, I got logged in

I then created two Cache groups, one was readonly and the other asynchronous. The readonly autorefresh group only started working after I did the extra step in TTiSql of issuing the command
LOAD CACHE GROUP bm.testgroup COMMIT EVERY 256 ROWS;

The asynchronous group started working once i'd start the 'RepAgent' using
ttadmin -repstart cgdsn

P.S. You have to stop both the repagent and the cache inorder to create a new cahe group
ttadmin -repstop cgdsn
ttadmin -cachestop cgdsn

create the new group

ttadmin -cachestart cgdsn
ttadmin -repstart cgdsn


Conclusion

Install and set up all looks pretty straight forward, on Windows at least. The relevant documentation ain't bad either, once you have found it. To progress further, we really need to get PHP talking to Timeten either via the ODBC or directly via the C++ libraries.

Database Server info

SELECT
user
,sys_context('USERENV', 'DB_UNIQUE_NAME') DB_UNIQUE_NAME
,sys_context('USERENV', 'HOST') HOST
,sys_context('USERENV', 'INSTANCE') INTSANCE
,sys_context('USERENV', 'SERVER_HOST') SERVER_HOST
,sys_context('USERENV', 'SID') SID
FROM dual

or
select name from v$database;

Date Format

For years in SQLPlus I've TO_CHAR'ed dates to get them in the format I wanted.
This is quicker in most situations :-
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Database Links

There has been a long running debate at work about how to 'join databases together'. We have a number of different database each for their own application. However they share certain information e.g. user account data. Currently the databases are 'joined' by the PHP application software. I have long argued for using db links as a way to 'join' the data together, arguing that the CBO will do it in a far more efficient manner than any application ever could. A quick search on the asktom website came up with the answer. The best link turned out to be
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14688146259705

My summary of it's conclusions goes as follows:-
1) should I use the application to 'join' different database together?
Answer: definitely not , no way, nicht, never.

2) Should I use db links as part of my real time application code?
Answer: not unless you can absolutely avoid it, by all mean use DB links to copy data via materialized views or even in pl/sql that copies data from one db to another but anything that relies on both databases being up at the same time as part of a real time application should be avoided.

3) Should I use one of Oracle replication technologies to copy and sync data between different databases, so that real time applications are depend on one and only one database?
Answer: yes, were possible

4) Should I strive to keep everything on one database and use technologies like RAC to provide both processing power and resilience?
Answer: yes , yes yes.