Oracle Scratchpad

November 26, 2014

Lunchtime quiz

Filed under: CBO,dbms_xplan,Oracle — Jonathan Lewis @ 12:41 pm GMT Nov 26,2014

There was a question on OTN a few days ago asking the following question:

Here’s a query that ran okay on 11g, but crashed with Oracle error “ORA-01843: not a valid month” after upgrade to 12c; why ?

The generically correct answer, of course, is that the OP had been lucky (or unlucky, depending on your point of view) on 11g – and I’ll explain that answer in another blog posting.

That isn’t the point of this posting, though. This posting is a test of observation and deduction. One of the respondants in the thread had conveniently supplied a little bit of SQL that I copied and fiddled about with to demonstrate a point regarding CPU costing, but as I did so I thought I’d show you the following and ask a simple question.’


drop table T;

Create Table T
As
with
periods as (
                  Select 'January' period, 1 cal  From Dual
        union all Select 'February' period , 2 cal From Dual
        union all Select 'March' period , 3 cal From Dual
        union all Select 'April' period , 4 cal From Dual
        union all Select 'May'  period, 5 cal From Dual
        union all Select 'June' period, 6 cal From Dual
        union all Select 'July' period, 7 cal From Dual
        union all Select 'August' period, 8 cal From Dual
        union all Select 'September' period, 9 cal  From Dual
        union all Select 'October' period, 10 cal From Dual
        union all Select 'November' period, 11 cal From Dual
        Union All Select 'December' Period, 12 Cal From Dual
        Union All Select '13 Series' Period, Null Cal  From Dual
)
Select  Period,Cal
from periods;

prompt  ==================================
prompt  When we invoke below SQL it works.
prompt  ==================================

set autotrace on explain

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,
                to_date('November, 2014','Month, YYYY') col2
        From  T
        Where  Cal > 0
        )
;

prompt  ================================================
prompt  But when we add comparison operations , it fails
prompt  ================================================

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY')   col1,
                to_date('November, 2014','Month, YYYY')         col2
        From  T
        Where  Cal > 0
        )
where
        col1 >= col2
;

set autotrace off



All I’ve done is create a table then run and generate the execution plans for two queries – with a comment that if you try to run one query it will succeed but if you try to run the other it will fail (and raise ORA-01843). As far as the original supplier was concerned, both queries succeeded in 11g and the failure of the second one appeared only in 12c. In fact, for reasons that I won’t discuss here, it is POSSIBLE for the failure to appear in 11g as well, though not necessarily with this exact data set.

Here’s the COMPLETE output I got from running the code above on an 11.2.0.4 instance:



Table dropped.


Table created.

==================================
When we invoke below SQL it works.
==================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
January            1 01-JAN-14 01-NOV-14
February           2 01-FEB-14 01-NOV-14
March              3 01-MAR-14 01-NOV-14
April              4 01-APR-14 01-NOV-14
May                5 01-MAY-14 01-NOV-14
June               6 01-JUN-14 01-NOV-14
July               7 01-JUL-14 01-NOV-14
August             8 01-AUG-14 01-NOV-14
September          9 01-SEP-14 01-NOV-14
October           10 01-OCT-14 01-NOV-14
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |   228 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |   228 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("CAL">0)

Note
-----
   - dynamic sampling used for this statement (level=2)

================================================
But when we add comparison operations , it fails
================================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    19 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("CAL">0 AND TO_DATE("PERIOD"||', '||'2014','Month,
              YYYY')>=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


So this is the question. What’s the anomaly in this output ?

Bonus question: What’s the explanation for the anomaly ?

Answers:

If I had asked why the query might, or might not, crash – the answer would be about the order of predicate evaluation, and simply collecting stats (or not) might have made a difference. Ever since “system stats”  and “CPU costing” appeared the optimizer has been able to change the order in which it applies filter predicates to a table (there’s a pdf of an article of mine from Oracle magazine in the 9i / 10g timeline linked at this URL) .  In this case, applying the “cal > 0” predicate first luckily eliminates the rows that would fail the second predicate. Since the effect is driven by the optimizer’s stats this type of failure could occur ANY TIME you have a predicate that requires coercion between types to take place – which is one reason why you see the injunctions to use the correct data types; and why, if you need coercion to work around incorrect data types you have to consider writing your own functions to trap and resolve the necessary errors raised by Oracle’s implicit conversion mechanisms.

For a quick sketch of the optimizer strategy, the arithmetic is roughly:  predicate A costs c1 and predicate B costs c2; if I apply predicate A to every row I have to apply predicate B to only N surviving rows; if I apply predicate B to every row I have to apply predicate A to M surviving rows; which is smaller: (input_rows * c1 + N * c2) or (input_rows * c2 + M * c1).

The answer to the question I actually asked is this, though: I stressed the fact that this was the COMPLETE output because, as Narenda highlighted in comment 7 below –  the first query shows a note about dynamic sampling and the second query does not. This is a little surprising; we don’t have stats on the table, and the two queries are different so we have to optimizer both of them.  In 12c, of course, it’s possible that the optimizer may have done something clever with statistics feedback (formerly cardinality feedback) and created an SQL directive – but even then we should have seen a note about that.

For the bonus question: given the second output doesn’t report dynamic sampling we should be curious why not – did the optimizer simply decide not to try, did it try then decide not to use the results for some reason, or is there some other reason.  The obvious next step is to look at the 10053 (optimizer) trace – where you find that the optimizer DID do dynamic sampling or rather, it tried to do dynamic sampling but the query generated to take the sample failed with Oracle error ORA-01843, as suggested by Chinar Aliyev in comment 9  and expanded by Mohamed Houri in comment 11.

The irony of the sampling problem (hinted by Chinar Aliyev in comment 10) is that you could be in a position where you have a large table and oracle picks a small sample which happens to miss any of the problem rows and then return a sample that persuades the optimizer to pick an execution plan that is bound to find a problem row; alternatively the SQL used to generate the sample might apply the predicate in an order that manages to eliminate the problem rows, while the final plan derived after sampling persuades the optimizer to use the predicate in the order B, A.

 

18 Comments »

  1. Hi Jonathan,

    We can able to generate the ORA-01843 if we modify the query as below (skipping cal > 0 condition)

     select *
      2  from    (
      3          select
      4                  Period,
                    Cal,
      5    6                  to_date(Period || ', ' || 2014,'Month, YYYY')   col1,
      7                  to_date('November, 2014','Month, YYYY')         col2
      8          From  T
      9          )
     10  where
     11          col1 >= col2
     12  ;
    ERROR:
    ORA-01843: not a valid month
    
    Plan hash value: 1601196873
    
    ----------------------------------------------------------------------------------
    | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |      |     4 |   132 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS STORAGE FULL| T    |     4 |   132 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - storage(TO_DATE("PERIOD"||', '||'2014','Month, YYYY')>=TO_DATE('
                  2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
           filter(TO_DATE("PERIOD"||', '||'2014','Month, YYYY')>=TO_DATE('
                  2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    PERIOD                             CAL
    --------------------------- ----------
    January                              1
    February                             2
    March                                3
    April                                4
    May                                  5
    June                                 6
    July                                 7
    August                               8
    September                            9
    October                             10
    November                            11
    December                            12
    13 Series
    

    Since, the record “13 Series”, can’t be converted to date.

    Comment by Pavan Kumar — November 26, 2014 @ 1:33 pm GMT Nov 26,2014 | Reply

  2. Hi , i will take the bonus question and return to work :D . i think it’s related to predicate evaluation order that has changed.

    Will work :

    filter(“CAL”>0 AND TO_DATE(“PERIOD”||’, ‘||’2014′,’Month,
    YYYY’)>=TO_DATE(‘ 2014-11-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

    Will not work :

    filter((TO_DATE(“PERIOD”||’, ‘||’2014′,’Month, YYYY’)>=TO_DATE(‘
    2014-11-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) AND “CAL”>0))

    Comment by Mahmoud Hatem — November 26, 2014 @ 1:45 pm GMT Nov 26,2014 | Reply

  3. BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
    PL/SQL Release 12.1.0.2.0 - Production                                                    0
    CORE    12.1.0.2.0      Production                                                                0
    TNS for Linux: Version 12.1.0.2.0 - Production                                            0
    NLSRTL Version 12.1.0.2.0 - Production                                                    0
    
    
    
    SQL> select *
    from    (
            select
                    Period,
                    Cal,
                    to_date(Period || ', ' || 2014,'Month, YYYY')   col1,
                    to_date('November, 2014','Month, YYYY')         col2
            From  T
            Where  Cal > 0
            )
    where
            col1 >= col2
    ;  2    3    4    5    6    7    8    9   10   11   12   13  
    
    PERIOD           CAL COL1      COL2
    --------- ---------- --------- ---------
    November          11 01-NOV-14 01-NOV-14
    December          12 01-DEC-14 01-NOV-14
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1601196873
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |    19 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T    |     1 |    19 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("CAL">0 AND TO_DATE("PERIOD"||', '||'2014','Month,
                  YYYY')>=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    

    Even on 12c it works fine for me.

    Comment by Pavan Kumar — November 26, 2014 @ 1:45 pm GMT Nov 26,2014 | Reply

    • Even though this doesn’t address the question, it adds another data point to my comment: “the OP had been lucky (or unlucky, depending on your point of view)”; the OP’s issue is not (directly) about the upgrade.

      Comment by Jonathan Lewis — November 26, 2014 @ 2:41 pm GMT Nov 26,2014 | Reply

  4. I agree with Mahmoud Hatem. Actually oracle successfully parsed this query but when “filtering” rows occur error for knowing reason.
    I tested evaluating predicate order is a matter and in our case its appeared clearly.

    filter(“CAL”>0 AND TO_DATE(“PERIOD”||’, ‘||’2014′,’Month,YYYY’)>=TO_DATE(‘ 2014-11-01 00:00:00′, ‘syyyy-mm-dd hh24:mi:ss’))

    In this case Oracle discard “Invalid” rows using/applying CAL>0 predicate and then apply second predicate but below case CAL>0 predicate applied last time. therefore we get an error

    filter((TO_DATE(“PERIOD”||’, ‘||’2014′,’Month, YYYY’)>=TO_DATE(‘
    2014-11-01 00:00:00′, ‘syyyy-mm-dd hh24:mi:ss’) AND “CAL”>0))

    Comment by Chinar Aliyev — November 26, 2014 @ 2:27 pm GMT Nov 26,2014 | Reply

  5. So far all the replies have been about why the query might fail; but as it says near the start: “That isn’t the point of this posting”

    The penultimate line supplies the question I want answered: “What’s the anomaly in this output ?”

    Comment by Jonathan Lewis — November 26, 2014 @ 2:38 pm GMT Nov 26,2014 | Reply

  6. I think here is anomaly can be

    to_date(Period || ‘, ‘ || 2014,’Month, YYYY’) col1 ,
    to_date(‘November, 2014′,’Month, YYYY’) col2

    and actually oracle converts these as below

    TO_DATE(“PERIOD”||’, ‘||’2014′,’Month,YYYY’) — this is col1
    TO_DATE(‘ 2014-11-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) — this equivalent of col2 with different format

    Comment by Chinar Aliyev — November 26, 2014 @ 2:55 pm GMT Nov 26,2014 | Reply

  7. The output of first query mentions that Oracle used level 2 dynamic sampling but output of second query does not indicate any dynamic sampling used. Is that the anomaly or just a negligible minor thing?

    Comment by Narendra — November 26, 2014 @ 3:27 pm GMT Nov 26,2014 | Reply

    • Narendra,

      That’s the anomaly. (Even negligible minor things can be significant anomalies).
      It’s 11g, I didn’t collect stats on the table, I’ve got level 2 sampling by default, but it looks as if one of the calls to explain plan didn’t use dynamic sampling.
      That may or may not be significant but it’s definitely an anomaly.

      Comment by Jonathan Lewis — November 26, 2014 @ 3:42 pm GMT Nov 26,2014 | Reply

      • For me the bug also occurred on 11g when collecting stats before execution. With the stats, there is no dynamic sampling.
        Other interesting observation: With or without stats, with >= or <=, the estimated row count is always 1 for the 2nd statement.

        Comment by Salek Talangi — November 26, 2014 @ 3:54 pm GMT Nov 26,2014 | Reply

  8. In the second plan, the date predicate is pushed into the subquery, or the subquery is unnested.

    Comment by Alistair Wall — November 26, 2014 @ 3:55 pm GMT Nov 26,2014 | Reply

  9. Actually it should be use dynamic sampling, probably sampling result oracle reads “13 Series” rows which generate “ORA-01843”, due to dynamic sampling ignored. Reason can be that

    Comment by Chinar Aliyev — November 26, 2014 @ 4:10 pm GMT Nov 26,2014 | Reply

  10. Because table is small and randomly read that block which contain “Invalid” row in our case 13 row.

    Comment by Chinar Aliyev — November 26, 2014 @ 4:12 pm GMT Nov 26,2014 | Reply

  11. In the corresponding 10053 trace file I saw this

    ** Generated dynamic sampling query:
        query text : 
    SELECT
      /* OPT_DYN_SAMP */
      /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) 
      opt_param('parallel_execution_enabled', 'false') 
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
      NVL(SUM(C1),0),
      NVL(SUM(C2),0)
    FROM
      (SELECT
        /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */
        1 AS C1,
        CASE
          WHEN TO_DATE("T"."PERIOD"
            ||', '
            ||'2014','Month, YYYY')>=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
          AND "T"."CAL"             >0
          THEN 1
          ELSE 0
        END AS C2
      FROM "C##MHOURI"."T" "T"
      ) SAMPLESUB;
    
    ** Error while executing dynamic sampling query (1843).
    DS Failed for : ----- Current SQL Statement for this session (sql_id=anct55cqgcx9j)
    

    This is the explanation of why the Note dynamic sampling has been ignored (1843 which is ora-1843)

    Best regards
    Mohamed Houri

    Comment by hourim — November 26, 2014 @ 5:21 pm GMT Nov 26,2014 | Reply

  12. Nice that Oracle can trap and ignore an error that occurs during dynamic sampling so that the calling query doesn’t fail.

    Comment by Hemant K Chitale — November 29, 2014 @ 2:22 pm GMT Nov 29,2014 | Reply

  13. […] is, Jonathan lewis post on same. Please Click here for actual reasons in 12c for not evaluating condition 1 first and experts comments on […]

    Pingback by SQL worked fine on pre 12c but failed with “ORA-01843: not a valid month” after upgrade. | Oracle Internals and Performance concepts — November 30, 2014 @ 1:33 am GMT Nov 30,2014 | Reply

  14. I tried the above on a 11.2.0.2 database. If I run the SQL without gathering statistics on the table the query works. That is, the Cal > 0 predicate is evaluated first. If I gather statistics and then run the SQL it fails. My gues is that with stats the optimizer is able to determine that Cal > 0 is not that selective and chooses to evaluate the other predicate first. Now if I remember right, Oracle builds the stats automatically for a CTAS statement in 12c – which I guess is why you see the difference in behaviour between the 2 versions.

    Comment by Lakmal Rajapakse — December 9, 2014 @ 5:42 pm GMT Dec 9,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.