| |
 |
|
Oracle Tips by Burleson |
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.
By querying v$sqlarea view, the following simple 9i script shows SQL
statements by their CPU Usage:
* CPU.SQL
rem
rem CPU.SQL
rem Mike Ault
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
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
