Markus Winand's Blog

Archive for the ‘Maintainability’ Category

,

Analytic Top-N Queries

In Maintainability, Performance on 2010-07-30 at 10:55

One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.

The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that efforts? The article concludes with my answer to that question.

Read the rest of this entry »


About Software Quality

In Maintainability, Performance, Reliability, Scalability on 2010-06-12 at 13:00

You might have noticed that this blog never had a kickoff post that explains what this blog is about. Time has come to spend some words on the topic of software quality—as I see it—and how this blog covers some aspects of software quality.

Read the rest of this entry »

,

Oracle Trace File Rotation

In Maintainability on 2010-02-01 at 17:30

Under very rare circumstances, I need Oracle SQL trace files from a long period of time. Because trace files usually grow large—especially over several days—there is the need to rotate the trace file during that time so that they can be compressed and put away. The problem is that there is no “rotate tracefile“ button in Oracle. However, I have found a “undocumented feature“ that does exactly that—without disabling tracing.

My procedure uses the close_trace call of oradebug. This call closes the currently written trace file for a session. Alex Gorbachev has used this to delete big trace files that are still open. My procedure goes a little bit further and exposes one more side effect of oradebug close_trace.

Read the rest of this entry »

, ,

To Trace or Not to Trace

In Maintainability on 2009-11-24 at 17:49

I have always been a fan of the oracle SQL trace facility. It’s a very powerful method to analyze performance problems, especially if you don’t know the application very well. If you don’t know oracle SQL tracing yet, you will find some info at orafaq and some more details at orable-base. Obviously, Oracle has also some documenation about it.

In short, the Oracle SQL tracing facility causes the database server to write all executed SQL statements to a file for later analysis. One of the main issues with SQL tracing is the huge size of the trace files; every statement execution is logged, potentially with additional data about the execution plan, wait events, and bind values.

This article introduces the new possibilities of Oracle 10g to enable tracing on a very fine level. With a properly instrumented application, tracing can be enabled for specific users or program modules only. With such a fine control over the tracing, the generated data volume can be kept small and SQL tracing becomes a much more useful tool.

Read the rest of this entry »