4 Replies Latest reply: Feb 19, 2013 1:55 PM by P.Forstmann RSS

    inline views and parallelism

    977635
      On Oracle 11.2, our application uses a "lot" of inline views.

      Not knowing much about the benefits or advantages to using inline views, I wondered a few things about the use of them.

      First, When I look in OEM, SQL Monitoring, I noticed that queries on inline views that have tables with parallelism set, the queries on inline views do not appear to be using parallelism on the inner query. Or, perhaps they are, but I'm not able to see that they are. How can I tell if they are?
      If they are not, would it be necessary to use a hint to use parallelism?

      So what are the advantages of using inline views?
      I would assume they would use more PGA and/or temp tablespace, but that doesn't seem to be the case in our database (or at least it is not causing any impact that I can tell).
        • 1. Re: inline views and parallelism
          JohnWatson
          974632 wrote:
          So what are the advantages of using inline views?
          I would assume they would use more PGA and/or temp tablespace, but that doesn't seem to be the case in our database (or at least it is not causing any impact that I can tell).
          A critical point is whether the inline view can be merged into the calling query. As a general rule, if the view can be merged then the query will be more efficient than if it cannot be. A non-mergeable view has to be instantiated as a memory structure before the calling query can run against it. consider these examples:
          orcl>
          orcl> set autot on exp
          orcl> select count(ename) from (select ename from emp);
          
          COUNT(ENAME)
          ------------
                    14
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2083865914
          
          -----------------------------------------------------------------------------------
          | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          -----------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT           |      |     1 |     6 |     4   (0)| 00:00:01 |
          |   1 |  SORT AGGREGATE            |      |     1 |     6 |            |          |
          |   2 |   TABLE ACCESS STORAGE FULL| EMP  |    14 |    84 |     4   (0)| 00:00:01 |
          -----------------------------------------------------------------------------------
          
          orcl> select count(ename) from (select distinct ename from emp);
          
          COUNT(ENAME)
          ------------
                    14
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1851925981
          
          -------------------------------------------------------------------------------------
          | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT             |      |     1 |     7 |     5  (20)| 00:00:01 |
          |   1 |  SORT AGGREGATE              |      |     1 |     7 |            |          |
          |   2 |   VIEW                       |      |    14 |    98 |     5  (20)| 00:00:01 |
          |   3 |    HASH UNIQUE               |      |    14 |    84 |     5  (20)| 00:00:01 |
          |   4 |     TABLE ACCESS STORAGE FULL| EMP  |    14 |    84 |     4   (0)| 00:00:01 |
          -------------------------------------------------------------------------------------
          
          orcl>
          The first query was merged, the second was not: the DISTINCT makes merging impossible, because Oracle cannot be certain that there are no duplicate names. But if I add a unique and not null constraints:
          orcl> alter table emp add constraint ename_uk unique(ename);
          
          Table altered. 
          
          orcl> alter table emp modify ename not null;
          
          Table altered.
          
          orcl>  select count(ename) from (select distinct ename from emp);
          
          COUNT(ENAME)
          ------------
                    14
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1457629042
          
          ---------------------------------------------------------------------
          | Id  | Operation        | Name     | Rows  | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------
          |   0 | SELECT STATEMENT |          |     1 |     1   (0)| 00:00:01 |
          |   1 |  SORT AGGREGATE  |          |     1 |            |          |
          |   2 |   INDEX FULL SCAN| ENAME_UK |    14 |     1   (0)| 00:00:01 |
          ---------------------------------------------------------------------
          
          orcl>
          now the inline view is merged (and happens to use the index). This trivial example illustrates how important it is to know and understand your data. If there are no duplicates or no nulls, tell Oracle this. And one of my pet hates: lazy programmers who use aggregations or distinct because they don't know if there are duplicates, and so don't bother to handle them properly.
          --
          John Watson
          Oracle Certified Master DBA
          http://skillbuilders.com
          • 2. Re: inline views and parallelism
            977635
            Thanks for taking the time to show your example. It is very explanatory.

            In our case, we have several queries similar to:
            SELECT  * FROM
             ( SELECT  T2179.C1 FROM  aradmin.T2179  WHERE
               (
                 (
                    ( T2179.C536881093 = '000000000005505' )
                 OR
                    ( T2179.C536881095 = '000000000005730' )
                 OR
                    ( T2179.C536870923 = '000000000009773' )
                 )
               AND
                 ( T2179.C536871037 = 'Trouble' )
               AND
                 ( T2179.C536870944 != 'Approved By Change Control' )
               AND
                 ( T2179.C536870944 != 'Assigned' )
               AND
                 ( T2179.C536870944 != 'In Progress' )
               )
            ORDER BY C1 DESC
             )
            WHERE  ROWNUM <= 1
            • 3. Re: inline views and parallelism
              P.Forstmann
              Inline views are first a syntaxic feature that allow to code complex queries without creating new views that must be stored in database dictionary.

              Your application looks like Remedy and I'm not sure these kind of queries would need parallelism.
              Why do want to use parallelism ?
              If you have performance issues with some queries try to post data mentioned in HOW TO: Post a SQL statement tuning request - template posting
              • 4. Re: inline views and parallelism
                977635
                Yes, correct.
                I inherited the database and it already had parallelism set to 2 on 80% of the tables.
                I don't know if BMC set these up this way or not, but was more just curious why inline views did not use the parallelism set on the tables, or if they did, then how can I tell.

                I've been testing various queries and adding indexes where needed, but nothing really pokes out at me for performance problems now.
                Most things are fairly well tuned at this point, but always on the lookout for better possibilities.
                Just like using results_cache. I see this hint used in many queries and I only assume BMC wrote them to use it.