Archive for the 'Databases' Category

Modality? What’s Modality?

Here’s a comment I received from Paul the other day about my database normalization tutorial:

Very nice. Excellent. The only thing I would question would be that a customer could have zero orders. In order to exist as a customer in your system, they must have placed an order at some point. So, the modality should be one.

I always like hearing nice things about my work, but the remark about modality gave me pause. What does that mean? That’s not a word I ever learned at the Database School of Hard Knocks. What is Paul talking about?

Modality, it turns out, is another word for what we database people call cardinality, or “the number of unique things in a set”. You might also hear it referred to as degree amongst erudite set-theory folks. When we talk about one-to-one relationships (or one-to-many, many-to-many, etc.), we’re talking about the modality or cardinality of the relationship between things in different sets. In other words, one row in a table can be related to rows of data in another table. It doesn’t have to be, but it can be, depending on what real-world relationships you are trying to model.

For instance, suppose you have a database of people, and EMAIL_ADDRESS is an optional field in your database. A person might have an email address, or she might not. We say there is a one-to-zero-or-one relationship, because one person can have zero or one email addresses.

If, on the other hand, EMAIL_ADDRESS is not optional, the relationship becomes one-to-one because one person must have one email address.

If, on the third hand, a person can have any number of email addresses, we call that one-to-many, because one person can have many email addresses. If the email address is still optional, we call that one-to-zero-or-many.

Each of these possibilities can be shown in an Entity Relationship Diagram or ERD, which is a visual representation of the relationships in your database. There are different ways of showing the relationships on a diagram, and terrible religious wars have been fought over which system is best. The system I chose for my tutorial uses the following symbols:

One-to-one
One-to-one
  • Every A has exactly one B.
  • Every B has exactly one A.
One-to-one
One-to-one
  • Any A can have no more than one B; maybe none.
  • Any B can have no more than one A; maybe none.
One-to-many
One-to-many
  • Every A has one or more Bs.
  • Any B has no more than one A; maybe none.
One-to-many
One and only one-to-many
  • Every A has one or more Bs.
  • Every B has exactly one A.
One-to-zero-or-many
One-to-zero or many
  • Any A can have any number of Bs; maybe none.
  • Any B can have no more than one A; maybe none.
Many-to-many
Many-to-many
  • Every A has one or more Bs.
  • Every B has one or more As.

Notice how the symbols (which are sometimes called “crowsfeet” for reasons that should be obvious) change the cardinality of the relationship. You should understand that this is not an exhaustive list of entity relationships, and again, there are many different sets of symbols out there.

So what’s all this got to do with my tutorial?

Check out Figure J, the diagram representing Third Normal form. Originally I had drawn it like this:

Third Normal Form: incorrect relationship

See if you can spot the problem.

Give up?

The symbols connecting ORDERS with CUSTOMERS say,

Every order has exactly one customer

and

Every customer can have any number of orders; maybe none.

In other words, a customer could exist in the database without ever placing an order.

I call this a “problem” rather than a “mistake” because there are lots of databases out in the real world that have customers who have never placed an order. Although logically, the definition of “customer” is “someone who has bought something from you”, I can think of all sorts of reasons why you might want to keep track of order-less customers… maybe their order is pending. Or maybe the customer is just a sales prospect, in which case they should perhaps go into a PROSPECTS table, and then migrate to CUSTOMERS after the order is made… or maybe you could add a STATUS field to the table to differentiate between pending customers, prospective customers, and actual customers… The possibilities are delightfully many, as are the possible solutions.

But guess what? I don’t care. At least not in the context of my tutorial. I wanted things to be clean and simple– I just wanted to model a stack of invoices, where every invoice has a customer, and every customer has made a purchase. But by selecting the wrong crowsfoot symbol, I introduced a whole big mess that would need considerable explanation that would dilute the simplicity of the tutorial.

So, what to do?  Easy; just change the symbol connecting CUSTOMERS to ORDERS. Here’s my new Figure J.

Third Normal Form

Which says,

Every order has exactly one customer

and

Every customer has one or more orders.

I’m grateful to Paul for pointing out this inconsistency.

The whole truth behind candidate keys

A reader of my tutorial on the 3 Normal Forms recently asked:

The question is: why was customer_id not used as part of the composite primary key (order_id, customer_id , item_id) in 1NF in the first place?

It’s a good question. The questioner identified one of the normalization issues that I simplified in order to make (I hope) my tutorial more readable.

Going back to the tutorial, we said that every table row has to have a column or group of columns that uniquely identifies it, right? We called this the Primary Key. Formal database jargon refers to this as a “candidate key”. There could be more than one candidate key in a table; the one that we actually select becomes the Primary Key.

In truth, that’s only part of the story. The candidate key has to have two properties: First is Uniqueness (as we just stated); the second property is called Irreducibility (or Minimality).

Let’s consider some examples of the three columns the questioner mentioned: order_id, customer_id, item_id:

 order_id  customer_id  item_id
125 56 563
125 56 851
125 56 652
126 2 563

Does this set of columns satisfy the Uniqueness property? Yes, it does. Every combination of {order_id, customer_id, item_id} is unique in our table, and this makes sense according to the invoicing rules of our imaginary business: Every item ordered by a customer appears once and only once on an invoice, and invoices are never duplicated (if a customer makes a duplicate order, we issue a new invoice).

The trouble is, it is redundant to say this. We don’t call {order_id, customer_id, item_id} the candidate key because customer_id isn’t required to satisfy the uniqueness requirement; each row is already uniquely identified by {order_id, item_id}. This is what is meant by the Irreducibility property: If a set of columns can be reduced to a smaller set of columns that still satisfies the Uniqueness property, then it does not qualify as a candidate key.

Here’s a somewhat more formal definition of these two properties (quoted from C.J. Date’s An Introduction to Database Systems, 7th Ed., p. 258):

Let K be a set of attributes of relvar R. Then K is a candidate key for R if and only if it possesses both of the following properties:

  1. Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K.
  2. Irreducibility: No proper subset of K has the uniqueness property.

If you’re unfamiliar with database set theory terminology, here relvar (“relation variable” in case you’re curious) means “table” and tuple means “row”. A proper subset is a subset that does not include the whole set itself (in other words if you have a set of things {a,b,c} then {a} is a subset, {a,b} is a subset, {a,b,c} is a subset, and so forth; a proper subset includes {a}, {a,b}, etc., but does not include {a,b,c}).

I don’t think it’s necessary to get into this kind of detail in the tutorial itself, but it’s worthwhile mentioning and I’m glad someone asked.

3 Normal Forms Tutorial update

Many people have, over the years, both in the blog comments and in private messages, objected to my contention in the Normal Forms tutorial that ITEM_PRICE leads to failure of Second Normal Form. I always go back and forth about this, and I generally tell people something like “it depends on the business rules, it’s not a matter for Normalization to solve, I wanted to keep things simple,” etc. Enough people have pointed this out that I finally decided to spell out the problem in the tutorial itself, under the NF2 discussion. Hopefully this will not prove too confusing for beginners, while at the same time keep the advanced readers satisfied!

Debugging ORA-01001: invalid cursor

End-to-end performance tuning is something you hear more and more about. I have seen many presentations about how the “modern” DBA has to be intimately concerned with all layers of the application stack and cannot just focus on the database. I’m on board with that, but the reality is, I have rarely seen it in practice. The only time DBAs, developers, and netops folks seem to venture out of their silos is during crisis meetings and in emergency-situation chatrooms.

A case in point was when I was asked to troubleshoot an invalid cursor error on a client’s database. Here’s the helpful documentation Oracle provides for this error:

oerr ora 1001
01001, 00000, "invalid cursor"
// *Cause:
// *Action:

Hm. I guess that this is one of those things you’re just supposed to “know”. :)

Actually, invalid cursor is generally a problem with the application design. Perhaps the code is squandering resources and opening too many cursors. A common solution is to jack up the value of MAXOPENCURSORS. (Note that this is not an Oracle parameter as some people seem to think. It’s precompiler option. Meaning that you set this in a C header file and recompile your application in order to change it. But don’t ask me about this stuff; I’m a DBA, not a developer, remember?)

Well, there was no chance of throwing this problem back on the developers and saying “it’s your problem, fix it.” The application in this system is a black box, the source code is unavailable, and the vendor who wrote the software is long gone. Must be a pretty sweet time for the developers in this shop; they get to spend their days with their feet up or playing foosball. Thus it was up to us DBA-types to come up with a solution. Where to begin?

The error arose when a user of the application tried to change her password through the web interface. Our first thought was of course to look at the full error message, which provided the line in the code where the error arose:

09/20/2009 12:24:13 => User edit failed:
UserId=dolores
updateUser(): 62004
ORA-01001: invalid cursor
ORA-06512: at "APP_OWNER.SECURITY_CHECK", line 634
ORA-06512: at "APP_OWNER.SECURITY_CHECK", line 105
ORA-06512: at "APP_OWNER.USER_UPDATE_OPTIONS", line 2
ORA-04088: error during execution of trigger
'APP_OWNER.USER_UPDATE_OPTIONS'

The top point of failure (updateUser) was in the compiled app code, and inaccessible to us. But the rest of the stack was in the server-side PL/SQL code. The first of these (SECURITY_CHECK line 634) is a SELECT using CAST and MULTISET functions. What does that tell me? Well, I know that BULK COLLECT is often preferred over MULTISET because it involves fewer context switches between the SQL and PL/SQL engines… but that would be indicative of a CPU issue, and we seem to be dealing with a cursor cache memory issue here.

I have the client’s DBA try to recreate the error in the QA environment, but he can’t. That’s an interesting tidbit, but it’s not definitive because the QA and production environments are not (alas) completely identical. So there could be some configurable database parameter (shared pool? PGA?) that might help with this.

But rather than blindly start tweaking parameters, it’s time to look at what the user’s session is actually doing right up until the point of failure. I have my DBA buddy run the password update while I repeatedly query v$session but it goes by too fast; I am unable to identify his session amidst the regular user chatter (it’s not a terribly busy database, which is why I thought this might work). So I set up a logon trigger to capture user activity in a tracefile:

create or replace trigger enable_sql_trace after logon on database
begin
if( dbms_session.is_role_enabled('SQL_TRACE') )
then
execute immediate 'alter session set timed_statistics = TRUE';
execute immediate 'alter session set max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
end if;
end;
/

Then I set up the SQL_TRACE role and assign it to the application user:

create role sql_trace;
grant sql_trace to app_user;

The DBA runs his update, gets the error, but… no new tracefile appears in USER_DUMP_DEST. So I turn off my trigger…

revoke sql_trace from app_user;
alter trigger enable_sql_trace disable;

…and I go back to to looking at v$session. Why was no new session created? I look at the INACTIVE sessions… say, there’s a whole bunch of APP_USER sessions here that have been logged in since… last March! That can only mean… Connection pool!

The DBA said yes, indeed, the app server runs a Resin connection pool in the middle tier. Well, case closed. We scheduled a restart of Resin, all the cursors that the app sessions had been hanging onto were freed, and the problem vanished (for the next 6 months or so, anyway).

My point is, I could have been a much more effective troubleshooter if I had known more about the architecture of the full application stack. Situations like this inspire me to get outside of my database box and ask more questions… but really, this kind of groping in the dark seems standard; I see it all the time.

See Note 1007395.6: Common Causes of ORA-01001 for more background on ORA-01001.

Backup failure… One RMAN’s odyssey

Here’s a cautionary tale about not doing your homework first.

There’s this Oracle 10.2 database on a Windows 2003 server that I manage. I noticed a big hard drive attached to it that was just sitting there unused, so I asked the sysadmin if I could use it for RMAN backups, since they were starting to gradually consume more and more space. He said “sure.”

So I modified the backup script to use this new K: drive, but the next morning discovered the backup had failed with the following error:

RMAN-00571: ==================================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS ============
RMAN-00571: ==================================================
RMAN-03009: failure of backup command on c1 channel at 07/21/2009 22:46:18
ORA-19502: write error on file 
  "K:\ORACLE\RMAN\PROD1\PROD1_4364_1_692830812_RMAN.DBF", 
  blockno 524545 (blocksize=8192)
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 112) There is not enough space on the disk.
ORA-19502: write error on file 
  "K:\ORACLE\RMAN\PROD1\PROD1_4364_1_692830812_RMAN.DBF", 
  blockno 524161 (blocksize=8192)
ORA-27070: async read/write failed

Strange. I checked the disk; no files were there. Plenty of free space, though; over 400 GB (and the compressed backup size for this database is typically only 8 GB).

I reviewed the backup statistics recorded in v$rman_backup_job_details and found that the backup had failed after writing just 4,099 MB. It was also much slower than usual, running at 0.6 MB/s (usually it runs at 2.3 MB/s).

select
  (select instance_name from v$instance) || ' ' ||
    (select instance_number from v$instance) instance
  ,to_char(start_time,'yyyy-mm-dd hh24:mi') start_time
  ,to_char(output_bytes/1048576,'999,999,999.9') output_mb
  ,to_char(output_bytes_per_sec/1048576,'999,999.9') mb_S
  ,time_taken_display elapsed
  ,status
from v$rman_backup_job_details
order by start_time
/

INSTANCE   START_TIME        OUTPUT_MB MB_S  ELAPSED    STATUS
---------- ----------------- --------- ----- ---------- -------------
prod1 1    2009-07-18 21:00  8,846.5   2.3   01:02:59   COMPLETED
prod1 1    2009-07-19 21:00  8,852.6   2.3   01:04:08   COMPLETED
prod1 1    2009-07-20 21:00  8,921.5   2.3   01:04:51   COMPLETED
prod1 1    2009-07-21 21:00  4,099.0    .6   01:46:07   FAILED

Hmm, that 4,099 MB output size sounds fishy. Isn’t there something about a 4 GB file size limit on some Windows filesystems? How can I find out what filesystem is on that disk?

You could use the Disk Management snap-in (compmgmt.msc) but since GUIs are for sissies I use the DISKPART command-line utility:

d:\oracle\>diskpart

Microsoft DiskPart version 5.2.3790.3959
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: ORASRV-1

DISKPART> list disk

Disk ###  Status      Size     Free     Dyn  Gpt
--------  ----------  -------  -------  ---  ---
Disk 0    Online        17 GB  4080 KB
Disk 1    Online        34 GB      0 B
Disk 2    Online        34 GB      0 B
Disk 3    Online       137 GB      0 B
Disk 4    Online       137 GB      0 B
Disk 5    Online        34 GB      0 B
Disk 6    Online        34 GB      0 B
Disk 7    Online       466 GB      0 B
Disk 8    Online       466 GB      0 B

DISKPART> select disk 8

Disk 8 is now the selected disk.

DISKPART> detail disk

WDC WD50 00AAKS-00YGA0 USB Device
Disk ID: D6232DTD
Type   : USB
Bus    : 0
Target : 0
LUN ID : 0

Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 3     K   HD-W983      FAT32  Partition    466 GB  Healthy

Aha. K: is a FAT32. And the file size limit for FAT32 is 4 GB (or actually, (2^32)-1 bytes, which is 4 GB minus one byte).

OK, that explains why the backup failed. I can workaround that by tweaking my RMAN configuration so that it creates files no bigger than 4 GB-1, right?

RMAN> configure maxsetsize to 4294967295;

But wait… Is that something I really want to do? Take another look at that DISKPART output. The K: drive is USB. Dang. So that’s why the backup went so slow!

OK, that does it then. That K: drive is completely unsuitable for any Oracle-related purpose. If you have Metalink access you can read more about problems using USB drives with Oracle in Note 604494.1: Errors backing up to a USB disk device. Basically the slowness of the USB writes causes Oracle to fill up the memory buffer before it can get emptied, and the operating system winds up killing the Oracle process to protect itself. I should have taken a closer look at this disk before even attempting to use it for the Oracle backup.

The moral of the story: always look a gift horse in the mouth.

Testing disk I/O with the O_DIRECT flag

Sometimes I find the need to test disk IO rates, especially in the context of one of those delightful intra-office debates where the DBAs are blaming the lousy hardware for bad database performance and the sysadmins are blaming the lousy database.

You can perform a simple test of disk IO using the dd command. First, create a 1 GB file full of zeros:

dd if=/dev/zero of=1G bs=1024k count=1024

Then scan and time it…

time dd if=1G of=/dev/null bs=1024k count=1024

…and then use the output from that to calculate your MB/s.

This technique is flawed because it does not account for OS-level caching, which can give a sigificant boost to IO. The Linux 2.4 kernel allows programs to disable this caching by setting the O_DIRECT flag. Starting in Oracle 9.2 you can use this feature by setting the FILESYSTEMIO_OPTIONS init parameter to DIRECTIO or SETALL. (Direct IO is a controversial subject in the Linux community but Oracle has built significant functionality around it).

Therefore, if Oracle does not use OS-level caching (i.e. FILESYSTEMIO_OPTIONS is set to DIRECTIO or SETALL) , the results you get from the dd test will not be representative for your Oracle database.

The standard dd command does not provide a way to set the O_DIRECT flag (I think this has changed in RHEL5), but Oracle provides a free replacement for the standard Linux coreutils package, which contains the dd and cp commands. The folks at Oracle so generously provided this replacement package to help us debug Clusterware problems, but it can be used for other tasks as well. The replacement can be found at http://oss.oracle.com/projects/coreutils/files/

Oracle has not updated their “official” coreutils package since RHEL3, but there is an updated unofficial version for RHEL4 here: http://oss.oracle.com/~smushran/.coreutils/RHEL4-i386/

I was told that the RHEL3 version would work fine with Centos 4 (and, presumably, RHEL4), but the package installer complained about missing dependencies when I tried to force-install it, so rather than risk breaking something on a production server I sought a different solution.

I downloaded Robin Miller’s dt utility http://www.scsifaq.org/RMiller_Tools/index.html and this provided O_DIRECT functionality. To use it, simply unzip the archive for your platform and then move the single dt executable to your system path (or execute it directly from the unzip folder; it requires no special installation).

dt allows you to create a big file similar to one created by dd; then it will scan that file and calculate the transfer rate for you.

When you run dt in output mode, it performs both a write and a read test:

$ dt of=1G limit=1g bs=1m dispose=keep flags=direct

Write Statistics:
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 29059319 bytes/sec, 28378.241 Kbytes/sec
Number I/O's per second: 27.713
Total passes completed: 0/1
Total errors detected: 0/1
Total elapsed time: 00m36.95s
Total system time: 00m00.19s
Total user time: 00m06.30s

Read Statistics:
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 50840048 bytes/sec, 49648.485 Kbytes/sec
Number I/O's per second: 48.485
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m21.12s
Total system time: 00m00.13s
Total user time: 00m10.08s

Total Statistics:
Output device/file name: 1G (device type=regular)
Type of I/O's performed: sequential (forward)
Data pattern read/written: 0x39c39c39
Total records processed: 2048 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 2147483648 (2097152.000 Kbytes, 2048.000 Mbytes)
Average transfer rates: 36980948 bytes/sec, 36114.207 Kbytes/sec
Number I/O's per second: 35.268
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m58.07s
Total system time: 00m00.32s
Total user time: 00m16.38s
Starting time: Fri Nov 14 15:09:12 2008
Ending time: Fri Nov 14 15:10:10 2008

When you run it in input mode, it performs just a read test:

$ dt if=1G limit=1g bs=1m dispose=keep flags=direct

Total Statistics:
Input device/file name: 1G (device type=regular)
Type of I/O's performed: sequential (forward)
Data pattern read: 0x39c39c39
Total records processed: 1024 @ 1048576 bytes/record (1024.000 Kbytes)
Total bytes transferred: 1073741824 (1048576.000 Kbytes, 1024.000 Mbytes)
Average transfer rates: 55290516 bytes/sec, 53994.645 Kbytes/sec
Number I/O's per second: 52.729
Total passes completed: 1/1
Total errors detected: 0/1
Total elapsed time: 00m19.42s
Total system time: 00m00.09s
Total user time: 00m09.30s
Starting time: Fri Nov 14 15:10:38 2008
Ending time: Fri Nov 14 15:10:57 2008

Try running it without the flags=direct argument, and you will notice a significant difference in these stats.

See also:

Calculating the delta between two row values

In Oracle, the standard way to calculate the difference (delta) between two values is using the LAG() analytic function.

For example… Suppose you’ve got a table of numeric values–

create table t as
select rownum id, value from v$sesstat where value > 0
/

select * from t;

ID              VALUE
---------- ----------
1                   1
2                   1
3                 967
4                  10
5                   3
6                  71
7               14649
8                 175
9                4517
10                207
...

Here’s how you calculate the delta between one value in the VALUE column and the value in the previous row:

select id, value,
value - lag(value) over(order by id) delta
from t
/

ID              VALUE      DELTA
---------- ---------- ----------
1                   1
2                   1          0
3                 967        966
4                  10       -957
5                   3         -7
6                  71         68
7               14649      14578
8                 175     -14474
9                4517       4342
10                207      -4310
...

OK, that seems pretty straightforward. I bring this up because I was using subquery factoring clauses (“WITH clause subqueries”) before I found out about analytic functions, so this is how I used to approach the problem of calculating deltas:

with v as (
select * from t
)
select
b.id,
b.value,
b.value - a.value delta
from v a, v b
where a.id = b.id-1
/

ID              VALUE      DELTA
---------- ---------- ----------
2                   1          0
3                 967        966
4                  10       -957
5                   3         -7
6                  71         68
7               14649      14578
8                 175     -14474
9                4517       4342
10                207      -4310
...

That method (joining a table to itself) is fairly inefficient. To demonstrate, I gathered stats for t
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t', estimate_percent=>100, cascade=>true);

…and executed each query (the one using LAG, and the one using WITH) after setting
set autotrace traceonly

Here’s the execution plan for the method using LAG:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   826 |  6608 |     3  (34)| 00:00:01 |
|   1 |  WINDOW SORT       |      |   826 |  6608 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

…and here it is using the subquery factoring clause (WITH):

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   826 | 13216 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   826 | 13216 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |   826 |  6608 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

But the remarkable thing is the change in consistent gets:

using LAG:       4
using WITH:     62

This is cool because you can use LAG to retrieve a graphable table of wait events from the AWR that make it easier to spot problems. For instance, you can retrieve the time waited for some wait event such as enq: TX – row lock contention

select
to_char(a.begin_interval_time,'YYYY-MM-DD HH24:MI') day,
nvl(b.time_waited_micro,0) - nvl(lag(b.time_waited_micro) over(order by a.snap_id),0) time_waited_micro
from dba_hist_snapshot a, dba_hist_system_event b
where a.snap_id = b.snap_id(+)
and upper(b.event_name)=upper('&event_name')
and a.begin_interval_time between trunc(sysdate-&num_days) and sysdate
order by a.snap_id
/

and then graph it, helping you visualize how row locks have affected your database over time:

enq: TX - row lock waits

Three Normal Forms tutorial revised

After two months in the wilderness, my tutorial on the Three Normal Forms is back online.

I took it down because in the original, it had some serious flaws. While not factually inaccurate, the section pertaining to NF1 was vague and misleading, and actually belonged in the NF2 section. I have corrected and expanded the NF1 discussion, and I hope I have not obscured the simplicity of the original tutorial.

Thanks especially to Richard Moignard whose helpful feedback finally inspired me to make the changes.

Oracularly Certified

One of my projects this summer was to get Oracle Certified. I already felt myself fairly strong in most aspects of Oracle database administration due to a 9-month course I took at BMCC. I found myself having to explain too often that this was not the “official” Oracle certification, however, so I finally decided I had better get on the bandwagon and pay for some exams if I was going to be taken seriously as a DBA.

While the test isn’t exactly easy, the subject matter is frankly not very deep compared to what we did in the BMCC course. I also had a few qualms with the book I used to prepare for the exam, OCA: Oracle 10g Administration I Study Guide. Most noticable was the presence of several different voices in the text– understandable in a book written by 4 people, but a little more editing could have been done to smooth out the edges. This is especially true in the Flashcard app that comes with the book; some sections were written with careful explanations of the answers while others just said “true” or “false” without comment.

Also annoying were a few factual errors in the text, and vague, misleading, or incomplete answers to questions (for example, in practice test 1, question 18, there is no NAMES.DEFAULT_DIRECTORY setting in the sqlnet.ora file, guys)… I frequently found myself arguing with the book. It is hard to call this a flaw, however, because it inspired me to dig deeper into the Oracle documentation to find out the “real deal”.

Despite my misgivings I found the book useful and, on the whole, well written. I had a couple of local Oracle installations that I could play around with, but the book provides copious screenshots of the GUI tools that should allow people without adequate server resources to get a fair sense of what to expect. One final note of caution is that the book is geared for 10g Release 2, and I did all my practicing on a R.2 server, but Oracle is not actually using the Release 2 exam until November 1 2006. So I studied a lot of material that wasn’t even on the exam. I didn’t notice that until the night before.

Well, anyhow, I passed! And I now have the right to have one of these tatooed on my forehead:

10goca_logo.gif