Tuesday, September 20, 2005

SQL*Net message from client

Sql*net message from client is generally considered an idle event as the Oracle server process is waiting for the client process. Some dbas do not pay any attention to this event. It is good this seems to be changing.

This case i have come into shows the importance of this event.
User queries a simple base block form which gets one row from a table (which contains that single row), changes one column and commits the change. The user is complaining about the performance of this form and says he always waits 5-6 seconds for this update. The sql trace for this session shows the following sql statements:

Select row from table;
Select row from table where rowid=:1 for update;
Update table set col=value where rowid=:2;

Each sql statement’s statistics show that all their elapsed times were 0.00. But here are the totals at the end of the trace file:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.01       0.00          0          0          0           0
Execute      7      0.00       0.01          0          0          0           1
Fetch        5      0.00       0.00          0         12          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.01       0.02          0         12          0           6

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                     172        0.00          0.00
  SQL*Net message from client                   172        0.68          6.20
  latch free                                      1        0.00          0.00
  SQL*Net message to dblink                       8        0.00          0.00
  SQL*Net message from dblink                     8        0.00          0.01
  log file sync                                   1        0.00          0.00

Here we see why he is talking about the 5 seconds response time of this simple form. Sql*net message from client waits account for 6 seconds of elapsed time. There are 172 waits for this event. Surely any other action than reducing sql*net message from clients here can not make any improvements in performance. When we quit using base block and populating the block using a “select col_list into ... “ statement and then updating the row, we reduced the number of sql*net message from clients from 172 to 36 and response time from 6.20 sec to 1 sec.

5 comments:

  1. I agree, this event is extremely important, especially in n-tier applications that improperly move a lot of data.

    ReplyDelete
  2. This post really caught my interest, since we have an application that gives us a huge number of wait events, though we cannot find anything wrong with the network.

    However, I'm no DBA, so I don't understand exactly why changing the query fixed this problem. Why is there so much IO anyway? (Or is that only apparent?)
    If anyone would care to give me an explanation, you can reach me at reedmohnAThotmail.com.

    Thanks in advance!

    ReplyDelete
  3. Why there are so many round-trips between the client and the server when using a base block form is related to the behaviour of Oracle Forms. I have asked about this to Oracle Support but did not get a satisfying answer.

    ReplyDelete
  4. many moons ago, there used to be a tuning parameter for multi-row blocks in Forms.
    it allowed us to specify how many rows to retrieve on each fetch off the database.
    the default was 1. maybe that is the cause of the multiple client waits?
    replacing it with the "hand-crafted" SQL is a solution, but I'm not 100% sure of the impact of that on locking when updating?
    the default Forms locking is quite elaborate and not easy to replicate.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete