Hi Chris,sorry for call you directly. Because you give me many good answers about my many newbile questions these days:)
You told me that the dynamic cache group is not suitable for the intensive insertion operation
because each INSERT to a child table has to perform an existence check against Oracle even if load the cache group into RAM manually(Please correct me if wrong).
Here I have many log tables that they only have a primary key and no foreign references and they are basically used to reflect changes from the related main tables.
Every insert/update/delete on the main table will insert a log record in the related logging table(No direct foreign references).
In order to cache these log tables, I have to create a independent cache group for each one, right?
I do not want load these logs data into RAM because my application do not use them or these logs will waste my RAM clearly.
so here comes my question.Which kind of cache group should I use to gain the best performance with no loading them into RAM?
As my understand,the dynamic cache group load data on demand while the regular cache group need load all the data into RAM firstly and it won't load data from oracle anymore?
Let me be more specific. Consider this cache group:
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP CG_SWT
PPK NUMBER(8,0) NOT NULL PRIMARY KEY,
CPK NUMBER(6,0) NOT NULL PRIMARY KEY,
CFK NUMBER(8,0) NOT NULL,
FOREIGN KEY ( CFK ) REFERENCES TPARENT ( PPK )
INSERTS into TPARENT will not do any existence check in Oracle. An INSERT INTO TCHILD has to verify that the corresponding parent row exists. If the parent row exists in TimesTen then no check is doen in Oracle. If the parent row does not exist in TimesTen then we have to check if it exists in Oracle and if it does we will load it into TimesTen from Oracle (along with any other child rows) before completing the INSERT in TimesTen. So in the case where the parent always exists already in TimesTen there is no overhead but on the other case there is a lot of overhead.
If your log table is truly not related to the main table (not in TT and not in Oracle either) then they should go into separate cache groups. If each insert into the log table has a unique key and there is no possibility of duplicates then you do not need to load anything into RAM. You can start with an empty table and just insert into it (since each insert is unique). Of course, if you just keep inserting you will eventually fuill up the memory in TimesTen. So, you need a mechanism to 'purge' no longer needed rows from TimesTen (they will still exist in Oracle of course). There are really two options; investigate TimesTen auotmatic aging (see documentation) - thsi may be adeuate of the insert rate is not too high - or implement a custom purge mechanism using UNLOAD CACHE GROUP (see documentation).
Cache auotmatic aging will be a good choice if no more impact on performance.
I'm not sure if application is inserting logs when I issue UNLOAD CACHE GROUP what will happen?
Dead lock or a long block will impact on performance too.
The idea is to unload small batches of records (UNLOAD CACHE GROUP can take a WHERE clause) which are the oldest and so are not close (in key range) to the ones currently being inserted so there should be minimal impact. Similarly with aging, the idea would be to use LIFETIME based aging so the oldest records get aged out thsi avoiding any conflict with the new ones being inserted. Ultimately you would have to try both approaches and see if either works well for your scenario.
Dynamic AWT cache group provides you an opportunity to load some rows from Oracle DB to TimesTen by using SQL, transparently for application. It means that if you run some SQL query and it satisfied some conditions (return no rows from TimesTen and others), TimesTen create a connection to Oracle DB, run this SQL query and load rows into TimesTen. Regular AWT cache group doesn't have this functionality.
Aging feature helps you to control the memory amount and delete some rows from cache. You can specify the aging type. There are two methods - based on timestemp value and based on LRU algorithm.
Chris, I mean why choice of the dynamic AWT cache group for those log tables.
Those log tables are only used to reflect changes of their logical main tables
and for my application I never read them but only write them always.
So they do not need to be cached in the RAM in TimesTen so I donot need dynamic loading on demand.
Using the regular AWT cache groups with a appropriate aging policy seems appropriate in this case.
I only want to make sure for this.