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!