How to find high CPU using sessions in Oracle Database

If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat  view can be queried to find high cpu using sessions and then SQL can be listed.

1.Find the 'CPU used by this session' statistic.

SQL>SELECT name ,statistic# FROM v$statname WHERE  name LIKE '%CPU%session';

    NAME                                STATISTIC#
    ----------------------------------- ----------
    CPU used by this session                    14

2. Then determine which session is using most of the cpu.

SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;

           SID STATISTIC#      VALUE
    ---------- ---------- ----------
             1         14          0
             2         14          0
             3         14          0
             4         14          0
             5         14          0
             6         14          0
             7         14          0
             8         14          0
             9         14          0
            10         14          0
            11         14          0
            12         14          0
            16         14       1930

3. Lookup details for the session which is using most of the cpu.

SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG
    FROM   v$sqlarea a, v$session s
    WHERE  sid = 16
    AND    s.sql_address = a.address
    AND    executions > 0
    ORDER BY 5;

4. Use v$sqltext to extract the whole SQL text.

set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/

5. Once the whole SQL statement has been identified it can be tuned.

Explain the queries and examine their access paths.
Autotrace is a useful tool for examining access paths.

Syntax:-

Explain plan for 'sql statement';