1 2 3 Previous Next 35 Replies Latest reply on Jun 16, 2017 10:02 AM by Jonathan Lewis Go to original post
      • 30. Re: How to take advantage of data already being "grouped" in rows
        Stew Ashton

        mathguy,

         

        There you go again, adding to the discussion while asking that it end. Practice what you preach.

         

        ...

        With all of that said: I did see the Explain Plan, and the output of EXPAND_SQL_TEXT. As you admit yourself, you don't know why they are different (and don't expect ME to know why!) So - why do we choose to believe one and not the other?

        Because this is not an "Explain Plan" (which is a command to parse a query without executing it).

         

        This is a report of how Oracle actually executed the statement.

         

        EXPAND_SQL_TEXT is not meant to report on query execution. DISPLAY_CURSOR is meant to do that.

         

        Neither Solomon nor you should cast doubt on the use of DBMS_XPLAN.DISPLAY_CURSOR (or SQL trace as used above) to show how Oracle is executing a query. This issue is much more important than UNPIVOT or your original question!

         

        Allowing it to be said that EXPAND_SQL_TEXT is a way to see what Oracle is doing "under the covers" is a great disservice to anyone who comes to this forum to learn how to analyze query execution.

         

        I shall keep arguing this point as long as doubts are expressed.

         

        Best regards, Stew

        • 31. Re: How to take advantage of data already being "grouped" in rows

           

          EXPAND_SQL_TEXT is not meant to report on query execution. DISPLAY_CURSOR is meant to do that.

           

          . . .

          Allowing it to be said that EXPAND_SQL_TEXT is a way to see what Oracle is doing "under the covers" is a great disservice to anyone who comes to this forum to learn how to analyze query execution.

           

          Can't speak for anyone else but I don't believe for a minute that the 'EXPAND_SQL_TEXT' Oracle exposes is 1) the SAME functionality as what Oracle itself actually uses, 2) was designed for the purpose being discussed, 3) represents the sql AFTER any final rewrites or reorgs.

           

          1. Oracle likely either uses different functionality altogether or has exposed a watered-down version of the functionality it uses - for example there could be other overloaded versions of the function that take additional parameters.

           

          I also think it unlikely that Oracle would use a function that returns a single, all-inclusive 'final version' sql query. I haven't been involved in writing compilers for 15+ years but parsing is ALL about breaking things down into pieces, analyzing those pieces, making appropriate substitutions and then putting constructing 'SOMETHING' that can be executed.

           

          It is extremely unlikely, IMHO, that the 'something' would be a query represented as a string since that is the STARTING point - not anything that can be executed directly. The whole point of the CBO is to transform a query into code that can be executed. That 'expand' function is designed to do the opposite: transform one query into a different query.

           

          2. The Oracle doc certainly makes NO MENTION of anything related to a 'final, executable' query. The only statement it makes is this:

          DBMS_UTILITY

          Recursively replaces any view references in the input SQL query with the corresponding view subquery

          That suggests to me that the resulting query is nothing more than an EARLY transformation of the original query with view/function/other references replaced by object references from the actual schema/owner/object that will be used.

           

          I would expect it to take into account the normal 'scope' resolution related to the roles (or no roles for named blocks using definer rights) and privileges granted to users and that whole object resolution issue for views-within-views etc.

           

          So at best the 'expand' function might be a 'transform' to generate a query that references the actual objects to be used. Personally I doubt that.

           

          3. Oracle can't directly execute a query - it needs to parse/transform it into a series of steps that can be executed (perhaps in parallel or remotely). So it makes no sense at all that Oracle would parse an original query and then construct another one for it's own internal use. Once it parses the query into components it would just use those components for further processing.

           

          The above leads me to the same conclusion as you and what the doc said: the result of 'expand' is just a representation that shows the objects being used but NOT the exact manner in which the query itself will be executed.

          • 32. Re: How to take advantage of data already being "grouped" in rows
            mathguy

            Whoa!   What have I gotten myself into?

             

            Stew Ashton wrote:

             

             

             

            Allowing it to be said that EXPAND_SQL_TEXT is a way to see what Oracle is doing "under the covers" is a great disservice to anyone who comes to this forum to learn how to analyze query execution.

             

             

            Far from me to intentionally do a great disservice to our community! Sorry if it came across that way.

             

            I think it's fair to say that I mentioned EVERY SINGLE TIME that I am not qualified to discuss this. In particular, no one should take what I say seriously; they should believe the Oracle documentation (first), and the established experts (next) - and sometimes in reverse order, alas. And it should be very clear that I am not an expert; I may be smart and I may be learning quickly, but I am a novice with no formal training and no real-life work experience in any kind of IT. I hope that is clear and understood.

             

            Now to the disservice:  When Solomon first used EXPAND_SQL_TEXT to show how UNPIVOT is re-written, I did some reading on EXPAND_SQL_TEXT on my own, since I wasn't familiar with it. What I found on Tom Kyte's blog seemed to support what Solomon said. How am I, or any other neofite visitor to this Forum, to know that Somomon and Tom Kyte are wrong, and Stew and RP4028 are right?

             

            "Now it is easy to see what query is really being executed at runtime - regardless of how many views of views you might have." 

                   You will find this quote about one-third of the way down in this post: The Tom Kyte Blog: 12c - SQL Text Expansion

             

             

            If this is false (and I have no reason to think otherwise), then Tom is doing a great disservice to anyone who visits his blog - including me! - and I am doing a further great disservice by repeating it here. I am 100% willing to accept that that statement is indeed false, and that I shouldn't repeat what I read from other places without full attribution, as I have done here...

             

            I am not trying to defend any of the statements I made about UNPIVOT and EXPAND_SQL_TEXT - all of them may be wrong, and they probably are. I am just showing you where they come from, and why I don't automatically assume that the last expert who spoke is always right, and disregard whatever other experts had said to the contrary before them.

             

            Best regards,    -    mathguy

            • 33. Re: How to take advantage of data already being "grouped" in rows

              "Now it is easy to see what query is really being executed at runtime - regardless of how many views of views you might have."

              You will find this quote about one-third of the way down in this post: The Tom Kyte Blog: 12c - SQL Text Expansion

              Obviously can' speak for Tom but I read that quote in the context of what he said at the TOP of the article and what the doc itself says

              You are asked to tune what looks like a simple query - maybe a two table join with simple predicates.  But it turns out the two tables are each views of views of views and so on... In other words, you've been asked to 'tune' a 15 page query, not a two liner.

              In other words I read 'what query is really being executed' to mean 'what query is really being SUBMITTED for execution'. That is the original query but with the actual objects being referenced instead of views and things.

               

              Specifically I do NOT read that quote as meaning HOW it is being executed and excluding any additional rewrites, materializations, etc.

               

              Only Oracle (maybe even Tom) knows what goes on under the covers. But Oracle does NOT 'execute' strings - it parses those 'query strings' and executes code based on the pieces. The steps involved are in the docs

              https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL175

              About SQL Processing

              SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit some of these stages.

              Figure 3-1 Stages of SQL Processing

              Description of Figure 3-1 follows

              I've already stated my opinion that the 'expand' occurs BEFORE the 'Optimization' phase and represents the actual objects involved as discovered during the syntax and semantic checks.

               

              I don't know if the shared pool check has even been done to decide between a soft and hard parse.

               

              But see in the above diagram next to 'Optimization' where it says 'generation of multiple execution plans'? A query doesn't 'per se' correspond to a particular execution plan.

              • 34. Re: How to take advantage of data already being "grouped" in rows
                Stew Ashton

                In order to see whether the optimizer was rewriting the UNPIVOT query, I used the 10053 trace event. This dumps a trace of what the optimizer is actually doing as it parses a query. There is a new fancy way to do this trace, but the "old" way is:

                 

                alter session set events '10053 trace name context forever, level 1';
                
                select name
                from employees
                unpivot ( name for descr in (first_name, last_name) );
                
                alter session set events '10053 trace name context off';
                
                -- Then use this query to get the trace file name 
                select value from v$diag_info where name = 'Default Trace File';
                

                 

                To my surprise, the query was rewritten to use a UNION ALL, just as EXPAND_SQL_TEXT did!

                 

                However, as I showed above, the execution plan actually used, as provided by DBMS_XPLAN.DISPLAY_CURSOR, is different for the original query and for the "rewritten" query if submitted separately!

                 

                The optimizer is doing something fancy when it sees the UNPIVOT, not simply rewriting the query.

                 

                I thank Jonathan Lewis for shedding light on what may be happening here.

                 

                Best regards, Stew Ashton

                • 35. Re: How to take advantage of data already being "grouped" in rows
                  Jonathan Lewis

                  Stew,

                   

                  I posted a quick blog note about this yesterday - adding a few bits to the comments I made above.

                   

                  [UPDATE:] It might have been a good to mention the URL: https://jonathanlewis.wordpress.com/2017/06/14/unpivot/

                   

                  Regards

                  Jonathan Lewis

                  1 2 3 Previous Next