SQL*Net more data from client

The error SQLNet more data from client* usually happens when there’s a large amount of data sent from client (or other database in case of dblinks), which doesn’t fit into single SDU size Oracle packet. The server process knows that the call hasn’t ended and there is more data/packets to come before the call ends.

Also, the large amount of data may actually mean few kB only, but these waits appear as long as the whole call’s data doesn’t fit into one single SDU size packet. The SDU size can range from 512 bytes to 65535 bytes. The default SDU for the client and a dedicated server is 8192 bytes, while the default SDU for a shared server is 65535 bytes.

The first session data unit (SDU) bufferful of return data is written to TCP socket buffer under SQLNet message to client* wait event.

So this one particular wait event SQLNet more data from client* will include the network time along with some processing time from the user tool process, so it can indicated either network latency or a problem in the tool. The only problem I guess is there isn’t much way to know whether the slow down is because of the network or a problem in the tool.

However, it appears that time is logged to that event only until the packet is turned over to the network. It appears that Oracle then starts logging time to the SQLNet more data FROM client* event while it waits for the next response from the client. Therefore, the actual network transfer time is clocked to the wrong event. You can understand why the developers made this choice, because Oracle has no way of knowing how long it actually takes the packet to get to the client. It only knows how long the round trip takes. So maybe they should have named the event slightly differently, like SQLNet more data from client including the entire round trip network time* or something.

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read

Registering with the listener

#oracle #network

When restarting a database, if you can’t acess it from outside, you may have to associate the newly restarted database with the listener. The Pmon process that is started with the instance is responsible for registration of oracle server with listener. Pmon process wakes up at every 60 seconds and provide information to the listener. If any pro... Read more

20 Jun 2015 - less than 1 minute read

Getting the server IP Address from SQL*plus

#system #network

Here’s a small script to get the IP address of the host server directly from the SQL*plus command line. DECLARE v_host_name v$instance.host_name%type; v_ip_address varchar2(50); BEGIN SELECT host_name INTO v_host_name FROM v$instance; dbms_output.put_line('the database server name is ' || v_host_name); SELECT UTL_INADDR.GET_HOST_ADDRESS(v_host... Read more

20 Nov 2014 - less than 1 minute read