Select statement is running more than 6 hours
Guys,
10.2.0.5
Linux AS4.
This below query runs more than 6 hours. We don't have long running queries, no blocking sessions, no db/table locks and we have indexes on this table.
select Deviceinstanceguid,to_char(LOCATIONTIME,'IW') week ,ABSTRACTMESSAGETYPECD,count cnt
from devicetravelstatushistory
where abstractmessagetypecd IN ('IN1HI','POWUP')
AND LOCATIONTIME>=to_date('01-JAN-2012 00:00:00','DD-MON-YYYY HH24:MI:SS')
and LOCATIONTIME<to_date('18-APR-2012 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND Deviceinstanceguid IN('61429','63302','204486','202034','205823')
group by Deviceinstanceguid,to_char(LOCATIONTIME,'IW'), ABSTRACTMESSAGETYPECD;
where abstractmessagetypecd IN ('IN1HI','POWUP')
AND LOCATIONTIME>=to_date('01-JAN-2012 00:00:00','DD-MON-YYYY HH24:MI:SS')
and LOCATIONTIME<to_date('18-APR-2012 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND Deviceinstanceguid IN('61429','63302','204486','202034','205823')
group by Deviceinstanceguid,to_char(LOCATIONTIME,'IW'), ABSTRACTMESSAGETYPECD;
Could anybody help me on this to improve the response time for this query ? Suggestions plz.
0