Speedy GROUP BY

We had a big problem with our production system today. The customer determined we had not been transferring some files for a couple week. And those are some of our most crucial files. We had a big TO DO list to work this problem. I signed up to analyze the damage that was done to the business.

At first I ran some queries on a pretty large set of data. Our server is a massive one with tons of CPUs and memory. However these queries took a long time. I published the results. The managers wanted more details. They were looking for a way to spin the data to do the least amount of damage to our image. I told them this would take a long time. So I went home and brought my computer with me.

Here was the surprising thing. When I ran the SQL and divided up the result with a GROUP BY function, the queries seemed to run faster. Perhaps the database was able to split the pieces of the query amongst the CPUs better. Or maybe my prior query results were cached, and the database could take advantage of the result set. Whatever the reason, I was happy that my homework was not taking too long. I was able to knock out the tasks and move on to the next emergency. I even got time to eat dinner and watch a movie this evening. Thanks Oracle10g.

Back End Development

My project at work seems to be loaded with too much to do with little resources. Yes I know that happens to everybody. But there is no way were are going to even be close to completing everything on time. My team leader has been tasked with trying to come up with a plan to get everything done. Today he had a brilliant idea. We could carve out the pieces of the newest changes that are for the back end. Then we could assign those to a junior PL/SQL developer that we have.

I gave this idea some thought. At first I thought there was maybe 10% of the work that needed to be done in PL/SQL packages and database triggers. However on second thought, the number might be closer to 15-20%. Out PL/SQL developer has some work to do. However she could help out this latest development task.

Here is the difficulty in this arrangement. The actual PL/SQL coding is not difficult. The hard part is understanding the business. This is why we cannot give this part to some new developers on the team. The business of our customer takes a couple years to understand. I guess we could give the junior developer some hints, and let her code away. We shall see how this would pan out. I was hoping that I would get to do the back end work. I like writing PL/SQL code. And I know the business inside out.

Definer and Invoker Rights

In our system, we have the stored procedures owned by a central schema. Users have their own database accounts (schemas) where we stored temporary tables. The stored procs frequently create these tables. One developer reported that a rewrite of some stored procedures broke this pattern. The stored procedure, owned by the central schema, was creating temp tables in the central schema. That is no good, as each user needs their own copy of the temp table.

When I was asked for help, I said that we have the technology to make this work. We have it set up in the old stored procedures. However I referred the developer to our DBA Team. The lead DBA told us to set up invoker’s rights for the procedure. Normally we seemed to use whatever the default was. And it seemed to work before. We tried all kinds of debugging, but could not get this to work without using invoker’s rights.

Definer’s rights s the default in Oracle. Whoever owns the stored procedure (the definer) has the credentials that determine what other code the stored proc can call, and what tables and objects are used by default. This is not what we want on our project.

You have the ability to override the default behavior and specify invoker’s rights. This is done by specifying the AUTHID CURRENT_USER keywords when creating the stored procedure. This causes the context of the current user to be used when a stored procedure is called. Sure enough when we made this change, the temporary tables started getting created in the calling users’ schemas. Now we got some new stored proc code to update.

Automatic Documenation

How can you create an Entity Relationship diagram automatically from the structure of the Oracle database? Other databases have tools to do this such as PostgresSQL Autodoc and MySQL Workbench.

I got some advice from some people for products to try. One of them is Schema Spy. Another is Enterprise Architect. A final one is DB Visualizer. Right now I use tools to execute SQL. Somebody else on the database team generates documentation from the database. However I don’t think they use any specialized tools. I think they either user Oracle tools, or some custom written scripts to do the work.

Our documentation is mainly text information on the database. This is normally based on the data dictionary. But it would be nice to have some fancier ER diagrams to use and show off. It might be time to ask for some new software to be purchased.

Oracle Alert Log

The other day, our customer reported that some functions in our system were slow. Others were just plain hung. This issue went to our DBA Team. They asked me about some of the functionality that was involved. I provided them the overall flow, as well as the major database tables involved. Then I went back to my normal work.

Our manager held a conference call to work the issue. I share a room with the manager, so I overheard some of the talk. My manager was interested in the DBAs looking at the Oracle Alert Log. Now I have heard this term before. But I did not know exactly what it was. Time for a little research.

The alert log is a text file containing messages and errors. Specifically it will contain ORA-00600 deadlock errors. It also contains database startup and shutdown events. The filename itself is alert_.log. The location of the file is governed by parameter background_dump_test. Go figure.

Apparently the alert log is the first place a DBA should look when there are database problems. In fact, a proactive DBA will be monitoring this file a couple times a day. One interesting fact is that you can write your own messages to the alert log using the ksdwrt procedure in the dbms_system package.

If you have Oracle 11g, the alert log can be directly queried using the X$DBGALERTTEXT fixed table. However you must connect as SYSDBA to access this table like you would any other fixed table. Finally I should mention that Oracle itself computes a number of metrics based on the contents of the alert log. Now I am at the beginning of my journey to becoming a DBA. I just don’t know if that is where I want to go.

Disabling Constraints

My boss told me to go help a teammate out in replicating a problem found by our customer. I worked with her to look at the code. We needed to set up some very specific data to make the condition occur. I told her we should execute some SQL to generate the new records required.

At first we wanted to clone an existing record. She told me that some triggers were preventing that. So I had her disable all the triggers on a few tables. The syntax for that command was straight forward:

ALTER TABLE my_table DISABLE ALL TRIGGERS;

Then it came to disable the constraints. She tried a similar command. However we could not figure out the syntax to disable all constraints in a single commend. Later I found out that the command does not exist. You need to disable each constraint manually, one at a time. Or you could write a script that finds all the constraints and disables them one at a time automatically. Come on Oracle. Can't you make it easy for us? We were in a hurry.

Stored Procedures

Recently I read two blog posts about stored procedures. Personally I like writing stored procs. However I am sometimes hesitant to do so because of the difficulty in releasing the software to clients. It is simple to write all the code in C++. We have an single installer which deploys the application on the workstation. If I include part of my solution in a stored procedure, I need to make sure the client and back end stored procedures are in sync. That is one extra headache that makes me choose the easier route more times than not.

But let’s get back to the debate on stored procedures. One author stated that placing code in a stored procedure does not give you a performance advantage per se. That is because normal queries from a client get cached and have equal performance. I am not sure if I agree. A stored procedure which has been compiled into the database is going to have some inherent benefits for performance, especially if there is a lot of SQL involved.

Stored procedures stop injection attacks. They also enforce data integrity if you choose to do so. I recall from my Oracle programming class that you should use constraints to enforce integrity, not stored procs. However that might be a personal decision. One author said that you should not code business logic within a stored procedure. Again I don’t think I agree. Your business layer can be at the stored procedure level.

When you do code logic in a stored procedure, you are normally locked into a particular database vendor. That’s not a problem on my current project. We are an Oracle shop, and will most likely be so until the end of time. One benefit with stored procedures are that you can have fine control over the permissions on who can execute a stored procedure. That is one ability that I like.