Log Buffer #112: A Carnival of the Vanities for DBAs
Aug 29, 2008 / By David Edwards
Welcome to the 112th edition of Log Buffer, the weekly review of database blogs.
Oracle’s up first, starting with our old friend Doug Burns and his Time Matters series, in which he holds up to the light the concept of DB Time: “. . . [the] total time spent by user processes either actively working or actively waiting in a database call.” He continues, “There’s a lot more I could say about DB Time. Like all of the best performance concepts or methods (e.g. YAPP, Method-R) it can seem so obvious as to not be worth saying, but contains an enormous amount of common sense and technical rigour.”
Arup Nanda writes about the time he spent Diagnosing Library Cache Latch Contention. About half an hour, as it happened, but he’s a real pro, and his analysis just goes to show. To quote, Nuno Souto–who makes the best blog endorsements– “Damn useful stuff . . . bookmarked.”
Tanel Poder has another script for you to fall in love with, which makes its debut in flexible sampling of any V$ or X$ view with sample.sql. It is, writes Tanel, “ . . . a simple but powerful sqlplus script for ad-hoc sampling of any V$ view.”
Kenneth Downs, the Database Programmer, offers Advanced Algorithm: Sequencing Dependencies, a smart look at satisfying dependencies in databases. What does that mean? Well for example, Kenneth writes, “All popular Linux distributions have a package installation system in which each package lists its required dependencies. If you want to install a large number of packages in one shot, producing a tangled bunch of related dependencies, today’s algorithm can be used to work them all out.”
Laurent Schneider went off-road and came back something not on the map at all: the difference between rollbac and rollback.
SeguÃ©ing into SQL Server now, the SQL Programmability & API Development Team Blog illustrates how to create an autonomous transaction in SQL Server 2008. As they say, “It turns out to be a tricky thing to do since SQL Server doesn’t have built-in autonomous transaction support like Oracle.” Tricky, but not impossible.
Kalen Delaney believes that reading the transaction log is important enough to warrant a log reader tool, but not everyone agrees. She wants to hear from you: “How important do you think it is that Microsoft provide a log reader tool for us?” Lots of responses already.
Following up on his SQL Server wishlist, Aaron Bertand is pushing for more tools-related fixes for SQL Server 2011. This article links to all his pet issues on MS Connect.
If tall orders aren’t your thing, maybe large queries are. If so, you’ll appreciate this summary by Joe Chang of large query performance from SQL Server 2000 to 2008, 32 & 64-bit. He tests across versions the performance of a large query.
In the MySQL ‘sphere, Rob Young of The Open Product Manager writes about what he calls DBA Task #1: Finding Bad Queries, looking at the common approaches to the job. He favours MySQL’s Query Analyzer.
On the MySQL Performance Blog, Peter Zaitsev briefly states how to find wrong indexing with a glance view: “If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely be poor choice too.” That seems straightforward enough. But his readers have a lot to say about it.
Joe Izenman of SiteCrafting has part one of a series on statistical analysis in MySQL: Outliers, complete with methods and algorithms for identifying and handling them. You too can be saved by the bell curve.
The MySQL 5.1 use-case competition is extended until the end of September, as reports Kaj Arno. A chance to contribute a little something back. For prizes! You give a little, you get a little.
Greg Sabino Mullane of Greg’s Postgres stuff introduces OpenSQL Camp 2008, a project led by Baron Schwartz and others, which its website bills as, “. . . a free conference of, by, and for the open-source database community of users and developers.” A great–and overdue–initiative. Way to go, Baron et al!
There’s never a shortage of conferences. Adam Gartenberg links to Anil Mahadev’s pictures from the recently completed IDUG (International DB2 Users Group) India conference.
And that is all. See you next edition!