approach to get analysis on high CPU loads

1. Since the CPU usage was high, this query will show up in the “top session” field ---please check it immediately otherwise the you may lose the DDL.
2. If you lose the DDL, run AWR snapshot during the interval this “high CPU” was happening and look for the “offending query”.
3. From that line, get the “hash value” and run the following query:
a. select child_number,
b. io_cost,
c. lpad(' ',2 * (depth),'| ') || operation
d. || decode(options,
e. null, null, ' (' || initcap(options) || ')')
f. || ' ' || object_owner ||'.'|| object_name op
g. from v$sql_plan
h. where hash_value = &hash_value
i. order by child_number, id;
4. You will get the plan based on this.
5. If the previous two options doesn’t help, in this case, since you know the concurrent program name, run it in a development environment with trace on (from the concurrent program form) and extract the top DDL based on tkprof option (sort='(prsela,exeela,fchela)')
6. Use this DDL to generate explain plan in DEV and compare it in PROD.

Usually, whenever there is a high CPU usage, there is a “enqueue” contention or a bad index (fragmented or needs an updated stats or index rebuild).