Oracle Scratchpad

July 10, 2012

Expanding SQL

Filed under: Oracle — Jonathan Lewis @ 6:00 pm BST Jul 10,2012

Here’s a little thing that Dan Morgan mentioned to me some time ago. It’s a little routine from a package (owned by sys) that appeared in that gives you some idea of the mess hidden behind a query that uses views. The procedure is called dbms_sql2.expand_sql_text and it takes two (CLOB) parameters: an IN parameter that is the text you want expanded and an OUT parameter that is likely to be a long and messy piece of text.

[Update July 2013]: The procedure is officially available in 12c in the dbms_utility package – see comment 6 below – so I’ve updated the published script to show both packages, using the “conditional compilation” features of PL/SQL to ensure that the correct package is called depending on version – and I’ve also  (redundantly) used the same mechanism to demonstrate “quote escaping”.

Rather than say any more, here’s an example of usage – you will probably have to connect as a user with dba privileges for it to work:

rem     Script:         expand_sql.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2012

set linesize 70
set pagesize 0
set feedback off


        $if dbms_db_version.ver_le_11_2 $then
                m_sql_in    clob :='select * from dba_synonyms where owner = ''TEST_USER''';
                m_sql_in    clob := q'{select * from dba_synonyms where owner = 'TEST_USER'}';

        m_sql_out   clob := empty_clob();
        $if dbms_db_version.ver_le_11_2 $then

                dbms_output.put_line('Expanded with dbms_sql2');


                dbms_output.put_line('Expanded with dbms_utility');




Note, by the way, how I’ve had to double up all the single quotes in the 11g variant, which is what makes the 12c  quote escaping so convenient.

Here’s the output from – you might want to try this only after setting linesize to 32767, and then you can paste the result into an SQL formatter:

_LINK" FROM "SYS"."USER$" "A4","SYS"."SYN$" "A3", (SELECT "A6"."OBJ#"
A6"."TYPE#" "TYPE#","A6"."CTIME" "CTIME","A6"."MTIME" "MTIME","A6"."ST
."SPARE1" "SPARE1","A6"."SPARE2" "SPARE2","A6"."SPARE3" "SPARE3","A6".
"SPARE4" "SPARE4","A6"."SPARE5" "SPARE5","A6"."SPARE6" "SPARE6","A6"."
SPARE3" "OWNER#",CASE  WHEN (("A6"."TYPE#"<>4 AND "A6"."TYPE#"<>5 AND
"A6"."TYPE#"<>7 AND "A6"."TYPE#"<>8 AND "A6"."TYPE#"<>9 AND "A6"."TYPE
#"<>10 AND "A6"."TYPE#"<>11 AND "A6"."TYPE#"<>12 AND "A6"."TYPE#"<>13
AND "A6"."TYPE#"<>14 AND "A6"."TYPE#"<>22 AND "A6"."TYPE#"<>87) OR BIT
A9"."NAME" "NAME" FROM SYS."OBJ$" "A9" WHERE "A9"."OBJ#"="A5"."SPARE2"
ER$" "A5" WHERE "A6"."OWNER#"="A5"."USER#" AND ("A6"."TYPE#"<>4 AND "A
6"."TYPE#"<>5 AND "A6"."TYPE#"<>7 AND "A6"."TYPE#"<>8 AND "A6"."TYPE#"
<>9 AND "A6"."TYPE#"<>10 AND "A6"."TYPE#"<>11 AND "A6"."TYPE#"<>12 AND
 "A6"."TYPE#"<>13 AND "A6"."TYPE#"<>14 AND "A6"."TYPE#"<>22 AND "A6"."
TYPE#"<>87 AND "A6"."TYPE#"<>88 OR BITAND("A5"."SPARE1",16)=0 OR ("A6"
."TYPE#"=4 OR "A6"."TYPE#"=5 OR "A6"."TYPE#"=7 OR "A6"."TYPE#"=8 OR "A
6"."TYPE#"=9 OR "A6"."TYPE#"=10 OR "A6"."TYPE#"=11 OR "A6"."TYPE#"=12
OR "A6"."TYPE#"=13 OR "A6"."TYPE#"=14 OR "A6"."TYPE#"=22 OR "A6"."TYPE
#"=87) AND ("A5"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition
_name')='ORA$BASE' OR "A5"."TYPE#"=2 AND "A5"."SPARE2"=SYS_CONTEXT('us
erenv','current_edition_id') OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "A8"
,SYS."USER$" "A7" WHERE "A8"."TYPE#"=88 AND "A8"."DATAOBJ#"="A6"."OBJ#
" AND "A8"."OWNER#"="A7"."USER#" AND "A7"."TYPE#"=2 AND "A7"."SPARE2"=
SYS_CONTEXT('userenv','current_edition_id'))))) "A2" WHERE "A2"."OBJ#"
="A3"."OBJ#" AND "A2"."TYPE#"=5 AND "A2"."OWNER#"="A4"."USER#") "A1" W

This expansion is probably the first step the optimizer takes in handling your code – but it’s still not necessarily the transformed text that is ultimately the “unparsed” version of your statement that you may find in the 10053 (optimizer) trace file.


The procedure will also handle “with” subqueries – at least as far as subqueries that are in-lined is concerned. If you have “with” subqueries that materialize they will still be presented as inline views by this package but with the difference that the alias for the inline view will be the name you gave the subquery rather than a generated alias like “A1″.

The procedure is also convenient for showing you the transformation that Oracle does to convert “ANSI” style SQL to Oracle’s traditional format before optimising it.



  1. Excellent! This is exactly what I’ve always wanted. Previously used for this tracing 10053, for example, recently to optimize the “gv$lock”: solved problem with a large number of latch free: dml allocation latch with queries from vlock. The key to solving the problem is found in your “Oracle core” – x$ktadm. Just created a view where you could specify a block of “union all” with needed type of lock of the original gv$lock: or full post in russian:

    Comment by Sayan Malakshinov — July 10, 2012 @ 6:24 pm BST Jul 10,2012 | Reply

  2. Really helpful … Thanks

    Comment by deepaklipu — July 11, 2012 @ 3:28 am BST Jul 11,2012 | Reply

  3. Cool stuff.

    Thanks !

    Comment by Amardeep Sidhu — July 11, 2012 @ 7:29 am BST Jul 11,2012 | Reply

  4. Very resourceful. “Thanks”.

    Comment by Mojeed Adetokunboh — July 11, 2012 @ 11:50 am BST Jul 11,2012 | Reply

  5. Thanks Jonathan.

    BTW what is the significance of “unparsed query text”? Once I was tracking down a problem due to an ANSI outer join, and took this “unparsed query text” and ran an explain plan on it — it gave me a different plan from what the optimizer eventually arrived in the 10053 trace file. There weren’t any binds in the query, so apparently after it gets to this “unparsed query text”, the optimizer does more things to it, which we don’t know (and can never find out about). Is this correct or am I missing something?… And if this is correct, then is there any use in “unparsed query text” at all?

    Best regards,

    Comment by savvinov — July 12, 2012 @ 8:16 am BST Jul 12,2012 | Reply

    • Nikolay,

      I don’t have a definitive answer on the unparsed query. I know that it is not (always) the whole truth because, for example, you can have an unparsed query that shows something that appears to be a join when the actual plan will have to use a semi-join. I just take it a possible clue about how the optimizer is working and where it’s heading, but I won’t assume that it’s an infallible guide line (at least for us outsiders) to the optimizer’s final destination.

      Comment by Jonathan Lewis — July 12, 2012 @ 7:03 pm BST Jul 12,2012 | Reply

      • Thanks for your reply.

        The problem I was referring to was with an ANSI-style outer join — there was a thread about it with a reproducible test case

        It was giving a bad plan, and rewriting it in the traditional style solved that problem. However, I was surprised to see in 10053 trace file for the ANSI-style query that the “unparsed query” was nearly exactly the same as the rewritten one. So at least in that particular case “unparsed query” was very misleading.

        Comment by savvinov — July 13, 2012 @ 6:11 am BST Jul 13,2012 | Reply

        • Nikolay,

          Thanks for linking to the OTN post – that was an excellent example of how to present a problem.

          I’ve had a look at the problem, and written a couple of notes – I may write a little more on my blog over the weekend. I think an interesting point here is that the “unparsed” SQL can show the join order and join conditions, but (in line with my prevous comment about semi-joins) can’t indicate all the limitations that apply as you go from table to table. In this case I think there was a hidden side-effect of the order of the FROM clause that made the default rewrite mechanism block (or introduce an error for) a particular index.

          Comment by Jonathan Lewis — July 13, 2012 @ 10:32 am BST Jul 13,2012

  6. […] This seems to have worked in earlier versions too, one example is on Jonathan Lewis’ blog. […]

    Pingback by How to resolve the text behind v$views? « Martins Blog — January 21, 2015 @ 2:24 pm GMT Jan 21,2015 | Reply

  7. […] there’s the alternative (though not necessarily correct) way to find an “unparsed” version of the query – dbms_sql2.expand_sql_text() in […]

    Pingback by Unpivot | Oracle Scratchpad — June 14, 2017 @ 6:51 pm BST Jun 14,2017 | Reply

  8. […] The dbms_utility.expand_sql_text() function is new to 12c, and you’ll need the execute privilege on the dbms_utility package to use it; but if you want to take advantage of it in 11g you can also find it (undocumented) in a package called dbms_sql2. […]

    Pingback by ANSI expansion | Oracle Scratchpad — August 30, 2018 @ 12:02 pm BST Aug 30,2018 | Reply

  9. […] you’re curious, here (courtesy of dbms_utility.expand_sql_text() but cosmetically enhanced) is the transformed SQL that was actually optimised and […]

    Pingback by Index Engineering | Oracle Scratchpad — January 20, 2020 @ 4:53 pm GMT Jan 20,2020 | Reply

  10. […] gave us a reason for the slowness of the lag() function by running a sample query through dbms_utility.expand_sql(). Oracle rewrites the query to use variants of the nth_value() function when you use “ignore […]

    Pingback by Lag/Lead slow | Oracle Scratchpad — May 5, 2022 @ 10:05 am BST May 5,2022 | Reply

  11. […] I took a closer look at what Oracle was doing to the ANSI code. I passed both statements to the dbms_utility.expand_sql_text() procedure – and they both produced the same expand “traditional Oracle” SQL […]

    Pingback by ANSI bug | Oracle Scratchpad — December 13, 2022 @ 7:23 pm GMT Dec 13,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: