This can be used to find queries that use CPU heavily.  It starts by determining a baseline and then identifies those queries that use more CPU than the baseline * an arbitrary multiplier.  If you want to use this in a balanced RAC environment change the references to v$sqlarea to gv$sqlarea.  Functions are used to return topCPU and user to the mainline.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

topCPUkount                 NUMBER;
multiplier                  NUMBER     := .60;

CURSOR cpuCursor (cKo...
Continue reading ...