4 Replies Latest reply: Sep 19, 2013 12:03 PM by Chrisjenkins-Oracle RSS

    Timesten running slower than Oracle RDBMS

    Shukla Ji

      Hi,

      I've installed timesten, & just wanted to compare the performance of following pl/sql block on timesten with same block on Oracle.

       

      declare

      temp_date date;

      temp_date1 date;

      my_id number;

      my_data varchar2(200);

      cursor c1 is select MASTER_ID, MDATA

      from AKS_TAB_MASTER;

      cursor c2(p_id number) is select detail_ID, dDATA from

      AKS_TAB_DETAIL

      where master_id=p_id;

      begin

      for t in c1 loop

      open c2(t.master_id);

      fetch c2 into my_id,my_data;

      insert into aks_temp values(t.master_id,my_id,t.MDATA,my_data);

      close c2;

      end loop;

      end;

       

      I've created a cache group in Timesten to cache the tables AKS_TAB_MASTER & AKS_TAB_DETAIL

      I've created table AKS_TAB_DETAIL in Oracle and timesten separately to avoid pass-through

       

       

      Some how, TimesTen is taking 4 times more time than Oracle.

       

      I've gone through link TimesTen Database Performance Tuning and my database parameters as as follows:

       

      Permanent Data Size 640

      Temporary DAta Size 300

      Replicate Parallelism Buffer MB 480

      Log File Size(MB) NULL

      Log Buffer Size (MB) 320

      Cach AWT Method 1-PLSQL

      CAche AWT Parallelism NULL

      PL/SQL Connection Memory Limit (MB) 320

      PL/SQL Optimisation Level 2

      Pl/SQL Memory Size(MB) 240

      PL/SQL Timeout(seconds) 600

       

      Still I'm getting poor performance from TimesTen.

      Any Idea, what might be wrong on my instance.

      Please suggest.

       

      Thanks

      Amit

        • 1. Re: Timesten running slower than Oracle RDBMS
          Chrisjenkins-Oracle

          Can you please provide more information so we can figure out what is occurring.

           

          1.   Output of the ttVersion command (so we know the TimesTen version and platform).

           

          2.    The full set of DSN attributes for this DSN from sys.odbc.ini

           

          3.    The full definitions of the cache group(s) including indexes.

           

          4.    The definition (in TimesTen) of the table aks_temp including indexes.

           

          5.    The row counts (in TimesTen) for the tables AKS_TAB_MASTER & AKS_TAB_DETAIL.

           

          Thanks,

           

          Chris

          • 2. Re: Timesten running slower than Oracle RDBMS
            Shukla Ji


            Hello Chris, Please find the details here:

             

            1.   Output of the ttVersion command (so we know the TimesTen version and platform).
            C:\TimesTen\tt1122_64\bin>ttVersion
            TimesTen Release 11.2.2.5.0 (64 bit NT) (tt1122_64:53396) 2013-05-23T16:26:12Z
              Instance admin: shuklaam
              Instance home directory: C:\TimesTen\TT1122~1\
              Group owner: ES\Domain Users
              Daemon home directory: C:\TimesTen\TT1122~1\srv\info
              PL/SQL enabled.


            2.    The full set of DSN attributes for this DSN from sys.odbc.ini
            Please see the screen shots from page 1 to 5 in the doc available at
            https://docs.google.com/file/d/0BxQyEfoOqCkDX05JNVdqOWItSEE/edit?usp=sharing

            Please let me know if you are looking for something else.

            3.    The full definitions of the cache group(s) including indexes.:

            I've created Two CACHE GROUPS as follows:

            a. AKS_DT_CG:
            -------------
            -- Database is in Oracle type mode
            create readonly cache group MTAX.AKS_DT_CG
                autorefresh
                    mode incremental
                    interval 300000 milliseconds
                    /* state on */
            from
                MTAX.AKS_TAB_DETAIL (
                        DETAIL_ID NUMBER(38) NOT NULL,
                        MASTER_ID NUMBER(38),
                        DDATA     VARCHAR2(135 BYTE) NOT INLINE,
                    primary key (DETAIL_ID));

            b. AKS_MT_CG:
            -------------
            -- Database is in Oracle type mode
            create readonly cache group MTAX.AKS_MT_CG
                autorefresh
                    mode incremental
                    interval 300000 milliseconds
                    /* state on */
            from
                MTAX.AKS_TAB_MASTER (
                        MASTER_ID NUMBER(38) NOT NULL,
                        MDATA     VARCHAR2(128 BYTE) INLINE,
                        STATUS    VARCHAR2(7 BYTE) INLINE,
                    primary key (MASTER_ID));

            To View trhe indexes, please see the screen shot on page 6 in the doc available at
            https://docs.google.com/file/d/0BxQyEfoOqCkDX05JNVdqOWItSEE/edit?usp=sharing

            4.    The definition (in TimesTen) of the table aks_temp including indexes.
            -- Database is in Oracle type mode
            create table MTAX.AKS_TEMP (
                    MID   NUMBER,
                    DID   NUMBER,
                    MDATA VARCHAR2(200 BYTE) NOT INLINE,
                    DDATA VARCHAR2(200 BYTE) NOT INLINE);

            There is not index on this table.

            5.    The row counts (in TimesTen) for the tables AKS_TAB_MASTER & AKS_TAB_DETAIL.

            Command> select count(*) from AKS_TAB_MASTER;
            < 81183 >
            1 row found.
            Command> select count(*) from AKS_TAB_DETAIL;
            < 175176 >
            1 row found.
            Command>

             

            Please let me know if you need any othe info to debug it.

             

            Many Thanks

            Amit

            • 3. Re: Timesten running slower than Oracle RDBMS
              Shukla Ji

              Chris,

              I just experimented a little more on this and now my pl/sql block is running like a rocket.

              Earlier, I had two tables AKS_TAB_MASTER, & AKS_TAB_DETAIL, but there was no foreign key between these two. I had created two different cache groups in times ten for each Oracle table. It was taking ~500 sec to complete.

               

               

              Later on, I created a foreign key in AKS_TAB_DETAIL. Dropped both cache groups in TT, & created one single CG with both tables in master detail mode. Now, it is taking just two seconds.

               

               

              This is really encouraging.

               

               

              Now, I'm looking for a guideline kind of thing that can explain which kind of code/design will run faster in TimesTen.

               

              Thanks

              Amit

              • 4. Re: Timesten running slower than Oracle RDBMS
                Chrisjenkins-Oracle

                I was just looking at the info you posted and I was about to point out the missing foreign key. In TimesTen defining a primary or foreign key results in an index being created. You would have seen the same improvement if you had just created an index on MTAX.AKS_TAB_DETAIL(MASTER_ID). Without that index every execution of the query for cursor c2 was doing a full table scan of the detaik table (which is much slower of course than indexed access).

                 

                Although TimesTen is an in-memory database you still need to apply the usual database optimisation techniques of which correct indexing is a very important one.

                 

                Chris