Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Connection Headaches

    DB Server:
    Windows 2003
    Oracle 10.2.0.2
    4 GB RAM (/3GB switch enabled)


    Web Server:
    Windows 2000 (SP4)
    Oracle JDBC thin driver (ojdbc14.jar) ** UPDATE: see below **

    I'm pretty sure this is an application issue, but I am having a devil of a time pinpointing the precise source of the problem. Here is what I am seeing:

    1. In the listener log file, I am seeing 12-15 connection attempts per second
    2. In performance monitor, I see the number of open handles for the Oracle process increase linearly with time (never going down, always going up);
    3. In v$session, I see the number of open connections fluctuating between 30 and 50 with a mean of 40.
    4. CPU utilization is low (10-15%)
    5. Hard pages/sec will start out low, but at a certain point will skyrocket to over 500 pages/sec.

    Eventually what happens is the oracle instance chews up all the available memory and system resources and the Windows server comes crashing down (bad Windows, bad).

    I guess what's got me is that the listener log file shows so many connection attempts, but the number of user sessions is very low. Also, why is Oracle not releasing (closing) open handles? The line on the perfmon graph is as straight as any y= mx + b graph that I ever saw in Algebra.


    The database is blowing up approximately every 2-6 hours depending on how much monitoring I am attempting to do. If anyone has any thoughts, I would be grateful.

    Regards,

    hmscott
    Last edited by hmscott; 05-02-06 at 18:02.
    Have you hugged your backup today?

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Does the sessions increase while running any application process?
    This sounds like a parallelism issue on bad programmed process.
    Check for the degree on the all_tables view to see if is set to 1 or more than 1.

    Or... perhaps the problem is that the application is not using a connection pool and it's creating a new connection every time they're running something.
    That's just bad programming and IT HAPPENS!

    Saludos,
    DKG.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    What does Oracle do with a connection that sends a malformed query or sends the wrong parameters?

    In our case, what we are seeing is a dozen or so malformed calls to a stored proc per second.

    Okay, so the obvious answer is to go back to the developer and have them fix their code. But it still begs the question: what is Oracle doing that the number of open handles just continues to increase with no handles being closed. The connections aren't their, v$session says that only about 40 sessions are open.

    I don't get this.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    More information:

    Part of the application is written in VB6. The connection is made with Oracle 8i client drivers (client install of Oracle 8.1.7.0). The connection string being used is:
    Code:
    Provider=MSDAORA.1;Password=XX;User ID=XX;Data Source=XX;Persist Security Info=True
    Already, I can sense that much of this is bad. I don't know why we are using Oracle 8i drivers with a 10g database, but the vendor claims that it will take too much time and effort to rip out 8i and replace it with 9i drivers.

    The next bit of info that I have is that the application is making frequent calls to stored procedures in the database. the procs are defined with cursor type output parameters:

    eg:
    Code:
    PROCEDURE sp_GetAvailableReports
                (usr_id IN tusr_user.usr_id%TYPE, curResults in out PKG_REPORT_SERVER.TCursor)
    Apparently, VB6 does not handle an output parameter of type cursor. In the code, the procedure is called as follows:

    Code:
    Public Function GetAvailableReports(ByVal objSession As Object) As ADODB.Recordset
    
      strSPName = "PKG_REPORT_SERVER.sp_GetAvailableReports" 
      Set GetAvailableReports = Exec(Me, eRecordset)
    
    End Function
    We're seeing packet errors in a network trace showing responses from the database server with "wrong number or type of arguments". The developer says that everything is fine (because the proc runs and the client gets the data). I'm wondering what happens with that return parameter when the client throws it away.

    Anyway, we continue to see a rising thread count on the Oracle process. The rise is absolutely linear with no dips or declines.



    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by DKG
    Does the sessions increase while running any application process?
    DKG.
    Sorry, I don't think I ever answered this one. The number of active session (v$session) in Oracle remains stable, fluctuating between 35 and 45. It's like a connection is being made, a dedicated process created, the connection does what it does and then releases, but Oracle never releases the resources from the dedicated process.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    below is real UGLY code, but it does the job.
    You might want to consider implementing something similar & leave it enabled for a reasonable period of time.
    Code:
    CREATE OR REPLACE TRIGGER LOGON_TRIGGER
    AFTER LOGON ON DATABASE
    DECLARE
    v_user_identifier varchar2(64);
    v_ip              varchar2(16);
    n_SID NUMBER;
    n_cnt NUMBER;
    v_machine       varchar2(32);
    v_username      varchar2(32);
    v_program       varchar2(48);
    v_osuser        varchar2(32);
    TIC       VARCHAR2(1)    := CHR(39);
    BEGIN
    select SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO V_ip from dual;
    select count(*) INTO N_CNT             from v$session ;
    select sid INTO N_SID             from v$mystat where rownum = 1;
    select machine, username, program,osuser INTO v_machine, v_username, v_program, v_osuser from v$session where sid = n_sid;
    insert into dbadmin.sessions_log values(sysdate,v_ip,v_machine, v_username, v_program, v_osuser,n_cnt);
    commit;
    END;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by anacedent
    below is real UGLY code, but it does the job.
    You might want to consider implementing something similar & leave it enabled for a reasonable period of time.
    Yowza! Thanks!

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You say active sessions from v$session stays constant, but what about inactive sessions? Could it be the connection is never being released by the app after it exceptions, thus becoming inactive but still connected?

    Alan

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by AlanP
    You say active sessions from v$session stays constant, but what about inactive sessions? Could it be the connection is never being released by the app after it exceptions, thus becoming inactive but still connected?

    Alan

    This is exactly what I was wondering but can't figure out how to prove. When I said "active connections" in v$session, I am doing a count(*) with no where clause.

    It turns out that I was wrong about which server is causing the problem. Please don't ask me how I could make such a basic mistake, I'll plead fatigue.

    The problem child is an application server running Tomcat. We've asked the vendor to look at the code; I'm hoping that they can find something in there.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •