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.