SQL*Net more data from dblink Reference Note

• Versions 7.3.4.5 - 11.2.0.3
  • 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

    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 the next message chunk 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 network plus the remote process. If the remote process is another database instance (accessed via a database link) then the information in <> on the REMOTE database will have the same client details as the V$SESSION entry on the local database for the blocked session. You need to look at the remote instance to determine where that session is spending time.

Systemwide Waits:

If systemwide waits for this event are significant it is best to determine where the remote connections are to and switch attention to the remote instance / instances to determine where they are spending time. One can also look at:

• Sessions with high values in <> for:
  • SQL*Net roundtrips to/from dblink
  • bytes received via SQL*Net from dblink
• The Network between the local and remote systems (problems are usually related to time spent ON the remote instance rather than in the network but it is worth checking if the network between the instances is slow / not)

Reducing Waits / Wait times:

It is normally best to see what propertion of the time is actually spent on the remote instance as the most common cause of long waits over a DB link is that the time is actually all spent doing work on the remote instance (either waiting or working).

• Trace the session locally and at the remote instance to see what work a user "transaction" actually consists of
• Check the execution plans for any distributed queries
• See if frequently accessed remote data can be kept in a local snapshot (Materialized View).