SQL by CPU Usage
In order to determine the SQL that may be
contributing to the CPU usage, query the v$sqlarea view.
This view contains statistics about SQL in the shared pool
and its usage statistics.
Also see my
notes on
100% CPU consumption.
By querying v$sqlarea view, the following simple 9i
script shows SQL statements by their CPU Usage:
* CPU.SQL
rem
rem SQL by CPU Usage (v$sqlarea)
rem
column sql_text
format a40 word_wrapped heading 'SQL|Text'
column
cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets
heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
ttitle 'SQL By CPU Usage'
spool cpu
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000
elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time
desc, disk_reads desc
)
where rownum < 21
/
spool off
set pages 22 lines 80
ttitle off
The following example report of SQL statements ordered
by cpu_time and disk_reads was edited for brevity. Once the
problem SQL is isolated, tune the offending SQL to reduce
Logical IO and CPU usage will usually be reduced as well:
Wed Sep 22 page 1
SQL By CPU Usage
SQL CPU Elapsed Disk Buffer Rows
Text Time Time Reads Gets Processed
------------------------------------ ---------- ----------
---------- ---------- ----------
select OWNER,
SEGMENT_NAME, .16501 .172953798 140770 5871602 0
SEGMENT_TYPE, TABLESPACE_NAME,
NEXT_EXTENT from ( select
seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
t.NEXT_EXTENT from
dba_segments
seg,
dba_tables t where
(seg.SEGMENT_TYPE = 'TABLE'
and
seg.SEGMENT_NAME = t.TABLE_NAME
and seg.owner =
t.OWNER and
NOT EXISTS (
select TABLESPACE_NAME
from dba_free_space free
where free.TABLESPACE_NAME =
t.TABLESPACE_NAME
and BYTES >= t.NEXT_EXTENT))
union
select seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
c.NEXT_EXTENT from
dba_segments
seg,
SELECT .10371 .118568367 97872 100559 136
EMP.DIVISION,CMD.COLL,EMP.NAME,SUM(CMD.S
ACC_COMM),SUM(DECODE(DLT_TYPE,'P',CMD.AM
T,-1*CMD.AMT)),COUNT(CLM)FROM CMD,EMP
WHERE CMD.DT_TRANS
BETWEEN:b1 AND
TO_DATE(:b2)+1 AND
TO_CHAR(DT_TRANS,'HH24MISS')BETWEEN:b3
AND:b4 AND DLT_TYPE||ADJ_TYPE
IN('J1','J5','J7','P ')AND
EMP.NO=CMD.COLL GROUP BY
EMP.DIVISION,EMP.NAME,CMD.COLL
ORDER BY
EMP.DIVISION,CMD.COLL
