Oracle Scratchpad

June 25, 2009

Explain VIEW

Filed under: Execution plans,Tuning — Jonathan Lewis @ 7:32 pm BST Jun 25,2009

A brief note on reading execution plans.


------------------------------------------------------------
| Id  | Operation                     | Name    | Time     |
------------------------------------------------------------
|   0 | SELECT STATEMENT              |         | 00:00:17 |
|*  1 |  HASH JOIN                    |         | 00:00:17 |
|   2 |   VIEW                        | VW_SQ_1 | 00:00:01 |
|   3 |    HASH GROUP BY              |         | 00:00:01 |
|   4 |     TABLE ACCESS FULL         | EMP     | 00:00:01 |
|   5 |   VIEW                        |         | 00:00:17 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP     | 00:00:17 |
|   7 |     INDEX FULL SCAN           | E_D     | 00:00:01 |
------------------------------------------------------------

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

   1 - access("DEPT_NO"="OUTER"."DEPT_NO")
       filter("OUTER"."SAL">"VW_COL_1")

Where you see the VIEW operator this is Oracle telling you that it has had to “suspend” operations and produce an intermediate result set by running the “sub-plan” under the VIEW.

You’re likely to see this operator if your query includes a non-mergeable view (perhaps because of a no_merge hint), or if Oracle has transformed your query in some way that has produced a non-mergeable view.

In the example above, one of the view operators (the one at line 2) refers to an object named “VW_SQ_1″. This is an internal view name that gives me a clue that Oracle has unnested an “existence” subquery. Its a convenient fact that many of the names generated by Oracle start with “VW_”, e.g. vw_nso_1, vw_nsq_1, vw_wif_1, vw_gbc_5, though there are some, inevitably, that don’t follow this convention, e.g. index$_join$_001

The other view operator (line 5) has appeared because I had an inline view with a no_merge hint in the main query.

Although the view operator requires an intermediate result set to be built, it’s important to remember that it need not create the entire result set before passing rows up to its parent (which is why I put quote marks around the word “suspend” earlier on in the article – the presence of the view operator  may have no visible blocking effect on the execution).

Looking at the example – the hash group by at line 3 has to complete before any rows can be passed up to the view operator and on to build the hash table in line 1, so there is a blocking effect there. By comparison, in line 5 the view operator can retrieve rows from the emp table in line 6 one at a time by virtue of the index full scan in line 7 and pass them on up to line 1 to probe the hash table – there is no blocking effect associated with the view.

In fact, when you look closely at what’s going on, you can see that the while the view represents the need to recognise an intermediate result set, the need for a complete data set isn’t dictated by the view operator, it is a function of the first child of the view.

12 Comments »

  1. This particular operator (VIEW) is what I don’t quite understand. I do knew what it says (some kinda buffering – but didn’t know that it’s “partial”, I thought it is a full execution of underlying steps without passing control until the end) in a plan, but I still can’t get why it is needed. Could you please elaborate a little bit further as to why Oracle needs VIEW? It can help to better understanding.
    Thanks.

    Comment by Timur Akhmadeev — June 25, 2009 @ 8:45 pm BST Jun 25,2009 | Reply

    • Timur,

      Another way to think about it is to go back to the concepts of the “left deep” and “bushy tree” optimisation plans.

      The “unit of optimisation” – the thing where Oracle examines a set of possible join orders – is based on the “left deep” tree, and is the unit that Oracle calls the “query block”.

      If Oracle cannot transform your query into a single query block, it has to optimise each query block separately. In effect, each query block is a non-mergeable view – and in some cases Oracle will indicate this through the use of the VIEW operator.

      Unfortunately, history and development cycles being what they are (a) the appearance of the VIEW operator is not totally consistent, and (b) as time passes the optimizes is enhanced to improve it’s ability to transform more complicated queries into single query blocks.

      Finally – compare a query block with a single table: there are execution plans where Oracle has to process the whole of table 1 before touching table 2; (e.g. hash join) there are execution plans where Oracle processes a bit of table 1 then a bit of table 2, and alternates between them (e.g. nested loop join) – the same type of thinking applies to query blocks, some have to be evaluated completely before they can be used, some can be evaluated piecewise.

      I’ll try to remember to produce a different example some time over the next few weeks.

      Comment by Jonathan Lewis — June 26, 2009 @ 7:06 pm BST Jun 26,2009 | Reply

  2. At a guess the underlying sql is doing something like “Select employees who’s salary is greater than the average salary for their department”. Oracle has to derive the average salary by department (causing a Hash group by). Until a department average is obtained, no rows can be returned for that department (since we do not know whether the salary is > the average).

    The optimiser is mimicing the existance of a view that contains the columns DEPT_NO and AVG_SAL. Given that to gather the average it already has to load all the employees in that department, it would be better to process this on a dept by dept basis, since you are increasing the chances of having the data you want in the buffer. Hence the plan to not “suspend” processing for the completion of the entire statement

    Comment by Yorick Iddon — June 26, 2009 @ 7:05 am BST Jun 26,2009 | Reply

  3. Jonathan,

    Thanks. It is nice to read about EXPLAIN PLAN output in between.
    Now, I had this doubt since I read one of Tom’s responses recently
    (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2853107469873#1772855900346026443)
    What makes CBO decide NOT to merge inline views/subqueries? I had thought use of ROWNUM (and GROUP BY, DISTINCT etc.) would FORCE the optimizer to materialize the subquery/inline view. Apparantly, Tom suggests that is not the case.
    I am especially puzzled about ROWNUM not forcing the subquery to materialize.
    Any chance you can throw some light?

    Comment by Narendra — June 26, 2009 @ 8:08 am BST Jun 26,2009 | Reply

    • What version of Oracle are you using?

      This may be related:

      As of 10.2.x.x and up there is a parameter called OPTIMIZER_SECURE_VIEW_MERGING (http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams146.htm) that can prevent merging from happening and cause a view to materialize. Christian Antognini has an excellent explanation in his book Troubleshooting Oracle Performance.

      Comment by Corey — June 26, 2009 @ 11:54 am BST Jun 26,2009 | Reply

      • Corey,

        Thanks for your reply. I have not managed to test the scenario but what has surprised me is that Tom has suggested that “it never had to” materialize as a result of using ROWNUM. I believe that means it was the case in versions prior to 10g, as well.

        Comment by Narendra — June 26, 2009 @ 12:15 pm BST Jun 26,2009 | Reply

    • Narenda,

      Addressing the rownum issue specifically – I think that Tom has pointed out in the past that if you “select rownum” it’s a very good way of forcing Oracle to materialize the result set. The example you’ve referenced is using a “rownum <= N" clause which limits the results returned from select. This has a very different effect, and can be optimised in various ways in different versions of Oracle.

      Even in the case of the "select rownum" though, I've never been certain that the full result set has to be materilized before any of it can be used – it probably depends on the exact nature of the surrounding query.

      Comment by Jonathan Lewis — June 26, 2009 @ 6:54 pm BST Jun 26,2009 | Reply

  4. [...] Jonathan Lewis – Explain View [...]

    Pingback by Blogroll Report 19/06/2009 – 26/06/2006 « Coskan’s Approach to Oracle — June 26, 2009 @ 8:09 pm BST Jun 26,2009 | Reply

  5. Yep, selecting rownum in a query block prevents optimizer from merging that query block with parent but rows are still returned there in incremental, cascading fashion – so Oracle does not have to fully materialize that query block (otherwise it would need some sort of workarea memory for storing intermediate results).

    Comment by Tanel Poder — June 27, 2009 @ 7:00 am BST Jun 27,2009 | Reply

    • I think just selecting the rownum without doing anything with it could be dangerous. At some point in the future the Optimizer could start recognizing the trick. It could find out that rownum doesn’t affect the result at all – and abandon it – and the view suddenly would get merged.

      Comment by Todor Botev — July 3, 2009 @ 2:16 pm BST Jul 3,2009 | Reply

      • Todor,

        I think that’s an important point to consider. I always prefer to make “dirty tricks” explicit if possible, and certainly document why they’re there.

        That’s partly why I prefer the no_merge hint to adding a rownum – but even there I’ve been caught out by an optimisation in 10gR2 which took out an “order by” clause on an inline view with a no_merge because (as you point out) it wasn’t goint to make any difference to the final result set.

        Comment by Jonathan Lewis — July 3, 2009 @ 3:53 pm BST Jul 3,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers