How to resolve SQL object and column names all the way to base tables and columns in Oracle?

September 16, 2007 by 13 Comments

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

So unless you see something really obvious from the execution plan, you need to start mapping the SQL query and view texts back to the physical base tables which Oracle eventually has to access. This can be a tedious and boring (!) process.

The good news is that in Oracle 10.2+ there’s a hidden parameter that can do this mapping task for us.

Update: It looks like 9.2.0.8 patchset also has this parameter (so I guess later patchsets of 10.1.0.x have it as well now)

Let’s see an example:

I create a view on a view to illustrate the point:

SQL> create view myview as select * from all_users;

View created.

Now let’s set that parameter _dump_qbc_tree to 1 and run a query against the view:

SQL> alter session set "_dump_qbc_tree"=1;

Session altered.

SQL> select count(*) from myview;

  COUNT(*)
----------
        31

Now let’s look into the server process tracefile:

*** ACTION NAME:() 2007-09-16 12:19:57.500
*** MODULE NAME:(SQL*Plus) 2007-09-16 12:19:57.500
*** SERVICE NAME:(SYS$USERS) 2007-09-16 12:19:57.500
*** SESSION ID:(146.1984) 2007-09-16 12:19:57.500
QCSDMP: -------------------------------------------------------
QCSDMP:  SELECT: (qbc=2B8D1C28)
QCSDMP:    . (COUNT(*)) (opntyp=2 opndty=0)
QCSDMP:  FROM:
QCSDMP:    .MYVIEW
QCSDMP:      VQB:
QCSDMP:        SELECT: (qbc=2B8D163C)
QCSDMP:          .USERNAME
QCSDMP:        FROM:
QCSDMP:          .ALL_USERS
QCSDMP:            VQB:
QCSDMP:              SELECT: (qbc=2B8CAF78)
QCSDMP:                U.NAME (USERNAME)
QCSDMP:              FROM:
QCSDMP:                SYS.TS$ (TTS)
QCSDMP:                SYS.TS$ (DTS)
QCSDMP:                SYS.USER$ (U)

Here it is, the query text generated directly from parse tree, showing the base tables regardless that they had been hidden behind multiple views.

Also there’s few interesting things to note:

[Read more...]

Filed Under: Uncategorized Tagged With: , ,

My version of SQL string to table tokenizer

June 20, 2007 by 14 Comments

This one’s a short post on a fairly random topic as unfortunately I don’t have time today to come up with anything deeper :)

I needed to come up with a delimited string to table tokenizer for an Oracle development project. There are quite a few examples out there how to do that, including Adrian Billington’s www.oracle-developer.net and the http://technology.amis.nl/blog/?p=1631.

So far the simplest solution I had seen was using a bunch of INSTR’s, SUBSTR’s and DECODE’s in a CONNECT BY loop.
However as this application is using Oracle 10g, I could use regular expressions to make the SQL even shorter:

Tanel@Sol01> define separator=":"
Tanel@Sol01> define mystring="A:BCD::EFG:H:IJKL"
Tanel@Sol01>
Tanel@Sol01> SELECT
  2             LEVEL,
  3             REGEXP_REPLACE(
  4                     REGEXP_SUBSTR( '&mystring'||'&separator', '(.*?)&separator', 1, LEVEL )
  5                     , '&separator$'
  6                     , ''
  7             ) TOKEN
  8  FROM
  9     DUAL
 10  CONNECT BY
 11     REGEXP_INSTR( '&mystring'||'&separator', '(.*?)&separator', 1, LEVEL ) > 0
 12  ORDER BY
 13     LEVEL ASC
 14  /

LEVEL TOKEN
----- ----------
    1 A
    2 BCD
    3
    4 EFG
    5 H
    6 IJKL

6 rows selected.

For anyone not yet familiar with regular expressions, I definitely recommend to get to know at least the basics – they can save you hours (or even days) of work in some cases. And the cool thing is that regular expressions are fairly standard across the development platforms too (e.g. if you know Oracle regexp, then it’s very easy to handle Java, perl or C# regexp too).

And regexp is not only a developer thing, it can be very useful for everyday production DBAs as well. For example, every decent “grep” utility has regexp support built in – this makes search for complex conditions from log or config files easier for you.

Filed Under: Uncategorized Tagged With:
Newer Posts»