Pythian Group

Welcome to the 151^st 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.”

Read the rest of this entry . . .

Welcome to the 147^th edition of Log Buffer, the weekly review of database blogs.

Let’s start this week, with blogs from the SQL Server world, where a number of excellent technical posts appear. Alexander Kuznetsov surprises his readers with this assertion: without ORDER BY, there is no default sort order. “Sounds trivial? Right, but different flavors of this myth still persist.  . . .  Because apparently many visitors agreed with this myth, I decided to post a repro script which demonstrates that this is simply not true.”

In their post on Read/Write deadlock, the rusano.com blog likewise was answering a question, how does a simple SELECT deadlock with an UPDATE? “Surprisingly, they can deadlock even on well tuned systems that does not do spurious table scans. The answer is very simple: when the read and the write use two distinct access paths to reach the same key and they use them in reverse order.” Examples and fixes follow.

Linchi Shea looks for an answer to this mystery—why the tempdb log keeps growing. Linchi says, “Whether it’s a user database or tempdb, when its transaction log is filled up or forced to autogrow, large long-running transactions should not be the only potential culprit for you to track down.”

The SQL Programmability & API Development Team Blog was also concerned by the matter of unexpected growth, in their case seeking to provide an answer to the question, Why did the size of my indexes expand when I rebuilt my indexes?

On a related matter, Paul S. Randal gave an example of a nasty cluster key on In recovery… Paul’s readers offers examples from their own experience of disastrous primary keys and clustered indexes.

Michelle Ufford, the SQL Fool, covers overhead in non-unique clustered indexes, giving a walk-through of scenarios in which the DBA will encounter this.

In the Oracle ’sphere, Read the rest of this entry . . .

Welcome to the 145^th edition of Log Buffer, the weekly review of database blogs.

MySQL

Since MySQL was surely the belle of the bloggers’ ball this week—why, everyone was talking—let’s begin with it.

Baron Schwartz started something with his post examining why MySQL might not benefit from having a mother ship. Dean Ellis of niflheim responded, arguing that everyone needs the MySQL mothership. And that got Sheeri’s Cabral’s attention—she took the middle path in her post, What If, and her readers had plenty to say.

Justin Swanhart gave news and opinion in one headline: MySQL documentation team announces docs will NOT be GPLed. Boo MySQL. Boooo, adding, “I’m now totally convinced that MySQL does not understand, and will never understand the MySQL community.” In his piece on MySQL docs freedom, Arjen Lentz wrote, “I believe this is a serious concern for the product as a whole, and hope this concern will be addressed by Sun Microsystems very soon – with action.” Read the rest of this entry . . .

Welcome to the 138^th edition of Log Buffer, the weekly review of database blogs. If you aren’t aware of who I am, my name is Nick and I am a Senior DBA at The Pythian Group. This is my second run at hosting Log Buffer, and I hope you will enjoy it as much as I did creating it.

As we see winter giving way to summer, I thought I would start with Informix.
Over at Informix-technology Fernando talks about FUD for thought where he talks about the future of Informix vs. DB2.

Sticking with IBM, I thought that we should take a look at what Bryan Smith says in this post about call for feedback, where he discuss the need for feedback on the Data Studio administration console.

I don’t know about you, but I’m tired of Informix. Let’s switch to DB2, and Henrik Loeser where he discuss the DB2 Information Center and does still mention Informix as well as DB2.

Let’s do some general SQL Read the rest of this entry . . .

Welcome to the 135^th edition of Log Buffer, the weekly review of database blogs.

How about a little DB2 news to whet the palette? On IT, Life, DB2 pureXML, House Construction, Henrik Loeser Friedrichshafen has an item about Organic Food and pureXML. Completely unrelated! In the on-topic second part of this duo, Henrik relates the news: “I am happy to tell you that the so far separately priced pureXML feature will now be included in the core DB2 for Linux, UNIX, and Windows.” And relevant links are included in this blog.

On the DB2PORTAL Blog, Craig Mullins admonishes, Don’t Forget DISPLAY as a Part of Your DB2 Tuning Efforts. Craig begins, “Although a DB2 performance monitor is probably the best solution for gathering information about your DB2 subsystems and databases, you can gain significant insight into ‘what is going on out there” using the simple DISPLAY command.” He goes on to provide, “a quick tour of the useful information provided by the DISPLAY command.”

On Informix Application Development, Guy Bowerman writes that it’s the last chance to submit papers for European IOD Conference, “taking place in Berlin, June 2-5 2009, and the deadline for submitting papers is Feb 13.” That’s today!

Read the rest of this entry . . .

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

Let’s begin with some SQL Server, where it was a week of technical tips. Alexander Kuznetsov looks at defensive database programming. “In most cases LIKE conditions should by followed by ESCAPE clauses,” he asserts, continuing, “You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure,” thus error-proofing your logic. Readers Alejandro Mesa and Adam Machanic suggest a couple refinements.

On In Recovery, Paul S. Randal shows how to move constraint indexes, taking as his cue a question he heard at SQL Connections Fall in Las Vegas: “Can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints?” Short answer: yes. “This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX.”

Piotr Rodak offers his example of the use of a numbers table, which “ . . . [implements] string split functionality for parameters passed as CSV arrays.” He asks for more examples, and Adam pops up there, too.

Stephen Forte has a method for using a TSQL Common Table Expression to find dupe records, which he was discussing at this week’s Tech Ed Europe. Nifty.

Speaking of duplicates. Mark, of Ajarn’s SQL Corner, wants us to know why he hates DISTINCT: Read the rest of this entry . . .

Welcome to the 119^th edition of Log Buffer, the weekly review of database blogs.

We start in the MySQL world with some engine news. On Brian “Krow” Aker’s Idle Thoughts, Brian explains the state of engines in Drizzle, the pared-down MySQL. He begins, “So many engines, and so little to choose from. This is one of our two major decision points in Drizzle right now.” Maria, Falcon, PBXT, and InnoDB are in the dock.

Arjen Lentz asks a simple question: Would you prefer InnoDB to be the default storage engine?, also the subject of a quick poll he created.

While we’re on the subject of defaults, Giuseppe Maxia, the Data Charmer reports on STATEMENT-based replication as the default in MySQL 5.1: “MySQL 5.1.29, the next (and last) release candidate, will revert the default replication mode to STATEMENT based  . . . MIXED mode won’t be the default anymore.” The commenters are not totally happy with that.

Well, one of the good things about MySQL is that, if you’re not pleased with it as it comes, you can soup it up yourself. Read the rest of this entry . . .

It’s time again for another edition of the weekly review of database blogs, Log Buffer. Since it was a big week for SQL Server, let’s start there, shall we?

The big news — SQL Server 2008 is released, as reported by SqlServer-qa.net, in seven different versions. Aaron Bertrand introduces a new kid on the block: SQL Server 2008 Web Edition — “. . . designed for highly available Internet facing web serving environments for the next generation Windows Server,” according to MS. (I’ve heard it rains quite a lot in Seattle. I guess it also Drizzles, too.) Says Aaron, “Basically, it has higher scalability than Express or Workgroup Editions : it supports up to 4 CPUs, no artificial limit on RAM, and unlimited database size.”

(Before you crack open your server case, Tara Kizer on Ramblings of a DBA has some advice on how to get physical CPU count on a server.)

The SQL Server Express Express blog helpfully offers a table outlining the differences in the features of the differences between SQL Server 2008 Express editions.

A couple little issues with the release have already emerged. Here’s Euan Garden on SQL Server 2008 Installation Confusion, VS 2008 Sp1 and NetFx 3.5 Sp1: “SQL Server 2008 has dependencies on (and includes) VS 2008 SP1 and its components (BIDS is just VS 2008, SSMS uses components), plus NetFx 3.5sp1. SQL includes the RTM versions of both of these, HOWEVER they are not broadly released yet. There is a check in SQL Server Setup that if you have an old version (Beta, RC, whatever) it will bounce the install and ask you to upgrade to the RTM bits…which of course are not yet available independent of SQL.” Click through for the KB and some other help. Denis Gobo and his readers have some more to say about this.

In all this change, you may lose sight of an important fact or two, and you may ask yourself, how did I get here . . . what version of SQL Server am I running? A couple resources are shared by Kevin Kline.

Read the rest of this entry . . .

Welcome the the 101^st edition of Log Buffer, the weekly review of database blogs.

This edition was originally claimed by Ward Pond for his SQL Server Blog. Unfortunately, Ward is, in his own words, “dealing with the aftermath of a burst appendix,” which is a very good reason not to spend your time at the computer. Ward, heal up soon! We’ll see you on LB before too long.

In lieu of the normal Log Buffer, I throw it open to our readers. Please leave a comment mentioning your favourite database blog items from the week that was, and anything else you care to say about them.

LB will be back to normal next Friday. See you then!

Welcome to the 89^th edition of Log Buffer, the weekly review of database blogs.

Welcome, welcome everyone.

In writing this week’s Log Buffer, I’ve had a chance to sit down and read some excellent posts on all sorts of platforms. The depth and breadth of what’s available to house and retrieve data is astonishing.

Many of you who have read my posts will know that I’m a fan of vegetables. They are something most of us don’t eat enough of. Come on DBAs! I think we need to make a collective effort to get healthy. We need you to keep all these systems alive. I say this because I have a new found appreciation for the work we do day in and day out.

Six months ago my wife and I said hello to our baby girl for the first time. I don’t say this to elicit any type of congratulations, but to illustrate something entirely different. If you have ever been to a hospital for any reason — to celebrate, to hope, or to say goodbye — you know the sheer complexity of the vast numbers of systems that need to interact. Daily, these systems save lives and help bring new ones to this world. I saw first-hand how the work I do on a everyday keeps the wheels turning.

Some of our customers run systems used by hospitals and I saw them in action. In a simple world, treating people can be done without technology, but this is an issue of scale, and our involvement directly affects the sheer masses of people whose lives are better because of our behind-the-scenes support. It’s true here, and it’s true for the most serious, most mission-critical systems, to the least critical and most trivial systems. The work done by DBAs from all platforms should be recognized for what it is.

I’m proud of what I do for a living and happy that I get to work in an industry filled with so many savvy folks. Oracle, Microsoft, MySQL, Postgres, IBM, and countless other organizations, and the people involved in them have together created an industry filled with opportunities and challenges, and above all, they have together elevated our ability to communicate and share. It’s in this spirit that Log Buffer was created, so let us proceed!

Since I’m an Oracle guy, we’ll let Oracle go first this time.

Read the rest of this entry . . .