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.

Performance Problems

We have a database job that runs every night. It takes care of all kinds of tasks. For example, it detects and resolves data problems. It also implements certain time based business requirements. Lately this job has been taking a lot of time to complete.

I traced one certain part of the job that was taking all night. It was looping through a ton of records, then doing a bunch of updates on them. The algorithm to do the updates was painfully inefficient. I replaced all of this with single SQL updates of all the records at once. The job that took all night completed in 10 minutes.

The job was doing well for a while. Then it got slow again. Another developer started to look at it. All he came up with was that there were a lot of triggers on the tables involved.

I hate when somebody tries to solve a performance problem and asks if we can add some indexes to make it faster. Ouch. The latest analysis felt like this type of solution. Sure you don't want to do a lot of work in triggers for updates that happen frequently. But triggers, lots of them, or complicated ones do not always mean slow performance.

Performance tuning is quite a deep subject. Maybe we need to send some of our developers to training on performance analysis and tuning. That might get us past the old "add indexes" solutions.

Pains from a Hack

I got assigned to work a curious bug in the system. The customer said a bunch of data was in the wrong state. I called our customer and got some more info. The best help they gave me was some examples in production.

I studied the audits and found the bad state transition. Then I poured over our database packages to find out why this was happening. Then I found the guilty party.

Somebody was making a call to some existing procedures in our packages. They found out they needed to set some data to an intermediate value first for the code to work. However when the code encountered an error, the package code left the data in the bad intermediate state.

Fail. I took out the hack. then I modified the packages to work with the new data pattern. Couldn't the original programmer have done this? Do the hard work up front. It will save everybody some pain. Next I started looking into why there was an error in the first place. That's a story for another time.