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:
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 to “Slow OracleConnection Creation”
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 184.108.40.206.0, but deployment is a big headache.
Thank you, thank you, thank you!
This made my day.
That is very helpful, Thank you very much:）
Comments are closed.