Clarity, Clarity, Clarity

This piece of SQL was recently quoted on the newsgroup comp.databases.oracle.server with the explanation that it had been downloaded from Metalink some time ago. The purpose of the SQL is to give some indication of how much current undo each session is holding in which undo segment and which objects the session is changing. But how long does it take you to read and understand (a) what it does and (b) whether or not it’s right ?

select
        substr(a.os_user_name,1,8)      "OS User",
        substr(a.oracle_username,1,8)   "DB User",
        substr(b.owner,1,8)             "Schema",
        substr(b.object_name,1,20)      "Object Name",
        substr(b.object_type,1,10)      "Type",
        substr(c.segment_name,1,5)      "RBS",
        substr(d.used_urec,1,12)        "# of Records"
from
        v$locked_object      a,
        dba_objects          b,
        dba_rollback_segs    c,
        v$transaction        d,
        v$session            e
where
        a.object_id = b.object_id
and     a.xidusn    = c.segment_id
and     a.xidusn    = d.xidusn
and     a.xidslot   = d.xidslot
and     d.addr      = e.taddr
;

The problem I have with this is that I don’t see any clue about the author’s intentions; and the aliasing in the from clause, combined with the predicate arrangement doesn’t make it easy to work out what the joins are doing. In part, of course, this is because the author is probably following a convention that I didn’t invent. But there are two details which I think stand out unarguably as ‘poor taste’.

First – aliases like ‘a’, ‘b’, … do not help.  I always advise a documented aliasing strategy of three-letter aliases (or four letters for systems with lots of tables) that give some sort of clue about the underlying table name. When a table is used multiple times in a single query, then its alias should have a number appended to it to make each occurrence unique across the query.

Second – when I write predicates like “where colX = 99” the search is against colX, the value 99 is a known input. The same logic should apply to “abc.colX = pqr.colY”. To me this  predicate suggests that the query has already reached table (alias) pqr at this point so that colY is a known value, and abc.colX is the thing I am searching.

Third – (and I believe the original author has followed this convention, which is why I cite only two details as poor taste above) I list the table names in the from clause in the order that I think the (cost-based) optimizer ought to visit them. The same ordering pattern appears in the where clause – specifically on the left-hand side of the list of predicates.

So, applying the first two rules to the original query, you get:

select
        substr(lkd.os_user_name,1,8)     "OS User",
        substr(lkd.oracle_username,1,8)  "DB User",
        substr(obj.owner,1,8)            "Schema",
        substr(obj.object_name,1,20      "Object Name",
        substr(obj.object_type,1,10)     "Type",
        substr(rbs.segment_name,1,5)     "RBS",
        substr(trn.used_urec,1,12)       "# of Records"
from
        v$locked_object       lkd,
        dba_objects           obj,
        dba_rollback_segs     rbs,
        v$transaction         trn,
        v$session             ses
where
        obj.object_id  = lkd.object_id
and     rbs.segment_id = lkd.xidusn
and     trn.xidusn     = lkd.xidusn
and     trn.xidslot    = lkd.xidslot
and     trn.xidsqn     = lkd.xidsqn      -- added for completeness
and     ses.taddr      = trn.addr
;

Of course it’s a matter of taste whether or not you feel this makes it a little easier to understand the query. I found that it made a difference the moment I had done a few global find and replaces, and that the predicate swap was just a little extra icing on the cake.

In passing, I make no claims for the efficiency (or correctness, or good sense) of this particular piece of SQL, that wasn’t the point of this posting but, if I thought the query was going to be useful on a regular basis, I would consider adjusting the order of execution (hence the ordering of the from and where clauses) and possibly introduce an in-line view to isolate the processing of the three dynamic performance views before doing the join to real tables.

I might also point out that v$locked_object is a simple join of x$ktadm, x$ksuse, and x$ktcxb, and the last two are just v$session and v$transaction anyway so I’d use the x$ objects to make the two v$ objects redundant; and then I might use undo$ instead of dba_rollback_segs, and I might think about obj$/user$ instead of dba_objects.