Search
Recent Tweets
« Red Gate Schema Compare for Oracle Review | Main | TFS Artifact Manager »
Friday
Aug192011

Slow OracleConnection Creation

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.

PrintView Printer Friendly Version

Reader Comments (2)

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.

September 3, 2013 | Unregistered CommenterDavid Douglas

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

March 13, 2014 | Unregistered CommenterJohn McPherson

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>