Approach to identify root cause of CPU spike by using ASH report

I have a question on what is the right approach to identify root cause of CPU spike from ASH report and will be thankful for review of the following , which is the approach that I have been using – and now I am getting some question/doubt on it:

For the scope of this question, I am assuming that it is a specific sql that is the cause of the issue. Please consider this to be an assumption that is true for this analysis, that given that as a fact that it is indeed a sql that causing the CPU spike, how it should be identified from the ASH report.

Also assume that we have required licence neeeded for AWR/ASH.

We are on Oracle 11.2.0.4 on Linux. We have alerts setup for CPU and monitor for CPU spike when it goes above 70%. –When this happens I do the following:

1. Identify which database node had the spike (we have 4 node RAC). For that node, take a 10 minute ASH report e.g. if spike is at 5.04 pm then I take an ASH report from 5.00 pm to 5.10 pm.
2. Look at what is the top most one/two sql id in the “Top SQL with Top Events” section and consider this as the root cause of the CPU spike.

Now I think that a better approach could have been:

1. Look at DBA_HIST_ACTIVE_SESS_HISTORY for that 10 minute period (basically the time frame of spike) and check which sql id was ‘ON CPU’ during that time. 2) Among all such sql id that were ‘ON CPU’ one that present for highest number of times is the one that is the culprit.

Again I am not 100% sure if this is the right approach but this is what I think as of now.

With Regards,

Nirav