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

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:

  • Oracle shows the base table column to “user-facing” cursor column alias mapping. Check the line with “U.NAME (USERNAME)”. The U.NAME is base table column of table alias U (which maps to SYS.USER$) as seen from last line of trace. And the U.NAME column is shown as column USERNAME when we describe the cursor
  • Even though there are more columns in “myview”, Oracle uses only one column for fetching data for the COUNT(*) operation. You see how Oracle queries only the USERNAME column even from the ALL_USERS view nested in the bottom of our query

If you ran a SELECT * instead of SELECT COUNT(*) then you’d see all columns “propagated” all the way do the lowest layers of that query. Oracle is smart being lazy – it only requests for those columns which it needs.

Lets look into the execution plan of this query:

SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3268326079

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |    15 |    12   (9)| 00:00:01 |
|   1 |  SORT AGGREGATE      |       |     1 |    15 |            |          |
|*  2 |   HASH JOIN          |       |    29 |   435 |    12   (9)| 00:00:01 |
|*  3 |    HASH JOIN         |       |    29 |   348 |     8  (13)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TS$   |     8 |    24 |     4   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| USER$ |    29 |   261 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | TS$   |     8 |    24 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("U"."TEMPTS#"="TTS"."TS#")
   3 - access("U"."DATATS#"="DTS"."TS#")
   5 - filter("U"."TYPE#"=1)

20 rows selected.

Interestingly the nested views which we had in our query and QBC trace do not appear in the execution plan. This is because the QBC trace is dumped before any query transformations (such view merging) are applied. And this is exactly what we want – because we are interested in business logic of the query, not Oracle’s optimizations done under the hood.

When we disable simple view merging transformation which happened in our case, then we see execution plan matching the QBC trace:

SQL> alter session set "_simple_view_merging"=false;

Session altered.

SQL> select count(*) from myview;

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

SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 259067589

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |       |    12   (9)| 00:00:01 |
|   1 |  SORT AGGREGATE        |           |     1 |       |            |          |
|   2 |   VIEW                 | MYVIEW    |    29 |       |    12   (9)| 00:00:01 |
|   3 |    VIEW                | ALL_USERS |    29 |       |    12   (9)| 00:00:01 |
|*  4 |     HASH JOIN          |           |    29 |   435 |    12   (9)| 00:00:01 |
|*  5 |      HASH JOIN         |           |    29 |   348 |     8  (13)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| TS$       |     8 |    24 |     4   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL| USER$     |    29 |   261 |     3   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL | TS$       |     8 |    24 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("U"."TEMPTS#"="TTS"."TS#")
   5 - access("U"."DATATS#"="DTS"."TS#")
   7 - filter("U"."TYPE#"=1)

22 rows selected.

So, this _dump_qbc_tree parameter is definitely a handy tool aiding interpretation of complex queries and queries based on multiple layers of views, filling the gap between “raw” SQL statement and its physical execution plan.

Note that as this is an undocumented parameter, use it carefully in non-experimental environments.

This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

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

  1. Paweł Barut says:

    This is really cool tip.

  2. tanelp says:

    Thanks Paweł!

    Note that I have also found your blog useful in past, when I found an explanation to a weird PL/SQL XMLType.extractValue problem from your site :)

  3. coskan says:

    God save you Tanel you are always saving our time :)

  4. tanelp says:

    Thanks Coskan!

    My golden rule of tuning is:

    The less time spent tuning – the more time available for talking about tuning in a local pub!

    I have spent many years getting to this result, however it has paid off – the rule is universal enough to be applicable to any Oracle version regardless which optimizer you use and as a matter of fact, it applies to any database technology.

  5. coskan says:

    Once I joined a class of Julian Dyke
    and he told about Oak Table Members that they meet and drink and talk about ORacle :) and you confirmed his words :)

  6. Mark Bobak says:

    Thanks Tanel! I definitely need to add this to my bag of tricks!

  7. tanelp says:

    Cheers Mark,

    You were talking about addng the golden rule of tuning to your bag of tricks, right? ;-)

  8. Mark Bobak says:

    Heh, absolutely!

    Speaking of…I’m looking forward to UKOUG in December! Should be good!

  9. heliodias says:

    It´s very good post.
    Thanks.

  10. Car says:

    But from trace we cannot get joining predicate, so we still need query plan to completely understand the logic, am I correct ?

  11. tanelp says:

    Yes, the _dump_qbc_tree trick acts as a useful additition to existing methods like reading query plans, etc

  12. Md. Zafar Ahsan says:

    Hi,

    I just recently found yr site and as i have already told from the time i have found it, i feel blessed, and for you i can just say.
    “God bless you with his love that makes everyday a joy to live”.
    Please keep the good work going, really very informative.
    Takecare
    Zafar
    Oracle DBA

Leave a Reply

Your email address will not be published. Required fields are marked *

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