This content has been marked as final. Show 3 replies
user8610659 wrote:Oracle DB better reports reality than you.
I am getting "sqlnet message from client" in top 5 wait events and sql*net trace .We are getting waiting for SQL*Net message from client for a huge number of sessions with huge total waits. From session level sql tracing (using sys.dbms_system.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE) we figured that the whole query took 7 to 8 Hours to complete. But in trace we see query executed within 10 to 12 minutes and after that it was showing waiting for SQL*Net message from client till next 7 hours.
idle_session_time out creates lots of issue. So, can anyone suggest some useful tip from db/application/network side?
Oracle DB can NOT control what happens at the client & can only report what actually occurs.
If client issues SQL & then goes home, Oracle will patiently wait for the next SQL.
What you report is normal & expected behavior & does NOT indicate any problem that needs to be fixed.
From session level sql tracing (using sys.dbms_system.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE) we figured that the whole query took 7 to 8 Hours to complete. But in >trace we see query executed within 10 to 12 minutes and after that it was showing waiting for SQL*Net message from client till next 7 hours.It means that after your query was finished, you session sat idle untill you loged ou this session or you disabled session trace. This wait event is not a database side processing time. You can just open a session with database and you will see this wait event in action because this is "idle wait".
To add to the existing explanations - there are major differences between stateful client-server and stateless client-server.
In stateless client-server (typical web browser client and web server), each and every client request is treated as a brand new client request and session.
The server sees all these requests as unique and isolated and dedicated client requests. There is no single client session for that browser on the web server. If the web server needs a cookie from the client, it is going to ask for that cookie with each client request. It does not remember that it already asked and received a cookie from that client with its previous request. Each request is a brand new client request. The web server does not keep and maintain client state.
Database client-server is stateful. The database do keep client state - there exist a client session. The client initiates that session with the server. The client can then issue (in sequence) requests to the server. 100s of 1000s of requests after one another using the same session. With the server responding to these requests - and maintaining session state for that client session (e.g. what the client's state is fetching a 100 rows at a time, via fetch requests, from a million row table).
As session state exists, and as multiple requests can be issues within that session, the server sees periods between client requests as idle time. Client runs a SQL. Server services it. It waits (idle) for the next request.
This does not happen in stateless client-server as each request is a brand new "session" that only exists for the duration of that request. The web server therefore does not see idle clients. The stateful database sever does see idle clients - sessions that are not at the moment active as the client is busy doing something (or waiting for user input) and the client has no request to give to the server to process.
And this idle time of the server is in Oracle seen as waiting for SQL*Net messages (requests) from the client.
Not only not unusual - it is expected. A standard feature and standard behaviour of stateful client-server.