This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Sep 21, 2009 3:42 AM by Centinul RSS

Optimizer choosing different plans when ROWNUM filter. [UPDATED: 11.2.0.1]

Centinul Guru
Currently Being Moderated
I'm having a couple of issues with a query, and I can't figure out the best way to reach a solution.

Platform Information

Windows Server 2003 R2
Oracle 10.2.0.4

Optimizer Settings
SQL > show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     30
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
The query below, is a simple "Top N" query, where the top result is returned. Here it is, with bind variables in the same location as the application code:
SELECT     PRODUCT_DESC
FROM
(
     SELECT     PRODUCT_DESC
     ,     COUNT(*)     AS CNT
     FROM     USER_VISITS     
     JOIN     PRODUCT     ON PRODUCT.PRODUCT_OID = USER_VISITS.PRODUCT_OID
     WHERE     PRODUCT.PRODUCT_DESC != 'Home'     
     AND     VISIT_DATE
          BETWEEN
               ADD_MONTHS                    
               (
                    TRUNC                    
                    (
                         TO_DATE               
                         (
                              :vCurrentYear
                         ,     'YYYY'
                         )
                    ,     'YEAR'
                    )
               ,     3*(:vCurrentQuarter-1)
               )
          AND
               ADD_MONTHS                    
               (
                    TRUNC                    
                    (
                         TO_DATE               
                         (
                              :vCurrentYear
                         ,     'YYYY'
                         )
                    ,     'YEAR'
                    )
               ,     3*:vCurrentQuarter
               ) - INTERVAL '1' DAY               
     GROUP BY PRODUCT_DESC
     ORDER BY CNT DESC
)
WHERE     ROWNUM <= 1;
Explain Plan

The explain plan I receive when running the query above.
| Id  | Operation                         | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   2 |   VIEW                            |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |       |       |          |
|*  3 |    FILTER                         |                               |      1 |        |      1 |00:00:34.92 |   66343 |       |       |          |
|   4 |     SORT ORDER BY                 |                               |      1 |      1 |      1 |00:00:34.92 |   66343 |  2048 |  2048 | 2048  (0)|
|   5 |      SORT GROUP BY NOSORT         |                               |      1 |      1 |     27 |00:00:34.92 |   66343 |       |       |          |
|   6 |       NESTED LOOPS                |                               |      1 |      2 |  12711 |00:00:34.90 |   66343 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| PRODUCT                       |      1 |     74 |     77 |00:00:00.01 |      44 |       |       |          |
|*  8 |         INDEX FULL SCAN           | PRODUCT_PRODDESCHAND_UNQ      |      1 |      1 |     77 |00:00:00.01 |       1 |       |       |          |
|*  9 |        INDEX FULL SCAN            | USER_VISITS#PK                |     77 |      2 |  12711 |00:00:34.88 |   66299 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1))<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURR
              ENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   8 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   9 - access("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID" AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY')
              ,'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
       filter(("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2)
              TO SECOND(0) AND "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID"))
Row Source Generation

TKPROF Row Source Generation
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2     35.10      35.13          0      66343          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     35.10      35.14          0      66343          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=66343 pr=0 pw=0 time=35132008 us)
      1   VIEW  (cr=66343 pr=0 pw=0 time=35131996 us)
      1    FILTER  (cr=66343 pr=0 pw=0 time=35131991 us)
      1     SORT ORDER BY (cr=66343 pr=0 pw=0 time=35131936 us)
     27      SORT GROUP BY NOSORT (cr=66343 pr=0 pw=0 time=14476309 us)
  12711       NESTED LOOPS  (cr=66343 pr=0 pw=0 time=22921810 us)
     77        TABLE ACCESS BY INDEX ROWID PRODUCT (cr=44 pr=0 pw=0 time=3674 us)
     77         INDEX FULL SCAN PRODUCT_PRODDESCHAND_UNQ (cr=1 pr=0 pw=0 time=827 us)(object id 52355)
  12711        INDEX FULL SCAN USER_VISITS#PK (cr=66299 pr=0 pw=0 time=44083746 us)(object id 52949)
However when I run the query with an ALL_ROWS hint I receive this explain plan (reasoning for this can be found here Jonathan's Lewis' response: http://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4):
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  1 |  COUNT STOPKEY             |                |       |       |            |          |
|   2 |   VIEW                     |                |     1 |    39 |   223  (25)| 00:00:03 |
|*  3 |    FILTER                  |                |       |       |            |          |
|   4 |     SORT ORDER BY          |                |     1 |    49 |   223  (25)| 00:00:03 |
|   5 |      HASH GROUP BY         |                |     1 |    49 |   223  (25)| 00:00:03 |
|*  6 |       HASH JOIN            |                |   490 | 24010 |   222  (24)| 00:00:03 |
|*  7 |        TABLE ACCESS FULL   | PRODUCT   |    77 |  2849 |     2   (0)| 00:00:01 |
|*  8 |        INDEX FAST FULL SCAN| USER_VISITS#PK |   490 |  5880 |   219  (24)| 00:00:03 |
---------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   3 - filter(ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*(TO_NUMBER(:
              VCURRENTQUARTER)-1))<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*TO_N
              UMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
   6 - access("USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID")
   7 - filter("PRODUCT"."PRODUCT_DESC"<>'Home')
   8 - filter("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYY
              Y'),'fmyear'),3*(TO_NUMBER(:VCURRENTQUARTER)-1)) AND
              "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),
              3*TO_NUMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))
And the TKPROF Row Source Generation:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.51       0.51          0        907          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.51       0.51          0        907          0          27

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  FILTER  (cr=907 pr=0 pw=0 time=513472 us)
     27   SORT ORDER BY (cr=907 pr=0 pw=0 time=513414 us)
     27    HASH GROUP BY (cr=907 pr=0 pw=0 time=512919 us)
  12711     HASH JOIN  (cr=907 pr=0 pw=0 time=641130 us)
     77      TABLE ACCESS FULL PRODUCT (cr=5 pr=0 pw=0 time=249 us)
  22844      INDEX FAST FULL SCAN USER_VISITS#PK (cr=902 pr=0 pw=0 time=300356 us)(object id 52949)
The query with the ALL_ROWS hint returns data instantly, while the other one takes about 70 times as long.

Interestingly enough BOTH queries generate plans with estimates that are WAY off. The first plan is estimating 2 rows, while the second plan is estimating 490 rows. However the real number of rows is correctly reported in the Row Source Generation as 12711 (after the join operation).
TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
USER_VISITS                        196044       1049

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ---------- ----------- ------------- ----------------- -------------------
USER_VISITS#PK                          2         860        196002          57761 07/24/2009 13:17:59

COLUMN_NAME                    NUM_DISTINCT LOW_VALUE            HIGH_VALUE                                 DENSITY     NUM_NULLS HISTOGRAM
------------------------------ ------------ -------------------- -------------------- -------------------------------- ---------- ---------------
VISIT_DATE                           195900 786809010E0910       786D0609111328                      .0000051046452272          0 NONE
I don't know how the first one is estimating 2 rows, but I can compute the second's cardinality estimates by assuming a 5% selectivity for the TO_DATE() functions:
SQL > SELECT ROUND(0.05*0.05*196044) FROM DUAL;

ROUND(0.05*0.05*196044)
-----------------------
                    490
However, removing the bind variables (and clearing the shared pool), does not change the cardinality estimates at all.

I would like to avoid hinting this plan if possible and that is why I'm looking for advice. I also have a followup question.

Edited by: Centinul on Sep 20, 2009 4:10 PM

See my last post for 11.2.0.1 update.
  • 1. Re: Optimizer choosing different plans when ROWNUM filter is applied
    CharlesHooper Expert
    Currently Being Moderated
    You might want to take a look at this page:
    http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/

    Quoting from the above:
    "Even for the web-base reporting system, though, you may still be able to run under all_rows from from 10g onwards. In 10g when the optimizer sees predicates like “rownum <= 35″ it will behave as if you had included the hint /*+ first_rows(35) */ in the query."

    The ROWNUM <= 1 in the where clause apparently switched the optimizer mode from ALL_ROWS to FIRST_ROWS(1) - and FIRST_ROWS favors index accesses which avoid sorts (INDEX FULL SCAN does not require a sort, while INDEX FAST FULL SCAN does).

    I did not spend a lot of time looking very closely at what you posted, so I might have missed something.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 2. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    Charles --

    Thank you for the information. If I have to apply the hint I will, but it seems as though I shouldn't have to.

    My secondary question (as noted in the post), how can I get the cardinalities correct? If they are corrected, will the correct plan surface negating the need for the hint?
  • 3. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Tubby Guru
    Currently Being Moderated
    Not that i'm expecting this to actually change anything, but in one query plan you have declared VCURRENTQUARTER as a NUMBER whereas in another it's declared as a VARCHAR (i'm looking at the FILTER section here where i see).
    TO_NUMBER(:VCURRENTQUARTER)-1)
    In one output, and not in the other...

    Edited by: Tubby on Jul 27, 2009 12:06 PM
  • 4. Re: Optimizer choosing different plans when ROWNUM filter is applied
    CharlesHooper Expert
    Currently Being Moderated
    Centinul wrote:
    Charles --

    Thank you for the information. If I have to apply the hint I will, but it seems as though I shouldn't have to.

    My secondary question (as noted in the post), how can I get the cardinalities correct? If they are corrected, will the correct plan surface negating the need for the hint?
    Is bind peeking enabled? It is enabled by default on Oracle 10g (and 9i). Are there automatically generated histograms on any of the columns in the WHERE clause? Do any of the tables involved have out of date statistics (or cases where the statistics as of 10 PM do not match the statistics as of the current time)? Could you post a 10053 trace for the SQL statement?

    You could potentially perform testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance. The question then becomes whether this be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.

    Tubby potentially found a problem with your test run which used the ALL_ROWS hint, but that may just be an issue of using Explain Plan in SQL*Plus with bind variables. My memory is a little fuzzy at the moment, but I thought I remembered reading that explain plan does not peek at bind variables, as happens when the query is actually executed (but that may have been a 9i problem, where 10g uses DBMS_XPLAN for explain plans), and that SQL*Plus passes in numeric bind variables as if they are VARCHAR2. (I have an example in 11.1.0.7 which shows that this might not be the case).

    Could you use the ROW_NUMBER analytic function instead of ROWNUM:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#SQLRF06100

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 5. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    Tubby --

    Both queries were executed in SQL*Plus with bind variables defined as NUMBERs. So as Charles has stated it may be an issue with SQL*Plus/DBMS_XPLAN.

    Thanks!
  • 6. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Tubby Guru
    Currently Being Moderated
    OK, good to know :)

    Out of personal curiosity (if you have time) what happens if you remove the ANSI syntax? I've seen things go awry with the optimizer before using ANSI vs Non-ANSI notation.
  • 7. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    I don't have access to the DB right now, but I'll make sure I answer all the questions in the morning.

    Thanks for being interested in my question!
  • 8. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    Tubby wrote:
    Out of personal curiosity (if you have time) what happens if you remove the ANSI syntax? I've seen things go awry with the optimizer before using ANSI vs Non-ANSI notation.
    That is true. I tried removing the ANSI syntax and still received the same execution plan.
    Charles Hooper:
    Is bind peeking enabled? It is enabled by default on Oracle 10g (and 9i). Are there automatically generated histograms on any of the columns in the WHERE clause? Do any of the tables involved have > out of date statistics (or cases where the statistics as of 10 PM do not match the statistics as of the current time)? Could you post a 10053 trace for the SQL statement?
    I'm running 10.2.0.4 and don't have any undocumented parameters set as far as I know. Statistics are up to date on all tables and indexes.
    You could potentially perform testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance. The question then becomes > whether this be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.
    I tried doing that this morning by increasing the cardinality from the USER_VISITS table to a value such that the estimate was about that of the real amount of data. However the plan did not change.
    Could you use the ROW_NUMBER analytic function instead of ROWNUM
    Interestingly enough, when I tried this it generated the same plan as was used with the ALL_ROWS hint, so I may implement this query for now.

    I do have two more followup questions:

    1. Even though a better plan is picked the optimizer estimates are still off by a large margin because of bind variables and 5%* 5% * NUM_ROWS. How do I get the estimates in-line with the actual values? Should I really fudge statistics?

    2. Should I raise a bug report with Oracle over the behavior of the original query?

    Thanks!
  • 9. Re: Optimizer choosing different plans when ROWNUM filter is applied
    680087 Pro
    Currently Being Moderated
    Can you upload 10053 trace for both versions (default & ALL_ROWS) somewhere?
  • 10. Re: Optimizer choosing different plans when ROWNUM filter is applied
    CharlesHooper Expert
    Currently Being Moderated
    Centinul wrote:
    You could potentially perform testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance. The question then becomes > whether this be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.
    I tried doing that this morning by increasing the cardinality from the USER_VISITS table to a value such that the estimate was about that of the real amount of data. However the plan did not change.
    Could you use the ROW_NUMBER analytic function instead of ROWNUM
    Interestingly enough, when I tried this it generated the same plan as was used with the ALL_ROWS hint, so I may implement this query for now.

    I do have two more followup questions:

    1. Even though a better plan is picked the optimizer estimates are still off by a large margin because of bind variables and 5%* 5% * NUM_ROWS. How do I get the estimates in-line with the actual values? Should I really fudge statistics?

    2. Should I raise a bug report with Oracle over the behavior of the original query?
    That is great that the ROW_NUMBER analyitic function worked. You may want to perform some testing with this before implementing it in production to see whether Oracle performs significantly more logical or physical I/Os with the ROW_NUMBER analytic function compared to the ROWNUM solution with the ALL_ROWS hint.

    As Timur suggests, seeing a 10053 trace during a hard parse of both queries (with and without the ALL_ROWS hint) would help determine what is happening. It could be that a histogram exists which is feeding bad information to the optimizer, causing distorted cardinality in the plan. If bind peeking is used, the 5% * 5% rule might not apply, especially if a histogram is involved. Also, the WHERE clause includes "PRODUCT.PRODUCT_DESC != 'Home'" which might affect the cardinality in the plan.

    Your question may have prompted the starting of a thread in the SQL forum yesterday on the topic of ROWNUM, but it appears that thread was removed from the forum within the last couple hours.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 11. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    Charles Hooper wrote:
    That is great that the ROW_NUMBER analyitic function worked. You may want to perform some testing with this before implementing it in production to see whether Oracle performs significantly more logical or physical I/Os with the ROW_NUMBER analytic function compared to the ROWNUM solution with the ALL_ROWS hint.
    I did check the I/O items and there was virtually no difference so at least I have one solution.
    As Timur suggests, seeing a 10053 trace during a hard parse of both queries (with and without the ALL_ROWS hint) would help determine what is happening. It could be that a histogram exists which is feeding bad information to the optimizer, causing distorted cardinality in the plan. If bind peeking is used, the 5% * 5% rule might not apply, especially if a histogram is involved. Also, the WHERE clause includes "PRODUCT.PRODUCT_DESC != 'Home'" which might affect the cardinality in the plan.
    The best I could do was Pastebin the results, see the links below:

    [Pastebin: No Hint|http://pastebin.com/d73af25e5]
    [Pastebin: ALL_ROWS Hint|http://pastebin.com/d2447833f]

    Looking at the trace for the non-hinted plan it seems the optimizer is making incorrect estimates at multiple steps once the K Mode operations have started. For example, it's predicting a cardinality of 1 for the PRODUCT table, but in reality it should be on the order of about 77. However there is a frequency histogram on this column. The value in the predicate does exist in the histogram. The CBO Trace says it's using 1 for the density and by my calculations the density*num_rows should give a cardinality of 78.

    On a side note, I don't think I have a full understanding of how to read the CBO trace when the K Mode is involved. If anyone wants to enlighten me I would greatly appreciate it.

    Thanks!
  • 12. Re: Optimizer choosing different plans when ROWNUM filter is applied
    mbobak Oracle ACE
    Currently Being Moderated
    Centinul,

    Since you mentioned problems with cardinality estimates being way off, I thought you may want to check out Wolfgang Breitling's "Tuning by Cardinality Feedback". There's a paper and Powerpoint presentation available at:
    http://www.centrexcc.com/papers.html

    Hope that helps,

    -Mark
  • 13. Re: Optimizer choosing different plans when ROWNUM filter is applied
    Centinul Guru
    Currently Being Moderated
    Thanks for the links. I have read these papers previously. I'm just trying to determine if that is the approach I should take or not.

    Thanks!
  • 14. Re: Optimizer choosing different plans when ROWNUM filter is applied
    680087 Pro
    Currently Being Moderated
    Interesting. From what is reported in CBO trace it seems like CBO tries to cost an access to product table as if Oracle won't require too many rows from that table. I've tried to reproduce your scenario in 11.1.0.7 with optimizer_features_enable='10.2.0.4' (and my intention was to test this assumption by using different first_rows_n hints), but no luck. What happens with cardinality estimates when you use FIRST_ROWS_N hints with different N?
    Can you build a reproducible test case on your database?
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points