Re: SQL*Net message from client - But very high CPU
- From: Ajay Thotangare <ajayoraclel@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 8 Aug 2008 18:40:50 -0700 (PDT)
More info from trace
Rows Row Source Operation
------- ---------------------------------------------------
25840 SORT UNIQUE (cr=73249464 pr=47 pw=0 time=2031803917 us)
43371 UNION-ALL (cr=73249464 pr=47 pw=0 time=3154971516 us)
14457 SORT AGGREGATE (cr=35410991 pr=0 pw=0 time=904116332 us)
29960 TABLE ACCESS BY GLOBAL INDEX ROWID GROUP_BLK_INV PARTITION: ROW
LOCATION ROW LOCATION (cr=35410991 pr=0 pw=0 time=720613921 us)
13128002 NESTED LOOPS (cr=35381236 pr=0 pw=0 time=630270143 us)
13083585 TABLE ACCESS BY INDEX ROWID GROUP_BLK (cr=8833254 pr=0 pw=0
time=86857291 us)
14977452 INDEX RANGE SCAN UK_GROUP_BLK_01 (cr=115656 pr=0 pw=0
time=17299887 us)(object id 1003269)
29960 INDEX RANGE SCAN PK_GROUP_BLK_INV (cr=26547982 pr=0 pw=0
time=776521765 us)(object id 1187554)
14457 SORT AGGREGATE (cr=37795102 pr=47 pw=0 time=1125139872 us)
1867 PARTITION RANGE ALL PARTITION: 1 74 (cr=37795102 pr=47 pw=0
time=1115949417 us)
1867 TABLE ACCESS BY LOCAL INDEX ROWID WHLSLR_INV PARTITION: 1 74
(cr=37795102 pr=47 pw=0 time=1121998442 us)
70665816 INDEX SKIP SCAN X_WHLSLR_INV_01 PARTITION: 1 74 (cr=20716900
pr=47 pw=0 time=1561699110 us)(object id 1189978)
14457 SORT AGGREGATE (cr=43371 pr=0 pw=0 time=1325795 us)
0 TABLE ACCESS BY GLOBAL INDEX ROWID FREESALE_INV PARTITION: ROW
LOCATION ROW LOCATION (cr=43371 pr=0 pw=0 time=958358 us)
0 INDEX RANGE SCAN PK_FREESALE_INV (cr=43371 pr=0 pw=0 time=852273
us)(object id 1187547)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13267 0.03 0.15
gc cr block 2-way 1895 0.01 1.35
gc current block 2-way 521 0.00 0.40
db file sequential read 47 0.02 0.50
gc cr grant 2-way 24 0.00 0.00
gc cr block 3-way 967 0.00 1.10
gc current block 3-way 250 0.00 0.28
SQL*Net message from client 13267 1.73 148.81
latch: KCL gc element parent latch 12 0.00 0.00
latch free 344 0.02 0.19
latch: cache buffers chains 16 0.00 0.00
gc cr block busy 6644 0.10 21.89
buffer busy waits 1 0.00 0.00
latch: enqueue hash chains 1 0.00 0..00
gc cr block congested 13 0.05 0.08
gc current block congested 5 0.00 0..02
latch: undo global data 1 0.00 0.00
latch: gcs resource hash 1 0.00 0.00
gc current grant busy 1 0.00 0.00
********************************************************************************
--- On Sat, 8/9/08, Ajay Thotangare <ajayoraclel@xxxxxxxxx> wrote:
From: Ajay Thotangare <ajayoraclel@xxxxxxxxx>
Subject: SQL*Net message from client - But very high CPU
To: oracle-l@xxxxxxxxxxxxx
Cc: ajayoraclel@xxxxxxxxx
Date: Saturday, August 9, 2008, 1:36 AM
Hi List,
I need help in solving performance problem of a query. My database is 10.2.0.3
on HP-UX Itanium.
I traced the session during the run and noticed the session is using very high
CPU but the wait event in v$session shows "SQL*Net message from client"..
In top(UNIX command) this session is the top most. Not able to interpret whats
happening.
Below is the trace details
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13266 25.92 28.85 0 0 0 0
Fetch 13267 1768.47 1875.36 47 67207044 0 23592
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26534 1794.39 1904.21 47 67207044 0 23592
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 998
Noticed fetch is using 1768.47 CPU of the elapsed time 1875.36.
Any help appreciated.
--regards,
Ajay
Other related posts:
