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

Welcome to the 83^rd edition of Log Buffer, the weekly review of database blogs.

Little things can make big differences. Archimedes (no blogger, but a very smart guy) said, “Give me a lever long enough and a fulcrum on which to place it, and I shall move the world,” and With CLUE as (Select * from Random_Thought ORDER BY Common_Sense DESC) proves him right with a story of leverage: “This story is how changing one character in a 300 line stored procedure removed 90% of the impact of the worst single query on the entire server.”

On Third Abnormal Form, Paul McMillan shows that in SQL Server 2005, 1 and 0 may not quite equal True and False.

Mladen Pradić of I want some Moore writes, “Probably everyone is familiar with the Count(*) function in SQL Server. But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.” In Back to Basics: Count, Count, Count, Sum or how to Count, he gives a remedial lesson.

Roland Bouman has another little thing, the most misunderstood character in MySQL’s SQL-Dialect, the humble semi-colon.

On FruitCase’s Blog, Richard Case offers a little Task to run PowerShell scripts from within a SSIS package.

The SSQA.net blog gives a very nice introduction showing what it takes for a newbie DBA in resolving performance issues.

Eddie Awad covers Oracle SQL Developer’s better SQL formatter.

Staying with Oracle for the moment, Red Database Security’s blog reports on the First exploits for January’s CPU.

Integrigy’s Oracle Security Weblog provides some background on Oracle exploits. “Most Oracle vulnerabilities are very difficult to exploit solely based on the information delivered by Oracle. . . . However, all is not lost for the newbie, novice attacker. . . .security researches routinely publish detailed exploit code for at least a handful of the security bugs fixed each quarter. Any Oracle developer could easily execute almost all these published exploits. . . .Possibly an accounts payables clerk who did a little homework could exploit some of these vulnerabilities.”

Encryption can provide some security. Systems Engineering and RDBMS gives a howto on encrypted tablespaces in Oracle 11g: “Starting with Oracle 11g, we can now encrypt an entire tablespace instead of encrypting a single column. With the increased security concerns pertaining to data, this new feature can definitely help a lot. Data from encrypted tablespace remains encrypted when data is written to the operating system files or to a backup device.”

Tanel Poder pointed to what he deems an excellent article on Oracle 11g PL/SQL function result cache.

Jason Arneil delivers a howto on upgrading to Oracle 11g clusterware.

Pythian’s Gregory Guillou contributes a howto on setting up Oracle ASM on Ubuntu Gutsy Gibbon.

On the Ardent Performance Computing Blog, Jeremy looks into Oracle I/O and Operating System Caching, specifically on Linux systems.

Jonathan Lewis, on his Oracle Scratchpad, has a dbms_stats surprise. “It’s been several years since I started telling people that one of the solutions to dealing with optimizer problems was to adjust (or create) stored statistics using the dbms_stats procedure set_column_stats, set_index_stats, and set_table_stats. Imagine my surprise to discover an old (2003) Metalink note confirming my claim that it is perfectly reasonable to take this approach.”

By now you’re probably wondering where all the MySQL stuff is. I won’t make you wait any longer.

On Diamond Notes, Keith Murphy asks, Can We Scale? He writes, “I was laying in bed last night thinking. I should have been sleeping, but I was thinking about how MySQL could create a better product. Not for version 6.0 but post-6.0. What would it really take for our little dolphin to be the number one database product in the world not just the open-source world.” Click through for the rest of Keith’s reverie, followed by lots of worthwhile comment.

Lenz Grimmer reports on an interview with Marten Mickos, the CEO of MYSQL AB, in the immediate wake of its sale to Sun Microsystems. Lenz also links to an article by Mickos entitled, “Why Did I Change My Mind? or What Makes a Freedom Fighter Join a Giant? or Ten Reasons to Get Acquired.”

451 CAOS Theory responds to that with their thoughts on why MySQL sold out: “One of the things I like about Marten is that despite his enthusiasm and excitement he is at heart a realist. As he puts it ‘in a typical Scandinavian contrarian way: Perhaps we will be unable to maintain our passion within Sun. And at the most extreme, perhaps we should not have done this deal. The reality is it will take many years before we can judge this decision to know if it was the right course.’ You just don’t hear US executives talking like this.”

On Monty Says, Monty Widenius, another big man in the MySQL world, announces the availability of the specifications for the Maria storage engine.

Patrick Galbraith, (capttofu) in turn announces the release of FederatedX Pluggable storage engine. He writes, “I developed the Federated Storage Engine when I worked at MySQL, and really saw a lot of potential with it. The Federated Storage Engine is a proof-of-concept storage engine . . . that allows you to create a table which uses a network connection to a remote database table as its data source, as opposed to most storage engines that use a table file or table-space file as a data source.”

DBA Dojo has a very thorough round-three of its contest between MySQL and mysqlslap, a load-emulation tool.

On Whatever. . ., Jayant Kumar shows how to loop through some strings and perform the same query on several tables.

Carsten’s Random Ramblings has a wish-list item for MySQL — controlling the MySQL slow log. “It would be great to have the option to turn off logging to the slow query log on a per-statement basis. MySQL provides the SQL_CACHE and SQL_NO_CACHE modifiers for fine-tuning the usage of the query cache. Alas, there’s no DO_NOT_GENERATE_A_SLOW_LOG_ENTRY_AND_YES_I_REALLY_MEAN_IT modifier.”

Analytic Arts’s John Dzilvelis looks into unusable space in MySQL key buffers.

Mihai Criveti’s UNIX System Administration gives us MySQL, Oracle, DB2, PgSQL and Firebird versus Leap Years and Division by Zero. MySQL seems to lose these two bouts.

If you want more Rosetta Stone kind of stuff, take a look at the new MySQL vs Postgres Wiki, reported to us by Lewis Cunningham on his MySQL Database News blog.

The Revolution Systems Blog reports, with a summary and links, that PostgreSQL version 8.3 has been released.

On the Postgres OnLine Journal, Leo Hsu and Regina Obe give their howto on moving tables from one schema to another. They write, “As time goes by . . . [the] simple database you had that does one thing suddenly starts doing a lot of other things . . . Now to keep your sanity you really need the benefit of schemas for logical groupings. How do you retroactively do this? The answer is not quite as easy as one would hope.”

Tom Kyte has some images from his presentation stressing the unimportance of change/configuration management in databases, and a link to an item on Coding Horror that seems to say just the opposite. I guess I’ll never understand these gurus.

Finally, Kevin Closson links to Morten Egan’s and Mogens Norgaard’s latest video of an unconventional Oracle install exposing the great ease with which this can be done, even with a SAN and backups set-up. I think this demands a screen capture.

Oh, there’s also a DBA in a straitjacket. But we’re used to that already.

That’s all for now. As always, a reminder that Log Buffer needs you! Please read the Log Buffer homepage to see how you can get involved, then get in touch. See you in a week’s time for LB#84!