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

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

Starting with Oracle stuff, Chen Shapira (just a simple DBA on a complex production system) is looking for great PL/SQL. Why? To become a better PL/SQL programmer. “But,” she writes, “for PL/SQL , I’m a bit stuck. I can still read my own code for bad examples, but where can I find examples for great code?  . . .  Somehow, there is simply no open-source code written in PL/SQL that I can read to get a good idea of how PL/SQL should be written.” Niall Litchfield recommends the contents of $ORACLE_HOME/rdbms/admin. Any other ideas to help Chen?

There was no shortage of people offering their help, as always. Beginning here on the Pythian blog, Grégory Guillou posted the fifth of his ten-part series on Oracle silent mode, this part covering adding a node to a 10.2 RAC. Says Gregory, “Even if you don’t leverage RAC’s ability to add or remove nodes to gain in agility, it’s still very likely you’ll come to these techniques when you want to upgrade some of your Servers or Operating Systems.” The post includes links to the earlier articles in the series.

On his Core IT for geeks and pros, Tanel Poder expounds another use case for WaitProf. He begins, “I recently diagnosed a performance issue where the ‘events in waitclass Other’ occasionally took significant part of the session’s response time.  . . . So, what to do when this ‘events in waitclass Other’ wait becomes significant in the response time profile?” This is where waitprof, his custom tool, comes into play.

Jeff Hunter of the So What Co-operative posts Something to be aware of, Part II, in which he and Oracle Support tussle over NUMA in Linux, and where he is heard to say, “My next step is talking to a Duty manager. I really don’t want to do that.” And Part III, which Jeff begins, “I escalated my issue to a duty manager . . .”

On Oracle Applications DBA, Aviad Elbaz knows how to do tricks with a pipe, as he shows in his article on how to execute TKPROF on trace files larger than 2GB. Aviad, I think Bilbo and Gandalf would be proud.

Are you like me, shamefully ignorant about the whole Business Intelligence thing? Do you, too, secretly believe it might be an oxymoron? Mark Rittman is here to help, with an introduction to the Oracle BI Applications.

Vivek Sharma of Database/SQL Experiences informs us that he will be contributing a seminar on “Real Time Performance Tuning” to the very first event mounted by the IOUG (All India Oracle User Group). This is their TechNight, taking place in Hyderabad on Friday, July 18th.

There is more aid and succour to had in the SQL Server scene, of course. Brian Knight tells us that he will be hosting another free SSIS webinar, this one on administering, deploying and configuring SSIS packages.

Jason Massie points us to Aaron Lowe’s SQL Server 2008 screencasts on YouTube.

On In Recovery… Paul S. Randal announces public classes in the UK in September, covering Best Practices in Performance and Availability for SQL Server 2005/2008, and Indexing for Performance in SQL Server 2000/2005/2008.

Also in taking place in September, in Birmingham, UK, is SQLBits III, and session submission is open.

Back to Jason Massie, who points out Microsoft’s new security advisory on SQL injections. He writes, “If preventing this is not high priority for the Dev’s and IIS Admins that manage app’s that touch your db’s, you should make it so.”

Denis Gobo also looks a little deeper into MS’s tools to Address SQL injection attacks, showing both the obfuscated SQL used in many of these attacks, and the cleartext version.

Rob Carrol of The Premiers looks at some snazzy enhancements to Performance Studio in SQL Server 2008. He writes, “Along with Resource Governor and compression, it’s one of my favourite new features and a good reason to consider an upgrade.”

The sqldbatips.com blog also looks at something new in SQL 2008 in their overview of transparent data encryption.

Paul Neilsen shows us how to find duplicate indexes. And I would be remiss if I did not also mention Paul’s confession of his love for Canada: “I admit it. I love all Canada: maple sugar candy, loonies and toonies, Mounties, old town Montreal with its French history, the CN tower in Toronto, Stanley Park in Vancouver, and high tea at the Fairmont Empress Victoria.  . . .  Canada is pure Jazz.” Wow. And somewhere in there he gets around to mentioning his seminar at SQLTeach next month in Vancouver, B.C. — Smart Database Design.

Here’s Jason Massie one last time, with his post on open source SQL Server projects on CodePlex, highlighting three tools: SQL Server Cache Manager (“ubercool”); Open DBDiff (“Nice looking tool if you don’t use Data Dude.”); and SQLServerFineBuild (“I am definitely going to give this one a try next time I have to do a 2005 install.”).

Let’s look at MySQL now. On the MySQL Performance Blog, Baron Schwartz shows off some neat tricks for the MySQL command-line pager. Getting what you want from query output can be a big hassle. This piece has lots of good ideas, both in the article and the comments.

This just in: Arjen Lentz’s post on mysql cmdline tricks. Sheeri pipes up, saying she’s also on the case. Is it getting crowded in here?

Baron (and his dog) also show off, on his xaprb blog, his copy of his brand new magnum opus High Performance MySQL. Congratulations, Baron!

Back on the MySQL Performance Blog, Vadim is wondering, will Falcon fly? in the wake of its daddy, Jim Starkey, abandoning the nest. Vadim writes, “I expected some comments about Falcon future development from MySQL / Sun side. Jim was not just ordinary developer, but lead of project and main architect of Falcon and his leaving may change a lot. For now MySQL’s calm seems stunned or indifferent to Falcon’s destiny.”

Lukas Kahwe Smith of Poo-tee-weet offers a much commented-on item called, Prepared statements are dead, long live prepared statements?. Lukas writes that they are necessary in a world of fast-moving data, web-apps, and SQL injection attacks, but also necessarily flawed: “the database guys did not go back to the drawing board to solve the real issue: To have some way to send the SQL statement separated from the data in a single roundtrip.  . . .  So how about it? How about inventing a syntax that will do this for us?”

Kevin Burton has been thinking about detecting corrupt data in the MySQL protocol, specifically about the absence of a checksum or hashcode test of incoming data.

Alex Tomic has the first part of a series on testing MySQL sequential IO performance with different IO schedulers in the Linux kernel. Tis is fine-tuning, and it can make a real difference.

From the PostgreSQL world, Robert Treat’s responded to Lukas’s post on prepared statements, asking, “Could OSDB replace the SQL Standard Committee?” He begins, “If open source databases implement something, how likely is it to become a defacto standard?  . . . [The] number of people who have joined the tech industry within the last 5 years who have hands on experience with MySQL high enough that it is not uncommon for people to look for their syntax in other database products . . . And remember, Postgres is even more popular than MySQL in some places . . .”

This just in: Lukas responds to Robert’s response: PostgreSQL is already there!

Robert also lends a hand for dealing with out of disk issues.

And that is all. Next week’s edition will come from Andrew Clarke’s Radio Free Tooting. Of course, there’s room for your blog, too. Just send me an email, and we’ll get started.

Till next time!