SQL*Net message from client - Understanding Oracle Idle Waits

SQL*Net message from client

Overview

SQL*Net message from client is an idle wait event. It records the time a server-side Oracle session spends waiting for the client application to send the next network message — a new SQL statement, a fetch request, a bind variable package, or any other protocol message. The database server has finished its last unit of work and is parked, doing nothing, waiting for the client to give it something to do.

In terms of Oracle wait event infrastructure, SQL*Net message from client sits in the Idle wait class. This classification is critically important: idle wait events must be excluded from performance analysis and should never appear in a “top wait events” analysis of real DB time. Oracle’s AWR and Statspack reports filter them out of the top foreground wait events for exactly this reason.

Understanding why this event exists and what it represents:

The Oracle client-server protocol (SQL*Net / Net8 / Oracle Net) is request-response based. The server processes a request and returns a response, then waits for the next request. The SQL*Net message from client event is literally the time the server process is sleeping in a network recv() call waiting for the client’s next message. The higher this time, the longer the application is “thinking” between database calls.

That said, this event is not entirely ignorable. In specific diagnostic contexts, high accumulated SQL*Net message from client time can reveal genuine application architectural problems — just not the kind that internal Oracle tuning can fix.

When This Wait Is a Problem

Normal behavior (safely ignored):

• Appears as the #1 wait event by total time in AWR background events
• Accounts for 80–99% of elapsed session time in interactive applications
• Average wait time ranges from milliseconds to seconds — this is just application think time

Worth investigating when:

• Individual sessions are stuck in this event for very long periods (hours) without any CPU activity, yet the application believes they are active — this may indicate a hung connection or orphaned server process
• Connection pool sizing issues: many sessions stuck in SQL*Net message from client simultaneously while new application threads wait for a connection — indicates the pool is exhausted by parked idle sessions
• Network latency is suspected: if average wait time is measurably increasing over time without application behavior change, network degradation may be adding latency to each round trip
• Long-running open cursors: some applications open cursors and loop over millions of rows fetching one row at a time. The server waits on SQL*Net message from client between each fetch. AWR will show millions of very short waits — application-side batching (array fetch) would dramatically reduce round trips

Key insight for developers: If an application executes 10,000 SQL statements in a loop that could be replaced by a single set-based SQL statement, you will see 10,000 SQL*Net message from client waits per iteration. The fix is application code, not database tuning.

Diagnostic Queries

1. Identify Sessions Parked in SQL*Net message from client

-- Sessions currently idle (waiting for client message)
-- Focus on those idle for suspiciously long times
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.module,
    s.action,
    s.machine,
    s.status,
    s.seconds_in_wait,
    s.last_call_et AS secs_since_last_call,
    s.logon_time,
    s.sql_id,
    s.prev_sql_id,
    -- Format idle time for readability
    FLOOR(s.seconds_in_wait / 3600) || 'h ' ||
    FLOOR(MOD(s.seconds_in_wait, 3600) / 60) || 'm ' ||
    MOD(s.seconds_in_wait, 60) || 's' AS idle_duration
FROM
    v$session s
WHERE
    s.event = 'SQL*Net message from client'
    AND s.status = 'INACTIVE'
ORDER BY
    s.seconds_in_wait DESC
FETCH FIRST 50 ROWS ONLY;

2. Filter SQL*Net Idle Waits from Top-N Analysis (Correct AWR Methodology)

-- Correct way to analyze top wait events — EXCLUDE idle class
-- This is how you should always query V$SYSTEM_EVENT for performance analysis
SELECT
    event,
    wait_class,
    total_waits,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(100 * time_waited / NULLIF(SUM(time_waited) OVER (), 0), 2) AS pct_total
FROM
    v$system_event
WHERE
    wait_class != 'Idle'         -- Exclude ALL idle events
    AND event NOT IN (
        'SQL*Net message from client',
        'SQL*Net message to client',
        'SQL*Net more data from client',
        'client message',
        'pipe get',
        'null event'
    )
ORDER BY
    time_waited DESC
FETCH FIRST 20 ROWS ONLY;

3. Application Round-Trip Analysis — Detect Chatty Applications

-- Sessions with very high parse/execute counts (chatty application pattern)
-- High counts with low rows_processed per execution = too many small SQL calls
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.module,
    ss_parse.value AS parses,
    ss_exec.value AS executions,
    ss_fetch.value AS fetches,
    ss_rows.value AS rows_processed,
    CASE
        WHEN ss_exec.value > 0
        THEN ROUND(ss_rows.value / ss_exec.value, 2)
        ELSE 0
    END AS rows_per_execute
FROM
    v$session s
    JOIN v$sesstat ss_parse
        ON s.sid = ss_parse.sid
    JOIN v$statname sn_parse
        ON ss_parse.statistic# = sn_parse.statistic#
        AND sn_parse.name = 'parse count (total)'
    JOIN v$sesstat ss_exec
        ON s.sid = ss_exec.sid
    JOIN v$statname sn_exec
        ON ss_exec.statistic# = sn_exec.statistic#
        AND sn_exec.name = 'execute count'
    JOIN v$sesstat ss_fetch
        ON s.sid = ss_fetch.sid
    JOIN v$statname sn_fetch
        ON ss_fetch.statistic# = sn_fetch.statistic#
        AND sn_fetch.name = 'session fetch count'
    JOIN v$sesstat ss_rows
        ON s.sid = ss_rows.sid
    JOIN v$statname sn_rows
        ON ss_rows.statistic# = sn_rows.statistic#
        AND sn_rows.name = 'session cursor cache hits'
WHERE
    s.username IS NOT NULL
    AND ss_exec.value > 1000
ORDER BY
    ss_parse.value DESC
FETCH FIRST 20 ROWS ONLY;

4. V$SYSTEM_EVENT — Quantify SQL*Net Round-Trip Volume

-- SQL*Net wait event statistics — understand overall round-trip volume
SELECT
    event,
    wait_class,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(average_wait / 100 * 1000, 2) AS avg_wait_ms
FROM
    v$system_event
WHERE
    event LIKE 'SQL*Net%'
ORDER BY
    total_waits DESC;

5. ASH — Distinguish Genuine Activity from Idle Sessions

-- ASH correctly filters idle sessions (wait_class != 'Idle')
-- But you can explicitly check what sessions were actually doing:
SELECT
    ash.session_id,
    ash.sql_id,
    ash.event,
    ash.wait_class,
    ash.session_state,
    COUNT(*) AS ash_samples
FROM
    v$active_session_history ash
WHERE
    ash.sample_time > SYSDATE - 1/24  -- Last hour
    AND ash.session_id IN (
        -- Sessions currently idle in SQL*Net message from client
        SELECT sid FROM v$session WHERE event = 'SQL*Net message from client'
    )
GROUP BY
    ash.session_id, ash.sql_id, ash.event, ash.wait_class, ash.session_state
ORDER BY
    ash_samples DESC;

6. Connection Pool Starvation Detection

-- Detect connection pool starvation:
-- Many idle sessions (SQL*Net message from client) + few active sessions
-- = Pool is fully occupied by parked idle connections
WITH session_stats AS (
    SELECT
        status,
        event,
        COUNT(*) AS session_count
    FROM v$session
    WHERE username IS NOT NULL
      AND username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'ORACLE_OCM')
    GROUP BY status, event
)
SELECT
    status,
    event,
    session_count,
    ROUND(100 * session_count / SUM(session_count) OVER (), 2) AS pct_of_sessions
FROM session_stats
ORDER BY session_count DESC;

Root Causes

1. Normal Application Think Time (Expected — Not a Problem)

In any interactive or batch application, sessions spend time processing results, performing application-tier calculations, or waiting for user input between database calls. This time appears as SQL*Net message from client. It is expected, normal, and does not indicate any Oracle problem.

2. N+1 Query Pattern (Application Code Issue)

An application executes one query to retrieve N parent rows, then executes one query per row to retrieve child data — resulting in N+1 total database calls. The server spends time in SQL*Net message from client between each of the N child queries. The fix is to replace the N child queries with a single JOIN or bulk fetch. This is an application design problem, not an Oracle problem.

3. Row-by-Row Fetch Instead of Array Fetch

Some JDBC, OCI, or Pro*C applications fetch one row at a time rather than using array/batch fetch. Each fetch is a separate round trip. The server waits in SQL*Net message from client between each fetch. Enabling array fetch (e.g., setFetchSize(100) in JDBC) dramatically reduces round trips and this wait’s contribution to total elapsed time.

4. Orphaned or Hung Sessions

If a client application crashes or disconnects abnormally without closing its connection, the server-side process may be stuck in SQL*Net message from client indefinitely, occupying a connection slot in the pool. These are true “dead” connections that Oracle’s dead connection detection (DCD) or firewall connection reset rules may need to clean up.

5. Network Latency

Significant network latency between the application tier and the database server adds measurable time to each SQL*Net message from client wait. This is visible as an elevated average wait time. If latency increases over time, investigate network path changes, firewall rule changes, or load balancer configuration.

6. Connection Pool Misconfiguration

A connection pool configured with an excessively large maximum size (e.g., 500 connections to Oracle) can exhaust Oracle’s SESSIONS and PROCESSES limits, or fill all available slots with idle connections while the pool thinks all connections are “in use.” The result: new application requests cannot get a connection even though most are idle in SQL*Net message from client.

Resolution Steps

Step 1: Confirm This Is an Idle Event — Do Not Tune It Directly

The first resolution step is education: this event does not require Oracle-side tuning. If you are asked to “fix” high SQL*Net message from client waits, the response is: filter it out of your analysis and look at the remaining non-idle wait events. The correct tool is:

-- The right query: top non-idle wait events
SELECT event, total_waits, ROUND(time_waited/100,2) AS total_secs
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

Step 2: Kill Orphaned Sessions Stuck in Long Idle Waits

-- Sessions idle for more than 8 hours with no activity
-- Candidates for cleanup (verify with application team before killing)
SELECT
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
        AS kill_command,
    username,
    program,
    machine,
    ROUND(seconds_in_wait / 3600, 2) AS idle_hours,
    logon_time
FROM v$session
WHERE event = 'SQL*Net message from client'
  AND seconds_in_wait > 28800  -- Idle more than 8 hours
  AND username NOT IN ('SYS', 'SYSTEM')
ORDER BY seconds_in_wait DESC;

Step 3: Configure Dead Connection Detection (DCD)

-- Enable DCD in sqlnet.ora to automatically detect and clean up dead client connections
-- Add to $ORACLE_HOME/network/admin/sqlnet.ora:
-- SQLNET.EXPIRE_TIME = 10   (probe every 10 minutes)

-- Verify current DCD setting:
SELECT * FROM v$parameter WHERE name = 'sqlnet.expire_time';

Step 4: Implement Connection Pool Idle Timeout

Configure the connection pool to close connections that have been idle for a period (e.g., 30 minutes). This prevents pool exhaustion from parked idle sessions:

-- In UCP (Universal Connection Pool) or equivalent:
-- pool.setInactiveConnectionTimeout(1800);  -- 30 minutes
-- pool.setAbandonedConnectionTimeout(300);  -- 5 minutes

-- Or set profile limits at the Oracle database level:
ALTER PROFILE DEFAULT LIMIT
    IDLE_TIME 30;  -- Disconnect idle sessions after 30 minutes

Prevention & Tuning

Always filter idle waits from performance analysis: Build this into your standard AWR analysis process. The WHERE wait_class != 'Idle' clause belongs in every top-wait query.

Use ASH instead of raw session monitoring for activity analysis: ASH samples only active sessions (by definition, sessions not in idle wait classes). An ASH report showing SQL*Net message from client prominently is a sign that the ASH collection is incorrectly including idle sessions.

Implement array fetching in all client code: JDBC setFetchSize(), OCI prefetch, or Pro*C PREFETCH_ROWS dramatically reduce round trips for result-set iteration. The rule of thumb is to fetch in batches of 50–500 rows depending on row width.

Profile N+1 query patterns during development: Use Oracle’s SQL Trace and TKPROF to identify applications making excessive individual queries that could be replaced by JOINs or bulk operations.

Monitor the ratio of executions to physical I/O: Applications with good SQL design have high execution counts but proportionally high logical reads per execution. Applications with N+1 patterns have high execution counts with very low work per execution.

Related Wait Events

• SQL*Net more data from client — Client is sending a large bind variable or LOB data in multiple packets; not idle
• SQL*Net message to client — Server is sending data to the client; can spike if network is slow outbound
• SQL*Net more data to client — Server is sending a large result set across multiple packets
• pipe get — Idle wait for database pipe message; similar idle semantics to SQL*Net message from client
• client message — Idle wait in client-server message receipt (scheduler job sessions)