Sql-server – Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits

linked-serveroracle-11goracle-12csql server

I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This caused the 11g Client to not be able to connect to the Oracle Instances because of the PATH variable using the 12c TNSnames instead of the 11g TNSnames.

The PATH variable was corrected and the 11g Client can now connect to the Oracle Instances using TNSPing or SQLPlus but when trying to use the linked server the SQL Server will go into PREEMPTIVE mode with the wait type PREEMPTIVE_COM_COCREATEINSTANCE. The linked server query will not make a connection to Oracle or error out and cause the SQL Server to not allow any new connections. They will get stuck trying to login with the wait PREEMPTIVE_OS_GETPROCADDRESS and wait for the Linked Server query to finish in order to login.

Any thoughts on why the Linked Server would be causing long PREEMPTIVE_COM_COCREATEINSTANCE waits?

Best Answer

These calls are all part of loading the Oracle OleDb provider. Not sure why they would block instead of failing, but it suggests that your Oracle client setup is still not not right.

The 11g OraOLEDB.Oracle provider not only needs to access the correct TNSNAMES.ORA file. It also needs to load the correct OCI.DLL, which it does through the path.

OraOLEDB.Oracle, like all OleDB providers, is a COM component. So it's loaded with a call to CoCreateInstance(). The OleDb provider in turn calls calls LoadLibrary() to find OCI.DLL, and then (in the normal DLL-loading process) calls GetProcAddress() to find the expected DLL entry point functions. Any time SQL Server calls one of these Win32 functions it enters a 'PREEMPTIVE_...' waits.

So you need to ensure that the LoadLibrary function call finds and loads the correct copy of OCI.DLL. You typically do this by setting the system path so that the folder containing your desired OCI.DLL appears first, and rebooting.

You can verify you're loading the expected OleDB provider from an elevated powershell prompt by running

PS C:\WINDOWS\system32> ls  hklm:\SOFTWARE\Classes\OraOLEDB.Oracle

to discover the CLSID of the OleDb provider, and then looking it up

PS C:\WINDOWS\system32> ls  "hklm:\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}


Name                           Property
----                           --------
ExtendedErrors                 (default) : Extended Error Lookup Service
InprocServer32                 (default)      : c:\oracle\odac64\bin\OraOLEDB12.dll
                               ThreadingModel : Both
OLE DB Provider                (default) : Oracle Provider for OLE DB
ProgID                         (default) : OraOLEDB.Oracle.1
TypeLib                        (default) : {0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}
VersionIndependentProgID       (default) : OraOLEDB.Oracle

The full process is ProgId `OraOLEDB.Oracle' is resolved to a CLSID. The CLISD is resolved to a .dll (InProcServer32). The .DLL is loaded and it, in turn, runs LoadLibrary to load oci.dll. Any link in that chain can break, including finding a copy of OCI.DLL of the wrong "bitness". CoCreateInstance is bitness-aware, but LoadLibrary is not.

You can also test connectivity in powershell. First ensure you are in a process with matching "bitness" to SQL Server.

PS C:\WINDOWS\system32> [system.environment]::Is64BitProcess

Then

PS C:\WINDOWS\system32> $con= new-object system.data.oledb.oledbconnection
PS C:\WINDOWS\system32> $con.connectionstring="provider=OraOLEDB.Oracle;data source=//server/xe;user id=xxx;password=xxx"
PS C:\WINDOWS\system32> $con.Open()
Related Question