Log Buffer #151: A Carnival of the Vanities for DBAs
Jun 26, 2009 / By David Edwards
Welcome to the 151st edition of Log Buffer, the weekly review of database blogs. We’re going to take a fast tour through the best blogs from the week gone by, beginning this time, with Oracle.
Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”
Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”
Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers. . . . Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”
Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation . . . so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later . . . and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”
Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation. . . . So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”
The Open Query blog published Good Practice/Bad Practice: Table Aliases. “When writing queries, try making a habit out of using short table aliases, no matter how small the query is.” An example using the World DB follows, as does a worthwhile discussion.
Roland Bouman examined MySQL stored functions: the impact of DECLARE HANDLER on performance, responding to Get the error return value in a variable by Peter Gulutzan.
Ronald Bradford looked into using statpack with SHOW STATUS. He writes: “Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack. . . . Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings. . . . This post is more about detailing those little annoyances that I’d like to improve, or see improved.”
Morgan Tocker writes, “I think 5.1 gets some bad press for not being a compelling upgrade. It’s not the big features that make the difference, but the subtle ones. I wanted to highlight some of the these that may make your life easier when it’s time to upgrade . . . ” The post is Hidden gems in 5.1.
Let’s turn to SQL Server now, starting with a post from Aaron Alton, The HOBT. He writes, “If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command. . . . Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.” Click through for Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server.
Alexander Kuznetsov posted one of those skill-testing quizzes—calculating third Wednesday of the month with inline UDFs. Alexander begins, “Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as ‘third Wednesday of the month’, and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.”
Linchi Shea also was working the paradoxes: Performance impact: Logical scan fragmentation — 100% may not be as bad as 85%. “In [a] previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans. . . . The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.”
Reading is cool. I learned this from the Fonz. But look at this, Jeremiah Peschka says there are circumstances when less reading is a good thing. I’m confused! What else does he say? “Less reading is a good thing when you’re reading from a physical disk.” Ahhh. “During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called. . . . It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?” I bet he hits his server machine in just the right way.
In the DB2 world, Craig Mullins advised: know your ISOLATION levels. Craig writes, “Did you know that DB2 provides a way to change the way that a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement.”
That’s all for now. Please add your favourite blogs from this week to the comments. And of course, don’t forget that you can publish an edition of Log Buffer on your own blog. Just send me an email to get started.
Till next time!