Oracle as a processing engine: loading into Oracle Database
Published on: Author: Team Communication Category: OracleSenior Oracle Developer Patrick Barel specializes in optimizing the Oracle Database. His client, Alliander, transfers large amounts of data, such as measurements and readings, from an online system to the Oracle Database using file transfer. This used to be done with a C# program. This went well during the day. But at midnight, the file size was so large that the processing time increased to 2 hours. Cleverly using the power of the Oracle Database, Patrick reduced the processing time to less than 5 minutes.
Patrick explains: “There wasn’t that much going on during the day. Every 5 minutes, files with changes were offered. Processing these files took under 5 minutes. The problem was the large file at midnight. It meant data was not up to date for a few hours. Seeing how we move more and more towards a 24/7 society, a solution was needed.”
The power of an Oracle Database
“The first thing I did was bring the processing of the file to Oracle. The C# program caused the file to be dumped into a ‘staging’ table via a bulk operation. PL/SQL code was then able to work with the data. By using the power of Oracle Database – specifically the power of the SQL engine – the processing time of the entire file could be reduced to less than 5 minutes.”
Optimal use of time saved
The time saved during the night was reason to leave the file processing to the Oracle Database. Patrick: “First, I put all the files in the Oracle Database Server. From the server, I approached the files as ‘normal’ tables, using External Tables. The processing time is now so fast that we increased the frequency of certain files from every 5 minutes to every minute. In theory, we could go even faster. But the providing system can’t deliver the files faster.”
Instrumentation in Oracle Database
There is another benefit to using the Oracle Database as a processing engine. Patrick: “Now, we can do the instrumentation directly in the Oracle Database. I use the Logger Framework for this. By instrumenting the code properly, we can exactly follow the flow of the code. The code from the Logger Framework is only executed when the log level is at the right value. This way, code is turned on when needed, without having to bring new code to production.”
More information?
Want to know more about Oracle as a processing engine? Please contact us at sales@qualogy.com or 070 319 5000.
