New Concepts in Performance Enhancement with OIC for ATP databases

Executive Summary

Uploading data from a flat file into a database table is a common use case found in many integration applications. Oracle Integration Cloud (OIC) includes the ATP adapter that can read rows of data in chunks from a CSV file and insert them into a table in the ATP database. However, with the support of new cloud libraries available in ATP database, this operation can now be completed much faster. The rest of this blog provides the details about this new performance enhancement that can be achieved with OIC and ATP Adapter to insert data from large flat files into ATP databases.

Solution Architecture

The performance enhancement is demonstrated here by picking a simple integration flow as baseline that picks up a CSV file from the embedded SFTP server, which is now part of OIC-FileServer Gen2. One can learn more about the OIC-File Server in the standard product documentation^[1] and the blog^[2] published by Kundan Vyas of OIC Product Management team. The file is then processed in chunks by the ATP adapter that performs a standard insert operation into an ATP database table. The integration flow is built using basic OIC components, whose details can be found in Oracle Integration Cloud product documentation^[3]. The overall integration architecture is show in Fig. 1 below.

Fig. 1 Baseline Integration Flow - Loading data from CSV file into ATP database table using insert operation of ATP adapter

Next, to demonstrate the performance enhancement, the same file is read from the same SFTP server location and uploaded to an object storage location within OCI via a REST adapter invoke. The ATP adapter then invokes a PL/SQL procedure COPY_DATA from the standard package library DBMS_CLOUD, which reads the file from the object storage location and inserts the data into the target table within ATP database. More details about the new PLSQL package library can be found in the ATP documentation^[4]. Fig. 2 describes the modified integration flow for the performance enhancement.

Fig. 2 Enhanced Integration Flow - Loading data from CSV file into ATP database table by calling PL/SQL library from ATP Adapter

Configuration Process

The configuration process is described in 2 sections. First, we build the standard integration flow for baseline. Next we build the enhanced integration that uses the PLSQL libraries of the ATP database.

Standard Integration Flow

Steps to build the standard integration flow are described below.

1. Configure the OIC fileserver for enabling the SFTP server functionality. Details of this configuration can be found in the OIC Fileserver documentation^[1]. 
2. Create an SFTP adapter connection to pick up the file from the embedded OIC fileserver. 
3. Create an ATP Adapter connection to point to the target ATP database.
4. Build the OIC integration flow for the baseline as shown in Fig. 1. In the ATP Adapter wizard, select the insert operation option with the target table in the database.
5. At the end, the integration flow should look like the one shown in Fig. 3.

Fig 3. Standard flow in OIC

Enhanced Integration Flow

Steps to build the enhanced integration flow is described below.

1. Use the same SFTP connection built in Step 1 of previous section.
2. Use the same ATP database adapter connection from Step 2 of previous section.
3. Create a REST API connection that will be used to upload a CSV file to an OCI object storage location.
4. Create a PL/SQL credential in the database to access the object store using DBMS_CLOUD.CREATE_CREDENTIAL procedure.
5. Create a PL/SQL wrapper package and procedure to call DBMS_CLOUD.COPY_DATA using the credentials from step 4 and the target table in the ATP database for inserting the data.
6. Build the OIC integration flow for enhanced performance as shown in Fig. 2. This time inside ATP adapter wizard, instead of selecting the insert operation, choose the PLSQL procedure option and select the wrapper procedure created in step 5. 
7. At the end, the complete end-to-end flow should look like the one shown in Fig. 4.

Fig. 4 Enhanced Flow in OIC

Testing Methodology

A CSV file containing text data was placed in the OIC fileserver area. OIC integration flows for the 2 options were submitted to pickup the CSV file via SFTP adapter and load the data into an empty database table. After completion, the Activity Stream in the monitoring section of OIC console was used to note down the times spent in each part of the flow and the total time for the end-to-end process. The integration flow was repeated twice for each file and the average time was then calculated for reporting purposes.

Different file sizes tested are tabulated below in Table 1.

Table 1. CSV Test File Size Details

Results

The results for the baseline integration flow with different file sizes are shown in Fig. 5 below.

Fig. 5 Results for Baseline Integration Flow

The results for the enhanced integration flow with different file sizes are shown in Fig. 6 below.

Fig. 6 Results for Enhanced Integration Flow

To summarize, all the times noted for different parts of the flow are tabulated and shown below in Table 2.

Table. 2 Summary of Test Results

Analysis

A comparison of the results shown in Table 2 clearly demonstrates the significant performance gains achieved by using the PL/SQL cloud libraries in ATP database. To provide an alternate perspective of the improvement, the corresponding completion times of the integration flows are plotted side-by-side in a cluster bar chart and shown in Fig. 7 below.

Fig. 7 Comparison of End-to-End Completion Times for both Integration Flows with different record counts in CSV file

Summary

Although the integration flow had to implement an extra hop via the Object Storage Cloud Service to leverage the use of new ATP cloud libraries, the performance gains clearly justifies the overall approach. The simple comparison exercise was carried out in a lab environment primarily to highlight the capability of the new cloud library in ATP databases. The documentation for the library provides more information on advanced options that can be used to address complex use case requirements in real life situations.

Acknowledgements

At the end, I would like to acknowledge Kundan Vyas of OIC Product Management, who provided valuable assistance with OIC Fileserver and Cemil Alper of Database Product Management, who introduced me to the new PL/SQL cloud library in ATP database. 

References

1. OIC Fileserver Documentation - Oracle Product Documentation
2. Leveraging Oracle Integration File Server for File Based Integrations - Blog by Kundan Vyas, OIC-FileServer Product Management 
3. OIC Documentation - Oracle Product Documentation
4. Autonomous Transaction Processing Database Library Documentation - Oracle Product Documentation