Before a month or so, I was given an interesting PL/SQL code for tuning. Its function was to read certain data from database and then export them into a flat file. I was told that customer is complaining that time to export usual batch of data (not very large) is unacceptable. So it was typical "make it faster" problem.
As I was reading through and analyzing the code, I've decided I'm going to make a blog from that as it had all the nice mistakes in one place.
All we'll do in our first post is that we are going to setup our example. After that, in the mean time, you can read the code and try to identify mistakes and slow parts. Also you can try to construct your own faster solution and then you can compare it with next blog posts.
The code below is given for demonstration purposes only and replicates all the mistakes and tuning issues original program had. I’ve also cut some code to make it more readable.
DDL script to setup our data model and populate it with data:
Package specification and body:
To test it, just run following code:
SET SERVEROUTPUT ON;
BEGIN
data_to_ascii.load_tmp_table;
data_to_ascii.create_blob;
END;
/
BEGIN
data_to_ascii.load_tmp_table;
data_to_ascii.create_blob;
END;
/
Continue to Part 2
No comments:
Post a Comment