Asynchronous Programming and Pipelining

Asynchronous Programming

Synchronous provider database operations, such as opening a connection, executing a query, or reading data, may take significant time to complete. Single-threaded applications block other operations to wait for the original operation to finish. In contrast, assigning the long-running operation to a background thread allows the foreground thread to remain active throughout. The long-running operation no longer blocks other app operations thereby realizing overall improved performance from using asynchronous behavior.

Asynchronous operations are ideal for long running operations whose results or completion are not needed immediately. Use it when user interfaces must remain as responsive as possible or when other operations can execute simultaneously without relying on the asynchronous operation completing.

.NET has a language-level asynchronous programming model. It allows writing asynchronous code without juggling callbacks nor conforming to an asynchronous library, which have historically complicated asynchronous .NET code. This model is called Task-based Asynchronous Pattern (TAP) and developers have found it easiest to develop with compared to earlier asynchronous patterns.

TAP uses the async modifier to specify an asynchronous method. When calling an async method, a task is returned. When the await operator is applied to the task, the current method exits immediately. When the task finishes, execution resumes in the same method. Most importantly, calling an async method does not allocate additional threads. Asynchronous functionality can be selectively added to existing ODP.NET apps to iteratively provide better runtime user experience.

Managed ODP.NET and ODP.NET Core support TAP starting with version 23. The feature is backwards compatible with Oracle Database 19c and higher. ODP.NET async APIs are identical to standard ADO.NET async APIs. Only OracleBulkCopy WriteToServerAsync method is not currently supported. This makes developing ODP.NET async apps easy for experienced ADO.NET developers.

Oracle extends async capabilities to ODP.NET-specific classes as well, including OracleBlob, OracleClob, OracleBFile, and OracleXmlStream. They have async methods for reading, copying, and writing data as these data types can have very large sizes with their operations I/O bound.

Asynchronous ODP.NET sample code:

static async void Main(){
	OracleConnection oc = new OracleConnection(connectionString);

	// Establish a connection, asynchronously
	Task task = oc.OpenAsync(CancellationToken.None);

	// Execute operation(s) that do not require the connection
	Console.WriteLine(“Hello World”);
	OracleCommand cmd = oc.CreateCommand();
	cmd.CommandText = " select * from employees";

	// "await" OpenAsync completion before executing operations needing connection
	await task;

	// Execute the command
	OracleDataReader = await cmd.ExecuteReaderAsync();
}

ODP.NET Async Methods

OracleConnection:

public Task OpenAsync()

public override Task OpenAsync(CancellationToken cancellationToken)

public Task OpenWithNewPasswordAsync(string newPassword)

public Task OpenWithNewPasswordAsync(string newPassword, CancellationToken cancellationToken)

public Task OpenWithNewPasswordAsync(SecureString secureNewPassword)

public Task OpenWithNewPasswordAsync(SecureString secureNewPassword, CancellationToken cancellationToken)

OracleCommand:

public Task<int> ExecuteNonQueryAsync()

public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)

public Task<OracleDataReader> ExecuteReaderAsync()

public Task<OracleDataReader> ExecuteReaderAsync(CancellationToken cancellationToken);

public Task<OracleDataReader> ExecuteReaderAsync(CommandBehavior behavior)

public Task<OracleDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

public Task<object> ExecuteScalarAsync()

public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)

public Task<XmlReader> ExecuteXmlReaderAsync()

public Task<XmlReader> ExecuteXmlReaderAsync(CancellationToken cancellationToken)

OracleDataReader:

public Task<bool> IsDBNullAsync(int i)

public override Task<bool> IsDBNullAsync(int i, CancellationToken cancellationToken)

public Task<T> GetFieldValueAsync<T>(int i)

public override Task<T> GetFieldValueAsync<T>(int i, CancellationToken cancelToken)

public Task<bool> NextResultAsync()

public override Task<bool> NextResultAsync(CancellationToken cancellationToken)

public Task<bool> ReadAsync()

public override Task<bool> ReadAsync(CancellationToken cancellationToken)

OracleBlob:

public Task<Int64> CopyToAsync(OracleBlob obj)

public Task<Int64> CopyToAsync(OracleBlob obj, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(OracleBlob obj, Int64 dst_offset)

public Task<Int64> CopyToAsync(OracleBlob obj, Int64 dst_offset, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleBlob obj, Int64 dst_offset, Int64 amount)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleBlob obj, Int64 dst_offset, Int64 amount, CancellationToken cancellationToken)

public Task<int> ReadAsync(byte[] buffer, int offset, int count)

public override Task<int> ReadAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

public Task<int> WriteAsync(byte[] buffer, int offset, int count)

public override Task<int> WriteAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

OracleClob:

public Task<Int64> CopyToAsync(OracleClob obj)

public Task<Int64> CopyToAsync(OracleClob obj, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(OracleClob obj, Int64 dst_offset)

public Task<Int64> CopyToAsync(OracleClob obj, Int64 dst_offset, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleClob obj, Int64 dst_offset, Int64 amount)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleClob obj, Int64 dst_offset, Int64 amount, CancellationToken cancellationToken)

public Task<int> ReadAsync(byte[] buffer, int offset, int count)

public override Task<int> ReadAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

public Task<int> ReadAsync(char[] buffer, int offset, int count)

public Task<int> ReadAsync(char[] buffer, int offset, int count, CancellationToken cancellationToken)

public Task<int> WriteAsync(byte[] buffer, int offset, int count)

public override Task<int> WriteAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

public Task<int> WriteAsync(char[] buffer, int offset, int count)

public Task<int> WriteAsync(char[] buffer, int offset, int count, CancellationToken cancellationToken)

OracleBFile:

public Task<Int64> CopyToAsync(OracleBlob obj)

public Task<Int64> CopyToAsync(OracleBlob obj, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(OracleBlob obj, Int64 dst_offset)

public Task<Int64> CopyToAsync(OracleBlob obj, Int64 dst_offset, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleBlob obj, Int64 dst_offset, Int64 amount)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleBlob obj, Int64 dst_offset, Int64 amount, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(OracleClob obj)

public Task<Int64> CopyToAsync(OracleClob obj, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(OracleClob obj, Int64 dst_offset)

public Task<Int64> CopyToAsync(OracleClob obj, Int64 dst_offset, CancellationToken cancellationToken)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleClob obj, Int64 dst_offset, Int64 amount)

public Task<Int64> CopyToAsync(Int64 src_offset, OracleClob obj, Int64 dst_offset, Int64 amount, CancellationToken cancellationToken)

public Task<int> ReadAsync(byte[] buffer, int offset, int count)

public override Task<int> ReadAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

OracleXmlStream:

public Task<int> ReadAsync(byte[] buffer, int offset, int count)

public override Task<int> ReadAsync(byte[] buffer, int offset, int count, CancellationToken cancellationToken)

public Task<int> ReadAsync(char[] buffer, int offset, int count)

public Task<int> ReadAsync(char[] buffer, int offset, int count, CancellationToken cancellationToken)

Database Pipelining

Asynchronous ODP.NET calls send requests to the database and read the responses on the client side asynchronously. On the database server, operations are independent of the client and are synchronous by default.

Database pipelining, a new Oracle Database 23ai feature, provides asynchronous execution capability on the database server side. Subsequent operations can send requests even before previous operations responses are received. ODP.NET will still receive results in the same order as their respective commands were submitted to the database.

The key runtime benefit is higher throughput and performance. The database is ready to receive new ODP.NET commands as they arrive instead of blocking until after the previous command completes.

Pipelining requires using ODP.NET asynchronous APIs. Apps can add pipelining to async ODP.NET apps with virtually no code changes. Functionally, there is no difference for the client application when pipelining is enabled or disabled since it is a database server capability.

Managed ODP.NET and ODP.NET Core support database pipelining. By default, the feature is disabled. To enable it, set the OracleConfiguration Pipelining property to true. Alternatively for managed ODP.NET, change the .NET configuration file Pipelining setting to true. No database setup is required other than using Oracle Database 23ai or higher.

ODP.NET Pipelining sample code:

static async void Main(){ 

	//Enable Pipelining
	OracleConfiguration.Pipelining = true;

	OracleConnection oc = new OracleConnection(connectionString);
	await oc.OpenAsync(CancellationToken.None);

	OracleCommand cmd = oc.CreateCommand();
	OracleCommand cmd2 = oc.CreateCommand();

	cmd.CommandText = "update table1 set col1 = 1 where col2 = 2";
	cmd2.CommandText = "update table2 set col3 = 3 where col4 = 4";

	//Execute commands asynchronously with pipelining
	Task<int> task = cmd.ExecuteNonQueryAsync(CancellationToken.None);
	Task<int> task2 = cmd2.ExecuteNonQueryAsync(CancellationToken.None);

	//Execute other operations that do not require query results
	Console.WriteLine(“Hello World”);

	//Await the asynchronous tasks to complete
	int updatedRows = await task;
	int updatedRows2 = await task2;

	Console.WriteLine(“Number updated rows are ” + updatedRows + “ and ” + updatedRows2);
}

Async ODP.NET command cancellation by the OracleCommand Cancel method call, command timeout and CancellationToken is not supported when pipelining is enabled. When pipelining is disabled, async ODP.NET command cancellation and timeout are supported and has the same behavior as synchronous ODP.NET command cancellation and timeout.