ORA-01722: upgrade error

I received an email recently from someone who had just upgraded the Oracle 11i Business Suite (11.5.9) from 9.2.0.6 to 10.2.0.2.  After the upgrade, the following SQL statement (shown here with its original format – not according to my standards) started failing with Oracle error: ORA-01722: invalid number.

select fr.APPLICATION_ID, fr.responsibility_id
from fnd_request_group_units frgu
,fnd_responsibility fr
,fnd_user_resp_groups furg
,fnd_concurrent_programs fcp
where frgu.APPLICATION_ID = fcp.APPLICATION_ID
and   frgu.REQUEST_UNIT_ID = fcp.CONCURRENT_PROGRAM_ID
and   fcp.CONCURRENT_PROGRAM_NAME = 'OT044560'
and   furg.USER_ID = 3821
and   fr.APPLICATION_ID = frgu.APPLICATION_ID
and   fr.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
and   furg.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID
and   furg.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and   exists (select null
from fnd_profile_options fpo
,fnd_profile_option_values fpov
where 1=1
and   fpo.PROFILE_OPTION_ID = fpov.PROFILE_OPTION_ID
and   fpov.LEVEL_ID = 10003 -- responsibility level
and   fpov.LEVEL_VALUE = fr.responsibility_id
and   fpov.LEVEL_VALUE_APPLICATION_ID = fr.application_id
and   fpov.PROFILE_OPTION_VALUE = 2
and   fpo.PROFILE_OPTION_NAME = 'ORG_ID')
and rownum = 1
;

The DBA found that if he changed the predicate fpov.PROFILE_OPTION_VALUE = 2 to read fpov.PROFILE_OPTION_VALUE = ‘2’ (note the quote marks) the query stopped crashing. So what was going wrong ?

The answer is simple – 10g enables CPU costing (also known as system statistics) automatically. In 9i you have to enable the feature explicitly.

One of the benefits of system statistics is that it allows the optimizer to alter the predicate order to minimise CPU usage. Unfortunately this means that some SQL that used to work by accident in earlier versions of Oracle will stop working when you upgrade to 10g.

In this example the column  fpov.PROFILE_OPTION_VALUE  happens to be a character column which holds some values that look like numbers; but the predicate fpov.LEVEL_ID = 10003 eliminates all rows where the value doesn’t look like a number, so when Oracle does the implicit to_number() conversion on the remaining rows to compare them with the value 2, nothing breaks.

Because the cost based optimizer used to examine filter predicates in the order they appear in the where clause everything used to work nicely – until you enabled CPU costing and the optimizer decided to re-arrange the predicate order – in this case reversing the order of those two predicates. Suddenly the to_number() conversion takes place on a character value that hasn’t yet been filtered out … bad luck. In this case, you would see the to_number() conversion made visible, and the order in which the predicates were actually applied if you generated and reported the full execution plan using dbms_xplan.display().

A point that I alway stress in my tutorial on explain plan, and which I also made in a recent blog entry: ” make sure you check the predicates section of your execution plan”. It’s amazing the number of problems it explains.

There are two possible workarounds to this issue – neither desirable.

The first is to add the /*+ ordered_predicates */ hint to the query. Contrary to a note that used to appear in the manuals – and in some of the publications that simply copied the manuals – the hint goes in the normal place, not in the where clause. Unfortunately this hint has been deprecated in 10g, so you shouldn’t use it.

The second option is to disable CPU costing, which you can do by setting the hidden parameter _optimizer_cost_model to the value io. Of course, since it’s a hidden parameter, you shouldn’t do this either.

A compromise (until you fix the data model (!)) might be to use the new /*+ opt_param */ hint to disable CPU costing for the duration of query. The syntax is:

        /*+ opt_param(parameter, value) */
        /*+ opt_param('_optimizer_cost_model','io') */

Even then, you should exercise caution, as this hint is not yet documented. [Update: The hint was finally documented in the 11g manuals, with a limited list of “legal” parameters that could be set]

[Update August 2009: another option – documented in Metalink Bug number 8554306, dated May 2009,  is to set event 10158 at level 1. The example in the note uses the alter session command, but you might want to try setting the value in your parameter file. The oraus.msg file lists this this event  with the comment: 10158, 00000, “CBO preserve predicate order in post-filters”. I would only use this as a temporary workaround while correcting the guilty code.]