Directory Assistance

I got a new project that I am developing. And I needed to crunch some data for it. What better way to do that than using an Oracle database? So I installed the Oracle 10g Express Edition on my laptop. I did not need any great power.

One of the first tasks I needed to complete was to load in some data from a file. The logic to parse the data was a bit involved. Therefore I decided to write it in PL/SQL. To use the UTL_FILE pacakge to access the file, I needed to create a directory object.

The web user management tool provided by Oracle 10g Express edition did not list CREATE ANY DIRECTORY as one of the grants it could assign. Therefore I dropped down to the command line to get that job done. Next my stored procedure could not see UTL_FILE. I had to log in as SYS and grant access to this package. Wow that is a lot of work to read a little file. I guess this is not your standard use of an Oracle database.

Oracle Not Available

I had to return to a project I worked on last year. The first order of business was to ensure I could still run the program. Immediately the thing died with an ORA-01034: ORACLE not available.

The program was just running sqlplus, having it execute a script that had a single EXIT statement in it. I was able to successfully execute the sqlplus statement from the UNIX command prompt. Why was the program not able to do the same without error?

I googled around and found that this error happens when your ORACLE_HOME and ORACLE_SID do not match. These environment variables seemed good. I was pulling my teeth trying to figure this out. Eventually I found that the program was sourcing a different profile. This profile pointed to the wrong Oracle home. Dang.

Adaptive Cursor Sharing

We are going to Oracle 11g in our customer's system. One of our DBAs took a look at the new features for 11g. The developers were supposed to see whether we could take advantage of these features. So far I only took a look at the first feature mentioned. That is Adaptive Cursor Sharing (ACS).

There are specific requirements for ACS to kick in. You need to be doing an EXECUTE IMMEDIATE in your PL/SQL. You also need to be using bind variables. Finally I hear that if you use too many bind variables (15 or more), then ACS will be skipped.

The optimizer may use different execution paths based on the values in the bind variables. Skewed column data may cause the execution to be suboptimal. Normally the optimizer makes use of histogram data generated from gathering statics.

With ACS, the database tries to determine which values for bind variables work best with certain execution paths. Then it would use this information in the future to choose the best execution path. We get this benefit automatically in 11g. It can be disabled. I am thinking we shall not and try to get the benefit.

Database Costs

I just looked a the pricing structure of Microsoft's SQL Server platform. Some versions mimic the Oracle cost. There is a free express version of SQL Server. The developer edition also has no cost. The minimal web version of the database costs $3k. Workgroup goes for $7k. Enterprise comes in at $27k. And the Datacenter edition is a whopping $54k.

There are other fees that might bring the total cost a lot higher. If you install a separate copy on a virtual machine, you might need another license. Here is one good way to save costs. Use multi instancing. This is similar to Oracle database instances. They all have their own CPU, memory, and disk allocated. However the SQL Server instances all share the same operating system and physcial machine. There is no extra cost for an additional instance in terms of licensing.

Here is a final tip that I learned at a seminar I watched today. Do not keep the default install options for Microsoft's SQL Server. This is especially important if you are installing the production database. I wonder if the same holds true for Oracle.

Roles of the DBA

Today I continue sharing some things I learned watching a seminar about becoming a DBA. There are two main types of DBAs: (1) the development DBA and (2) the operational DBA. The seminar I watched focues on the operational DBA.

This type of DBA needs strong customer service skills. You will be talking to a lot of people. In fact, you will be the liason between many groups. Consider yourself the point of contact for all things support related.

Take responsibility. A DBA is the guardian of the data. You must own your platform like you own a house. Uptime is your responsibility.

One major part of a DBA's responsibility is to guard against SQL injection attacks. Apps must sanitize user input. Many web apps do not. The result is that hackers can send in SQL statements, and maybe even compromise your database box.

Surprise. You are the DBA.

I just finished watching a seminar on becoming a DBA. It seems a lot of times people get drafted into the DBA role. This is not a bad thing always. The DBA is a good career path. But I also saw a lot of potential for DBA pain.

You need to keep your boss happy in any job. But the DBA must keep the user happy. Otherwise you will suffer. There are ways to set up your environment to increase the chance of succes.
Have a sandbox to try out new technologies. Put your development database on a separate physical machine. Be ready to restore a production backup to the test environment. And have a staging database that mimics everything you see in production if your project has the cash.

Performance Redux

We continue to have performance problems with our nightly processes. I extracted some SQL that could run during the day. However a manager shot that idea down. It would look too suspicious to the customer. My next idea is to replicate the performance problem.

The production operations are taking a whopping 5 to 6 seconds per action. Each action has a number of updates and inserts. I got a small (500 record) sample in development. There is was only taking a tenth of a second to complete.

I ramped up the volume to 5000 records. Things slowed down a little bit to three tenths of a second per action. Now I want to try just a bit more data to see if preformance in develpment degrades futher.

Once I can recreate the problem<, I can start with the performance debug. I think I might log the different parts of the action to see where we are spending most of the time. Then it will be time for that magic SQL performance work.