1

we have a few tables storing temporal data that have natural a primary key consisting of 3 columns. Example: maximum temperature for this day. This is the Composite Primary key index (in this order):

id number(10): the id of the timeserie.
day date: the day for which this data was reported
kill_at timestamp: the last timestamp before this data was deleted or updated.

Simplified logic: When we make a forecast at 10:00am, then the last entry found for this id/day combination has his create_at changed to 9:59am and the newly calculated value is stored with a kill_at timestamp of '31.12.2999'.

typical queries on this table are: 

1) where id=? and day=? and kill_at=?
2) where id=? and day between (? and ?) and kill_at=?
3) where id=? and day between (? and ?)
4) where id=?

There are plenty of timeseries that we do not forecast. That means we get one valued when it's measured and it never changes. But there are some timeseries that we forecast 200-300 times. So for one id/day combination there are 200+ entries with different values for kill_at.

We currently only have the primary key (id, day, kill_at) as the only (unique) index on this table. But when I query with query 2 (exact id and day range), then the optimizer decides to only use the first column of the index.

ID  OPERATION         OPTIONS          OBJECT_NAME  OPTIMIZER  SEARCH_COLUMNS
 0  SELECT STATEMENT                                ALL_ROWS   0
 1  FILTER                                                     0
 2  TABLE ACCESS      BY INDEX ROWID   DPD                     0
 3  INDEX             RANGE SCAN       DPD_PK                  1

This really hurts us for those timeseries that have been updates 200+ times. Now I was looking for a way to force the optimizer to use all 3 columns of our index, but I can't find a hint for that. Is there one?

Or are there any other suggestions on how to speed up my query? We try to reduce the peak durations. The average Durations are of lesser concern.

what confuses me: The above execution plan is what I see in dba_hist_sql_plan. It is the only execution plan for this statement. But when I let my client show the explain plan, then it is sometimes a 1 or a 3 for search_columns. But it never is 3 for when our application runs this Statement.

EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
  • Can you compare the execution plans using `explain plan for select ...` and then `select * from table(dbms_xplan.display(format => '+outline'));`. The `+outline` will give you a set of optimizer hints that Oracle uses to create that exact plan. The output is usually confusing and full of undocumented hints, but it might give you a clue about what's causing the difference. – Jon Heller Oct 28 '16 at 03:45

1 Answers1

1

we actually found the cause of this problem. We're using JPA/JDBC and the JDBC date types weren't modeled correctly. While the oracle date type is with second precision, somebody (I now hate him) made the "day" attribute in our entity of type java.sql.Timestamp (although it is only day without time). The effect is that Oracle will need to cast (use a function on) each entry in the table to make it a Timestamp before it can compare with the Timestamp query parameter. That way the index cannot be used properly.

EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
  • Hint: you should not be using the same answer all over the place; rather write up your answer once and have other questions be closed as dups for example. – GhostCat Jan 21 '17 at 13:35
  • Those 2 questions I answered are very different. They just have the same root cause. I don't understand why 2 Problems can't both be fixed by the same solution. Why can't 2 different questions be answered with the same answers but still stay different and without any relation? If I deleted one of those questions, then we'd lose the Information.... (not good) – EasterBunnyBugSmasher Jan 23 '17 at 11:21
  • Pasting the exact same answer is simply not something that is seen as good practice. The normal way of handling things is to close as duplicate. Doesn't really matter for two occurrences, but be assured: when somebody puts the same thing down multiple times, somebody will notice; leading to all kinds of churn. – GhostCat Jan 23 '17 at 12:04
  • if one of the questions was a duplicate of the other, then I'd agree. But that is not the case. So what do I do now? How can I answer the other question. I tried giving a new answer and directing to this question, but apparently I'm not allowed to answer a question twice. This is a rather disappointing Situation because there is an answer to the other question, but I'm not allowed to post it. – EasterBunnyBugSmasher Jan 23 '17 at 12:32
  • now I was able to give a new answer to the other question. I hope it is "different enough" now. – EasterBunnyBugSmasher Jan 23 '17 at 12:37