Log Buffer #26: a Carnival of the Vanities for DBAs

Welcome to the 26th edition of Log Buffer, a Carnival of the Vanities Blog for DBAs.

In addition, welcome to a new year! 2007 is shaping up to be a very interesting year for the database world, and some of the recent posts in the blogosphere should help you see why.

Many database professionals are wondering what they can look forward to in 2007 from their database vendor of choice. Some bloggers have gone beyond idle speculation and have published their opinions, predictions, and ideas regarding the New Year’s impact on their job.

Ronald posted his predictions for the MySQL database for the year in the ARABX blog. One of his predictions has already come true regarding the Falcon storage engine, which we will see later. I find his idea on government use of open source technology both intriguing and plausible as well.

Mark Rittman has a very lengthy and in depth article on his views of 2006 and upcoming technologies for 2007 in his Oracle BI Blog. It is definitely worth a read!

Even more Oracle plans for 2007 have been posted in an article by Don Burleson. Don makes several references to Oracle’s most recent acquisitions and marketing schemes, as well as the push for multiple database support within Oracle products. Another article by Don lists new features that will be in Oracle 11g and is updated frequently.

Speaking of Oracle’s competition, Kevin Closson has a great post referencing a recent article by Time Magazine that pits “Truly Dedicated” unpaid workers against “Geeks in Cubicles.” Though the original Time Magazine article is about web browsers, Kevin (and commenters to the post) bring up several parallels in the database world.

Thomas O’Connell posts a similar message in Infoworld’s Database Underground column citing several shortcomings of MySQL, which is widely regarded as the most popular Open Source database available.

But hey, you use what you use, and as DBAs we have to make the best of whatever database we work with. Frank Mash has a post about slow deletes on MyISAM tables in MySQL. The most interesting part of the post is his update, where he mentions a horrible practice that was found to be in place in application code. Beware, it could happen to you!

Oracle Partitioning has been touted as a great way to speed up access to large tables. Jonathan Lewis answers a very common question in his blog regarding partitions: how many should you have? Jonathan also gives a comparison with Oracle 8i for those that remember some of the old partitioning pitfalls.

Speaking of partitioning, David Aldridge posted a recent article regarding the need for partition key statistics. He also finds an interesting and somewhat disturbing decrease in performance related to his test case when using check constraints. David knows his partitioning; while nosing through his site I found an older post detailing his ultimate performance conquest, a 180,000x performance improvement using partition pruning.

Peter Zaitsev goes over some major enhancements for MySQL databases utilizing InnoDB including several bug fixes. His benchmark results from a variety of tests are also posted.

Moving back to Oracle, Kent Crotty has performed a study using the PL/SQL FORALL operator to test loading speeds. In his tests, he found that FORALL loading performed roughly 30 times faster than vanilla SQL inserts. This method is also highly touted by Dr. Tim Hall, owner of the highly popular Oracle-Base site, Oracle ACE of the Year 2006, and author of “Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming.”

Kevin Closson wrote an in-depth article on Oracle over NFS using EMC or NetApp products. Citing several papers on the subject, he provides a great level of detail for DBAs thinking of deploying their Oracle databases via NFS storage. Anyone who has read the works of Kevin Closson knows about his uncanny ability to read between the lines of any whitepaper (and inject commendable amounts of wittiness), and this article is no exception.

Don Burleson has written an article regarding a common debate: should tables and indexes really be segregated? Don inspects the reasoning behind both sides of the argument and gives some food for thought when planning your object layout.

Moving on to general tips, tools, and news, there is a lot of buzz in the MySQL world regarding the new Falcon storage engine. Pablo J notes some specific information gleaned from the new documentation surrounding the engine, which replaces choices such as InnoDB and BDB which are now owned by Oracle. He also mentions a performance concern regarding table and index segregation, just as we saw in the previous note.

Greg Sabino Mullane has developed and presents a tutorial on a Postgres type to find a row’s neighboring rows. While his implementation of the function was used to find (and hopefully repair!) a corrupt object (called relations in Postgres), it can be used for other purposes. The tutorial also shows the use of polymorphic functions, which can be highly beneficial for scalable coding.

I have always found dates and times to be a pain to work with in any database or language, and it appears Jeff Smith would agree when using SQL Server. He posts an excellent package of functions he calls the essential date and time functions that any SQL Server database should have. There are truly some excellent functions in here that no SQL Server DBA should go without.

Getting back to Oracle (hey, it’s my favorite), Andrew Clarke has Yet Another Null Article regarding the differences between NULL and empty, and what they mean to Oracle DBAs. It’s an important concept, especially for DBAs that must manage other database platforms that may behave differently. In fact, he notes a point made by Laurent Schneider (Oracle Certified Master and Oracle ACE) in his article that even Oracle treats zero-byte values differently depending on datatype!

Tom Kyte answers a question (surprise, surprise!) regarding how redo logs work in RAC environments. As a RAC instructor, I can say that I’ve heard the same question a time or two as well. Tom and Arup Nanda both provide great detail in response.

René Nyffenegger of ADP GmbH provides a great article on using flashback query to find changed values in a table. As the post mentions, one highly convenient use for this ability is to compare current data in a table against a previous data set.

Texas Memory Systems is continuing work on their free Statspack and AWR tool, StatspackAnalyzer. It’s actually quite good, an evolving example of an expert system for Oracle tuning. I have been invited to contribute new decision rules and refine alert thresholds, and TMS invites anyone to submit their own feedback to improve the tool.

Of course, don’t forget to check out the latest issue of Oracle Magazine (January/February), which has some great tips by Steve Feuerstein, Arup Nanda, and Tom Kyte. Of course I AM a bit biased, since I was interviewed for this issue’s Peer-to-Peer section.

Lastly, don’t forget to sign up for Rocky Mountain Oracle User Group’s (RMOUG) Training Days, February 14th and 15th in beautiful Denver, CO. The conference will feature several prominent speakers and promises to be an outstanding event. I will be presenting on three topics, so I hope to see you there!

Hello all! It’s been quite a while due to the holidays, but I’m back online and doing fine. I hope all of you had a great holiday season.

And after all this time, what do I have for you? The 10 most dangerous toys of all time, of course!

It’s amusing, though the descriptions of consequences can be a bit frightening at times. Have a look.

Radar Online

My favorite has to be the Gilbert U-238 Atomic Energy Lab.

For a mere $49.50, the kit came complete with three “very low-level” radioactive sources, a Geiger-Mueller radiation counter, a Wilson Cloud Chamber (to see paths of alpha particles), a Spinthariscope (to see “live” radioactive disintegration), four samples of Uranium-bearing ores, and an Electroscope to measure radioactivity.

And what nuclear lab for kids would be complete without an Atomic Energy Manual and Learn How Dagwood Splits the Atom comic book? (The latter was written with the help of General Leslie Groves, director of the Manhattan Project.)

I’ve gotten quite a few requests to do another parody song (probably thanks to Lisa for mentioning me in in the Oracle Newbie’s Blog!), so I went ahead and wrote one. This is is The Life of DB Admins, sung to the tune of Hotel California by the Eagles. Enjoy!

—
Late at night at the office, I’m the only one there
Many new tasks to get done, lots of things to repair.
While setting up some table grants, to keep security tight
My head grew heavy and my sight grew dim
I want to stop for the night
There it was on the display;
A piece of query hell
And I was thinking to myself
Let’s give up Oracle and just go Excel
But I made a new index, and I started to pray
For an ending to the query war,
That it would end today…

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Always at work in the life of DB Admins
Any time of year, you will find us here.

Their queries don’t use indexes, and the, where clause never ends (uh)
They got a lot of inefficient joins no DBA recommends.
How they fill up the ORDER BY, disk sorts galore
And then for no reason, they add even more.

So I grow the PGA
Hoping sorts will decline
But then I have to free up some more RAM from my well planned design
And now there’s more full table scans, tons more each day.
With every new line of code that they write
I feel more dismay.

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Living to work in the life of DB Admins
What a nice surprise, loads are on the rise….

CBO is reeling,
Gathered stats once or twice
And I thought, ‘maybe I should just query for some DB_CACHE_ADVICE,’
Or I’ll add a new MView,
Hints are last but not least
I’ll do anything I can do
Until disk reads have all ceased.

Last thing I remember, I was
Looking to explore
A way to get the whole server
Off the rack to smash to the floor
‘Relax,’ said my manager,
Though the code does make you grieve.
You still have a lot of work to do,
Get it done or you’ll never leave!

This is way overdue, but I figured I’d post here about my trip to Jamaica. I got to go here via some work with Burleson Consulting; I even took my family.

Here’s a great picture of my kids chilling next to a beautiful waterfall at the hotel:

And I can’t forget the view…this was the view from our 17th floor balcony:

Jamaica’s an awesome place, full of cultural variety, excellent weather, and tons of things to do. We all had an outstanding time together and hanging out with the locals. Jamaica Night at the hotel was a special treat…the band was awesome, and I got them all to sign a little souvenir guitar to give my dad.

All in all, a wonderful trip. It’s nice working for a company that pulls clients in tropical environments!

It’s funny, right after writing The Day the DB Died, the American Pie parody about a DBA that loses their database and their job due to not having backups…the disk dies on my non-backed-up dedicated server that houses this website. Oracle Alchemist was down for a while, but he wasn’t out.

Got a fresh drive put in, reinstalled the OS, mounted the failed drive (/tmp was corrupted) and copied everything back over…voila! Lead into gold, the Alchemist lives to blather on aimlessly another day.

Anyways, sorry for the inconvenience to anyone affected by this horrible and dire tragedy.

I have a habit of writing parody songs about things I like, and Oracle is one of those things. I’ve never seen a full on Oracle parody song, so I will make the claim that this is the first. If it’s not, I’m sure it’s the first of this magnitude!

Now, the tragic epic of a DBA that allowed his system to go on without backups, to the tune of American Pie by Don McLean. It’s a bit long, so I have it with a “read more” link. Enjoy!

—
A long, long, time ago…
I can still remember
How an index used to make me smile.
And I knew if I had my chance
The database would be enhanced
And maybe it would run well for a while.

But doing upgrades made me shiver
With every version I delivered
Bad news in the GUI
The progress bar was screwy.

I can’t remember if I cried
When the data dictionary came out fried
I just wanted to run and hide
The day the DB died.

So bye bye to the database guys,
Sent a TAR to MetaLink,
But we got no reply
And the managers asked, “Is that all you can try,”
I said, this will be the day that it dies…
This will be the day that it dies.

Read the rest of this entry »

I just found a link to a command line wrapper for SQL*Plus called GQLPlus. Supposedly this wrapper allows historical editing, table/column name completion, and more.

Even better, it’s just a precompiled drop-in with SQL Plus. Download and run; nothing else is required. From the website:

“gqlplus is a drop-in replacement for sqlplus, an Oracle SQL client, for UNIX and UNIX-like platforms. The difference between gqlplus and sqlplus is command-line editing and history, plus table-name and column-name completion. As you know if you have used sqlplus, it is notoriously difficult to correct typing errors and other mistakes in your SQL statements. sqlplus does give you ability to use external editor to edit a statement, but only the last statement you typed. gqlplus solves this problem by providing the familiar command-line editing and history as in tcsh or bash shells, and table/column-name completion, while otherwise retaining compatibility with sqlplus. Thus, no user training is needed – simply use gqlplus instead of sqlplus. In addition, configuration/installation is trivial: gqlplus is a single binary compiled executable (written in C), so all you need to do is download it and put it anywhere in your PATH. After that, you’ll be ready to use it.”

I haven’t used it yet, but it looks really cool. Anyone out there who has given it a shot?

Oracle Streams is an outstanding way to get data from one server to another. It is NOTHING like the advanced replication of old, which is why I call it “outstanding” and not “horribly inefficient and error-prone, not to mention annoying.”

If you don’t know anything about Streams, you can catch up by reading a good book, use a quick tip, or read some documentation.

Recently I was asked how to implement a custom INSERT handler for a streams setup. The client had the following situation:

• A production table that housed important data
• A history table that was inserted into every time a record on the production table changed
• A remote database meant to hold history

The client wished to set up Streams as follows:

• On insert to the history table, attempt to stream the row across to the target DB
• On successful apply to the target DB, remove the row from the source DB history table
• Do not propagate deletes from the source to the target

Thankfully, it was pretty easy. We used a combination of a negative propagation rule for deletes and an apply-side DML handler for inserts. Let’s take a look.

First Step: Halt Propagation of Deletes

This was accomplished using the ADD_TABLE_PROPAGATION_RULES procedure of the DBMS_STREAMS_ADM package with a negative rule on DELETE. A negative rule simply means that we want to set inclusion to FALSE if the condition is TRUE.

  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'streams_test.test_hist',
    streams_name            => 'prop_test',
    source_queue_name       => 'streamsadm.rep_capture_queue',
    destination_queue_name  => 'streamsadm.rep_dest_queue@targetdb',
    include_dml             => true,
    include_ddl             => true,
    source_database         => 'primarydb',
    inclusion_rule          => false,
    and_condition           => ':lcr.GET_COMMAND_TYPE() = ''DELETE''');
END;
/

As you can see, we have set up a rule for stream prop_test, on table streams_test.test_hist. This rule applies to rows coming from the rep_capture_queue locally and going to the rep_dest_queue on the target database. We say to include DML, and include DDL; however, we also say in the and_condition that if the LCR (Logical Change Request) type is DELETE, to NOT include it (inclusion_rule => false).

That takes care of deletes. No deletes on that table will propagate, though anything else will.

Create a Handler on the Target

The next step was to create a procedure on the target database that would be used as an Apply handler. This procedure is owned by streams_test, the actual user being streamed.

create or replace procedure streams_test.del_orig_lcr (in_any in sys.anydata) is
  lcr sys.lcr$_row_record;
  lcrrow sys.lcr$_row_list;
  rc pls_integer;
  pkdata sys.anydata;
  pkval number;
begin
  rc := in_any.getobject(lcr);
  lcrrow := lcr.GET_VALUES('NEW');
  lcr.execute(true);
  for i in 1..lcrrow.count
  loop
    if lcrrow(i).column_name = 'ID' THEN
      pkdata := lcrrow(i).data;
      rc := pkdata.getNumber(pkval);
      delete from streams_test.test_hist@primarydb where id = pkval;
      commit;
    end if;
  end loop;
end;
/

This little piece of code will accept an LCR (Logical Change Request), and extract the values. First, it executes the LCR on the apply side. If that is successful, it finds the primary key column (in this case ID), and deletes it from the remote database. Note that in order to do the delete, you will need a database link pointing back to the source DB. When the delete is processed on the source database, it wont propagate over to the target because of the rule we made above. If the insert to the target does not work, it will not delete from the source. We have covered all our bases to have a process that will ONLY delete the original if the apply works properly.

Last Step: The DML Handler

The last step is to tell Oracle that it should do something special when the apply process runs. To accomplish this, we will set up an apply handler on the target database (not the source):

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
     object_name		=>	'streams_test.test_hist',
     object_type		=>	'TABLE',
     operation_name		=>	'INSERT',
     error_handler		=>	false,
     user_procedure		=>	'streams_test.del_orig_lcr',
     apply_database_link	=>	null,
     apply_name			=>	null);
END;
/

As you can see, we tell the apply that if DML comes in of type INSERT, to run the streams_test.del_orig_lcr procedure. We’re rocking and rolling now!

The Test – Finale

The following test was run on the primary database:

SQL> select * from streams_test.test_hist;

no rows selected

SQL> insert into streams_test.test_hist values (2, 'Jeff', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from streams_test.test_hist;

no rows selected

SQL> insert into streams_test.test_hist values (2, 'Kristina', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from streams_test.test_hist;

no rows selected

SQL> select * from streams_test.test_hist@targetdb;

        ID CHARFIELD                                          THEDATE
---------- -------------------------------------------------- ---------
         1 Steve                                              10-NOV-06
         2 Kristina                                           10-NOV-06

SQL> insert into streams_test.test_hist values (2, 'Duplicate', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from streams_test.test_hist;

        ID CHARFIELD                                          THEDATE
---------- -------------------------------------------------- ---------
         2 Duplicate                                          10-NOV-06

SQL> select * from streams_test.test_hist@targetdb;

        ID CHARFIELD                                          THEDATE
---------- -------------------------------------------------- ---------
         1 Steve                                              10-NOV-06
         2 Kristina                                           10-NOV-06

Conclusion

We inserted two records on the primary (source), and both of them propagated across to the target and deleted on the source side. However, when we tried to force an error with a duplicate entry for the PK on the ID column, notice that the row stayed in the table on the primary. This way, we are always guaranteed to keep our data, even if there is a streams error or some other kind of issue.

It looks like I got everything looking good on IE6 and 7 again. If you notice any pages that don’t look right, please leave me a note by clicking on the Contact link.