in Oracle

Which query takes the most CPU in oracle database?

Here is a simple sql query which will show which query is misbehaving and consuming the most CPU usage

SELECT ss.username,
       ss.OSUSER,
       ss.TERMINAL,
       se.SID,
       VALUE / 100 cpu,
       sq.SQL_TEXT
  FROM v$session ss, v$sesstat se, v$statname sn, v$sql sq
 WHERE se.STATISTIC# = sn.STATISTIC#
   AND NAME LIKE '%CPU used by this session%'
   AND se.SID = ss.SID
   AND ss.STATUS = 'ACTIVE'
   AND ss.username IS NOT NULL
   AND ss.SQL_ID = sq.SQL_ID
   AND VALUE / 100 >= 1
 ORDER BY VALUE DESC;

And when you see the top CPU using process, monitor its activity through this query:

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,  
       MACHINE,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(FLOOR(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(FLOOR(MOD(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(MOD(SES.LAST_CALL_ET, 60), '09'))    RUNTIME
  FROM V$SESSION SES,  
       V$SQLtext_with_newlines SQL
 WHERE SES.STATUS = 'ACTIVE'
   AND SES.USERNAME IS NOT NULL
   AND SES.SQL_ADDRESS    = SQL.ADDRESS
   AND SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   AND Ses.AUDSID <> userenv('SESSIONID')
 ORDER BY runtime DESC, 1,SQL.piece;