Forum Stats

  • 3,728,122 Users
  • 2,245,554 Discussions
  • 7,853,338 Comments

Discussions

Variance in query execution time - TimesTen IMDB Version 11.2.2.18.5

999378
999378 Member Posts: 2

Hi

We are using TimesTen IMDB Version 11.2.2.18.5. For the past few months, we see a variance in the query execution time. If the same SQL  is executed with same input data for 10 times, 8 times it executes within 2 milliseconds but 2 times it takes up to 5 milliseconds - 40 milliseconds. The application get a connection from the pool and executes a couple of sqls and stored procedures and close the connection. Either all the sqls in the the same connection are executed within the ideal time or all of them takes longer time to execute.

The execution time is captured from the application server , not from the TimesTen server. We are not sure if there is a network latency. Does TimesTen provide any kind of logs which give statistics on the query send by the application and the execution time.  Any pointers to debug this issue  is appreciated.

Thanks

Answers

  • ChrisJenkins-Oracle
    ChrisJenkins-Oracle Member Posts: 3,402 Employee
    edited July 2020

    Hi,

    That is not a valid version; did you mean TimesTen 11.2.2.8.5?

    There are a huge number of possible reasons why you may see variations in query times, even for identical queries against identical data. Computer systems are highly dynamic environments with a great many moving parts many of which influence one another. Once you add a network into the mix things get even more variable.  Computer systems in general are not deterministic and one has to work in terms of statistical distributions, percentiles and averages rather than in terms of absolutes.

    Having said that, this variation is quite large so clearly something is disrupting things. Sadly there are no logs maintained by TimesTen with the information you seek (maintaining those logs would significantly impact the performance of TimesTen). There is information regarding query execution time maintained in the SQL command cache and if you have enabled additional statistics then you can get things like minimum, maximum and average times for queries. You could also look at the ttStats utility which can capture additional metrics during a defined time period.

    From personal experience, tracking down the reason for this kind of latency spike can be a long and arduous process and you will almost certainly need to look at the entire system, not just TimesTen, as the cause could lie almost anywhere.

    Chris

Sign In or Register to comment.