2 Replies Latest reply: Dec 20, 2012 11:24 AM by Joe Weinstein-Oracle RSS

    Sessions going on Increasing in the DB

    951325
      Hi All,

      In our environment we are using weblogic on which CRM Application is deployed.

      Initial Capacity -- > 1

      Maximum Capacity -- > 25


      However the sessions in database when we query the below are huge in nos :

      select * from gv$session

      As the sessions at the database end , we are not able to connect to the DB until we manually kill the session

      Could anybody throw some light whether there is any eficient way to handle DB sessions from Weblogic end thru some parameter or Some best practices which could be followed ??

      Thanks in Advance
        • 1. Re: Sessions going on Increasing in the DB
          Fabian
          Hi,

          I m Just Throwing some light,with below few sql queries..this usually helps in my environment..Consult with DB Guy before executing the query in production.

          1)Below Query will show session information and time of execution

          col MODULE for a37
          col osuser for a18
          col username for a10
          col sid for 9999
          col serial# for 9999
          col spid for 999
          col LAST_CALL_ET for 99999
          select a.sid,b.spid,a.username,
          to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.osuser, a.status, a.module,a.last_call_et
          from v$session a, v$process b where a.paddr = b.addr
          and a.status = 'ACTIVE' and a.username is not null
          order by 8 desc

          2)Below Query will show all active session

          set pagesize 120
          set linesize 150
          select username, count(*) from v$session where status = 'ACTIVE'
          group by username;
          column spid format a5
          column sid format 9999
          column serial# format 999999
          column username format A10
          column schemaname format A8
          column osuser format a14
          column machine format a32
          column logondate format a15
          column terminal format a10
          column status format a8
          column module format a25
          select a.sid, a.serial#, b.spid, a.process, a.username,
          to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.osuser, a.machine, a.status, a.module
          from v$session a, v$process b where a.paddr = b.addr
          and a.status = 'ACTIVE' and a.username is not null
          order by b.spid;

          3)Below Query will show inactive plus active
          set pagesize 120
          set linesize 150
          column spid format a5
          column sid format 9999
          column serial# format 999999
          column username format A10
          column schemaname format A8
          column osuser format a18
          column machine format a25
          column terminal format a10
          column logondate format a15
          column status format a8
          column module format a20
          select a.sid, a.serial#, b.spid, a.process, a.username,to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.osuser, a.machine, a.status, a.module
          from v$session a, v$process b where a.paddr = b.addr
          order by 1
          /

          Simple Queries for finding active and Inactive connections at DB End

          select count(*) from v$session where MODULE like '%JDBC%';
          select count(*) from v$session where MODULE like '%JDBC%' and status='ACTIVE';
          select count(*) from v$session where MODULE like '%JDBC%' and status='INACTIVE';

          Regards
          Fabian
          • 2. Re: Sessions going on Increasing in the DB
            Joe Weinstein-Oracle
            make sure your application closes connections asap, and that a connection is a method-level object, not visible or shareable
            between application threads. Make sure the DataSource does not have pinned-to-thread. Then see if your JDBC driver has
            logging to verify when real connections are made and closed. If that doesn't match what the DBMS says, it's a driver/DBMS
            problem.