Log Buffer #153: A Carnival of the Vanities for DBAs

This is the 153^rd edition of Log Buffer, the weekly review of database blogs.

Let’s start by revisiting a perennial issue with Craig Mullins addresses with the question, Are DBAs Obsolete? “Before we go any further, let me briefly answer the question posed in the title of this blog entry: ‘No Way!’,” writes Craig. “Every time I hear this it makes me shake my head sadly as I regard just how gullible IT publications can be.” He argues that an Internet-paced attitude regarding the work of the DBA may be the first culprit in the devaluation of the DBA’s work.

Oracle

Dion Cho, the Oracle Performance Storyteller, looked at attempting to get an object name from file# and block#, and, ” . . . met with a big disappointment on the performance.” And he found a promising workaround.

Laurent Schneider gave a lesson in how to select from a column-separated list.

Randolf Geist exposed the matter of dynamic sampling and set current_schema anomaly. “Sometimes when I’m asked to check a particular SQL in a production like environment at first I only get rather limited access, e.g. a read-only user . . .  If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . . but I recently have come across an interesting anomaly in 10.2.0.4 . . . ”

How many bind variables is too many, asks Kerry Osborne. (How about, as an arbitrary limit, when scrolling the output of a SELECT statement causes seizures?)

Here on the Pythian Blog, Alisher Yuldashev published his HOWTO on performing a manual Oracle 11g SE switch-over.

Tanel Poder shared a link to Jared Still’s new Metalink GUI poll. The issue: “A new version of the Oracle Support site is set be mandatory as of 7/31/2009. Many users of Classic MetaLink are unhappy with this move for a number of reasons.”

IBM DBMSs

Speaking of reasons, here are four more reasons to migrate from Oracle to DB2, courtesy Dave Beulke. Coming in at #1: “Oracle contract pricing continues to go up. Every time we look Oracle wants more money because we had to add CPU cores to get application performance.”

Here’s Omer Brandis with a db2 riddle: ” . . . since when does a rebuild index change data in the base table tablespace?”

SQL Server

Kenneth Downs, the Database Programmer, examined approaches to “UPSERT”. “[We] look at something called an ‘UPSERT’, the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases.”

The Rambling DBA, Jonathan Kehayias, published Understanding the VAS Reservation (aka MemToLeave) in SQL Server. “MemToLeave is a moniker, or even better put a misnomer.  . . .  The appropriate terminology . . . is the VAS Reservation. This area of memory in SQL Server is frequently the source of many questions online that relate to Out of Memory Exceptions inside of SQL Server, and it is one of the most misunderstood portions of SQL Server memory.”

Pinal Dave posted an informative item on the difference between line feed and carriage return as a T-SQL newline char.

Greg Low gave his review of Refactoring SQL Applications. Nutshell: “Not recommended for SQL Server users!”

MySQL

Baron Schwartz and his readers discuss gathering queries from a server with Maatkit and tcpdump. ” . . . we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.”

Elsewhere on the MySQL Performance Blog, Morgan Tocker asks, Is DRBD the right choice for me? ” . . . if you are installing DRBD with the aim of purely “availability”, and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.”

Ronald Bradford has a go at diminishing the confusion over global and session status. “I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables. With an EXPLAIN plan you see ‘Creating temporary’. Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.  . . .  There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table.”

Daniel Veeden has updated his MySQL Wishlist. Here’s his #1: “Per user and/or per database quota
Would very useful in setups for shared hosting. This would also prevent one database from bringing down the whole server. Separate tablespaces on different mountpoint can ease the pain, but I consider that a nasty hack. No update. Still problematic.”

To close, here’s Tony Bain with a link to some amusing database videos. I repeat: amusing database videos. I have to say though, this list cannot be called complete without Mogens Nørgaard’s outstanding footage.

That’s all for now. See you next time!