2

I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two seperate queries each for one day.

 SELECT COUNT(*)  FROM ORDER_HISTORY
 WHERE organization = 'BA' AND  TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD') 
 AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+2;

I've an index on the column timestamps and another index on column organization

enter image description here

Here is the schema of my table. the column timestamp is of type DATE.

enter image description here

The execution plan of query over 2 days uses the index on organization:

enter image description here

The execution plan of query over 1 day uses the index on timestamp:

enter image description here

To have some stats:

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE ORGANIZATION = 'BA' ;

gives 2359847.

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE TIMESTAMP > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
   AND TIMESTAMP<= TO_DATE('2016-01-05', 'YYYY-MM-DD')+1;

gives 9260. and same query over 2 days gives 16510.

Why could I get the kind of strange behaviour of the oracle DB engine?

  • Have your tables been recently analyzed? – Gerard H. Pille Feb 15 '18 at 17:05
1

The reason is that the cost-based optimizer has a plan in memory from a previous select, where the number of records for a given organization, namely 'LOL', was much smaller, some 14000. When the plan is in memory, the CBO doesn't care about the parameter values anymore. Use:

WHERE organization || '' = 'BA'
  • @Gerard H. Pille: It's extremely unlikely - even if the old Oracle version (pre 11g) is used, so bind peeking potentially could be the case (and cursor sharing mode was set to force - note , literals , not bind variables ), both queries would have exactly the same plan because the only difference between them is +1 vs +2 which in both cases replaced by something like + "SYS_B_0" – a1ex07 Feb 15 '18 at 20:51
  • @a1ex07 you can see how unlikely in the predicates: "organization='LOL'". The same plan is the problem. – Gerard H. Pille Feb 15 '18 at 21:04
  • Not sure I follow. In the question , there are 2 queries , the only difference is +1 vs +2 . They generate 2 different plans. So unless I'm missing something, 2 plans are the problem. – a1ex07 Feb 15 '18 at 21:40
  • @GerardH.Pille : Ha , just now I noticed that 'LOL' was in screenshot . It shouldn't make any difference though - bind peeking would lead to 1 plan, not 2. – a1ex07 Feb 15 '18 at 21:58
  • 1
    @GerardH.Pille: You're wrong. 1. 14898 is an estimated number of rows, NOT a number of rows from previous select. (as well as 6250 is an estimated number of rows and not equal to actual 9260 ) 2. When literals are used instead of bind variables ('LOL', 'BA' , '2016-01-05') , Oracle performs a hard parse and can generate a new plan every time which is exactly what happened according to screenshots in question . – a1ex07 Feb 16 '18 at 4:43
1

It seems to me that optimizer statistics is either out of date, or you have a data skew(i.e. you have popular and unpopular in ORGANIZATION column). Optimizer estimates that filter ORGANIZATION = 'BA' returns 14898 rows which is way different from actual result. dbms_stats.gather_table_stats should fix the issue. ** According to screenshot it seems that "LOL" is a value of organization used with EXPLAIN , I'd suggest posting actual execution plan .

You may also create extended statistics on 2 columns (ORGANIZATION , timestamp).

Also, always check actual execution plan (dbms_xplan.display_cursor), not the result of explain .

Update

One more thing that may help investigating the problem. I also suggest finding query (or queries ) in v$sql /v$sqlarea and check their cost . Also, v$sql has is_bind_aware and is_bind_sensitive columns which shows whether optimizer considers and uses different plans depends on different values of bind variables. Other details about why multiple plans were generated V$SQL_SHARED_CURSOR system view that shows why existing child cursor wasn't shared with a new one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.