9 Replies Latest reply: Aug 21, 2014 3:21 AM by Chrisjenkins-Oracle RSS

    Partitioning Behavior in Timesten

    user10366531

      Hi ,

       

      I have installed timesten Release. TimesTen 11.2.2.7.4 . I have keep passthrough setting to 1.

      I have created AWT cache group . The table on which i have created AWT cache group .Table which is in oracle having Hash Partition .

       

      Query configured from Application :

      Example :

       

      Update table set column1 = 0 ,column2 =1   where column x = ? and column y = ?

       

      If i am pointing my application to oracle then query fired from oracle is

       

      Update table set column1 = 0 ,column2 =1   where column x = ? and column y = ?

      Query fired from Timesten data source

       

      Update table set column1 = 0 ,column2 =1   where column x = ?

       

      Now my question is : Column Y is partition column , is this only reason that timesten is skipping  And clause in update query ("And Column y=  ?" )

      Please guide us in such case. if possible please provide example of how to cache partition table in timesten.

       

      Thanks ,

        • 1. Re: Partitioning Behavior in Timesten
          user10366531

          Can anyone please help on this.

          • 2. Re: Partitioning Behavior in Timesten
            Chrisjenkins-Oracle

            I'm guessing that 'column x' is the primary key column for the table, right? A few things to note:

             

            1.    TimesTen is currently not 'partition aware'. It sees the Oracle table as just a table.

             

            2.   When DML is executed against an AWT table in TimesTen the following happens.

             

                  -   Application issues its DML and commits it.

             

                  -   The commit completes in TimesTen and then the application is free to move on to its next transaction or whatever.

             

                  -   In the background, the TimesTen replication agent captures the transaction from the TimesTen transaction log stream. It has no visibility of the original SQL statement issued by the application. Based on the definition of the AWT cache group, it uses the data from the log to generate a new DML statement for execution on Oracle. It will use the primary key, or the unique index on non-NULL columns, as the way to identify the target row in Oracle.

             

            So, you should not expect that the actual DML statements executed against Oracle as part of AWT change propagation will in any way exactly match the ones the application issued. The effects of the statements will be the same but the number and type of statements may differ substantially. AWT also uses operation batching and this can further affect this.

             

            Hope that clarifies.

             

            Regards,

             

            Chris

            • 3. Re: Partitioning Behavior in Timesten
              user10366531

              Thanks chris for your reply.

               

              Yes column x is primary key only.

               

              Does it mean that if query is  Executed  in oracle , that  uses single partition , then  there may be changes of full table scan at timesten side . Or It will also use single partition scan ?

               

              Thanks.

              • 4. Re: Partitioning Behavior in Timesten
                Chrisjenkins-Oracle

                If you run a query against TimesTen then the query runs in TimesTen. Since TimesTen does not have partitions you may get a full table scan or, hopefully if you have the right indexes, it will be nice fast indexed access. That depends entirely on the query, what indexes you have defined in TimesTen and whether your TimesTen optimiser statistics are up to date.

                 

                Perhaps you can give a specific example to illustrate your question?

                 

                Chris

                • 5. Re: Partitioning Behavior in Timesten
                  user10366531

                  Hi chris,

                   

                  below is the scenario .

                   

                  There is one table "Test" Having Range + hash partition in oracle . (Hash partition is on Primary key column and range partition on date column )

                  In below example you can consider column x having hash partition and column y having range partition .

                   

                  Update Test set column1 = 0 ,column2 =1   where column x = 123 and column y  between sysdate -1 and sysdate

                  Here column x is priamary key

                  and column Y having range partition so only one day partition will scan .


                  Now At timesten side -  AWT cache group is created .


                  Some questions regarding above scenario :


                  1) At Timesten side, if I am creating indexes on column x and  column y column then , does this query give  me best performance ?


                  2 ) I have set passthrough setting to 1. when timesten perform replication with oracle, at that time ,  there might be changes that it will not perform partition scan.

                  Due to this behavior, there might be possible that  query takes too much I/O and if it is the case then , other  applications running at oracle side get slower. how can i control such in above update statement  .


                  3) How can  i monitor what all queries are executed in timesten . oracle side we have EM to monitor all such things. is similar feature is available with  timesten  ?


                  we want your advise that what should we do in such cases .



                  • 6. Re: Partitioning Behavior in Timesten
                    Chrisjenkins-Oracle

                    Firstly, indexes are just as important in TimesTen as in any other database. it is vital that tables, including cache tables, in TimesTen have the correct indexes to support the queries and DML begin executed against them otherwise performance will be sub-optimal and in many cases could even be very poor. Do not think that 'in memory' means 'no need for indexes' as that simply is not true. So yes, for this update statement:

                     

                    Update Test set column1 = 0 ,column2 =1   where column x = 123 and column y  between sysdate -1 and sysdate


                    You at the least need an index on x. Since you have this defined as the primary key in the AWT cache table then that is okay and in fact this update will match at most 1 row. Creating an additional index on column y will not give any benefit (since this DML can only use one index and it will preferentially use the one on x as long as your optimiser statistics are up to date). If there was the possibility that many rows could match 'x = 123' (i.e. x was not unique / a primary key) then you could gain some additional benefit by creating a composite *range* index on (x, y).


                    Passthrough is not relevant for this example. With PassThrough=1, all queries and DML are executed against the cache tables in TimesTen, not against Oracle, unless


                    a)    The query/DML statement references a table that is not present in Timesten


                    OR


                    b)    The query/DML statement generates a syntax error in TimesTen (e.g. it may be using SQL syntax not supported by TimesTen)


                    Only if one or other or both those conditions are true will TimesTen then try and execute the query/DML directly in Oracle.


                    So for your example, the DML will execute in TimesTen and PassThrough=1 is not involved/relevant.


                    With AWT caching, changes made to data in TimesTen execute and commit in TimesTen and then, in the background, the replication agent extracts the changes from the TimesTen transaction log stream, generates 'equivalent' (in terms of their effects) DML and executes that against Oracle. So for this update, assuming that 'c' is also the primary key (or a unique index on a NOT NULL column) in the Oracle DB the DML that would be executed against the table in Oracle would be:


                    update Test set column1 = 0, column2 =1   where column x = 123;


                    I hope that clarifies.


                    Regards,


                    Chris


                    • 7. Re: Partitioning Behavior in Timesten
                      user10366531

                      Thanks Chris , This help us a lot.

                       

                      I have observed in ttmess.log file that log switch occurs frequently. I have defined log file size to 1Gb can you please advise when i should increase its size.

                       

                      Another i have set aging on cache group. if data has been aged out from cache, and i am firing query on those data then does timesten gives an error ?

                       

                      how can i make replication between timesten and oracle much faster. currently lots of log files are there who perform replication too slow and due to that my OS file system is full . can you advise what should we do in this case.

                      • 8. Re: Partitioning Behavior in Timesten
                        user10366531

                        Hi chris,


                        I need some more details on below two points


                        2 ) I have set passthrough setting to 1. when timesten perform replication with oracle, at that time ,  there might be changes that it will not perform partition scan.

                        Due to this behavior, there might be possible that  query takes too much I/O and if it is the case then , other  applications running at oracle side get slower. how can i control such in above update statement  .


                        I would like to know that if update query is fired on timesten with primary key then , if I am not passing partiition at oracle side, does  oracle server  gets slower . there may b chances that other applications which are pointing to oracle that can b impacted .

                        3) How can  i monitor what all queries are executed in timesten . oracle side we have EM to monitor all such things. is similar feature is available with  timesten  ?


                        • 9. Re: Partitioning Behavior in Timesten
                          Chrisjenkins-Oracle

                          Lot's of questions :-) Here are some responses:

                           

                          1.   I have observed in ttmess.log file that log switch occurs frequently. I have defined log file size to 1Gb can you please advise when i should increase its size.

                           

                          Do you mean that the message log is switching a lot, or the TimesTen database transaction log file? Which 'size' did you increase? I am assuming that you are referring to the transaction logs. Frequent log switches are not too much of a problem; the frequency will depend on the defined log size (it seems that is now 1 GB) and the workload. Note that checkpointing can only purge entire log files so making them too large can cause increase log spec consumption between checkpoints.

                           

                          2.  Another i have set aging on cache group. if data has been aged out from cache, and i am firing query on those data then does timesten gives an error ?

                           

                          Is this a 'DYNAMIC' AWT cache group or a regular AWT cache group? If it is a regular cache groups and the rows are no longer present due to aging then you will get 'no rows found' or a partial result depending on the query. If it is dynamic then you may get the same behaviour or, depending on the specific query, it may reload the rows back into the cache from Oracle.

                           

                          3.  How can i make replication between timesten and oracle much faster. currently lots of log files are there who perform replication too slow and due to that my OS file system is full . can you advise what should we do in this case.

                           

                          Have you enabled parallel propagation for AWT (CacheAWTParallelism in DSN settings)? If not then you should try that. You might also want to play with CacheAWTMethod too. But at the end of the day the rate of propagation from TImesTen to Oracle is limited by the rate at which Oracle can ingest the changes. If you run 100,000 TPS in TimesTen but Oracle is only capable (due to configuration and/or hardware capacity) of ingesting 50,000 TPS then you will have an ever increasing backlog. That's a basic laws of physics issue.

                           

                          4.   I have set passthrough setting to 1. when timesten perform replication with oracle, at that time ,  there might be changes that it will not perform partition scan.

                          Due to this behavior, there might be possible that  query takes too much I/O and if it is the case then , other  applications running at oracle side get slower. how can i control such in above update statement.


                          I already said that PassThrough is not relevant here. It has no effect and you do not need to set it. There is no way to control what INSERT/UPDATE/DELETE statements Timesten executes against Oracle. They are generated automatically by TimesTen based on the table definition in Oracle and the changes picked up from the TimesTen transaction logs. If you want the column 'y' in the above example to be included in the generated update then you need to make it part of the primary key in Oracle.


                          5.  I would like to know that if update query is fired on timesten with primary key then , if I am not passing partiition at oracle side, does  oracle server  gets slower . there may b chances that other applications which are pointing to oracle that can b impacted.


                          I suppose it might but you'd have to perform actual performance measurements to see if this is really the case.


                          6.   How can  i monitor what all queries are executed in timesten . oracle side we have EM to monitor all such things. is similar feature is available with  timesten?


                          Currently you cannot monitor what SQL is currently in execution in TimesTen. You can however view the contents of the prepared statement cache (recently excited SQL) using the various ttSQLCmdCache...() builtin procedures. Note that this will *not* show the queries/DML that the replication agent uses to apply changes to Oracle (since those are being executed in Oracle not in TimesTen).


                          Chris