CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
Bhagwat
Bhagwat

Reputation: 71

How to improve the performance of my query? / My query is running slow.

workitem_routing_stats table is having around 1000000 records .all records are acceesed thats why we are using full scan hint. it takes around 25 seconds to execute is there is any way to tune this query.

SELECT /*+ full(wrs) */
wrs.NODE_ID,
wrs.bb_id--,
SUM(CASE WHEN WRS.START_TS >= (SYSTIMESTAMP-NUMTODSINTERVAL(7,'day'))
AND wrs.END_TS <= SYSTIMESTAMP THEN (wrs.WORKITEM_COUNT) END) outliers_last_sevend,

SUM(CASE WHEN WRS.START_TS >= (SYSTIMESTAMP-NUMTODSINTERVAL(30,'day'))
AND wrs.END_TS <= SYSTIMESTAMP THEN (wrs.WORKITEM_COUNT) END)
outliers_last_thirtyd ,

SUM(CASE WHEN WRS.START_TS >= (SYSTIMESTAMP-NUMTODSINTERVAL(90,'day'))
AND wrs.END_TS <= SYSTIMESTAMP THEN (wrs.WORKITEM_COUNT) END)
outliers_last_ninetyd ,
SUM(wrs.WORKITEM_COUNT)outliers_year

FROM workitem_routing_stats wrs
WHERE wrs.START_TS BETWEEN (SYSTIMESTAMP-numtodsinterval(365,'day')) AND SYSTIMESTAMP
AND wrs.END_TS BETWEEN (SYSTIMESTAMP-numtodsinterval(365,'day')) AND SYSTIMESTAMP
GROUP BY wrs.NODE_ID,wrs.bb_id ;

Upvotes: 0

Views: 231

Answers (3)

Randy
Randy

Reputation: 16677

one other possibility - it seems that maybe this data will be adding new timestamps as of today, but the rest is just history...

if this is the case, then you could add a summary table to hold the summarized historic information and only query this current table for the recent stuff, and UNION to the summary table for the older stuff.

you will then need to think through the JOB or other scheduled process to get the summaries populated, but it would save you a ton in this query time.

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

You may range partition the table in a monthly manner on START_TS column. (will scan only the year you are interested in)

Secondly(not a very intelligent solution) you may add a parallel(wrs 4) hint if your storage is powerfull.

You can combine these two things.

Upvotes: 1

Randy
Randy

Reputation: 16677

a full scan is going to be painful in any case...

however - you may avoid some computation if you simply put in the proper numbers instead of calling the conversion functions:

(SYSTIMESTAMP-numtodsinterval(365,'day'))

should just be the same as

(SYSTIMESTAMP-365)

this should remove overhead of calling the function, and parsing the parameter string ('day')

Upvotes: 0

Related Questions