RE: TKPROF output

Have you tried running the SQL directly on the server itself?

Tom Kyte has several posts on this I think
Google:
site:asktom.oracle.com + +SQL*Net message from client +fetch

Here's some of his summaries to have something to think about:

This could be indicative of a client application inefficiently processing data 
- if you think the client should be

a) getting data
b) doing something very fast
c) getting more data and repeat

this would indicate the client is not achieving B
So, I would say the report is getting hung up doing something, perhaps it was 
starved for cpu, perhaps it get stuck writing to disk.

sqlnet message from client - we are waiting to be told what to do next in the 
database. we are not currently doing anything in the database.

&&

set arraysize larger, that'll cut down on the round trips - if the problem is 
that remote is on a high bandwidth - high latency network (like a satellite 
connection), then this will help.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
Sent: Wednesday, March 30, 2011 1:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: TKPROF output

Trying to understand Fetch in a TKPROF output.  We have an application on 
Oracle Apps Server 10.1  Database 10.2.0.4  On production, a specific query 
runs in about 3 seconds.  On this new database server we created, it runs about 
30 secs.  Looks like the query does the same thing in the database, but we have 
a ton of SQL*Net message waits on the query below.  What are Fetches?  What are 
reasons why waits for SQL*Net messaging happens that relate to Fetches?  See 
below...

Here it is:
SELECT ROWID,SCRAP_ID,TX_ID,SHIFT_ID,ON_TX_ID,SCRAP_COMP_CODE,WEIGHT_UOM,
  DEPT_CODE,INV_COMP_CODE,INV_ITEM_CODE,SCRAP_CODE,TYPE,CUST_NUM,PART,
  QUANTITY,LENGTH,SCRAP_WEIGHT,TX_START_DT,RESPONSIBILITY_CODE,DEFECT_CODE,
  NOTES
FROM
 ST_PRODTX_SCRAP WHERE (WEIGHT_UOM=:1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    27457      0.91       0.90          0      29757          0      164741
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    27459      0.91       0.90          0      29757          0      164741

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 677  (LBARBOUR)

Rows     Row Source Operation
-------  ---------------------------------------------------
 164741  TABLE ACCESS FULL ST_PRODTX_SCRAP (cr=29757 pr=0 pw=0 time=165118 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
 164741   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ST_PRODTX_SCRAP' (TABLE)



Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   27457        0.00          0.01
  SQL*Net message from client                 27457        1.07        100.33

Other related posts: