9 Replies Latest reply on Jan 30, 2012 11:58 AM by johnnie d

    performance of query poor, cardinality wrong for view

    johnnie d
      I have a query, whose performance is unsatisfactory.

      It produces (11.2.0.2) the following trace. The slowest part of the query is the UNION-ALL operation on the two index full fast scans on the PRODUCTS_DATES indices. These indexes are in the two tables that make up a view, V_SALES_ALL.

      The cardinality estimate for the fast full scans seems to be way out - 100 rows as against 78,000,000 and 1,703,000 respectively. The estimate of 100 looks suspiciously like a default because the two tables are, as I have said, inside a view. In reality, if I break up the view to its constituent tables, the queries runs in a tenth of the time.


      How can I correct this misinformation, presumably created by the view ?

      Am I reading the trace right ?

      Regs

      Johnnie


      Rows Row Source Operation
      ------- ---------------------------------------------------
      321 SORT GROUP BY (cr=6759441 pr=176970 pw=176955 time=480 us cost=63 size=896 card=14)
      5322875 NESTED LOOPS (cr=6759441 pr=176970 pw=176955 time=109327744 us)
      5322875 NESTED LOOPS (cr=241360 pr=176970 pw=176955 time=55796544 us cost=62 size=896 card=14)
      5322875 HASH JOIN (cr=241049 pr=176970 pw=176955 time=7774711 us cost=48 size=280 card=14)
      80445738 VIEW V_SALES_ALL (cr=241001 pr=0 pw=0 time=569162368 us cost=4 size=1800 card=200)
      80445738 UNION-ALL (cr=241001 pr=0 pw=0 time=404890176 us)
      78742696 INDEX FAST FULL SCAN PRODUCTS_DATES_IDX (cr=235954 pr=0 pw=0 time=85524904 us cost=2 size=900 card=100)(object id 221975)
      1703042 INDEX FAST FULL SCAN PRODUCTS_DATES_IDX_HARD (cr=5047 pr=0 pw=0 time=1850486 us cost=2 size=900 card=100)(object id 241720)
      2238 VIEW index$_join$_003 (cr=48 pr=0 pw=0 time=14474 us cost=44 size=24618 card=2238)
      2238 HASH JOIN (cr=48 pr=0 pw=0 time=9737 us)
      2238 INDEX RANGE SCAN PRODUCTS_GF_INDEX2 (cr=8 pr=0 pw=0 time=2609 us cost=6 size=24618 card=2238)(object id 255255)
      16206 INDEX FAST FULL SCAN PRODUCTS_GF_PK (cr=40 pr=0 pw=0 time=20415 us cost=45 size=24618 card=2238)(object id 255253)
      5322875 INDEX UNIQUE SCAN DATES_PK (cr=311 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 151306)
      5322875 TABLE ACCESS BY INDEX ROWID DATES (cr=6518081 pr=0 pw=0 time=0 us cost=1 size=44 card=1)


      Rows Execution Plan
      ------- ---------------------------------------------------
      0 SELECT STATEMENT MODE: FIRST_ROWS
      321 SORT (GROUP BY)
      5322875 HASH JOIN
      5322875 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DATES' (TABLE)
      5322875 HASH JOIN
      80445738 VIEW OF 'index$_join$_003' (VIEW)
      80445738 HASH JOIN
      78742696 INDEX MODE: ANALYZED (RANGE SCAN) OF
      'PRODUCTS_GF_INDEX2' (INDEX)
      1703042 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
      'PRODUCTS_GF_PK' (INDEX (UNIQUE))
      2238 VIEW OF 'V_SALES_ALL' (VIEW)
      2238 UNION-ALL
      2238 INDEX MODE: ANALYZED (FULL SCAN) OF
      'PRODUCTS_DATES_IDX' (INDEX)
      16206 INDEX MODE: ANALYZED (FULL SCAN) OF
      'PRODUCTS_DATES_IDX_HARD' (INDEX)
        • 1. Re: performance of query poor, cardinality wrong for view
          Rene Argento
          Are the statistics of your database/tables envolved up to date?

          That can interfere in the estimated cardinalities.
          • 2. Re: performance of query poor, cardinality wrong for view
            rp0428
            Is this a new query? If not, did it used to perform but not it doesn't? Does it perform in one environment/server but not in another?
            Is it a new view or one that has worked well in the past with other queries? Are there other queries on the view that perform ok?

            Can you post the query and execution plan for the view itself? We'd like to see how the tables are being joined.
            • 3. Re: performance of query poor, cardinality wrong for view
              johnnie d
              ---------------------------------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              ---------------------------------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 14 | 896 | 63 (7)| 00:00:01 |
              | 1 | SORT GROUP BY | | 14 | 896 | 63 (7)| 00:00:01 |
              | 2 | NESTED LOOPS | | | | | |
              | 3 | NESTED LOOPS | | 14 | 896 | 62 (5)| 00:00:01 |
              |* 4 | HASH JOIN | | 14 | 280 | 48 (7)| 00:00:01 |
              | 5 | VIEW | V_SALES_ALL | 200 | 1800 | 4 (0)| 00:00:01 |
              | 6 | UNION-ALL | | | | | |
              | 7 | INDEX FAST FULL SCAN | PRODUCTS_DATES_IDX | 100 | 900 | 2 (0)| 00:00:01 |
              | 8 | INDEX FAST FULL SCAN | PRODUCTS_DATES_IDX_HARD | 100 | 900 | 2 (0)| 00:00:01 |
              |* 9 | VIEW | index$_join$_003 | 2238 | 24618 | 44 (7)| 00:00:01 |
              |* 10 | HASH JOIN | | | | | |
              |* 11 | INDEX RANGE SCAN | PRODUCTS_GF_INDEX2 | 2238 | 24618 | 6 (0)| 00:00:01 |
              | 12 | INDEX FAST FULL SCAN | PRODUCTS_GF_PK | 2238 | 24618 | 45 (3)| 00:00:01 |
              |* 13 | INDEX UNIQUE SCAN | DATES_PK | 1 | | 0 (0)| 00:00:01 |
              | 14 | TABLE ACCESS BY INDEX ROWID| DATES | 1 | 44 | 1 (0)| 00:00:01 |
              ---------------------------------------------------------------------------------------------------------

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

              4 - access("V_SALES_ALL"."PRODUCT_ID"="PRODUCTS_GF"."PRODUCT_ID")
              9 - filter("PRODUCTS_GF"."GENERIC_FMAT_TYPE"='XBOX 360')
              10 - access(ROWID=ROWID)
              11 - access("PRODUCTS_GF"."GENERIC_FMAT_TYPE"='XBOX 360')
              13 - access("V_SALES_ALL"."PERIOD_ID"="DATES"."PERIOD_ID")
              • 4. Re: performance of query poor, cardinality wrong for view
                911612
                Hi,

                Can you post your execution plan using following statement. This will help us understand if cardinality estimates are right.

                Run your SQL statement..
                select * from table(dbms_xplan.display_cursor(null,null, ‘ALLSTATS LAST’));

                Regards
                • 5. Re: performance of query poor, cardinality wrong for view
                  rp0428
                  Can you post the query and answer the other questions? Thanks.
                  • 6. Re: performance of query poor, cardinality wrong for view
                    Nikolay Savvinov
                    Hi,

                    the optimizer estimates cardinality as total number of rows times estimated selectivity of filter predicates. Therefore, there are two possibilities: one, num_rows information in data dictionary is wrong. It's very simple to check this:
                     SELECT num_rows FROM DBA_TABLES WHERE table_name = <table_name> and owner = <owner> 
                    . Number two, filter selectivity is wrong. The check the latter, examine the predicate information from your plan or post it here (you can obtain that using dbms_xplan.display with format=>'advanced').

                    If it still doesn't clarify the situation, then you can see what's going on by checking the optimizer trace (event 10053). Make sure that the statement is hard-parsed during tracing or there won't be anything in the trace file (you can achieve that by making a non-significant modification to the query, e.g. adding a comment).

                    Hope this helps.

                    Best regards,
                    Nikolay
                    • 7. Re: performance of query poor, cardinality wrong for view
                      Jonathan Lewis
                      johnnie d wrote:
                      I have a query, whose performance is unsatisfactory.

                      It produces (11.2.0.2) the following trace. The slowest part of the query is the UNION-ALL operation on the two index full fast scans on the PRODUCTS_DATES indices. These indexes are in the two tables that make up a view, V_SALES_ALL.

                      The cardinality estimate for the fast full scans seems to be way out - 100 rows as against 78,000,000 and 1,703,000 respectively. The estimate of 100 looks suspiciously like a default because the two tables are, as I have said, inside a view. In reality, if I break up the view to its constituent tables, the queries runs in a tenth of the time.

                      Rows Execution Plan
                      ------- ---------------------------------------------------
                      0 SELECT STATEMENT MODE: FIRST_ROWS
                      Are you running with optimizer_mode=first_rows_100 ?

                      If so then you may have found a bug in the first_rows_N code. The 100 seems to have been pushed inside the view in a way that has made Oracle cost for the complete index fast full scans while only "intending" to find 100 rows in each table. It has the taken the 100 limit as the actual limit when deciding which row source to use as the hash, and which to use as the probe.

                      If you want the entire result set, or a large part of it, you could add the all_rows hint to the statement.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      Author: <b><em>Oracle Core</em></b>
                      • 8. Re: performance of query poor, cardinality wrong for view
                        johnnie d
                        Nicholas

                        The NUM_ROWS data is fine. The filter doesn't come into it as the view is not being filtered : there is a hash join with a filtered product table.

                        JBD
                        • 9. Re: performance of query poor, cardinality wrong for view
                          johnnie d
                          Jonathan

                          I should have known the 'guru' would have got it straight away !

                          Yep. I've been mooting a change to ALL_ROWS, and I think this is the final straw ...

                          Johnnie