Home » RDBMS Server » Performance Tuning » A question about SQL * net message in a batch program (9.2.0.8 on Solaris)
A question about SQL * net message in a batch program [message #285066] Mon, 03 December 2007 05:03 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi ,

We have a batch program to insert several thousands of rows into several tables...this program is having some problem - it times out during the load. For debugging it, I ran a 10046 event with level 12. portion of it is below:

here is one qurey that caused so much sql*net wait:

********************************************************************************

<one select query involing outer join of two tables>

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         68          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         68          0          32

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 178

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       5        0.00          0.00
[B]  SQL*Net message from client                     4       16.01         27.14[/B]



below is overall total:
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       20      0.00       0.00          0          0          0           0
Execute     24      3.79       4.34          0      61385      20366       20006
Fetch        8      0.04       0.04          0        303          0          45
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       52      3.83       4.39          0      61688      20366       20051

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     247        0.00          0.00
  SQL*Net message from client                   246       16.01         38.98
  log file sync                                  12        0.16          0.74
  db file sequential read                       854        0.05          0.39
  SQL*Net more data from client                6966        0.09          1.07
  direct path write (lob)                       431        0.32          0.46
  log file switch completion                      2        0.42          0.56
  SQL*Net more data to client                     1        0.00          0.00



I am not sure why SQL *net message wait event should appear and it should take so much time..in the overall summary also this even has max wait..can you suggest any pointers...how this can be avoided.

Thanks,
Nirav
Re: A question about SQL * net message in a batch program [message #285073 is a reply to message #285066] Mon, 03 December 2007 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"SQL*Net message from client" means Oracle is waiting for you to send him work.

Regards
Michel
Re: A question about SQL * net message in a batch program [message #285089 is a reply to message #285073] Mon, 03 December 2007 06:33 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Yes, but this is a batch program...it is a text file that is parsed and then rows are inserted into the database. So I do not know why it should have sql *net waits.
Re: A question about SQL * net message in a batch program [message #285092 is a reply to message #285089] Mon, 03 December 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not an Oracle problem.
Check your batch.
Check how it is coded.
Check your network.
But don't check Oracle, it is just waiting for you.

Regards
Michel
Re: A question about SQL * net message in a batch program [message #285104 is a reply to message #285092] Mon, 03 December 2007 08:32 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you Michel.

With regards,
Nirav
Re: A question about SQL * net message in a batch program [message #286588 is a reply to message #285066] Sat, 08 December 2007 09:03 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
How you define the "batch"?

Any chance that it's some C (or whatever) program running on separate machine?

Michael
Previous Topic: disadvantages of optimization
Next Topic: Need to tune SQL involving Join
Goto Forum:
  


Current Time: Tue Dec 06 02:36:19 CST 2016

Total time taken to generate the page: 0.12510 seconds