Pythian Group

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. Doug’s Oracle Blog has a series of posts about Hotsos. If all this talk about conferences has gotten you excited, Joshua Drake notes that 14 days and the hotel is almost full for postgresql conference east which is March 25th-28th in Philadelphia. And the Oracle database insider notes that the Oracle OpenWorld call for papers is now open.

According to Susan Visser this week (ending tomorrow) is also read an e-book week. So if you have not already done so, read an e-book! She links a coupon for an e-book in the post.
Read the rest of this entry . . .

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

MySQL

Blame it on MyISAM, says Mark Callaghan of High Availability MySQL, on considering sql_mode and type coercion. “I think that MyISAM has its place,” writes Mark. “It does fast table scans, but InnoDB is much faster on just about everything else. I am just not thrilled with the impact it has had on MySQL.”

Not that those other engines are without flaw. Peter Zaitsev reports on an InnoDB performance gotcha with larger queries.

Here on the Pythian Blog, Singer Wang unearthed a MySQL 5.1 and InnoDB hot backup gotcha.

Eric Bergen offers his InnoDB deadlock count patch, which he introduces thus: “[Deadlocks] usually aren’t a problem until they start happening too frequently.  . . .  [SHOW ENGINE INNODB STATUS] can be useful for debugging but it’s almost impossible to get the rate at which deadlocks are occurring. [This patch] adds a counter to show table status that tracks the number of deadlocks.”

Baron Schwartz had a script snippet to relative-ize numbers embedded in text to share. Read the rest of this entry . . .

This is the 158^th edition of Log Buffer, the weekly review of database blogs.

SQL Server

Simon Sabin has a TSQL Challenge – counting non zero columns. He says, “I’m working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them . . .  So the challenge is to find out the how many non zero columns there are, the twist is to use as little code as possible.”

On a cue from Simon, Aaron Bertrand shares a quick experiment in Unicode Compression on SQL Server 2008 R2. “ . . . what is going to happen.” Aaron writes, “is that NCHAR / NVARCHAR . . . columns, in objects that are row- or page-compressed, can benefit from additional compression, where realistically you can cut your storage requirements in half, depending on the language / character sets in use.  . . .  The difference is astounding: a space savings of roughly 60%, FOR FREE.”

Kimberly L. Tripp is here to tell us, Column order doesn’t matter… generally, but – IT DEPENDS! “SQL Server doesn’t care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last.  . . .  It’s all in the cost of the variable array’s offset values.”

Joe Webb of WebbTech Solutions exposes some some not-so-obvious side-effects UNION in a SQL query.

Aaron Alton, The HOBT, informs us that the Transact-SQL OVER clause is not just for ranking functions. “Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient . . . ”

Jason “Hutch” Massie blows his cover by revealing his secrets of SQL Server consultant. (Well, not really, but the badge is kind of a giveaway.) Jason writes, “Well, I hope you are not looking for them from me. All of my secrets are common knowledge now. I was hoping you would share yours with us. Just leave them in the comments. I promise not to tell anyone. They can be our little secret, dawg.”

MySQL

On the MySQL Performance Blog, Morgan Tocker explains why you don’t want to shard. (It has nothing to do with The Dark Crystal, I already checked.) A good post—lots of debate and discussion.

You don’t want to shard, but you might want a slice of SQL pie—Shlomi Noach’s SQL-generated pie chart, that is.

Jayant Kumar provides a nice backgrounder on document oriented data stores, and compares some examples—TokyoTyrant, MongoDb, and CouchDb—to MySQL 5.4.

Let’s go back to Morgan Tocker for a moment, and his post helping everyone in understanding the MySQL forks, complete with a handy family tree. Thank you, Morgan.

Mark Callaghan of High Availability MySQL has found a reason to use 5.1: “I can reduce the size of the patch I need to maintain extreme performance with MySQL.” Mark also has some remarks on four new features from InnoDB, Percona, and Google.

Oracle

Jonathan Lewis writes, “Here’s a thought for the weekend . . . ” When people talk about ‘index fragmentation’, what do they mean, and why do they care?  . . . ”would you let me know what you mean, and how you measure [it]. (I can think of three or four interpretations for the term – but I’m interested to hear from people who actually use it.)”

On So Many Oracle Manuals, So Little Time, Iggy Fernandez shares Great Expectations: An Interview with Tanel Poder, covering, among other subjects, Tanel’s background, Oracle ACE and certification, and OSs.

Let’s stay with Iggy for The Tenth Solution—that is, his own solution to the NoCOUG Challenge.

Here’s Alex Fatkulin demonstrating how to install Oracle Grid Control agents on a Windows failover cluster with no downtime.

On Striving for Optimal Performance, Christian Antognini discusses 11g’s improvements to system managed extent size.

Read the rest of this entry . . .

Welcome to the 157^th edition of Log Buffer, the weekly, cross-platform review of database blogs.

SQL Server

We start with Michelle Ufford, the SQL Fool, who gives us the poor (wo)man’s graph, a fast and ingenious way to create handsome text-based graphs.

What is the importance of running regular consistency checks? Paul S. Randal returns with some survey results and analysis. He writes, “The results are actually surprising – I didn’t expect so many people to be running consistency checks so frequently . . . ”

On SQLblog.com, Aaron Bertrand offers his nuanced approach to processing a list of integers. Aaron points to some other approaches; and his readers kick it around.

Jonathan Kehayias appears this week with with a rant: Got Performance Problems? Buy bigger hardware!. “ . . . The title of this thread is very tongue in cheek . . .  However, it is the most common answer I seem to get from application vendors these days when dealing with performance issues, despite the fact that I can point out a dozen reasons why their application design/code is the problem.  . . .  How does this relate to SQL Server? Read the rest of this entry . . .

Welcome to the 154^th edition of Log Buffer, the weekly review of database blogs. Let’s dive right in, shall we?

Oracle

On Radio Free Tooting, Andrew Clarke says, “No SQL, so what?” taking as his keynote something Nuno Souto said: “ . . . Google, Facebook, Myspace, Ning etcetc, and what they do as far as IT goes, are absolutely and totally irrelevant to the VAST majority of enterprise business.”

Aman Sharma gives an overview of Library Cache on Arista’s Oracle Blog.

On The Dutch Prutser’s Blog, Harald van Breederode gives a lesson in rolling cursor invalidation. He writes, “ . . . I call DBMS_STATS to create a histogram and I expected that dependent cursors would be marked INVALID afterwards but this simply didn’t happen.  . . .  Somehow I forgot, or maybe completely missed, the fact that cursors are invalidated in a rolling fashion since the introduction of Oracle10g.”

Miladin Modrakovic looks into another 10g-ish thing—Wide Table Select (Row Shipping): “Row shipping is feature which allows row data from the datablock to be shipped directly to the client.  . . .  Aperently [sic], this feature had some issues in earlier version of 10g and fix was to disable the ‘row shipping’ feature by default.Oracle introduced ‘fix’ in version 10.2 . . . ”

Dominic Brooks reports a gotcha in application contexts, “ . . . one of those feature behaviours which isn’t surprising, but you probably wouldn’t think about it unless you saw it.”

Who should tune SQL: the DBA or the developer? Read the rest of this entry . . .

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

Let’s start by revisiting a perennial issue with Craig Mullins addresses with the question, Are DBAs Obsolete? “Before we go any further, let me briefly answer the question posed in the title of this blog entry: ‘No Way!’,” writes Craig. “Every time I hear this it makes me shake my head sadly as I regard just how gullible IT publications can be.” He argues that an Internet-paced attitude regarding the work of the DBA may be the first culprit in the devaluation of the DBA’s work.

Oracle

Dion Cho, the Oracle Performance Storyteller, looked at attempting to get an object name from file# and block#, and, “ . . . met with a big disappointment on the performance.” And he found a promising workaround.

Laurent Schneider gave a lesson in how to select from a column-separated list.

Randolf Geist exposed the matter of dynamic sampling and set current_schema anomaly. “Sometimes when I’m asked to check a particular SQL in a production like environment at first I only get rather limited access, e.g. a read-only user . . .  If I’m now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command . . . but I recently have come across an interesting anomaly in 10.2.0.4 . . . ”

How many bind variables is too many, asks Kerry Osborne. (How about, as an arbitrary limit, when scrolling the output of a SELECT statement causes seizures?)

Here on the Pythian Blog, Alisher Yuldashev published his HOWTO on performing a manual Oracle 11g SE switch-over.

Read the rest of this entry . . .

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 . . .

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards’ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Read the rest of this entry . . .

This is the 148^th edition of Log Buffer, the weekly review of database blogs. Welcome.

PostgreSQL

Since PGCon ‘09 has concluded not long ago (and not far away), let’s start with Postgres stuff, much of which has to do with the convention.

Here are Robert Treat’s reflections on PGCon 2009, on his zillablog: “ . . . PGCon always presents the strongest line up of Postgres information available, and this year was certainly no exception.”

Josh Berkus was there, of course, and he sends two detailed reports: pgCon 2009 part 1, and part 2.

tail -f /dev/dim has a review of PGCon 09: “ . . . I found the experience to be a great one . . . ”

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 . . .