Think about your rubbish bin for a second. Because, clearly this is going to be an oh so obvious metaphor leading into UTL_FILE right?  OK, maybe a little explanation is needed. I have a basket next to my desk into which I throw any waste paper. It is where I throw my stupid ideas and broken dreams

Melancholy aside, once it is full I’ll take it out and empty it into the main recycling bin that is collected once a fortnight from my street front.

Metaphor make sense now? Still No? OK, let me bring it around to the database. When you make changes to data in the database, most of the time these changes are only made to data blocks in memory. Obviously we have to keep a permanent record of the changes in the redo log to ensure that database recovery is possible, but the changed data blocks themselves are only dumped out to disk on an intermittent basis. That way, the process of changing data in the database is nice and efficient.

That is similar to what I do with the waste paper basket. I don’t take each piece of paper out to the street front recycling bin; I wait until the waste paper basket is full and then just do the one trip. It is more efficient. (This is hopefully your light bulb moment on my metaphor skills )

So back to UTL_FILE. You can take the same approach when you need to unload some data using UTL_FILE. There is an inherent overhead with every call you make to write out a line of output to a flat file using UTL_FILE. Hence, if you can minimize the number of calls you make, you’ll see some benefits.

Here’s an example of that in action. I’m building a simple CSV file based on a table called T which contains approximately 40 million rows.  Here is my first cut at the solution which I’d wager most people have written in their IT lives. We simply loop through each record and write it out to the file:

SQL> set serverout on
SQL> declare
  2    f utl_file.file_type;
  3    line varchar2(255);
  4    s timestamp;
  5  begin
  6    f := utl_file.fopen('TMP','demo.dat','w');
  7    s := systimestamp;
  8    for i in ( select * from t )
  9    loop
 10      line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
 11      utl_file.put_line(f,line);
 12    end loop;
 13    dbms_output.put_line(systimestamp-s);
 14    utl_file.fclose_all;
 15  end;
 16  /
+000000000 00:04:07.368000000

PL/SQL procedure successfully completed.

I’d contend that four minutes for 40 million records is pretty good, but maybe we want to go faster.  Based on my metaphor it might not be immediately apparent how could I perform less UTL_FILE calls? Surely I need to call it once per line of data written? But don’t forget that a “line” in a file is merely the data terminated with a end-of-line pattern specific to your platform. It could be ASCII 10 or an ASCII 13,10 pair, but either way, it is really just an extension of the data you need to write to the file. A “line” is merely our interpretation of that data where that pattern means move to the next line.

So rather than call UTL_FILE for each line, I’ll build up multiple lines and then write them out with a single UTL_FILE.PUT_LINE call. I’m choosing a cap of around 300 lines to ensure that my concatenation does not exceed the maximum size of a VARCHAR2, but as a result I should cut down on the number of UTL_FILE calls by a factor of 300.

SQL> set serverout on
SQL> declare
  2    f utl_file.file_type;
  3    line varchar2(255);
  4    big_line varchar2(32767);
  5    s timestamp;
  6    cnt pls_integer := 0;
  7  begin
  8    f := utl_file.fopen('TMP','demo2.dat','w',32767);
  9    s := systimestamp;
 10    for i in ( select * from t )
 11    loop
 12      line := i.object_id||','||i.object_name||','||i.object_type||','||i.data_object_id;
 13      cnt := cnt + 1;
 14      if cnt < 300 then
 15         big_line := big_line || line || chr(10);
 16      else
 17         utl_file.put_line(f,big_line||line);
 18         cnt := 0;
 19         big_line := null;
 20      end if;
 21    end loop;
 22    utl_file.put_line(f,big_line);
 23    dbms_output.put_line(systimestamp-s);
 24    utl_file.fclose_all;
 25  end;
 26  /
+000000000 00:02:23.297000000

PL/SQL procedure successfully completed.

That’s pretty cool. We reclaimed around 100 seconds of elapsed time just by reducing the number of UTL_FILE.PUT_LINE calls. In this particular instance, that’s about 40% but obviously your mileage may vary based on a myriad of factors – so make you sure do your own benchmarking on your own systems.

As you can see, with just a couple of lines of extra code, we can really make UTL_FILE sing from a performance perspective. Having said that, in an upcoming blog post I’ll make a bold assertion – that you probably don’t need to use UTL_FILE ever again!  Stay tuned for that one.

My friends at Priority Bicycles have released a new model bicycle with a German-made gearbox that is sealed against the elements and is centered on the bike for optimal weight distribution. While it's the gearbox that is the marquee feature on this new bike, it is the wide tires that fire my soul. I want to share where those tires can take you.

Full disclosure: I am a friend of Priority Bicycles, and was in the loop on the design and testing of the Priority 600 All Road bicycle. I am not a disinterested party.

Pavement is easy. Priority has specified WTB Horizon road-plus tires on the bike. These are 47 mm wide tires that you can run at low pressures for comfort over rough surfaces. Priority did not skimp here! You are not getting a cheap OEM version of the Horizon tires. Priority spec'd the full-on, high thread count enthusiast version that is tubeless ready, and mounted on tubeless rims. 

Rough. Smooth. Doesn't matter. You can't go wrong with the Horizon tires on pavement.

Gravel is the sweet spot. I can't say enough about how much fun it is to head out on the Horizon tires and just follow my nose down whatever interesting road -- paved or unpaved -- presents itself. Run the tires at 30 psi. Adjust up or down to suit your taste. These tires feel planted and secure, and comfortable!

The Horizon tires are planted and secure on gravel

Doubletrack's good too! Because even better than gravel roads are the endless miles of leftover logging doubletrack and ATV trails in the Hiawatha National Forest. One of my favorite rides for exercise is a mix of gravel road to ATV trail to pavement, and back to the parking lot. The WTB Horizon tires take all those surfaces in stride.

Doubletrack? Big Gravel? It's all good!

Singletrack's possible. Just make it smooth singletrack. I don't recommend slamming into rocks and roots, and for sure don't be hitting jumps and grabbing air. But go easy, and the Horizon tires have let me push the envelope to include easy singletrack into my weekend afternoon rides.

Smooth Singletrack? Sweet Solitude.

Boardwalks and other unusual surfaces are easy, with the wide tires providing stability and grip. I don't often get the opportunity to ride long distances on boards, but last summer on vacation with my wife I had fun circumnavigating a lake on a trail that included over two miles of boardwalk. 

Does it get any better than this?

And now for the crazy stuff! What follows is not recommended(!), but I'm an enthusiast and have pushed the bike -- willing to risk that either the bike or myself might break in the process -- beyond its intended design parameters. This next photo shows the first prototype of the Priority 600 All Road just as I'm entering a rough patch of mountain bike trail in Manhattan's Highbridge Park. 

The Priority 600 prototype handled this rough section. Skill and experience are needed! Surfaces like these are outside the design parameters and bring risk of damage and personal injury.

I love the bike! I've ridden the WTB Horizon tires for over a year now. I love where they can take me on my Priority bicycles. I love how the wide tires help me link together random trails and roads, bringing me to scenic places and helping me connect with nature and recoup from a tough day in the office. The Pinion gearbox on the Priority 600 makes the experience even better by centering the weight on the bike and adds tremendous gearing range for getting up (and down!) steep terrain. 

The Priority Smile

p.s., Ryan Van Duzer's video review of the Priority 600 is excellent.

In a 12.2.0.2 database it's possible that Oracle generates 6 different password hashes for one password for one user under certain circumstances when a password is changed or created (user is created). I will layout the 6 different ones first....[Read More]

Posted by Pete On 13/06/18 At 09:02 PM

Introduction

In a previous post, I covered the VMware Experts Program. When I was attending the Program, one of the things Don Sullivan mentioned was the VMware Hands On Labs (HOL). I hadn’t had a chance to explore those before, so I was interested to see what they were like.

The Good

Quite honestly, these are among the best hands on lab environments I have played around with. If I wanted to test a feature of Oracle software in my previous life, most of the time I built myself a virtual machine environment using Oracle’s VirtualBox, installed all the relevant software and started from there. Of course, much of that could be snapshotted to save repeating the exercise, but it was largely restricted to a single VM. If I wanted to test something like Oracle’s Real Application Clusters technology, I built myself a small two node cluster out of two separate VMs and went from there. It really didn’t give me the feel of a real-world environment.

Enter the VMware HOL. Obviously, to get a real-world environment to test things like vMotion migration of VMs takes much more in the way of resources than my poor old laptop could handle, even if it does have 32 GB of RAM. The VMware HOL environment solves that issue for you. It takes only a small amount of time to actually crank up an environment for each lab you want to do, and each lab comes complete with online instructions integrated with the lab, as well as a separate PDF file and HTML version of the lab if you want to use that.

The labs are largely standalone, but do reference other labs where more details can be found. For things that may take some time to execute, you generally find they have included an interactive simulation where you click your way around and type in a few things, but the actual steps are simply simulated to save time.

Overall, as I mentioned above, the VMware HOLs are pretty darn good!

The Bad

Frankly, there’s not much to say here. If I was to nit pick a few minor points, they’d be these:

• It would be nice to have a recommended path through the labs documented outside the labs themselves. There are quite a few labs there, and it’s a bit hard to determine which ones should be done in which order. In the labs themselves, there are sometimes suggestions to do other labs for more information, but that’s about it.
• Sometimes the interactive simulation may not take input correctly (sometimes need to hit a key twice – which can be more than a tad confusing when you’re entering passwords and don’t realize one got missed!). However, after a while you realize you can type any active key (i.e. not Shift) and it will think you’re typing what it expects, so you can just keep typing “jjjjjjjjjjjjj…” until the field populates with the right number of characters.
  • hands on labs
  • HOL
  • OS stuff
  • VMWare