6 Replies Latest reply: Feb 1, 2013 5:24 AM by Chrisjenkins-Oracle RSS

    Problems using Oracle TimesTen for Star Schema Queries

    988082
      Hi,

      I’m currently testing TimesTen with a star schema, for which we are normally using Oracle 11g. We are interested in the performance gains for our concrete schema and data that can be expected, when switching to TimesTen.
      For my performance tests I copied the schema including all views and indices and the data to TimesTen and updated the statistics for all tables. I am using the following TimesTen version:
      TimesTen Release 11.2.2.4.1 (64 bit NT) (tt1122_64:53396) 2012-10-30T06:32:14Z
      I ran into a problem and I hope you can help me.
      While the performance for small dimension queries is around twice as good and also the loading process (write performance) is even 6 times better than on Oracle 11g database, more complex queries for several dimension tables and one fact table (200.000 rows) are much slower using TimesTen than with Oracle 11g.
      Has anyone experienced similar results in this context or do you have an explanation for the results? Is Oracle TimesTen a possible environment for large star schema joins?

      I discovered two things when having a closer look at this problem.

      While the more complex query didn’t even finish after 20 minutes, I found out, that after executing the query as a prepared statement (using ttisql prepare and exec functions), the queries take around 30-90 seconds. Interestingly, the query can also be executed normally within that time after executing it as a prepared statement only once. Even though 30-90 seconds is a big improvement compared to 20 minutes, it is still way worse than the 7 seconds, Oracle 11g needs for the same query.

      When using direct mode, the application accessing the database (e.g. ttisql or sqlDeveloper) starts consuming huge amounts of RAM (around 6GB).
      Using client/server mode, the server process ttpcserver1122.exe consumes a similar amount of RAM.
      I find this odd, since I thought TimesTen uses its Temporary space for calculating query results. The final query result should be much smaller than that. It seems to me, that the application that queries data is used to prepare the result / stores intermediate results.

      Do you have any idea how to explain and maybe solve these problems?
        • 1. Re: Problems using Oracle TimesTen for Star Schema Queries
          Chrisjenkins-Oracle
          I would recommend the following:

          1. Drop all indexes carried over from Oracle.

          2. Update optimiser statistics for all tables (see statsupdate command in ttIsql)

          3. Run your query using the TimesTen Index advisor, create the recommended indexes, update optimiser stats again. Test performance.

          4. Rinse and repeat step 3 until no new indexes are recommended.

          Other things to note...

          A TimesTen database is a shared memory segment, In direct mode the application process 'maps' this segment into its address space. In client/server mode the server process (ttcserver) does the ame. So, this is what you are seeing; the database memory being 'accounted' as part of the application. This is normal, correct and expected and does not mean that the application / ttcserver is consuming lots of (extra) memory.

          Can you please advise also the following:

          1. How much RAM is installed in the machine?

          2. What are the settings for PermSize, TempSize and LogBufMB in the DSN?

          3. Are there any other memory intensive processes running on the machine?

          Thanks,

          Chris
          • 2. Re: Problems using Oracle TimesTen for Star Schema Queries
            Tim Vincent
            Also a recommendation would be to change the RamPolicy to manual and load the TT shared memory segment into memory manually.

            ttAdmin -RamPolicy manual MyDSN
            ttAdmin -RamLoad MyDSN

            Then run your tests so the connection (direct or Client/Server) is not taking the hit of loading TT into memory.

            Tim
            • 3. Re: Problems using Oracle TimesTen for Star Schema Queries
              988082
              First of all, thanks for your quick replies!

              @Chris:
              I followed your instructions. Unfortunately, the performance did not increase significantly. TimesTen still takes about 30 seconds for a query that can be answered by Oracle 11g within 7 seconds (on the same hardware).

              Do you have any other ideas?

              Concerning your questions:
              1. How much RAM is installed in the machine?
              16GB

              2. What are the settings for PermSize, TempSize and LogBufMB in the DSN?
              PermSize: 6144
              TempSize: 4096
              LogBufMB: 64
              By the way, executing dssize in ttisql leads to the following results:
              PERM_ALLOCATED_SIZE: 6291456
              PERM_IN_USE_SIZE: 4401952
              PERM_IN_USE_HIGH_WATER: 4401956
              TEMP_ALLOCATED_SIZE: 4194304
              TEMP_IN_USE_SIZE: 11868
              TEMP_IN_USE_HIGH_WATER: 3658567

              3. Are there any other memory intensive processes running on the machine?
              No

              @Tim:
              Thanks for the idea, but I was already using manual RamPolicy.

              Thies
              • 4. Re: Problems using Oracle TimesTen for Star Schema Queries
                Chrisjenkins-Oracle
                Would you be willing to discuss this in more detail off list? If so, please provide some contact details (e-mail address).

                Chris
                • 5. Re: Problems using Oracle TimesTen for Star Schema Queries
                  988551
                  Sure. Thanks for that offer!
                  Do you have access to my e-mail address specified in my user account details or how else can I contact you?

                  Thies
                  • 6. Re: Problems using Oracle TimesTen for Star Schema Queries
                    Chrisjenkins-Oracle
                    Unfortunately I don't have access to your e-mail address. You can e-mail me at chris dot jenkins at oracle dot com.

                    Chris