Script Failure

A developer was writing a script to set up some configuration data via script. He complained that the script was completing successfully, but the data was not getting inserted. He asked for help. His thought was that the success should mean that the records should be inserted.

Immediately I pointed out that running the script and seeing success means that the script did not throw any exceptions that were not caught. We looked at his script and saw that it was eating any exceptions. The developer floudered around some more. I later advised him to forget about the script, move over to SQL*Plus, and manually execute the SQL statements one at a time.

Bingo. In SQL*Plus, he found that some constraints were being violated and causing exceptions to be thrown. This taught me two things. You shouuld not jump to the script until you are sure your raw SQL is good. You should also not rely on some tool to execute your script if you are not in full command of what it is doing.

SQL Loader to the Rescue

I gave myself an action item to try to do some hands on SQL Loader practice. It turns out it is not that hard for default loading from file. I specified the filename, used absolute positioning, and loaded stuff into my database table.

The reference I used to learn how to do this was the SQL Loader FAQ. You should check it out. The FAQ goes over a lot of common scenarios, and how you use SQL Loader to accomplish your work. Good stuff I tell you.

So far I only tried SQL loading files with 10 to 15 records. The next step is to try to load 10k to 15k records and beyong. I got a good feeling about it. They say that SQL Loader is optimized to handle the big jobs. I should have already known this. The team on my project that loads huge files from the mainframe primarily uses SQL Loader to do the job.

Huge Script

I had a task to deal with a large amount of data for our customer. Being the programmer that I am, I got a couple files with the data. Then I ran them through a custome Java program to extract out the data of interest. Finally I stuck the data in a massive SQL script to do some work.

I did all my tests with some dummy data. Then when I got the actual data in the script, I ran one last smoke test to ensure the thing worked. It did not. The script kept hanging without inserting a single record. I called my DBA. He told me I should have used SQL*Loader. Well I prefer custom PL/SQL. Plus I needed to implement some logic for each of the inserts.

There was a tight deadline for this work to be completed. I decided to split the script up into a number of smaller scripts that actually did complete. The original script had about 300,000 lines in it. I found that 20,000 and 30,000 line scripts worked fine. So my plan is to break the big mamma up into 10 separate script. I will test them out and let you know how I fare.

New action item for self - learn how to do stuff with SQL*Loader.

Oracle 11g Express Edition

Recently I had to upgrade my company laptop to Windows 7. This was a painful ordeal that lasted a whole weekend. The upgrade consisted of a reimaging of my machine. One byproduct was that my Oracle 10g database was wiped off the machine. Ooops. I forgot to back that thing up.

I decided to reinstall Oracle after the upgrade. It was interesting to see that Oracle had an 11g version of their Express Edition free database. The latest version is 11.2, which is currently in Beta.

I was pleased with how fast I could install the Express Edition database. This thing was actually at Express speeds. I was also reminded that Express Edition does not come with much. You get a command line SQL tool. You get tools to start, stop, backup, and restore the database. That is it. Looks like I need to polish off my command line DBA command line programming.

The Trigger Project

There has been a lot of drama on our project based on performance problems we are having in production. Some people are attributing the performance issues to the number of triggers we have in the database. That sounds like nonsense to me. I deal in hard facts. There was a total lack of evidence to back up this theory. So I have decided to run some tests in our database to measure the overhead of adding triggers to DML.

I wrote a harness script that updates 10,000 records. It commits the change every update. The thing runs at a good speed. It takes around a minute to complete. The table currently has no triggers on it. Then I added one trigger that did nothing in the body of the trigger. I was shocked to find the harness took over 20% longer to run with the trigger.

I had another developer present when I ran the test. He could not believe the results either. Then he had some suggestions to vet the results. I ran the tests a couple more time. Looks like the first run was a fluke. He also suggested I run the tests locally on a database on my machine to eliminate irrelevant variables.

So next I plan to move the tests over to a local database on my machine. This way the network is not a factor. Other uses on the database are also eliminated from the equation. Next I am going to time having a lot of triggers on a table. I will implement some cascading triggers. When I am done, I will have a lot of empirical evidence to back up my opinoin that triggers in and of themselves have no impact on performance.

Triggers Did It

A job that runs at night has been taking too long every night this week. This causes pain as the system administrators call up our DBAs or help desk in the middle of the night. Obviously we have a problem. I would call it a performance problem. What makes this unique is the response I heard from this. Developers told me a manager decided there would be no new database triggers implemented due to the performance problem.

WTF? This made little sense. Who correlated our performance problem to the presence of triggers? I attended a meeting hosted by this very manager. So I asked about this new "no triggers" edict. He said that he has inspected the PL/SQL source code. He found many database triggers. He also saw triggers firings that caused other triggers to fire as well. Therefore they were suspects in the performance problem.

I said that by this logic, we would find many stored procedures in our source code. The stored procedures get exectured. The code runs slow. Therefore we should not use stored procedures. Obviously this argument does not hold up.

Rather than being an obnoxious developer who shoots down ideas, I decided to later pitch a plan to get to the actual root cause of the performance problem. We should find out what pieces of the code are taking a long time to execute. Then would should drill down into the slow pieces and find out why. If we do that and find some database triggers to blame, I agree we should optimize them.

I come to find that there is a proposal to do the very thing I recommend. Our manager clarified that we cannot add database triggers without analyzing the performance impact. I am all for that. I run timing studies all the time on slow code. We need to use logic and hard evidence to deal with tough and frustrating problems such as performance. If not, we might as well chirp "add more indexes" like any other lackey and get nowhere.

From the Trenches

I have been following a seminar on how to be a DBA. These talks give you hands on advice. They are geared towards individuals who find themselves appointed as a DBA by accident. While I am not formally a DBA, I often do some light DBA tasks. So I thought I would study up.

When you start a DBA job, you obviously need to find out what servers and databases you are responsible for. But another important discovery is to find which databases you are not supposed to manage. You should gain info on your systems by talking to actual users. If they are not available, rely on knowledge from developers and testers of the system.

I found out about a nice option in TOAD where you are execute a query against many databases at once. My tool of choice is an old copy of PL/SQL Developer. So far I only log into one database at a time to run queries.

Make sure you find out the cycles of use by the business. This will help you scope out the best time for backups of the database. Speaking of backups, make sure you can recover using the backups. Test out a restore to a different system. It is no good to do backups if in the end they do not work.