This afternoon I executed a simple DDL statement with Oracle.DataAccess.dll and noticed it took upwards of 30 seconds when it should have come back in a second or so. In stepping through the code I noticed the large delay was in creating the Oracle Connection, not connecting to the database or executing the SQL:

Oh Oracle you are the snake to my mongoose, or the mongoose to my snake. Either way it’s bad.

I vaguely remembered a similar issue years ago that had something to do with a logging directory not existing. I no longer had the email I sent with the details nor could I find the information online. So I fired up Process Monitor and found several writes to the below files:

C:\Users\username\Oracle\oradiag_username\diag\clients\user_username\host_1707249666_80\alert\log.xml
C:\Users\username\Oracle\oradiag_username\diag\clients\user_username\host_1707249666_80\trace\sqlnet.log

In inspecting the log files I found hundreds of occurrences of the below error:

Directory does not exist for read/write 
[C:\app\username\product\11.2.0\client_2\log] []

This was further confirmed in Process Monitor entries:

1:52:13.8086867 PM  OracleScriptExecutor.vshost.exe 8556    CreateFile  
C:\app\username\product\11.2.0\client_2\log   NAME NOT FOUND  
Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, 
Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a

At first I just tried creating the Log directory but that didn’t work. I checked security on the directory but it seemed sufficient. To resolve the error I had to create the folder structure “log\diag\clients” in my “C:\app\username\product\11.2.0\client_2” directory.

Afterwards I could get through the OracleConnection ctor in a reasonable time:

With the details in hand I changed my web search and discovered this Oracle forum entry posted 3 years ago! 3 years and this problem is still not fixed? Come on Oracle, get it together.

3 thoughts on “Slow OracleConnection Creation

  1. David Douglas

    Thanks, this was driving me crazy. new OracleConnection() was taking 130 seconds the first time it was called in a C# program – subsequent calls while the test program was runnig took maybe 100ms (odd, given that the variable was disposed, database connection closed etc and then redeclared). But if i restarted the program, the first call took 130 seconds again.
    Also, if i ran it from Visual Studio, it too 100ms every time!

    So a problem Oracle discovered now 5 years ago is still causing grief. We should, I suppose, upgrade out clients from 11.2.0.1.0, but deployment is a big headache.

  2. John McPherson

    Thank you, thank you, thank you!
    This made my day.

  3. HollyHuo

    That is very helpful, Thank you very much:)

Comments are closed.