techblog 

Our current live database is Oracle 10.1.0.3 on Solaris. We are testing the impact of migrating to a target platform of 10.2.0.2 on Linux and have discovered a performance problem with one of our third-party applications. Some functions within the application are taking 10 times longer on 10.2 than on 10.1.

The application connects to the database using the Oracle 10.1 ODBC driver. I traced sessions on 10.1 and 10.2 and identified where the timing differences were occurring.

The Oracle ODBC driver generates many SQL statements which query the Oracle dictionary (all_synonyms and all_tab_columns). Although there are many statements which do this, they take two basic forms:

SELECT /*+ RULE */ COUNT(*)
FROM
ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND SYNONYM_NAME='TABLKEY';

SELECT /*+ RULE */ '',owner,table_name,column_name,0,data_type,
data_precision, decode(data_type, 'DATE',16,'FLOAT',8,
'LONG RAW',2147483647,'LONG',2147483647,'CLOB',2147483647,
'NCLOB',2147483647,'BLOB',2147483647,'BFILE',2147483647,
'CHAR',char_length,'NCHAR',char_length,'VARCHAR2',char_length,
'NVARCHAR2',char_length,'NUMBER',NVL(data_precision+2,40),
data_length), data_scale, 0, decode(nullable, 'Y', 1, 'N', 0),
'' , '', 0,0,
decode(data_type,'CHAR',data_length,'VARCHAR2',data_length,
'NVARCHAR2',data_length,'NCHAR',data_length, 0),column_id,
decode(nullable, 'Y', 'YES','N', 'NO')
FROM
all_tab_columns WHERE TABLE_NAME='TABLKEY'
UNION
SELECT /*+ RULE */ '',b.owner,b.synonym_name,a.column_name,
0, a.data_type, a.data_precision,a.data_length,
a.data_scale, 0, decode(a.nullable, 'Y', 1, 'N', 0),
'' , '', 0, 0, 0, a.column_id,
decode(a.nullable, 'Y', 'YES', 'N', 'NO')
FROM all_tab_columns a, all_synonyms b
WHERE
((a.table_name = b.table_name and a.owner = b.table_owner) )
AND b.synonym_name='TABLKEY'  ORDER BY 2,3,17;

These statements are much slower on 10.2 than on 10.1.

The rule based optimiser, which the Oracle ODBC driver is using, was deprecated in Oracle 10.1. For some reason the rule based optimiser performs the statements OK on 10.1 but terribly on 10.2.

This is documented on Oracle MetaLink (Note 373129.1). The Oracle bug is 4150034 and is fixed in patch 5211342. Unfortunately this fix involves updating and patching the Oracle ODBC driver, which would require an update to every PC in the organisation.

A better solution we discovered was to create a logon trigger to set the _optimizer_ignore_hints parameter to true for all sessions connecting to the database via the ODBC application:

create or replace trigger logon_trigger
after logon on application_user.schema
begin
execute immediate
'alter session set "_optimizer_ignore_hints"=TRUE';
end;
/