SQL*Net message from client wait in Oracle

I will explain SQL*Net message from client wait in Oracle in this post.

 

SQL*Net message from client wait in Oracle

Most used wait event in the 10046 trace or AWR Report is the SQL*Net message from client wait event that can indicate a possible NETWORK problem on Server or Database. If you saw this wait event, it meand Oracle is waiting  some work to do.

 

 

 

 

 

 

Oracle foreground process is waiting for a message to arrive from the client process. This is generally considered as an “idle” event in that the Oracle foreground process is idle waiting for the client process to tell it what to do. Time waiting in this state is attributable to the client process itself plus any network transport time.

 

Client is sending data to foreground process that spans packets (think large data inserts, possibly large code blocks, large SQL statements)

Note that the remote end of a database link waits on “SQL*Net message from client” when it is waiting for the session which opened the database link to send it a packet as that is its “client” process.

 

 

SQL*Net message from client

 Parameters:

• P1 = driver id
• P2 = bytes#
• P3 = Not used

 

• driver id
  In Oracle8i onwards P1RAW can be decoded into ASCII characters to give a clue as to which Net driver is used.
  Eg: P1RAW=0x62657100 = ‘beq\0’ , P1RAW=0x54435000 = ‘TCP\0’ etc.In earlier releases the value here is the value of the disconnect function of the Net driver being used (which is not much use).

   

• bytes# (number of bytes received)
  The number of bytes we need to receive. This figure may be misleading it is often a “guess” of how many bytes might be sent in the next packet as opposed to the real number of bytes expected. (eg: It may be just 1 even though the expected packet will be much larger, or it may be a large number even if only a few bytes are needed)

 

Wait Time:

This wait blocks until a message is received from the remote connection (or until an abnormal end of file condition occurs on the underlying Net transport layer). There is no Oracle timeout on the wait.

 

 

  Finding Blockers:

The blocker is the client process and any network delay.

 

 

Systemwide Waits:

This event is classed as an “idle” wait so should be ignored when looking at systemwide timings. See Note:61998.1 for more information about “IDLE” waits.

 

 

Reducing Waits / Wait times:

One should look at the client processes and determine if the time is due to:

• the client process waiting for input ,
• time in the client process itself ,
• time in the network between the client and the server.
  (SQL*Net trace (with TIMESTAMPS) can be helpful to check out the time in the network between the client and server.

 

If you want to learn more details about Oracle wait event, read the following post.

Oracle Wait Events and Their Solutions in Oracle Database

 

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )