What should I be looking for if sql*net session is very slow

User_XYoci6

I have a goldengate classic replicat running remotely and updating a 12.1c Solaris. For all intentions and purpose, Goldengate is not that relevant as it is just an sql application especially a classic replicat. I have been getting 8000 ops per sec and is now down to 1.

I ran this query select sid,event, total_waits, time_waited*10 tw_ms,    average_wait*10 aw_ms, max_wait*10 mw_msfrom v$session_eventwhere sid in (select sid from v$session where username = 'GG_USER');

There are 9 sessions altogether,the session that is doing the SQL shows

              Total     Waited   Average     Max

    SID Waited for            Waits    for (ms)  Wait (ms)  Wait (ms)

---------- ------------------------------ -------- --------------- ----------- -----------

    498 Disk file operations I/O       29     180.00    6.10    30.00

    498 control file sequential read     67      40.00     .60     .00

    498 latch: cache buffers chains     110      20.00     .20     .00

    498 buffer busy waits           6       .00     .00     .00

    498 db file sequential read     35,392    18,660.00     .50    10.00

    498 db file scattered read      70,635   199,480.00    2.80    30.00

    498 latch: shared pool          1       .00     .70     .00

    498 SQL*Net message to client    129,181     570.00     .00     .00

    498 SQL*Net message from client   129,180  1,140,410.00    8.80  10,020.00

    498 SQL*Net break/reset to client     2       .00     .10     .00

                       Total     Waited   Average     Max

    SID Waited for            Waits    for (ms)  Wait (ms)  Wait (ms)

---------- ------------------------------ -------- --------------- ----------- -----------

    498 events in waitclass Other       1       .00     .00     .00

That seems like a crazy high value. Another session belonging to the same user shows

                       Total     Waited   Average     Max

    SID Waited for            Waits    for (ms)  Wait (ms)  Wait (ms)

---------- ------------------------------ -------- --------------- ----------- -----------

    498 db file sequential read     35,392    18,660.00     .50    10.00

    498 db file scattered read      70,635   199,480.00    2.80    30.00

    498 latch: shared pool          1       .00     .70     .00

    498 SQL*Net message to client    129,201     570.00     .00     .00

    498 SQL*Net message from client   129,200  1,340,710.00    10.40  10,020.00

Yet another shows

                       Total     Waited   Average     Max

    SID Waited for            Waits    for (ms)  Wait (ms)  Wait (ms)

---------- ------------------------------ -------- --------------- ----------- -----------

    15 SQL*Net message from client   124,233 163,353,360.00  1,314.90  53,840.00

    15 SQL*Net break/reset to client     2       .00     .10     .00

Question is, does this point to a comms issue or not necessarily so? And should I get the network techs to run a tcpip trace?

User_XP6TT

Hi,

SQL*Net message from clinet may even come when the session is idle and waiting for any response from the application/user. or else the application has responded but there may be some delay in the network. So it would be advisable to get the network team involved and check the network bandwidth and traffic from their end.

Trace the network traffic and bandwidth from your source & target server including the entire path of connectivity that includes the core switch of the network as well.