Streams without Primary Key December 31, 2008

We all know that every table should have a primary key. It is one of those database-architecture-101 basics. You need it for data integrity and you need it for cross references between tables. DBAs and developers argue about many things, but I’ve yet to hear anyone argue that primary key is not needed.

Which is why I was very surprised to discover a schema for an application that contained 200 tables without primary keys. Apperantly the application was not very picky and had no problem with duplicate values in these tables in these tables. The tables were tiny, so there were no performance issues. I decided to leave this issue alone, and all was well.

Until we decided to use Oracle Streams to replicate the schema to another DB.

Oracle Documentation says:

“If possible, each table for which changes are applied by an apply process should have a primary key. When a primary key is not possible, Oracle recommends that each table have a set of columns that can be used as a unique identifier for each row of the table. If the tables that you plan to use in your Oracle Streams environment do not have a primary key or a set of unique columns, then consider altering these tables accordingly.”

And then it says:

“In the absence of substitute key columns, primary key constraints, and unique key constraints, an apply process uses all of the columns in the table as the key columns, excluding LOB, LONG, and LONG RAW columns. In this case, you must create an unconditional supplemental log group containing these columns at the source database. Using substitute key columns is preferable when there is no primary key constraint for a table because fewer columns are needed in the row LCR.”

I think the documentation doesn’t make a clear enough statement here. Let me restate what they are trying to say:

1. You cannot use Oracle Streams to replicate a table which has duplicate rows. It will not work.
2. If you don’t have unique constraint on your table in some form, you will have duplicate rows.

The first sentence is important. Streams replication will not work with duplicates. There is no way around it. Here’s why:

Remember that with stream replications you are taking DML transactions that run on one schema (I’ll ignore DDLs for now as it is irrelevant to this topic) and attempt to run them on another schema. The way it is done is by mining the redo log and converting DML statements into LCRs. DML LCRs are also called ROW LCRs, and for a good reason – each DML statement is converted into one or more LCRs, each changing one row at most.

How does the apply process knows which row? Remember that you cannot use the ROWID, because the change will be applied in a different schema.

If each row is uniquely identified by a set of columns, and if you configured streams correctly by enabling supplemental logging of these columns, then the information in the supplamental logging will be placed in the LCR, and will be used to uniquely identified the row you try to modify.

But if the information in the DML statement and in the supplamental logging is not enough to uniquely identify the row, perhaps because the row is not unique at all, because your application allows duplicates. Then apply will fail and in the apply error queue you will see error  ora-01422: exact fetch returns more than requested number of rows. Because apply used all the information it had, expecting it to define a single row, but due to the duplicates it found more than one row. It cannot apply a row LCR to more than one row, so it fails.

Oracle does offer a workaround of sorts – Apply has a parameter called allow_duplicate_rows. Setting it to true makes the apply process update or delete one of the duplicates it found. Just one of them. The original DML updated or deleted all of them.

Lets reiterate – if you have duplicate rows in your original table, the streams replication of the table will have these duplicates as well.

If you try to update or delete these rows in the original table, you will update or delete all of the identical rows. In the replica, however, either apply will fail and you’ll not delete any row, or it will succeed and delete only one of the rows. Depending on the allow_duplicate_rows parameter. In either case, your replicated table is no longer identical to the original. Because Oracle Streams replication does not work with duplicate data.

Fork() on Windows. December 19, 2008

I was assigned a very simple task – write a script that opens SSH connection to a remote server, waits 30 seconds and closes the connection. About 5 minutes of work on Linux.

On windows it took an entire day.

First attempt: Using MKS32′s KSH.

d:/sitescope/tools/plink sitescope@testdb -pw pass -m d:/sshTest_cmd2;
SSH_PID=$!
echo $SSH_PID
(sleep 30 ; kill $SSH_PID);

Problem: The ssh process executed within a subshell, and I only got the PID for the subshell. Killing the subshell did not kill the SSH process.

Second attempt: Using Perl’s fork, exec and wait.

$pid = fork;
if($pid == 0) {
    exec("d:/sitescope/tools/plink sitescope@testdb -pw pass -m d:/sshTest_cmd2");
}elsif ($pid > 0) {
    sleep 30;
    kill(9,$pid);
} else {
    print "fork failed";
};

Problem: Under windows, fork is not a real fork. It creates a “pseudo process” (AKA thread), and $PID is a negative number. Not a real process ID. After the “exec”, the child thread will become a real process with PID, but again, I’ll not have the PID.

Third attempt: Perl has a WIN32 package, which includes a process object, allowing me to create a process, wait for it, and kill it.

use Win32;
use Win32::Process;
use Win32::Process (STILL_ACTIVE);

sub ErrorReport{
    print Win32::FormatMessage( Win32::GetLastError() );
}

$params = "plink sitescope\@$ARGV[0] -pw $ARGV[1] -m D:\\sshTest_cmd";
print "$params \n";

Win32::Process::Create($ProcessObj,"D:\\SiteScope\\tools\\plink.exe",$params,0,
NORMAL_PRIORITY_CLASS,".")|| die ErrorReport();

$PID = $ProcessObj->GetProcessID();
print "$PID \n";

$ProcessObj->Wait(30000);
$ProcessObj->GetExitCode($exitcode);

print "exit code is: $exitcode \n";

if ($exitcode == 0)
{
    print "exiting normally \n";
exit 0;
} elsif ($exitcode == STILL_ACTIVE)
{
    print "killing ssh \n";
    $ProcessObj->Kill(59);
    exit 59;
} else
{
    print "ssh exited abnormally \n";
    exit $exitcode;
}

Finally something works

Chicken and Egg Troubleshooting December 15, 2008

It all started about 10 days ago, with a phone call at 8am. 8am phone calls mean that something bad happened at 4am, but the system kept functioning, so it wasn’t worth waking anyone up about it.

One of our stand alone databases was swapping like there is no tomorrow (or more precisely, no free memory). Load average was up at around 20. On a 2 cpu machine. We could not find out any process that we could blame for the current situation, so we did the usual – restart the instance. This solved the immediate problem.

During the post mortem, one of the night DBAs reported that at some point during the night, this machine lost connectivity to the Netapp for about 10 minutes. He did not have an exact time when it happened, we couldn’t find any trace for this in the database logs, and the system people denied it completely – it did not happen, it could not happen. Except that we already seen that loss of connectivity to the Netapp will result in large number of processes in “uninterruptible sleep” mode, which will bring load average up… So this was still our best explanation. And it was not a very good one.

During the next few days, at least twice a day, load average and swap went way way up on that machine. Most of the CPU time was system time, and we also couldn’t find a single process that was taking too much memory. Sometimes the situation went away quickly, sometimes I had to kill a bunch of old connections to stabilize it. Naturally, I was unhappy about it. When I’m unhappy, I start looking at numbers. I compared the broken stand-alone to two other stand alone servers that were doing similar work. Good servers had an average of 200 concurrent connections. Each serving about 20 customers with 10 connections each. The broken server had 500 connections.  It had 30 customers each with 15 connections. It also has only 12G of memory, compared to 16G on other servers.

Aha! I said to my boss. The machine is overloaded. We can try to move few customers to a less loaded server, but we should also consider moving to a better machine, or add another server.

My boss was skeptical – if this is just connectivity load, why did it spring on us all of the sudden? Shouldn’t we have seen a slow degradation? Well, each connection consumes some memory. As long as the machine had enough memory, there were no issue. Once it started swapping, things went wrong very fast.

My boss was not convinced, and I was not convinced either. There was the possibility that the high load was the cause of too many connections, not the result of it. When each task takes much longer to complete, you will have more users trying to complete their tasks at the same time. But my plan was the only thing that was remotely close to a solution, so we decided to try anyway.

Lucky for us, that night the machine lost connectivity to the Netapp again. This time the issue did not resolve itself, and the sysadmins had to replace the network cards on the server to solve the problem.

Needless to say, all my problems – load, swap, too many connections, went away that night and never came back.  I’ve been staring at a bunch of eggs for days, trying to decide which one of them was the chicken.

How did the broken network card caused all these problems? Two ways, I think.

The first one is quite simple – every IO operation the DB had to do took much longer due to broken network connectivity. This means that everything takes longer, which means more connections. All of them waiting for IO most of the time.

Which leads straight into the second problem – since the network was now unreliable, and since TCP tries to cover up for the problems and keep communication reliable, each socket now had to keep huge buffer of information that has to be retransmitted, again and again. With more connections, each taking much more memory, we went into very high swap. Now our OS is so busy with managing the broken connections and swapping, that it has very little time for the work we actually want it to do, and load average is way up too. We have more and more connections, taking more and more memory and getting less and less IO and CPU to do their work.

Next time I see high swap and system taking lots of CPU, I’ll remember to test all my network connections.

The Small Difference Between XML and RDBMS December 14, 2008

OracleNerd posted a rather heated discussion between himself and an application developer, and few people jumped in to comment.  I can’t recommend reading this discussion, it is full of unsupported assertions and rude language. Something that was mentioned in it, did catch my attention.

MCohen wrote “So for example, it shouldn’t really matter to me that we’re using an Oracle database over say, an object database or xml files, or Amazon S3. We’re not quite there yet, but we’re making progress.”

I worked on few projects that stored data in XML files. Usually it starts rather innocently. The program needs to store and retrieve few configuration parameters, and using a database for that seems like an overkill. So they put it in an XML file.

As the project grows, more configuration parameters are added. Some of them may be depended on each other, so if you update one and fail to update the other, you need to undo your first update. Congratulations, you just reinvented transactions.

Few weeks later the project have several components and maybe even threads. Sometimes, several of those need to access the data at once. Maybe you serialize all the access to the data, completely locking the file while it is updated. Maybe you want to allow reads while an update is going on, but how will you prevent reading partially updated data? Different RDBMS has different solutions for concurrency, but Java’s XML library will let you write your own. Enjoy.

I’m not saying XML is always a bad idea. Sometimes, you need your data store to be human-readable. Sometimes, your data is just not very well structured, and would not fit in an RDBMS. But using XML has a cost, and you should make sure it is worth it. To make the right decision, you should know what is the difference between an XML file and an RDBMS.

If you don’t know whether your data is in XML or in RDBMS, how will you know whether or not you need to implement your own transactions?

Musical Analogy December 12, 2008

Every now and then someone talks about “Rockstar Programmers”. Usually when describing the type of programmers he wants to hire. I assume this term is used because saying “exceptionally good programmers” is too long or not sexy enough.

Then, this guy came along and explains that he prefer Jazz programmers. He actually makes one or two good points. Jazz musicians (just like exceptional programmers), play well in different groups, play for fun, and love theory.

But this left me thinking where this leaves us DBAs. What type of musicians are we? Obviously we work in a theory-heavy field, we shoulder an important responsibility, must be extremely detail oriented (unlike those jazz programmers), we are critical in any production system, but our efforts are largely invisible (unless we screw up).

I believe this makes us the Sound Engineers of the IT world.

Note that the traditional rivalry between musicians and sound engineers fits neatly into this analogy. Lots of musicians think that sound engineers are not needed, or they are not sure exactly what sound engineers do except annoy musicians. Lots of sound engineers think that musicians are overpaid prima donnas who don’t even realise that the sound they produce is not production quality and can’t go live without huge amount of additional effort.

Sounds familiar?

Pivot – You are doing it wrong December 2, 2008

Suppose you have a table like this:

drop table t_;

create table t_ (
  nm Varchar2(20),
  pr Char    ( 7),
  vl Number  
);

insert into t_ values ('company 1','2003-06', 10);
insert into t_ values ('company 1','2003-07', 29);
insert into t_ values ('company 1','2003-08', 39);
insert into t_ values ('company 1','2003-09', 41);
insert into t_ values ('company 1','2003-10', 22);

insert into t_ values ('company 2','2003-06', 13);
insert into t_ values ('company 2','2003-07', 17);
insert into t_ values ('company 2','2003-08', 61);
insert into t_ values ('company 2','2003-09', 55);
insert into t_ values ('company 2','2003-10', 71);

insert into t_ values ('company 3','2003-06', 33);
insert into t_ values ('company 3','2003-07', 18);
insert into t_ values ('company 3','2003-08', 27);
insert into t_ values ('company 3','2003-09',  5);
insert into t_ values ('company 3','2003-10', 32);

(Thanks to René Nyffenegger for the helpful create script)

And you want this output:

NM                          JUL        AUG        SEP
-------------------- ---------- ---------- ----------
company 2                    17         61         55
company 3                    18         27          5
company 1                    29         39         41

If you are using 10g or older versions, you can use Tom Kyte’s decode trick. In 11g, we have an official keyword to solve this problem.

What you should never, ever do, is this:

  select
    t1.nm,
    t1.vl jul,
    t2.vl aug,
    t3.vl sep
  from
    t_ t1,
    t_ t2,
    t_ t3
  where 1=1
  and t1.nm=t2.nm
  and t2.nm=t3.nm
  and t1.pr='2003-07'
  and t2.pr='2003-08'
  and t3.pr='2003-09'
  group by t1.nm, 
    t1.vl,
    t2.vl,
    t3.vl

And if you do this, and if t_ has more than 15 rows, and if you actually need more than 3 columns, don’t be surprised if the performance may be slightly disappointing.