Slow performance with TDE and CHAR data type on 10.2.0.5

In version 10.2.0.5 of the database there is a bug when using Transparent Data Encryption (TDE) on a column with the CHAR datatype. In the following test a table is created where the primary key is defined as CHAR(11) and then encrypted with NO SALT. A simple lookup on this PK works as expected by using the respective index on 10.2.0.4. But on 10.2.0.5 a trace on event 10053 shows that an INTERNAL_FUNCTION is wrapped around the PK-column and therefore impedes use of the index.

The following test was performed on an OVM guest created from a template downloaded from edelivery.oracle.com. It contains Oracle Server EE 10.2.0.4 running on Oracle Enterprise Linux 5, 64-bit. Configuration of TDE and wallet is not shown.

create table foo (
   id char(11) encrypt no salt primary key ,
   msg varchar2(1000)
  );

insert into  foo ( select * from (
  with generator as (
      select      rownum      rn
      from        dual
      connect by
                  rownum <= 100
  )
  select
      dbms_random.string('x',11) id,
      dbms_random.string('x',1000) msg
  from
      generator   g1,
      generator   g2
  where
    rownum <= 10000
  )
);
commit;
exec dbms_stats.gather_table_stats(user,'FOO', cascade=>true,method_opt=>'for all indexed columns size 1',estimate_percent=>100)
variable lid char(11)
exec :lid:='abc123def45';
alter session set events='10053 trace name context forever, level 1';
select msg from foo where id=:lid;

The query returns immediately since the index is used and the trace file shows the good plan (a simple explain plan will show the same):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | FOO        |     1 |   514 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN          | SYS_C007236|     1 |       |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("ID"=:LID)

Now, the database is patched to version 10.2.0.5 (patch 8202632). There are no invalid objects in the database after the upgrade. Then repeating the last query from above together with the trace on event 10053, the query takes a long time and the trace file shows this plan:

-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |   388 |           |
| 1   |  TABLE ACCESS FULL | FOO     |   100 |   50K |   388 |  00:00:05 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(INTERNAL_FUNCTION("ID")=:LID)

The INTERNAL_FUNCTION is used among other situations when oracle has to perform an implicit data conversion. This may happen when the data type of a bind variable is different from the data type used in the table.

This kind of mismatch may happen during upgrades of JDBC drivers where the mapping may change; Java had support for TimeStamp before it was introduced in the Oracle database. Later when Oracle supported TimeStamp this mapping changed, leading to this kind of errors. There are other reasons for such mismatches; a trace on event 10053 will reveal the data type of the bind variable:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=96 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=32 off=0
  kxsbbbfp=2b19acc082a0  bln=32  avl=11  flg=05
  value="abc123def45"

The part "oacdty=96" shows the type is CHAR or NCHAR. This did not change between the tests, so there has to be another reason why the INTERNAL_FUNCTION is used. Also in this case the only change is the upgrade, even the parameter compatible remained the same (10.2.0.3). The tests where performed from sqlplus on the database server.

This is bug 10296606 on MOS.

Update: It was discovered that if you use a variable of VARCHAR type the INTERNAL_FUNCTION is not applied, meaning that columns of CHAR type are converted to VARCHAR after encryption, but that USER_TAB_COLUMNS continues to report CHAR for the column.