OCI Pipelining

22 OCI Pipelining

Pipelining increases the overall throughput and responsiveness of the application. Starting with Oracle Database Release 23ai, pipelining feature is introduced to help the applications utilize the interleaving effectively and keep the server busy by overlapping the application requests, and the responses returned by the server.

This chapter contains these topics:

22.1 Blocking and Non-Blocking Concepts

This section describes the blocking and non-blocking concepts.

Blocking functionality

All OCI functions that make a network round trip are request and response in nature. An application sends a request and waits for a response before sending another request.

For example, OCIStmtExecute() function sends a request to execute an SQL and waits for the response from the server. A single OCI function is executed at a time. If another OCI function is invoked simultaneously without completing the previous request, then the operation results in an error. This model of execution leaves the server in idle state between the current response and the next request. It also leaves the client in an idle state between the current request and its response from the server.

Figure 22-1 Blocking Functionality

Description of "Figure 22-1 Blocking Functionality"

Non-blocking functionality

Non-blocking functionality is helpful to applications as they benefit from using the interleaving to do some other operations during the idle time. This model improves the application responsiveness that is, the client idle time by allowing the application to choose what to do instead of blocking for the responses from the server.

Figure 22-2 Pipelining Functionality

Description of "Figure 22-2 Pipelining Functionality"

22.2 Introduction to OCI Pipelining

This chapter introduces the pipelining functionality.

The fundamental idea of pipelining is to keep the server busy and allow an application to use the interleaving requests and responses appropriately.

The application keeps sending several requests, server builds up a queue and executes them one by one. The server sends the responses back to the client in the same order in which it received the requests.

It is the responsibility of the application to ensure that the requests are independent as it is an essential requirement for the pipeline functionality.

Note:

As the pipeline does not read the responses so often, using the data from a previous SQL response as in-bind data to the subsequent request creates a dependency and breaks the pipeline.

The following diagram shows the end-to-end depiction of a sample scenario in the pipeline:

Figure 22-3 OCI Pipeline Block Diagram

Description of "Figure 22-3 OCI Pipeline Block Diagram"

The OCI pipeline application is processing seven operations simultaneously as shown in the preceding diagram.
The server processes the first request, the response to this request reaches the client. The response is now ready to be read.
The second request is in the process of completion at the server end.
The requests, three to six are queued at the server end and are yet to be processed by the server.
The client is asynchronously sending the seventh request to the server.

The following new functions are introduced:

OCIPipelineBegin(): Indicates the start of the pipeline block of operations.
OCIPipelineProcess(): Processes an operation.
OCIPipelineEnd(): Indicates the end of the pipeline block of operations.

An OCI function that supports pipelining and involves in a round-trip is divided into the following two parts:

The top half (send a request)
The bottom half (receive a response)

The top half of the operation is the request sent to the server for processing. The bottom half is the response received from the server. The response is obtained using either OCIPipelineProcess() or OCIPipelineEnd() function.

With this approach, all the OCI functions that support pipelining (for example, OCIStmtExecute(), OCIStmtFetch2()) in the pipeline operation block are implicitly pipelined.

Note:

In pipeline mode, it is the responsibility of the application to ensure that the dependency on two consecutive operations is resolved or avoided.

Two adjacent blocks of pipeline operations are supported. The following is a sample code snippet:

OCIPipelineBegin();
OCIPipelineEnd();

;;;
OCIPipelineBegin();
OCIPipelineEnd();

Overlapping and nested pipeline blocks on the same service context are not supported. The following is a sample code snippet:

OCIPipelineBegin();
OCIPipelineBegin();
OCIPipelineEnd();
OCIPipelineEnd();

Note:

Starting with Oracle Database Release 23ai, enhancements are being made to both the client and the server, to provide pipelining functionality.

22.2.1 Enabling OCI Pipelining

This section describes how to enable pipelining for OCI APIs.

By default, OCI APIs are not available to an application for pipelining. A new attribute, OCI_PIPELINE_ENABLE, is introduced to enable pipelining. The pipeline functionality is available only when the application sets the attribute on the environment. Any call to OCI pipeline function without this mode set, returns an error.

Syntax

boolean pipelineEnable = TRUE;
status = OCIAttrSet((dvoid *) envhp,
           OCI_HTYPE_ENV,
           (dvoid *) &sts,
           (ub4) sizeof(sts),
           OCI_ATTR_PIPELINE_ENABLE,
           (OCIError *) errhp));

Most of the applications use OCI functions in a blocking or non-blocking mode. OCI_ATTR_PIPELINE_ENABLE attribute helps to avoid performance regressions in blocking and non-blocking scenarios and acts like a switch to disable pipelining in the OCI application.

22.3 Modes of Pipeline Operation

This section describes the different modes of pipeline operation.

The OCIPipelineBegin() function marks the beginning of the pipelined block of operations.

The modes of pipelined operations defines how the pipelined operations are executed. There are two levels of pipeline modes. Each level can set one of the two modes of execution.

Operation level mode (OCI_ATTR_PIPELINE_OP_MODE): The server continues executing the operations even after one of the pipelined operations fails with an error, until OCIPipelineEnd()function is executed.
Applications need to set the OCI_ATTR_PIPELINE_OP_MODE attribute on the handle ((svchp) in the operation using the OCIAttrSet attribute.

Example:
For OCIStmtExecute, the following attribute is set:
```
rc = OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVC,
                (dvoid *) OCI_PIPELINE_CONT_ON_ERROR, (ub4) 0,
                (ub4) OCI_ATTR_PIPELINE_OP_MODE, (OCIError *) 
                errhp);
rc = OCIStmtExecute(svchp, stmthp, … , OCI_DEFAULT);
```
Block-level mode (OCI_PIPELINE_ABORT_ON_ERROR): The pipeline operation aborts if an error is encountered while processing the pipelined block of operations.
If any error is returned in any operation in a pipelined block of operations, then it aborts all the pipelined operations following that operation. All the operations till the OCIPipelineEnd() function returns an error to the client.

The responses of the operations executed successfully before the failure of an operation are returned to the application. The operation that resulted in an error is also a valid operation. The error handle has more details on the error returned.
The following example shows the usage of OCI_PIPELINE_ABORT_ON_ERROR mode:
```
rc = OCIPipelineBegin(svchp, cbk, cbkCtx, errhp, OCI_PIPELINE_ABORT_ON_ERROR);
```
When the block level mode is OCI_PIPELINE_ABORT_ON_ERROR this means the pipeline aborts if an error has encountered while processing the pipelined operations.
The server returns an error (ORA-43610) for each operation until the end of the pipelined operations.

22.4 OCIPipelineOperation

OCIPipelineOperation is an opaque handle representing a pipelined operation in a pipeline block. OCIPipelineOperation contains the information of the OCI operation executed. An operation instance stores the parameters along with the states to complete the top (send request) and bottom (receive response) parts of the pipelined operations.

22.5 The Life Cycle of the OCI Pipeline Handle

This section describes the life cycle of the OCI pipeline handle.

The pipeline operation handle starts with an OCI function in the pipeline operation block. After the top half of the operation (send a request) operation is executed, the state of the operation handle is OCI_PIPELINE_OP_SENT. When a response is received, it changes to OCI_PIPELINE_OP_READY state. After the operation is completed implicitly or explicitly, the registered callback is processed.

Note:

It is the responsibility of the application to keep the OCI call parameters valid until the bottom half (receive a response) of the operation is executed.

Applications can modify the values for IN binds after the previous OCIStmtExecute() function has been completed.

Note:

The identical outbind or define parameters are overridden after the bottom half (receive a response) of the operation.

22.5.1 Status of the Pipeline Operation

This section describes how to obtain the status of the pipeline operation handle.

OCI_ATTR_PIPELINE_OP_STATUS

You can obtain the status of the pipeline operation handle using the OCIAttrGet() attribute. The value can be one of the following:

OCI_PIPELINE_OP_READY
OCI_PIPELINE_OP_SENT

Note:

All the operation handles are freed after the OCIPipelineEnd() operation is executed.

22.6 OCI Pipeline Attributes

This section lists and describes the OCI pipeline attributes.

Following are the OCI pipeline attributes:

OCI_ATTR_PIPELINE_PROCESS_FIRST

Mode: READ

Description: Obtains the first operation in the pipeline queue of the service context.

Attribute Data Type: OCIPipelineOperationID

Example 22-1 Example:

OCIPipelineOperationID first;
status = OCIAttrGet (svchp, OCI_HTYPE_SVC, &first, NULL,
OCI_ATTR_PIPELINE_PROCESS_LAST, errhp);

OCI_ATTR_PIPELINE_PROCESS_LAST

Mode: READ

Description: Obtains the last operation in the pipeline queue of the service context.

Attribute Data Type: OCIPipelineOperationID

Example 22-2 Example:

OCIPipelineOperationID last;
status = OCIAttrGet (svchp, OCI_HTYPE_SVC, &last, NULL,
OCI_ATTR_PIPELINE_PROCESS_LAST, errhp);

OCI_ATTR_PIPELINE_DEPTH

Mode: Write

Description

The depth of the pipeline queue is set to the value of this attribute throughout the pipeline.

The default depth of the pipeline is 256 operations. After 256 operations, one response is processed before enqueuing another request. This way, the depth of the pipeline queue is retained.

Attribute Data Type: ub4 *

Example 22-3 Example:

ub4 depth = 300;
status = OCIAttrSet (svchp, OCI_HTYPE_SVC, &depth, size of(depth), OCI_ATTR_PIPELINE_DEPTH, errhp);

OCI_ATTR_PIPELINE_HANDLE

Mode: Read

Description: The OCI_ATTR_PIPELINE_HANDLE attribute is used on an operation handle to get the actual OCI handle. After you get the actual handle in the operation, you can query for any attribute similar to any other OCI handle for attributes.

Attribute Data Type: void *

Example 22-4 Example:

OCIStmt *updateHandle; 
status = OCIAttrGet (operation, OCI_HTYPE_OPERATION, &updateHandle, NULL, OCI_ATTR_PIPELINE_HANDLE, errhp);

OCI_ATTR_PIPELINE_HANDLE_TYPE

Mode: Read

Description

A callback application can query the handle type obtained from OCI_ATTR_PIPELINE_HANDLE_TYPE attribute.

This makes the programming paradigm easy in callbacks. After you get the operation handle, query for the actual OCI handle and its type and then get the attribute of the OCI handle.

Attribute Data Type: ub4 *

Example 22-5 Example:

ub4 handleType = 0;status = OCIAttrGet (operation, OCI_HTYPE_OPERATION, &handleType, sizeof(handleType),
OCI_ATTR_PIPELINE_HANDLE_TYPE, errhp);

22.7 OCI Functions that Support Pipelining

The following is the list of OCI functions that support pipelining.

Note:

If OCIStmtExecute(), OCIStmtFetch(), and OCIStmtFetch2() requests include OCI objects, then they cannot be pipelined.
OCITransCommit() and OCITransRollback() transaction requests pipelining functions support only local transactions.

OCIStmtExecute()
OCIStmtFetch()
OCIStmtFetch2()
OCITransCommit
OCITransRollback
OCILobAppend
OCILobArrayWrite
OCILobArrayRead
OCILobClose
OCILobCopy2
OCILobCreateTemporary
OCILobErase2
OCILobFileClose
OCILobFileCLoseAll
OCILobFileExists
OCILobFileIsOpen
OCILobFileOpen
OCILobFreeTemporary
OCILobGetChunkSize
OCILobGetLength2
OCILobOpen
OCILobIsOpen
OCILobLoadFromFile2
OCILobOpen
OCILobRead
OCILobRead2
OCILobTrim2
OCILobWrite
OCILobWrite2
OCILobWriteAppend2

22.8 When to Use Pipelining Functionality

This sections describes when to use pipelining functionality.

Pipelining functionality is useful when many small operations are being performed in rapid succession.

For example:

OCIStmtExecute
DDL, DML, Queries, PL/SQL, array inserts, DML with returning clause
Exact fetch, multiple fetches
Fetches from two different statement handles can be pipelined

Pipelining is more performant when the server is distant. That is, when the network latency is high.

Pipelining is less performant when operation has either implicit or explicit dependency on the result of the previous operation. In such cases, the client must introduce a synchronization point and wait for a complete client or server round-trip to get the results it needs.

For example:

Implicit dependency: Execute a statement followed by a fetch on the same statement handle
Explicit dependency: The result from a previous operation is used to bind the next operation execution