Log Buffer #85: A Carnival of the Vanities for DBAs
Feb 22, 2008 / By David Edwards
Welcome the the 85th edition of Log Buffer, the weekly review of database blogs. Here we go!
We start with the obscure. Eddie Awad has started the Obfuscated SQL Code Contest on his Oracle Community site, thanks to an idea by Chen Shapira. If you’re familiar with this contest’s antecedents, like the obfuscated C or Perl contests, you’ll know how entertaining it is to see people turn their creativity to code that should never exist in the real world. Like Rob van Wijk’s interrogative submission, for example.
Chen herself (just a simple DBA on a complex production system) posts about the right way to handle Oracle on NFS and TCP throttling, reaching into relatively dark corners of Linux, like net.core.rmem and net.core.wmem.
Even the ordinary business of DBA can seem obscure. “Are any of you that run RAC in your production environments backing up your archive logs to an FRA that resides in an ASM disk group (and of course backing up the archive logs to tape from the FRA)?” That’s what Eric S. Emrick asks in his post, RMAN, RAC, ASM, FRA and Archive Logs, FYI.
On Halis way, Hampus Linden shows how to delete an object with a special character in Oracle. He writes, “There are some things in Oracle that are possible but shouldn’t be possible. One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it. . . . Horrible! And what’s even more horrible is that people actually do this.” Hampus attacks the horror with some PL/SQL.
On the Oracle Scratchpad, Jonathan Lewis links to a well regarded article on using Statspack, writing, “It’s been a few years since I last read this article from Connie Dialeris Green of Oracle about how to use Statspack – and I’d forgotten how good it was. . . . If you want to get the best out of Statspack . . . you need to create and validate a sensible hypothesis based on all the information available. This paper instructs you in the method.”
Jonathan also has an item on pushing predicates. “Some time ago I wrote a note . . . about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use ‘join predicate pushing’ when it is obviously a good idea.”
From Igor’s Oracle Lab comes an ecumenical piece by Gary Myers, who asserts that, while databases differ, problem-solving approaches don’t. He writes, “This entry is more SQL Server than Oracle, but it is generic in some ways, and its also got the closest I’ve found to v$sql in SQL Server 2005.”
In the SQL Server world, SSQA.net’s blog brings the news that the cumulative update package 6 for SQL Server 2005 Service Pack 2 is now available.
To go with that, Sean McCown of the Database Underground has some new Katmai install notes. He says, [For] those of you who don’t follow Microsoft that much, CTP is just a fancy way of saying beta. Actually, it’s not really all that fancy is it?”
On MaasSQL, an item offering an easy fix for poor performance. “I recently saw somewhere . . . a quote from Joe Celko, ‘You don’t have a table unless you have a key.’ . . . You have a poorly performing database. You can’t / don’t have time to re-write all of the stored procedures or many of them or whatever. Code can’t change right now. Run this script, find the tables without Clustered indexes and primary keys and start planning.”
Here’s a piece asking if an INT primary key truly need be an Identity on Jeff’s SQL Server Blog. “Ah, this is not an anti-identity rant, don’t worry! Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them — but not in the way you might expect. There is a simple rule I’d like to propose, let me know what you think: “If your client code, SQL code, or configuration files reference the primary key column of a table to determine any ‘application logic, that primary key column should not be an identity.’”
Dan Guzman says, don’t bloat proc cache with parameters. “Most of us have long been aware that non-parameterized ad-hoc queries can pollute procedure cache with entries that will never be reused. . . . However, I was surprised to learn recently that even parameterized queries can bloat procedure cache if one isn’t careful.” Dan demonstrates the problem and its prevention.
On his Diamond Notes, Keith Murphy looks into the future of MySQL for Your Database Warehouse. “It isn’t common knowledge, but the predecessor to MySQL, TCX (founded by Monty Widenius) . . . was a data warehousing company. That being said, MySQL isn’t known for serving as the centerpiece of a data warehouse. OLTP . . . is the most common use for MySQL. . . . Several weeks ago Monty made a few waves by announcing the release of a new storage engine – Maria.” Keith suggests that Maria might be destined for the warehouse.
Giuseppe Maxia, the Data Charmer, does some spring cleaning in MySQL supported platforms — it’s the end of the line for MySQL on several platforms.
Jay Pipes paints the big picture of MySQL, Sun, Linux, Solaris, and innovation. “if [MySQL under Sun] are to reverse the current course of a community-in-flux, we must embrace the fear we have of opening up our development process. We must get out of the cathedral and put up shop in the bazaar. We won’t be jumping out of the cathedral’s top window; more likely we’ll rappel down Rapunzel’s hair slowly and carefully. I see very good prospects about opening up in the future and I am excited. It will be happening in the nick of time.”
On mysqlguy.net another Jay, Janssen, wraps up a series of posts wit hone on using events to manage table partitioning by date.
When pet projects bite back! offers an excellent overview of using business rules in MySQL. “This article tries to explain how you can save time and effort on the development process by moving some of that development to the database. It does this by recommending you apply business rules to the database.”
The 451 CAOS Theory blog introduces Hypertable. “Hypertable is an open source (GPLv2) implementation of Google’s Bigtable, an internally-deployed database that serves the company’s web indexing, Google Earth and Google Finance services. According to Judd, the best way to think about Hypertable is as a traditional database, but one that trades advanced features like transactional capabilities for scalability: specifically thousands of commodity PCs.”
Brian “Krow” Aker’s idle thoughts on the matter — a couple more articles on Hypertable he picked up via Planet MySQL.
Ivan Zoratti offers his “webinar” on reducing the TCO with MySQL. “The main topic was the scale-out strategy with MySQL, based on Replication, MySQL Cluster, in combination with other HA solutions, such as DRBD.”
Not only can you reduce your TCO, you can also maximize your ROI at the MySQL Conference. So says George J. Trujillo Jr. on his MySQL DBA – An Oracle DBA’s Journey. He has seven points for getting the most out of this, or any other conference.
And while we’re on conferences, a final item from the Postgres scene. Dave Page’s PostgreSQL Blog reports that the first ever UK PostgreSQL Conference will be on April the 2nd, in Birmingham