A question about interpreting 10046 event trace file

From: nirav <shivam71_at_gmail.com>
Date: Sun, 6 Jan 2008 02:22:08 -0800 (PST)
Message-ID: <95d4564b-2a96-4235-880f-6007de194827@c23g2000hsa.googlegroups.com>

Hi,

I need help to understand and interpret the output of 10046 trace file (with level 8 ). The problem that we are trying to solve is that: in a batch process where thousands of inserts are being done, at some point application hangs - more correctly, transaction time out occurs. The developers say that: it is the because soemthing happening at the database level and we are analyzing that issue, for that this is a portion of the trace file:

<Query X>

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0
0           0
Execute      7      0.01       0.00          0          0
0           0
Fetch       14      0.00       0.00          0         63
0          70

------- ------ -------- ---------- ---------- ---------- ----------

---

total       22      0.01       0.00          0         63
0          70

Misses in library cache during parse: 0
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 933

Rows Row Source Operation

-------  ---------------------------------------------------
     50  SORT UNIQUE
     50   NESTED LOOPS OUTER
     40    INDEX RANGE SCAN PK_TABLE1 (object id 1581385)
     30    TABLE ACCESS BY INDEX ROWID TABLE2
     30     INDEX RANGE SCAN NK3_TABLE2 (object id 1581563)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  SQL*Net message to client 15 0.00 0.00 SQL*Net message from client 14 29.35 57.40

  ---

It is seen from the trace that maximum wait is "SQL*Net message from the client (54 seconds). In this regard, my question is:

1. Is the <Query X> responsible for this wait? or is the next query in the trace file after this <Query X>, responsible for this ?
2. Developers are asking that where or in what portion of the code should they look to investigate? (based on trace file, they expect me to tell them that.) - Now from the trace file, I found the maximum wait is in the portion copied above. so in that case does it mean that -
3. the <Query X> is reponsible for the wait ? or the next query in trace file just after the <Query X> is responsible or the application code somewhere around <Query X> is responsible?

With thanks,
Nirav Received on Sun Jan 06 2008 - 04:22:08 CST