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

Hello, and welcome to the 171st edition of Log Buffer, the weekly review of database blogs. Let’s get it going this week with . . .

Oracle

Uwe Hesse, the Oracle Instructor look at result cache, another brilliant 11g new feature. He says, “There are many amazing New Features in the 11g version, one of them is the possibility to cache the result sets of statements, that access large tables but return relatively few rows. Think of it like automagically created materialized views inside the SGA.” Commenters contribute some thoughts on problems with result cache and latch contention.

Christian Antognini is, as always, Striving for Optimal Performance. He has a worthwhile post on instance caging, ” . . . nother small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance.”

Kellyn Pedersen wears rugged DBA Kevlar. Here is her post, ORA-01427: single-row subquery returns more than one row. “A developer friend of mine,” she writes, “sent me an email today . . . ‘I have a report query that worked fine on Wednesday and today produced [an] error. So when I went to track it back through Toad, the query just flat out stopped working.’ . . . Here was my explanation and a few hints to correct the problem . . . ”

Ah, developers. David Aldridge, the Oracle Sponge, has some fun with DBMS_Xplan.Display. He says, “Why not amuse and insult your developers at the same time by setting up the following situation . . . ” Oh, real mature, David.

MySQL

In case anyone needs reminding, Oracle RDBMS != MySQL RDBMS. So writes Mark Callaghan on High Availability MySQL. Once this precept is understood, Mark asks, “Can we get this done and return our focus to the roadmap for 5.4, 6.0 and the MySQL User Conference?”

Now that MySQL conference season is over, there are plenty of good technical posts.

In his MySQL Diary, Hazan Ilan shows how to produce random rows from a table. He writes, “A while ago, I were searching for a way to produce random rows from a table in MySQL. I found several solutions but none of them satisfied me. . . . My solution to this problem is using User Defined Variables.” The commenters offer some constructive criticism and variations on the theme.

Shlomi Noach shares his thoughts on restoring a single table from mysqldump. “Given a dump file (generated by mysqldump), how do you restore a single table, without making any changes to other tables?” writes Shlomi, and he offers two approaches: a security-based solution, and a text-filtering-based one.

Giuseppe Maxia writes, “Comparing database schemas is one of those DBA tasks that occur all the time. . . . If you can afford the luxury of having a GUI, then MySQL Workbench is the tool for you. . . . If the only thing at your disposal is a command line interface . . . then this Poor man’s schema comparison tool may come handy.”

SQL Server

How do you use SQL Server? That’s a question making the rounds in the SQL Server ‘sphere, and here are a couple answers. This is the Scary DBA’s one. He tagged Jeremiah Peschka and Tim Ford.
Tim replied, Good Data Saves Lives. And here’s what Jeremiah had to say (he has tagged Tim Benninghoff and Mladen Prajdic).

Here’s another blog opportunity. Adam Machanic is offering an invitation to participate in T-SQL Tuesday #001: date/time tricks. He says, “T-SQL Tuesday is the SQL Server blogosphere’s first recurring, revolving blog party. . . . If you bothered to read the title of this post you’re aware that the topic for this month is Date/Time Tricks.” Sound like a wingding! Go read Adam’s post for the full details.

Thomas LaRock, SQL RockStar, describes the finer points of restoring SQL 2005 Master using LiteSpeed. “[Do] you know how to restore master from a LiteSpeed backup? . . . What could be easier? Well, lots of things, actually. Turns out that unless you have your process rehearsed you could be fumbling for answers at 3AM. Not good times.”

PostgreSQL

From Jeff Davis’sExperimental Thoughts comes this post on Postgres and the Linux OOM Killer. “The Linux OOM Killer heuristic,” he says, “can be summed up as: 1. Run out of memory. . . . 2. Kill PostgreSQL. . . . 3. Look for processes that might be using too much memory, and kill them, hopefully freeing memory.” Yes, that would make any Postgres DBA feel kind of ranty.