Hello All,

We are running 8.1.7.4 db in MTS mode on Windows NT 4.

Sometimes CPU utilization goes to 100% for 3 - 4 minutes and
we find out the session utilizing max CPU by following query:

SELECT SUBSTR (sn.NAME, 1, 30) parameter,
ss.username || '(' || se.SID || ',' || ss.SERIAL# || ')' user_process,
ss.OSUSER, ss.LAST_CALL_ET, se.VALUE, ss.MACHINE
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND sn.NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
ORDER BY sn.NAME, se.VALUE DESC

Now my manager has asked me to find out the queries run by
users utilizing max CPU so that we can tune those queries.

I have tried looking in v$sqlarea by joining ADDRESS colum with
both SQL_ADDRESS and PREV_SQL_ADDR columns of v$session but it
doesn't always give the results.

I would like to know is some other sure shot way of getting the
SQL. Any help in this regard will be highly appreciated.

Best Regards,

Kashif