Session in "Sql*net message from client", but is active

Question

I've Googled around, and my impression is that

Sql*net message from client

suggests the Oracle DBMS is waiting for the client to send new commands to the DBMS, and therefore any time spent in this event should be client-side time and not consume DB server CPUs. In other words, normally, if a session is in this event, it should be "INACTIVE" rather than "ACTIVE".

What's puzzling to us is that starting from this week (after we started using connection pools [we use dbcp]), we occassionally see sessions in the

Sql*net message from client

event and showing "ACTIVE" at the same time for extended periods of times. And during all this time, CPU usage on the DB is high.

Can anyone shed some light on what this means? If the DB session is waiting for the client to send a message, what can it be "ACTIVE" and consuming CPU cycles for?

Answer 1

Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, so it could cause all sorts of funny problems.

Our next step is to find out how ojdbc14 sneaked into the deployment in the first place.

Answer 2

If the session is active and and on an idle event it's probably on the CPU and not waiting. You can run a query like the following to see for sure

select
    nvl(s.username,s.program) username,
    s.sid sid,
    s.serial# serial,
    s.sql_hash_value sql_hash_value,
    substr(decode(w.wait_time,
           0, w.event,
          'ON CPU'),1,15) event ,
    w.p1  p1,
    w.p2  p2,
    w.p3  p3
  from         v$session           s,
                 v$session_wait   w
  where     w.sid=s.sid
  and s.status='ACTIVE'
  and s.type='USER';

but this kind of data collection is done automatically and collected in v$active_session_history, thus you can just query v$active_session_history to see what the session is really doing