Inline Views – What is Wrong with this Quote?

18092011

September 18, 2011

I do not recall putting together any articles about inline views, so let’s start out with a couple of examples before taking a look at a quote. We need to create a table for this example, so I will reuse a slightly modified table creation script from another article, and also collect statistics with 100% sampling.

Hints may be used to change the optimization behavior of the query optimizer. For instance, if we specify the NO_QUERY_TRANSFORMATION hint in the last of the above queries that used inline views, the autotrace generated execution plan appears as follows:

For fun, on the Enterprise Edition of Oracle Database (if I remember correctly, the Standard Edition will behave differently – Edit Sept. 18, 2011:just tested 11.2.0.2 Standard Edition and 10.2.0.5 Standard Edition – both versions generated execution plans similar to what is displayed below, so the MATERIALIZE hint apparently does not behave differently as originally described) we can also use the MATERIALIZE hint using the subquery factored (WITH block) version of the query:

Now that hopefully everyone is on the same page about what an inline view is and how to use one in a query, let’s take a look at two quotes about inline views from the book Oracle 11g Performance Tuning Recipes:

“While extremely useful, if misused or overused, inline views can cause database performance issues, especially in terms of the use of the temporary tablespace. Since inline views are created and used only for the duration of a query, their results are held in the program global memory area, and if too large, the temporary tablespace.”

Page 292 (note that this quote is quite a bit longer than I would prefer, but it is as short as possible without losing the context of the quote):

“In this query, there are three inline views created: the SERVICE_INFO view, the PRODUCT_INFO view, and the BILLING_INFO view. Each of these queries will be processed and the results stored in the program global area or the temporary tablespace before finally processing the true end-user query, which starts with the final SELECT statement shown in the query. In examples such as these, it is generally more efficient at the database level to create tables that hold the data defined by the inline views—in this case, three separate tables. Then, the final query can be extracted from joining the three permanent tables to generate the results.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Actions

Information

7 responses

Simply put, the optimizer can do any sort of ‘magic’ with an inline view that it could with a conventional view. It may merge the view and never ‘crystalize’ the data in it. While an inline view misses the reuse benefit of a conventional view, it also make the complexity of a query apparent in a way that may be hidden with a conventional view.

Personally I like inline views as they make the logic more obvious, for example keeping filter conditions close to the table being filtered.

It’s a bit worrying that the author/s seem to believe that inline views are “created”, even if we presume they mean materialised – and I’m not sure that this is what they mean.

Have they never heard for example of the whole query transformation process and view merging?

Regarding the second quote, a lot of effort is spent on the various forums fighting developer intentions to create working/temporary tables with interim data sets. This is a practice normally used by developers who do not have a strong Oracle background and more common with Sql Server and Sybase. In complete contrast to this second quote, the most efficient practice in Oracle is usually to write a single sql statement, inline views or not.

Furthermore, the quote at the bottom of page 292 that “large inline views can easily consume a large amount of temporary tablespace” is misleading. You could read it as meaning the length of the inline view query text. However it is consuming large amounts of data notably with insufficiently sized work areas which is a primary cause of queries having “problem” temporary tablespace usage and I would suggest that that is as likely with any sql statement.

On the plus side, it is true that materialising subqueries or views should be done with care and it’s wise to point out that there can be issues with views – notably with predicate pushing or lack of and not merging etc – and particularly because there are certain optimisations that don’t seem to happen with the WITH clause, etc. E.g. http://jonathanlewis.wordpress.com/category/oracle/tuning/subquery-factoring/

I will try to keep my comments in this blog article to a minimum for the next 24 hours, at which point I plan to post my review comments for recipe 8-15 “Creating Efficient Temporary Views”. Needless to say, I was very surprised when I read this recipe’s contents in a performance tuning book. The general tone of my review comments do not disagree with the two comments currently attached to this blog article.

I think that more could still be mentioned about what is wrong; how the material can be restated to help someone trying to learn from this book; when views should and should not be used (statically defined and otherwise); the difference between inline views, subquery factoring, statically defined views. and materialized views; and etc.

(Side note: checking the Google Books link as Dom Brooks apparently did was a good idea.)

Gary’s response in this blog article is very good and manages to drive directly to the point – and is probably a better answer using 100 words or less than what I could have put together using 100 words or less.

Dom Brooks’ answer not only examines the quoted section, but also the book page contents as a whole. He raised a valid point that developers posting to forums have historically had difficulty with either thinking using set based logic (wanting to do things slow-by-slow) or not attempting silly things on an Oracle platform such as creating intermediate result tables (either temporary or permanent) on the fly – as is likely typical in a SQL Server environment. It hurts the cause of educating developers when an Oracle performance book essentially tells not only the developers but also the DBAs to go ahead and create those tables because doing so will help improve performance.

As promised, below is the portion of my book review for this particular recipe (I may slightly adjust the wording before finalizing the second half of the book review – the review of this recipe was written before I posted this blog article). I missed some of the finer points mentioned by Gary and Dom Brooks, but also included a couple of points that were not covered in their comments. The book review is currently just over 29 type-written pages long for the first 10 chapters.

Recipe 8-15 “Creating Efficient Temporary Views” shows how to create inline views, and provides performance warnings about their use. The authors of the book make several critical mistakes in this recipe, giving the readers the impression that the rows requested by inline views are always materialized into tables, and as such should be avoided whenever possible. The SQL statement found in the How it Works section uses subquery factoring (WITH blocks) rather than inline views. Page 292 states about the query found in the How it Works section, “In examples such as these, it is generally more efficient at the database level to create tables that hold the data defined by the inline views—in this case, three separate tables. Then, the final query can be extracted from joining the three permanent tables to generate the results.” The recipe also contains the following cautionary note, “Large inline views can easily consume a large amount of temporary tablespace.” There is more wrong than right in this recipe. Oracle Database does not materialized every inline view, creating a temp table such as SYS_TEMP_0FD9D6628_D08144, as it processes the SQL statement. The optimizer is free to materialize an inline view into a temp table, but it is also free to push predicates into the inline view, and completely rewrite the original SQL statement so that it no longer contains an inline view. The book’s suggested alternative of storing the intermediate results of inline views in permanent tables, unnecessarily generating redo and undo in the process, is very strange (if we must head in this direction, why not suggest using materialized views). The original SQL statement with the inline views (the example SQL statement in the recipe shows factored subqueries – WITH blocks) is able to take advantage of not only pushed predicates from the parent query (and other inline views) to further restrict the rows requested by the inline view, but also to take advantage of any indexes and partitioning that may be present in the parent tables. Additionally, when troubleshooting the performance or accuracy of a SQL statement it is helpful to have the full SQL text that generates the resultset in a single SQL statement (this is an advantage of inline views over both statistically defined views and the approach suggested by the book). (pages 290-292)

The results of the queries – 3.3 million rows in the join of the two tables.

The calls to bcf_snapsess record v$sesstat and v$session_event and a few other things for the given session. Comparing that information from the two test sessions/queries, I found that in this case the session that used the inline view had “session pga%” statistics about 10% greater than without the inline view, not a large increase, but it is an increase. I had hoped to be able to compare the direct path write temp usage however neither session had to wait on that event. I’m tempted to run some tests against larger data sets, or tweak this one to fetch more than count(*)..

Sorry about the delay in responding. I am curious to know if you are reading the book (or have already read it) – the book seems to have an overall high rating on Amazon.com, with only one review (mine) mentioning that there are errors in the book.

What partially complicates matters with your test case is the use of the DBA_HIST_SQL_PLAN and the DBA_HIST_SQLSTAT views… which of course are views on tables that probably do not have up to date statistics. The NO_MERGE hint could be sufficient to change the execution plan from one that used a nested loops join, to one that uses a hash join – I suspect that change could explain the increase in the PGA usage when the NO_MERGE hint was used.

Here is a small modification that I made to your test case. I first specified the query with the inline view with the NO_MERGE hint, then removed the NO_MERGE hint, then executed your SQL statement without the inline view. If the two quotes from the book are correct, the execution plans for the first and second queries should have similar execution plans, and the last query should have a different execution plan:

Here is the execution plan for the third query – notice that the plan hash value is 2653281973, the same as the second query, and that the word “VIEW” does not appear anywhere in the execution plan even though the query is selecting from two views:

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: