5 Replies Latest reply on May 15, 2013 7:54 AM by Chrisjenkins-Oracle

    Suggestions required for the following set up of oracle timesten IMDB cache


      There are 10 tables with relationships which will be of 200GB , has to be cached from oracle RAC into the timesten through IMDB cache . For this setup upon oracle RAC
      there is timesten active-standby setup. Applictions will be accessing the timesten with read and write , in parallel to this there will be loading process into oralce db on the same tables.
      The tps of the application for read will be of 2000 and write will be of 1000

      Need suggestions on the following points
      1. What type of cache groups to be used
      2. Checkpoints files/log files of timesten should be on ASM or raw disk
      3. How much time will it take to load the cache group of 200GB size , which will be defined as first point
        • 1. Re: Suggestions required for the following set up of oracle timesten IMDB cache
          If you wish to update these cached tables directly in TimesTen and you also wish to have HA for the TimesTen caches using TimesTen active/standby pair replication then you have to use Asynchronous Writethrough (AWT) cache groups. Note that AWT cache group are uni-directional; changes made in TimesTen will be automatically propagated to Oracle but not vice versa. It is possibel to manually initiate the loading (caching) of new data from oracle to TimesTen using the LOAD CACHE GROUP SQL statement. Also note that there is no conflict detection/resolution mechanism for cache propagation. if the same rows are modified in both TimesTen and Oracle then data inconsistency will very likely result. ANy rows that are cached in an AWT cache group in TimesTen must only be modified via TimesTen.

          TimesTen does not support ASM or raw disk storage for checkpoint and log files. They have to go on regular filesystem storage; this can be local disk storage (recommended) or SAN storage but not NFS storage.

          It is impossible to say how long it will take to do the initial load of the 200 GB cache groups since there are many variables that will affect it. The initial load will be done via LOAD CACHE GROUP and this supports parallel processing so if the TimesTen machine has multiple CPU cores you can exploit those to reduce the time needed. The only way to get an accurate estimation of the time needed for the load is to load a reasonable volume of data (a few GB minimum) using the target hardware setup and backend Oracle DB and then you can extrapolate to the full volume.

          1 person found this helpful
          • 2. Re: Suggestions required for the following set up of oracle timesten IMDB cache
            Thanks chris

            You have recommended for local storage , but is that documented ,if so please share the link.

            I want to know the average time that will take to load the data into cache.
            • 3. Re: Suggestions required for the following set up of oracle timesten IMDB cache
              It's just a recommendation :-) SAN storage is okay as long as the performance is adequate. The restriction on NFS storage is documented.

              As I said, there is no way to give any 'average time' as there are too many variables. Test it yourself on your setup to see. It's a bit like asking how long it takes to get from San Francisco to New York but not specifying any of the parameters. It could take 5 hours (commercial airliner), 2 hours (supersonic fighter), 4 days (driving non-stop) or 80 days (walking). None of those answers are helpful without knowing all the details and the 'average' (251 hours) is also totally unhelpful. The key factors are (a) how quickly Oracle DB can deliver the data (SELECT * FROM TABLE) (b) how quickly the data can be transferred over the network to the TimesTen machine and (c) how quickly the data can be inserted into TimesTen. Unless the TimesTen machine is of a very low spec or very poorly configured then (a) or (b) are typically the limiting factors.

              1 person found this helpful
              • 4. Re: Suggestions required for the following set up of oracle timesten IMDB cache

                Some more ...

                I have three tables in IMDB Cache group ,in the same schema of timesten, can i create some other tables and access the cache group of timesten?

                Can a sequence be called in ttbulkcp? As the file is not having any unique column value , so want to create a sequence at DB and call it ttbulkcp

                For the following approach can you suggest the best or recommended method for loading the data and process in timesten.
                1. Load the data into a staging table of 1 million records or more
                2. call stored procedure
                3. When the procedure is called it will sequentially call the records and process and make appropriate DML activity to populate data to other tables
                4. truncate the staging table

                This process will be more than 5 times every day
                Which will be suitableTimesten or IMDB Cache to create staging table?

                Will there be any performance issue , as we are truncating the huge table ? Internally how timesten will be truncating , Can you refer the link

                In Timesten the Approximate size of control files will be the size of data occupied in RAM, will it be the same if IMDB Cache is used ?
                Will the control files of timesten be occupying the same size of RAM assuming DB size is 300 GB if IMDB Cache is used ?

                Can the Timesten be installed on same machine where oracle is installed?
                Will the Timesten support the oracle ASM ?

                Siva Kumar

                Edited by: 998700 on May 14, 2013 12:08 AM

                Edited by: 998700 on May 14, 2013 7:32 PM
                • 5. Re: Suggestions required for the following set up of oracle timesten IMDB cache

                  Firstly, lets be very clear. TimesTen IMDB and IMDB Cache are exactly the same thing. The only difference is the way they are licensed and the functionality that the different licenses permits you to use. If you just want a standalone or HA in-memory database then you can license TimesTen IMDB. If you want to use any of the cache features then you have to license IMDB Cache which is licensed as an Oracle DB EE option rather than as a separate product, IMDB Cache includes all the features of TimesTen IMDB. Regardless of which one you license the product works in exactly the same way.

                  If you use IMDB Cache then a single TimesTen database can contain both cache tables and non-cache tables so yes you can mix and match different table types in the same TimesTen database.

                  Sadly it is not possible to reference a sequence as part of a ttBulkCp load/unload. ttBulkCp simply copies a table to a flat file on disk or vice versa. Of cousre, you coudl quiet easily write your own C, C++ or Java program to do what you want. It really isn't very difficult.

                  The processing you propose is basically okay. If you do not need to synchronise any of this data with Oracle DB then you can use TimesTen IMDB. If you do need to sync the data with oracle then you can use IMDB Cache or you can use TimesTen IMDB plus some custom code whichever best fits your needs.

                  In TimesTen, truncating a table is really an internally optimised form of DELETE FROM table; It can be very fast but if the table has many out of line VARCHAR2/VARBINARY columns then it can be much slower.

                  As I previously stated, there is nothing to stop you installing TimesTen/IMDB Cache on the same machine as Oracle DB but in general thsi tends to be a less than optimal setup unless the machien truly has enough spare CPU, RAM and I/O capacity to handle the combined workload.

                  The files you are referring to are not 'control files'; they are the TimesTen persistence mechanism that protects your data and makes it recoverable and transactional. If your TimesTen database (PermSize) is x GB then you will need at least 2x + 0.3x GB for the database files. You might get away with a bit less or you might need significantly more depending on various factors such as the volume and frequency of write activity (INSERT/UPDATE/DELETE) in the TimesTen database.

                  As I stated before, TimesTen does not support ASM storage.

                  1 person found this helpful