Oracle Scratchpad

July 10, 2012

Expanding SQL

Filed under: Oracle — Jonathan Lewis @ 6:00 pm GMT 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 dbms_sql2.expand_sql_text and 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. Rather than say any more, here’s an example of usage:

set linesize 70
set pagesize 0
set feedback off

	m_sql_in	clob :='select * from dba_synonyms where owner = ''TEST_USER''';
	m_sql_out	clob := empty_clob();




Note, by the way, how I’ve doubled up all the single quotes.

Here’s the output – 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 the transformed text that is ultimately the “unparsed” version of your statement.


  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 GMT Jul 10,2012 | Reply

  2. Really helpful … Thanks

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

  3. Cool stuff.

    Thanks !

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

  4. Very resourceful. “Thanks”.

    Comment by Mojeed Adetokunboh — July 11, 2012 @ 11:50 am GMT 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 GMT 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 GMT 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 GMT 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 GMT Jul 13,2012

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at


Get every new post delivered to your Inbox.

Join 4,298 other followers