Concurrency - Interested Transaction List

Concurrency really just means how many people can be doing things at the same time in the same place. It directly affects scalability.

Many RDBMS products use a form of centralized "lock manager" to keep track of who's doing what to what. That is a valid solution, but as the number of concurrent users rises (not people just logged on to the database, but the people actively doing things) then a lock manager can and does become a point of contention. Oracle decentralizes almost all of this and let's the data blocks themselves deal with concurrent access (two or more people trying to manipulate rows in the same data block).

That is the reason why a well designed Oracle database can scale higher on less than any other database out there.

An interesting test I've performed (because my boss thought Oracle sucked and tried to prove it) is comparing Oracle to Brand X for a single user. Oracle was fine, but in numerous tests it lagged behind it's competitor. But, as I started to ramp up to dozens then hundreds of concurrent transactions (different sessions, etc.) I noticed something: the Oracle performance line stayed about the same, while Brand X started to fall behind more and more.
-------------------------------------

The ITL is controlled by the settings INITTRANS and MAXTRANS (just accept the defaults unless you really know what you're doing). As you can probably guess from those keywords, you set a starting number and then tell it how big it can grow (how many concurrent transactions you'll track before making people wait to get into the block).

There is also a row directory that contains data about each row in the block. One interesting thing to know about this (small) component is that once space has been allocated in the row directory, you don't get it back when you just delete a row. It's interesting, as we'll see later, but not a thing to worry about or to feel you're being robbed over.

All in all, the data block overhead is usually only about 100 bytes or more (let's round it up to 200 bytes and you can see that in a 4k block it's still a small percentage), so it shouldn't play too much of a role in your decision on a data block size.

There are about 2 dozen other things in the overhead that I haven't mentioned, but they are as simple as the rest of it and we will address those later.

I've mentioned the pieces I think you need to know for now.