ODP.NET freezing on ExecuteNonQuery

[mtessier]

Hello All,

I'm not sure if I'm posting this to the right area, so let me know if I should post it elsewhere.

I'm having an issue calling PL/SQL stored procedures in ASP.NET 2.0 using ODP.NET (Oracle.DataAccess assembly version 9.2.0.700). Many times the web page freezes during processing and I have to close the browser and try again. During debugging, I found that the freezing happens on the ExecuteNonQuery line of a stored procedure call. This behaviour is unpredictable as sometimes it works and sometimes it freezes, even WHEN using the same parameters. This happens when I try to run the web site hosted on the web server (Win2K3 web edition, IIS6) and also when I run the web site from my desktop using ASP.NET Development Server.

The stored procedures are quite complex and return an XML document in an XMLType output parameter. When the stored procedures return without freezing the web page, they are fairly quick. During a previous phase to this project, the same PL/SQL techniques were used to return a CLOB from a PL/SQL function to classic ASP pages and they executed flawlessly (and still do). I've seen the freezing happen on a PL/SQL function that was unchanged from the previous phase of the project. The PL/SQL functions and procedures reside in packages (in most cases, it's the same package).

Here's a typical stored procedure call (I stripped down exception handling for simplicity's sake. No exception is thrown when it freezes):

Using odpConn As OracleConnection = New OracleConnection(ConfigurationManager.ConnectionStrings("MBS").ConnectionString)
    'Open database connection
    Try
        odpConn.Open()
    Catch ex As Exception
        'Database connection error
        Return ...
    End Try

    Try
        Using odpCmd As OracleCommand = odpConn.CreateCommand()
            'Execute Stored Procedure
            odpCmd.CommandType = CommandType.StoredProcedure
            odpCmd.CommandText = "CSE_OWNER.CSE_ADMIN.MEMBER_SEARCH"
            odpCmd.Parameters.Add("ClientID", OracleDbType.Varchar2, clientID, ParameterDirection.Input)
            odpCmd.Parameters.Add("UserID", OracleDbType.Varchar2, userID, ParameterDirection.Input)
            odpCmd.Parameters.Add("GroupCriteria", OracleDbType.Varchar2, groupCriteria, ParameterDirection.Input)
            odpCmd.Parameters.Add("GroupSearchType", OracleDbType.Varchar2, groupSearchType, ParameterDirection.Input)
            odpCmd.Parameters.Add("MemberCriteria", OracleDbType.Varchar2, memberCriteria, ParameterDirection.Input)
            odpCmd.Parameters.Add("MemberSearchType", OracleDbType.Varchar2, memberSearchType, ParameterDirection.Input)
            odpCmd.Parameters.Add("ResultsPerPage", OracleDbType.Int16, resultsPerPage, ParameterDirection.Input)
            odpCmd.Parameters.Add("UserType", OracleDbType.Varchar2, userType, ParameterDirection.Input)
            odpCmd.Parameters.Add("out_xml", OracleDbType.XmlType, ParameterDirection.Output)

           '********* FREEZES WHILE RUNNING THIS LINE **************
            odpCmd.ExecuteNonQuery()

            ' Cast result to OracleXmlType and save cast into xmlResultsOracle
            Dim xmlResultsOracle As OracleXmlType = CType(odpCmd.Parameters("out_xml").Value, OracleXmlType)

            ' Return XMLDocument
            Return xmlResultsOracle.GetXmlDocument()
        End Using
    Catch ex As Exception
        Return ...
    Finally 'Close Connection
        If odpConn.State = Data.ConnectionState.Open Then
            odpConn.Close()
        End If
    End Try
End Using

Any ideas?

Thanks in advance,

Mike

 

[mtessier]

Oops, forgot to mention that we use Oracle 9i.

Thanks,

Mike

 

[jmeckley]

strip out all the try/catches and let the exception, if any, bubble up. handle this exception at a higher level in the application.

there are many articles online about proper usage of exceptions. some are language specific, others are agnostic to language, dealing strictly with the concept.

I would recommend this so you can consolidate, and better handle your errors.

as for the proc issue. what issues, if any do you encounter when executing directly on the database?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mtessier]

Hi Jason,

Thanks very much for the reply. I tried stripping out the try/catches and it didn't make a difference. No exception has ever been thrown when this freezing behaviour happens.

I have a developer looking at the PL/SQL of the procedure that exhibits this behaviour the most often to try and optimize the code. He says it runs fine when executed directly on the database. The XML that gets returned from this stored procedure gets inserted into a table before being returned to the UI via an XMLTYPE output parameter. I just found out that when the procedure call freezes on the web, the XML doesn't make it into the table so the procedure must not complete.

Anybody know of any issues with XMLTYPE (in general or as an output parameter)?

Thanks,

Mike

PS. The code I attached came from my data access layer. I wrote the exception handling that way so that we can display a more specific error message instead of a generic "an error occured" error message on database calls. When an exception occurs in the attached code, I return an XML document containing an error code relating to the error. The web page takes that error code and displays the friendly error message associated with that code, in English or French depending on the user's specified language. Truth be told if the programmer did his job right, the user should never see these errors (unless the database crashes).

 

[taree]

why do not you just run your procedure and see if it is slow.

 

[jmeckley]

as taree recommends, isolate the DAL code and confirm this is the actual problem. using a test framework like nunit or mbunit makes this very simple.

PS. The code I attached came from my data access layer...

doesn't matter, the same principles apply.

another preformance boost is the consolidate your database connections. currently it appears you are opening/closing a connection per method call. This is highly inefficient and creates duplicated code across your system. I would recommend the Unit Of Work concept. The idea is to encapsulate all the "work" within an atomic unit. All changes within the UOW are either completed (commit), or undone (rollback).

With a web application this is very simple. Create an IHttpModule with a begin and end request handler. When the request begins, open a connection. When the request ends, dispose. You can also incorporate transactions into this.

Store the connection in the Context.Items so you have access to the instance across the request. Then pass the connection to any objects that require it (you DAL). I prefer constructor dependency injection, but a property injection also works.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mtessier]

As I previously mentioned, when the procedure calls return successfully they return fairly quickly (5 seconds max), so the procedures aren't super-slow. I have breakpoints setup in my DAL on all stored procedure calls and I have a list of 6 of them that have exhibited the freezing behaviour. The PL/SQL package that gets used most often in this web application has about 60 procedures in total.

Unfortunately, my employer is very strict about what software gets used on what machines. No one has admin privileges on their individual desktops, so I can't just go and install a test framework on my own without going through the very painful approval process.

Thanks,

Mike

 

[jmeckley]

5 seconds to get data, or 5 seconds to run through the complete request/response? either way I would consider that a long time. if this is a common part of the app that is accessed by many users I would find the bottleneck and refactor.

If this is not a major feature, i would keep my eye on it as this could become an issue.

Unfortunately, my employer is very strict about what software gets used on what machines.

understandably. my boss doesn't want me downloading and installing anything i want. But there is value in using 3rd party tools. Until you ask you know the answer is "no". all the major tools for .net development (both $ and OSS) have good online documentation, trail versions (if $) and active communities. with some research and a written proposal what have you got to loose?

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mtessier]

That would be 5 seconds for a complete request/response and even that's only in cases where hundreds of rows of data are processed as XML. Don't see it that slow often enough for it to be a concern.

I may have stumbled on something that did the trick yesterday afternoon. Our connection string had "Pooling=True" in it, so we tried changing it to False. Ran the web app several times (both with breakpoints and without breakpoints) and it looked to be fine. However, this opens up another question because there are other areas of our web environment that are working well with Pooling set to True. Does anyone have any knowledge of connection pooling issues involving Oracle 9i or ODP.NET?

We also looked at the v$session view on our Oracle database. I changed the Pooling back to True in the web.config's connection string and tried running the web app again. The web app froze on the first try and one record was added to the v$session view for this connection. I closed the web browser and tried running the web app again. This time it worked and 4 records with the same logon_time were added to v$session for this connection. I realize that this is not an Oracle forum, but does anybody have any idea how to interpret these results?

Thanks,

Mike

 

[Cookstarr]

Hi Mike,


I know this was a while back but did you ever find a resolution to this? We are having a similar issue with ODP.net connection pooling.

In response to your last comment and what I know about pooling the default pool size is 5, and therefore when you first open a connection you should really see 5 connections in v$session. We are getting that far but subsequent calls are freezing in a similar way to you described. Any ideas?

Thx.

 

[mtessier]

Hi Cookstarr,

No, we never found a proper resolution to this problem. Setting Pooling=False in the connection string is the solution that has worked so far.

Thanks,

Mike