Re: SQL*Net message from client - But very high CPU

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: