Symptom
When we saw the 100% CPU usage, we saw this query as one of the top SQL and the AWR report, even suggested for query tuning. The reason sighted was there was lot of resources spent parsing the query. This happens when we use the IN operator and the inner select statement fetches large amount of data.
