One of our query has taken more time compared to everday. I see some part of time cpu is 100% busy and some part of time IO wait time was more than 80%.
I suspect the reason is because of CPU wait time and IO wait time.
How to get this stats. I think resusage tables will give at system level rather than at session/query level.
As ResUsage is on system level you can't use it to get session level data.
You might check the query in Viewpoint and use the Rewind feature to go back in time.
Otherwise compare DBQL step data for this query today and yesterday.
Yes. When I checked through rewind, I was able to know some part of time is 100% busy and some part of time IO wait time was more than 80%. In DBQL step info there is no provision that shows wait time. Basing on these I can tell it is because of cpu wait and io wait. But don't have exact period of wait time and stats.