CODEWITHSUNDEEP
sqloracle-database
Punter Vicky
Punter Vicky

Reputation: 16992

Oracle - Tuning queries with date range

I have 2 tables which both have start date and end date. Both these tables are related by a column named cust_id. I am joining these tables to fetch specific columns and restricting it by date range which is applied on one table. Irrespective of whether the date range spans 4 days or 1 hour , I see the query taking 50-55 seconds. I assumed when I provide a smaller date range , the number of rows that Oracle needs to be parse is less. Is this the expected behavior or should I look up something?

select to_char(t.start_ts,'YYYY-MM-DD HH24:MI'),
COUNT(CASE WHEN f.fault = 'N' THEN 1 END) success,
COUNT(CASE WHEN f.fault = 'Y' THEN 1 END) failure
from customer t,profile f  where 1=1
and t.cust_id = f.cust_id
and to_char(t.start_ts,'YYYY-MM-DD HH24:MI:SS') between '2017-03-01 00:00:00'
and '2017-05-01 23:59:59'
group by to_char(t.start_ts,'YYYY-MM-DD HH24:MI')
order by to_char(t.start_ts,'YYYY-MM-DD HH24:MI');

Ran a query against similar table in a different env where I was observing the same behavior :

Plan hash value: 2851258613


    ---------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                   |     2 |   362 | 11651   (1)| 00:00:01 |       |       |
    |   1 |  SORT GROUP BY                        |                   |     2 |   362 | 11651   (1)| 00:00:01 |       |       |
    |   2 |   NESTED LOOPS                        |                   |     2 |   362 | 11650   (1)| 00:00:01 |       |       |
    |   3 |    NESTED LOOPS                       |                   |     2 |   362 | 11650   (1)| 00:00:01 |       |       |
    |   4 |     PARTITION RANGE ALL               |                   |     2 |   284 | 11644   (1)| 00:00:01 |     1 |    41 |
    |*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID| TXNS              |     2 |   284 | 11644   (1)| 00:00:01 |     1 |    41 |
    |*  6 |       INDEX SKIP SCAN                 | XIE1TXNS          |     4 |       | 11641   (1)| 00:00:01 |     1 |    41 |
    |*  7 |     INDEX RANGE SCAN                  | XAK1FRONTEND_DTLS |     1 |       |     2   (0)| 00:00:01 |       |       |
    |   8 |    TABLE ACCESS BY GLOBAL INDEX ROWID | FRONTEND_DTLS     |     1 |    39 |     3   (0)| 00:00:01 | ROWID | ROWID |
    ---------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       5 - filter("T"."SRVC_NAME"='ControllerSvc' AND "T"."SRVC_VERSION"='10.00' AND 
                  "T"."SRC_SERV_ID"<>'test' AND "T"."SRC_SERV_ID"<>'endtoendtesting' AND "T"."SRVR_NODE_NAME" NOT LIKE 
                  '%test.net' AND "T"."SRC_SERV_ID"<>'test' AND "T"."SRC_SERV_ID"<>'SYN')
       6 - access("T"."SRVC_OP_NAME"='getTestInfo')
           filter("T"."SRVC_OP_NAME"='getTestInfo' AND TO_CHAR(INTERNAL_FUNCTION("T"."START_TS"),'YYYY-MM-DD 
                  HH24:MI:SS')>='2017-03-01 00:00:00' AND TO_CHAR(INTERNAL_FUNCTION("T"."START_TS"),'YYYY-MM-DD 
                  HH24:MI:SS')<='2017-05-01 23:59:59')
       7 - access("T"."TXN_ID"="F"."TXN_ID")

PS: I wasn't able lookup the EXPLAIN PLAN as I didn't have sufficient access.

Upvotes: 1

Views: 3414

Answers (3)

BobC
BobC

Reputation: 4424

In order to see the runtime stats and see if your cardinality estimates are accurate you should do this:

alter session set timed_statistics=ALL;


select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) );

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191295

It isn't clear if you have an index on start_ts, but your expectation that a shorter time span should return results faster suggests you might have. If you don't you may need to add one. With an index there, the way you are querying would stop it being used anyway. You are doing:

and to_char(t.start_ts,'YYYY-MM-DD HH24:MI:SS') between '2017-03-01 00:00:00'

and '2017-05-01 23:59:59'

which means what every row (that matches the other predicates) has to have its start_ts value converted to a string, and you're then comparing that string against two other fixed strings. While that will work, it's slow. You can see from the explain plan that that that column is being checked in the filter section, not the access section. (Even without an index, it's still extra overhead; with an index this - or most function calls - will prevent the index being used).

You should compare the proper data types, with or without an index, but especially with an index. If the column data type is DATE then you could do:

and t.start_ts between to_date('2017-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  and to_date('2017-05-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

or if it's a TIMESTAMP (as the name suggests) you could do:

and t.start_ts between to_timestamp('2017-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  and to_timestamp('2017-05-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

but that will skip any times with fractional seconds - e.g. 23:59:59.543 - and thus potentiually give wrong results. It's safer to do:

and t.start_ts >= timestamp '2017-03-01 00:00:00'
and t.start_ts < timestamp '2017-05-02 00:00:00'

... where I've also switch to timestamp literals to make it shorter, but it's the same as using to_timestamp() with the eight format mask.

Oracle may still decide not to use the index (if it exists); or may continue to use partition pruning first (or instead). It depends on the data and the selectivity of all the predicates you're using, and the optimiser choosing the best approach. Using the right data types and not preventing any indexes being available to use gives it a better chance of picking an optimal plan though.

Upvotes: 2

OldProgrammer
OldProgrammer

Reputation: 12169

Try changing this:

and to_char(t.start_ts,'YYYY-MM-DD HH24:MI:SS') between '2017-03-01 00:00:00'
and '2017-05-01 23:59:59'

to this:

and t.start_ts between to_date('2017-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
   and to_date('2017-05-01 23:59:59','YYYY-MM-DD HH24:MI:SS')

Calling a function on a column may prevent the index from being used properly. This assumes you have an index on start_ts. Alternatively (but I would recommend the first option), is to create a function based index - https://oracle-base.com/articles/8i/function-based-indexes

Upvotes: 2

Related Questions