4 Replies Latest reply: Dec 3, 2012 11:17 AM by 946515 RSS

    5 views perform great - view of 5 views does not perform at all

    946515
      I have 5 "small" views that perform great - they return all rows (a few hundred to a few thousand) in just a few seconds. They all have the exact same 3 fields that in view 1 would be the PK if it were a table. View #6 joins the other 5 together on those fields with left join syntax (some views do not have all the combinations, others have multiple matches). Because of the multiple matches in some of the views the grand result set has a fairly high level of duplication in some columns but we're OK with that.

      The problem is that view #6 for all practical purposes does not perform anything at all. It just sits there. One modest test batch ran for several days before we gave up on it. I know that complex views (views of views) do not always honor hints but we tried some anyway; parallel and hash join.

      An experiment of materializing the 5 views has view 6 return its results (and exactly as we want it) in a matter of seconds! Alas we are not allowed to use the materialize option without a lot of administrative hassle that isn't worthwhile. The plan for the view on view shows nested loop joins between the 5 while the view on materialized shows hash joins, thus the attempt to use the hash join hint to no avail. But the difference in join method doesn't seem to explain it.

      We've gone down another route of getting the data joined as needed but there's some debate and curiosity about whether this is a valid approach. Has anyone else experienced something like this? Is a view of several views a bad idea?
        • 1. Re: 5 views perform great - view of 5 views does not perform at all
          damorgan
          No version number.
          No DDL.
          No Explain Plan report generated using DBMS_XPLAN.

          It should not surprise you that no help is possible at this time.

          Please post ALL relevant information if you wish to receive help.
          • 2. Re: 5 views perform great - view of 5 views does not perform at all
            rp0428
            >
            An experiment of materializing the 5 views has view 6 return its results (and exactly as we want it) in a matter of seconds!
            >
            Oracle is rewriting the query and likely rewriting the views themselves.

            You can often get Oracle to materialize each view by adding something (e.g. ROWNUM) the the query for that view.

            So instead of (using only two views)
            CREATE OR REPLACE VIEW6 AS
              SELECT V1.*, V2.* FROM VIEW1 V1, VIEW2 V2 ...
            Try adding rownum to each
            CREATE OR REPLACE VIEW6 AS
            WITH QV1 as (SELECT V1.*, ROWNUM FROM VIEW1 V1)
            SELECT qV1.*, V2.* FROM VIEW1 V1, VIEW2 V2 ...
            That way Oracle will usually materialize view1. There may only be one view of the five that will fix the issue if you materialize it.
            {quote}
            The problem is that view #6 for all practical purposes does not perform anything at all. It just sits there. One modest test batch ran for several days before we gave up on it.
            {quote}
            Then you are clearly not designing your tests properly. You should NEVER test a new query on any substantial amount of data; a few records or tens of records is all.
            
            And you should always review the actual execution plan before doing the big test on the large datasets. If necessary you can dummy up the stats to make Oracle think there is a lot of data in the table.
            
            Whoever ran a test and then let it run for days ought to have a good talking to. That is pretty much a rookie mistake.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
            • 3. Re: 5 views perform great - view of 5 views does not perform at all
              Nikolay Savvinov
              Hi,

              what is probably going on here is that the optimizer cannot get cardinalities right -- either because there is a problem with stats (stale, not representative, wrong number of histogram bins etc.) or because of an optimizer bug/limitation, and because of that it chooses the wrong order of joins and/or wrong join methods. In order to get to the bottom of the issue we need an execution plan with rowsource stats (A-time and A-rows). If not sure how to produce this information, here is a link: http://savvinov.com/2012/09/24/a-sqlplus-script-for-diagnosing-poor-sql-plans/

              Best regards,
              Nikolay
              • 4. Re: 5 views perform great - view of 5 views does not perform at all
                946515
                Thanks! I had tried "WITH" but it hadn't made any difference because I didn't have the ROWNUM. Adding that made the topmost view super fast! How odd; I still don't understand what behind the scenes to make that 1/millionth the response time but I'll take the results.

                PS - I hadn't included a plan because it's over 500 rows with all the subqueries.