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

Jul 21, 2006 / By David Edwards

Tags:

Welcome to the second weekly edition of Log Buffer, a series of “Carnival of the Vanities” blogs for DBAs. (I get to call it a series now that there’s more than one.)

Let’s begin with some security-related news. Tonight on Fox — When SQL Attacks! There was quite a lot of chatter this week about an apparent increase of SQL injection attacks. SEO Expert Blog features video showing how simply this kind of crack can compromise databases with insecure web front-ends. Although this is not new, it’s unsettling to see the tools you use turned to mischief. Of course, laypersons will be frightened by any story that involves “injection” and “attacks” — let alone “SQL”, which is kind of scary in its own right.

Also Eddie Awad‘s Blog points to this real-time demonstration of an SQL injection exploit. Watch this and know thine enemy. If you work with developers, show it to them so that they know too — after all, your enemy’s enemy is your friend, right? (Of course, don’t forget that social engineering remains an equally dangerous vulnerability for your company’s production data.)

Oracle released its quarterly Critical Patch Update on Tuesday, fixing 65 vulnerabilities, and on Wednesday, Pete Finnigan’s Oracle security weblog had Pete’s critical but optimistic view of it, with a link to an interview with John Heimann, Oracle’s director of security program management and Darius Wiles, their senior manager of security alerts. I wonder if it’s even possible for an entity as large as Oracle to move as nimbly as their success demands?

Elsewhere in the Oracle blogosphere, Chris Foot of dbazine writes about the Bigfile functionality in Oracle 10G. In this context, “Big” means terabyte. That big. 10G can do it. Nonetheless, Tim Procter tells me that he knows someone who is disappointed that he can’t put more than one bigfile in the same tablespace. No pleasing some people.

The Pythian Group’s Christo Kutrovsky, just back from vacation, got straight to work and wrote this article on moving tables across schemas in Oracle without recreating their data segments or indexes, a task heretofore thought impossible. Bravo, Christo!

When Eddie Awad says that Oracle Trace Analyzer is TKPROF on Steroids, he does not mean that it has an unnaturally high-pitched voice. His thorough review covers what Trace Analyzer is, where you can get it, how to install it, and more.

Oracle has opened the gates to its official documentation, and Eddie covers that too.

Inexperienced DBAs too often omit indexes when they create tables, in the opinion of Coding Horror’s Jeff Atwood. He wonders Why Can’t Database Tables Index Themselves? I can hear you scoff (I have a Firefox plug-in for that), but he suggests some theoretical guidelines along which that could work. He gets some answers from his readers, too.

eWeek recently published the results of tests comparing the Open Source and .Net dynamic web stacks. Peter Zaitsev of the MySQL Performance Blog finds fault with eWeek’s methodology, and even with what they thought they were testing. He asks why the results of their comparison of LAMP versus WAMP are so lop-sided. Nice thinking, Peter — that is useful scepticism.

Peter also gives notice of a patch that enables stack traces in MySQL on the x86_64 platform.

Giuseppe Maxia, The Data Charmer implies that for MySQL, greater capability brings more complexity, and his post asks for a de-baffling of a section of the manual’s entry on clusters.

On gilfster, Andrew Gilfrin mentions a really snazzy-looking new monitoring tool for MySQL called Spotlight, which is produced by Quest Software. Real macho DBAs don’t need this kind of thing, of course.

Nor do they need dolphin knits. Undaunted, My-ess-queue-ell vs. My-see-quell‘s Sheeri Kritzer proudly shows off her knit pattern of Sakila, the MySQL dolphin, and includes the source code, naturally. “Sakila” is a Polynesian word for “swift little porpoise who catches up to big whales and nips their flukes on the way by”. Or so I imagine.

MySQL is on the agenda at next week’s O’Reilly Open Source Conference 2006 in Portland. Jay Pipes‘s Design, Develop, Discover, Define provides a summary of the conference’s MySQL-related activity.

On the other side of the OSS DB fence, PostgreSQL is also on the roster, and Josh Berkus has published a table of all the conference’s Postgres-specific events. Josh also points out this wrap-up of the PostgreSQL Anniversary Conference.

And Robert Treat’s zillablog has a helpful pointer to a tutorial on using PostgreSQL on Windows.

A couple DB2 items now. Willie Favero of ITtoolbox Blogs gives the first of a two-part overview of DB2 V8’s data compression capabilities. He points out that it can have a positive effect not only on storage demands but also on performance and logging. He also offers some info on IBM’s DB2 Tech Conference.

VMware began offering its VMware Server for free this week, and Howard Rogers of the Dizwell Blog believes it’s a DBA’s best friend, as it (and other virtualization software) allows you to dicker with Oracle under different host operating systems, and with new configurations, without getting your working systems in trouble. Howard says also that the performance of the Server edition is comparable to that of the VMware’s lighter products.

There are only two answers to a first question, according to Tom Kyte. He’s talking about how we deal, and how we should deal, with clients. They might know the TLA (ten-letter acronym) yet still not know what they want.

Positive Sharing provides another example of turning a truism — “the customer is always right” — on its ear, this time from the DBA Manager’s perspective. The word is, you may be right, and what’s more, your manager might believe so too.

On his OLAP BI IM stuff blog, Duncan Lamb points to a talk on how to get more out of a DBA (his words). If you saw it, I’d like to know what you thought of it.

Today I was shown three excellent items looking into larger questions about the work and responsibilities of the DBA. James F. Koopmann on ITtoolbox writes, DBA, You make an impact, encouraging DBAs to look up from their consoles and take a broader view of their role, for both their own good and that of their organizations.

In a related item on Computerworld Blogs, David Foote answers a reader’s question about the relative merits of database administration and IT auditing as career paths. David believes the latter of the two can be a productive and rewarding career in IT maintenance, perhaps without the never-ending learning curve that the DBA must climb.

In the third, Doug Burns asks What’s A Development DBA? His answer is, something less than it used to be. Read his thoughtful item to hear why he thinks that is.

Even the CBC has a blog now, and their Blake Crosby reveals that the whole cbc.ca site is thick with Open Source Software. Here’s a preview: they run PostgreSQL. I knew you were wondering.

Archive Log

Going about my appointed rounds, I found these older items interesting, both courtesy of Giuseppe Maxia, The Data Charmer. He has a short introduction of logs on demand in MySQL, a feature available in the development tree.

He also mentions and provides links for a tool called myqslresources, which he says allows you to get statistics about a server “from the outside”.

That’s a - 30 - as they say in journalism. Bill Thater of gruntdba will be editing and publishing the next edition of Log Buffer on Friday 28, and I’ll post a link to it here. We’re always looking for others to present their retrospective on DBA blogs too. Have a look at About Log Buffer to find out how it’s all done.

‘Til next time!

9 Responses to “Log Buffer #2: A Carnival of the Vanities for DBAs”

  • Paul Vallee says:

    Great Job, David. I look forward to seeing Bill’s next week!

    Paul

  • Doug Burns says:

    David,

    A couple of points of order :-

    1) Surely steroids would result in a suspiciously *low*-pitched voice?

    2) I feel a little uncomfortable with you using the word thoughtful in reference to me.

    Otherwise, nice job.

    Cheers,

    Doug

  • Paul, David,
    Nice job! I like non-Oracle related part as well – it makes our vision broader.
    Thanks! Alex

  • Here’s the real story of Sakila — it’s SiSwati.

  • I’ve got a thought on the same note as “Why Can’t Database Tables Index Themselves”.
    When CBO came to Oracle many people wondered – why can’t Oracle collect stats on their own? Well, here we go in 10g with GATHER_STATS_JOB.
    Results? Well, if your database is something that can be managed in Microsoft Access – not a big deal – the job will work fine. For more or less serious implementations – good DBA leaves no place for a chance.
    But who knows if Oracle won’t adapt this self indexing idea soon… At least, they should start an internal project on this feature – marketing will be very happy! :-)
    Or maybe someone is brave enough and has enoug time to start an indepedant project – like “Oracle Self-indexing Wizard”?

  • Paul Vallee says:

    Alex – re self-indexing tables. I think it could work too. It could be done using a mix of sql replay and genetic algorithms.

    What I would have in mind is a process for a server to identify “idle resources” and off-peak time.

    Then, identify expensive sql, preferably further characterized by its routine execution.

    Then, during off-peak time and scheduled with idle resources (dba could set a cap on resources assigned to this task), simply have the system go ahead and create “random” indexes on these objects and re-play the captured sql (and even other concurrent captured sql). The key is to then engage the genetic algorithm component, and either kill (i.e. drop the index) or mutate (i.e. play around with columns, column orders, etc.) and iterate.

    This would be challenging but would conceivably propose good indexing improvements, either for automated implementation or review.

    Your thoughts?
    Paul

  • Paul,
    Well, looking at the ADDM and Automatic SQL Tuning features in 10g, auto indexing functionality has been already partially implemented or at least basis of self-indexing has been created.
    DBMS_SQLTUNE can recommend creating of certain indexes and/or drop/change existing ones. Not that I would trust it too much now but the trend is here and Oracle 11 may well already have a hidden parameter to run automatic SQL tuning tasks and hidden parameter(s) to control the types of automatically accepted recommendations.
    Moreover, it doesn’t have to be limited to self-indexing but can include “self-materialized-viewing”, self-profiling (this is the one I like much more), self-sql-rewriting and etc.
    Indeed, looking at the automation that Oracle is introducing in such areas as UNDO, PGA, SGA, statistics, parallelism, and etc. I won’t be much off-track asserting that automation and self-tuning is the most probable direction for future Oracle innovations.
    Being a bit sarcastic (as usual) about GATHER_STATS_JOB, I should add that it’s just a first implementation and requires particular attention from DBA’s as any new feature enabled by default. Further improvements and, more important, increased awareness and understanding how to control and tune it, should make this job much more useful than it is now.
    Do you want to bet which Oracle version will include default self indexing feature?
    Cheers,
    Alex

  • John Scoles says:

    SQL injection attacks are a nasty annoyance but can be easily avoided it developers simply followed a few hard and set rules.

    1) Use Stored Procedures instead of inline SQL is one sure way of preventing these attacks –This of course brings up the problem of getting the SP written.
    You either need a DBA with allot of time on her hands (ha!) or a developer who can also write a good SP (double Ha!!) neither of witch are that common.

    2) Use named SQL place holders in your inline SQL. This makes it much harder for the hacker to get in but it is not 100%.

    3) Use some sort of Persistence engine such as Enterprise Java Beans, Perl Beans or alike. This of course brings up the problems of speed especially on the web.

    4) Limit the rights of the web server to just the data views and cursors needed. This can also works very well with SP in that you know that the web server can only access a know set of objects.

  • will dieterich says:

    The only rule that needs to be followed to advoide SQL injection is
    ‘Use parameterized SQL!!!!!!!!’ every modern language provides it in some form.

    Stored procedures don’t stop sql inection, it is that most people when using stored procedures do it using parameteried variables, besides for CRUD stuff stored procudures are slower.

    Named SQL place holder are parammeterized SQL.

    Most persistence engines use parameterized SQL because of its speed. So using one of thoses does make use of the technology

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>