2 Replies Latest reply: Mar 18, 2011 2:46 PM by 848587 RSS

    Help needed in debugging callablestatement changing performance

    848587
      Looking for some help debugging a performance problem.

      We are in the process of converting an ASP web application running on IIS to a java web application running on OAS. We have java code using a CallableStatement that calls an Oracle PL/SQL Procedure that runs inserts and updates as needed and does not return anything.

      When the Java application calls the procedure using the same parameters and doing the exact same work the execution time is either 3 minutes or 30 minutes. We have narrowed down the location of the slowdown and when it takes 30 minutes to execute it appears to be hanging during a cursor execution. What would cause the execution times to vary so much?

      Here's another piece to the puzzle. When the procedure is called from IIS with the exact same parameters that were used from OAS the procedure ALWAYS runs in 3 minutes. Why would IIS performance be so consistent and OAS so inconsistent?

      Any debugging suggestions would be greatly appreciated.

      Thanks,
      Bob
        • 1. Re: Help needed in debugging callablestatement changing performance
          jschellSomeoneStoleMyAlias
          user4908334 wrote:
          We are in the process of converting an ASP web application running on IIS to a java web application running on OAS. We have java code using a CallableStatement that calls an Oracle PL/SQL Procedure that runs inserts and updates as needed and does not return anything.
          More information is needed on that.

          There is one proc or 50?

          The proc is being called one time or 50 times?

          You are not using Batching right?
          When the procedure is called from IIS with the exact same parameters that were used from OAS the procedure ALWAYS runs in 3 minutes
          You are running the java server on exactly the same box as IIS?
          The java code is using exactly the same database as IIS? (Did you verify this?)
          • 2. Re: Help needed in debugging callablestatement changing performance
            848587
            Thanks for the quick response.

            There is one proc or 50? 1 proc

            The proc is being called one time or 50 times? The proc is called one time.

            You are not using Batching right? Correct.

            You are running the java server on exactly the same box as IIS? Java and IIS servers are on different boxes. Being on different servers shouldn't matter since the slow performance is inside the database.

            The java code is using exactly the same database as IIS? Yes, both IIS and java using same database. We write parameters and timing lines to a logging table. Log entries showing up for runs from both servers.