Tag Archives: oracle

Oracle’s 9i ODP.NET Driver is not Thread-Safe

Leave a reply

In the last year I ran into two problems with the ODP.NET driver of Oracle on a Windows platform. Both were related to the use of BLOB fields and the order of CRUD-operations in several threads accessing the BLOB fields in a DB.

Since Oracle is not willing to do anything about it, you might run into in it, even in future releases of the ODP.NET driver. To help you to avoid the pitfall I wrote this document.

Some context
I have .NET application that has a workflow (no WCF, just plain C# code) to handle document transformations in several separate processing steps called Actions (using a pipe-and-filter pattern).

To allow for optimal parallelism every Action is running in its own thread and multiple documents can be crawling through the flow at the same time.
Flow-1
To persist the original message and the final result, we take a copy of the message and store in the DB at specific locations in the flow. We use BLOB fields to store the message content. The original message creates (Insert) a new record in the DB and at the end this record is updated with the final transformation result.

The DB Actions (green) are the asynchronous to the message handling flow (blue): we take a copy of the message but we do not wait on the DB Actions to complete. The message keeps travelling through the flow.

First Problem
Since all the Actions are running on separate threads, they are all waiting on the OS to appoint them some CPU time. Sometimes this means that a message is moving very fast from left to right (the bleu flow)

  • Firstly because DB Actions are slow compared to the basic transformation steps in the other Actions.
  • Secondly because the CPU was just appointed faster to the threads of non-DB related Actions.

This can lead to the DB Update Action firing before the DB Insert Action is finished or even has started.

We’ve foreseen this problem. To avoid a complex scheduling mechanism we just added a retry mechanism to the DB Update Action to allow for the DB Insert to finish.

Technically this all works fine but we ran into a bug of the ODP.NET driver. A really annoying bug because the driver gets in a deadlock resulting in the above system coming to a grinding halt.

What’s happing?
After some time spent on debugging my code I started focusing on the calls to and from Oracle. So I activated Oracle Tracing. You can do this by adding some registry setting to the machine executing your code.

Go to 'KEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version' You'll find two parameters:
  • TraceFileName: any valid path name and file name.
  • TraceLevel: 
    0 = None
    
1 = Entry, exit, and SQL statement information
    
2 = Connection pooling statistics
    
4 = Distributed transactions (enlistment and delistment)
    
8 = User-mode dump creation upon unmanaged exception
    
16 = HA Event Information
    
32 = Load Balancing Information To obtain tracing on multiple objects, simply add the valid values.
    

    • 



Keep in mind that Oracle has made it so easy (pun intended) that it will start tracing any process on the machine that accesses the Oracle DB. So if you do this on a server, you'll end up very fast with some gigs of trace files!


To avoid showing the boring details of the traces, I just give you the results.


Basically it came down to Oracle having multithreading problems in its driver. When accessing BLOB fields, where updates are tried before inserts succeeded on a record, under high load the driver deadlocks.


In the trace file I saw that for BLOB fields, the driver keeps a local list used as temporary holding station to communicate BLOB-s between the .NET client and the DB server. The driver has also span off a thread responsible to clean-up this list. I couldn't deduct whether it was triggered by regular time intervals or based on the list's size. Anyway, it's this driver's proprietary thread that collided with the threads from my code accessing the DB.


The hard time I had to indentify the problem was due to the fact it only occurs when you really put some stress on the system. So if you locally test it on your development machine you'll never encounter it but once in production, under heavy load, your application becomes unresponsive.


Workaround!

When confronted Oracle with this, they just said they concur my results but weren't going to  solve it. So I modified my flow and instead of doing asynchronous inserts (outside the flow) the inserts became part of the flow. The updates stayed asynchronous. The flow now looks like:

Flow-2


Second Problem

Sometimes the flow receives two times exactly the same message as input. When the DB Insert Action tries to create a record for the message it gets a primary key violation because the record is already there.


My first strategy was: keep it simple, just always try to insert and ignore any primary key violation. Apparently Oracle does not like this.


What's happing?

Again the driver gets into a deadlock when the system is under heavy load. Looking at the traces I saw the same problem as explained before: the driver's internal thread colliding with the driver's client thread.


Workaround!

Just always check if the record exists before trying to insert the same record.


Final comments

Looking back at the first problem I'm not that annoyed by the bug. You could say it makes kind of sense of having your data access layer single threaded even if you have multi threaded business logic running above it.


This is not the case with the second problem. As user of an Oracle library I'm not aware of its internals. So even if I use the driver single threaded from my code it can lock up.


You might imagine I'm not that impressed by Oracle anymore. I lost valuable time  in debugging this.



					

		
		
	


	

				

			
						

				Oracle Dynamic Stored Procedures
			

										

					Leave a reply				

					


				

			
Below you’ll find a basic example of Oracle’s Execute Immediate statement. This statement allows executing dynamic queries or DDL statements from within PL/SQL.


In the example we count how many records there are in a specific table with a certain value for a field.


The table, field and value are not known upfront and passed as parameters of the stored procedure. The number of found records is returned as a result.


PROCEDURE DYNAMIC_GET_VALUE
(
	P_TABLE		IN  VARCHAR,
	P_FIELD		IN  VARCHAR,		
	P_VALUE		IN  VARCHAR,
	P_RESULT 	OUT  NUMBER
)
AS
	V_RESULT	NUMBER;
BEGIN
	EXECUTE IMMEDIATE
		'SELECT COUNT('||P_FIELD||')'||
		' FROM  '||P_TABLE||
		' WHERE '||P_FIELD||' = '||P_VALUE 
	INTO V_RESULT;

	P_RESULT = V_RESULT;

	RETURN P_RESULT;
END;



					

		
		
	


	

				

			
						

				Storing .Net Timestamps in an Oracle Database
			

										

					Leave a reply				

					


				

			
The precision of a MS .NET DateTime value is more than an Oracle Date can handle.


MS .NET expresses DateTime up to milliseconds (‘d-MM-yyyy HH:mm:ss.fff’) whereas Oracle only uses seconds as smallest time unit (‘dd-mm-yyyy HH24:MI:SS’). So if you want to store .NET DateTime in an Oracle DB you’ll lose precision. The milliseconds will be truncated.


How to overcome this!


The .NET framework comes to the rescue by allowing every DateTime to be converted into a number. DateTime.Ticks property returns the number of elapsed 100 nanosecond intervals since January 1st 1601 for the DateTime in question. So we just store this in a Number field of Oracle.


A drawback is these ticks are difficult to read. For example: December 14, 2007, at 15:23 becomes 2193385800000000 in the DB. If I want to write a select query to do some lookups I need to convert the dates into ticks and vice versa. This becomes a pain very fast.


Well, it became my pain very fast so I wrote a Oracle package. Remember this is not a full solution since Oracle will truncate the milliseconds. But it is good enough to find, for example, all records between this and that day without requiring to do the conversion in code upfront.


The Oracle Package: (example below)


CREATE OR REPLACE PACKAGE NET_DATE_UTIL 
AS
  FUNCTION DATE_TO_TICKS(P_DATE IN DATE) RETURN NUMBER;
  FUNCTION TICKS_TO_DATE(P_TICKS IN NUMBER) RETURN DATE;
  FUNCTION TICKS_TO_VARCHAR_FRMSTR(P_TICKS IN NUMBER,P_FORMAT IN VARCHAR) RETURN VARCHAR;
  FUNCTION TICKS_TO_VARCHAR(P_TICKS IN NUMBER) RETURN VARCHAR;
END;

CREATE OR REPLACE PACKAGE BODY NET_DATE_UTIL AS

-- NET Ticks expresses a timestamp in nanoseconds since 01-01-1601 01:00:00
DOTNET_BASE_DATE DATE := TO_DATE('01-01-1601 01:00:00','MM-DD-YY HH24:MI:SS');
CONVERSION_FACTOR NUMBER := 24*60*60*1000*10000;

FUNCTION DATE_TO_TICKS (P_DATE IN DATE) RETURN NUMBER IS
BEGIN
  RETURN (P_DATE-DOTNET_BASE_DATE)*(CONVERSION_FACTOR);
END;

FUNCTION TICKS_TO_DATE (P_TICKS IN NUMBER) RETURN DATE IS
BEGIN
  RETURN DOTNET_BASE_DATE + (P_TICKS/CONVERSION_FACTOR);
END;

FUNCTION TICKS_TO_VARCHAR_FRMSTR (P_TICKS IN NUMBER,P_FORMAT IN VARCHAR) RETURN VARCHAR IS
BEGIN
  RETURN TO_CHAR(TICKS_TO_DATE(P_TICKS),P_FORMAT);
END;

FUNCTION TICKS_TO_VARCHAR (P_TICKS IN NUMBER) RETURN VARCHAR IS
BEGIN
  RETURN TICKS_TO_VARCHAR_FRMSTR(P_TICKS,'DD-MM-YYYY');
END;

END;
/


Example:


SELECT NET_DATE_UTIL.TICKS_TO_DATE(TimestampField) 
FROM MyTable 
WHERE TimestampField < NET_DATE_UTIL.DATE_TO_TICKS(SYSDATE);


The two last functions convert the dates into strings. The last function will return the date according to a fixed format ‘D-MM-YYYY’ The first allows you to pass the format, in which you want to date to be returned. Any valid Oracle format is allowed.


SELECT NET_DATE_UTIL.TICKS_TO_VARCHAR_FRMSTR(TimestampField,'M-DD-YY HH24:MI')
FROM MyTable;