Oracle does not use index

[kgb]

Hi All,

I have a problem with couple of SQL statements which do not use index and have different execution plans in sqlplus and during execution. The one bellow is the most CPU consuming and effect on overal application performance. Execution plan in the sqlplus is correct and as expected, however in the EM console, I see full table scan.
Oracle 11.1.0.7, Windows server 64 bit.

Schema statistics collected on all objects using:

exec dbms_stats.gather_schema_stats(user,cascade=>true);

Problematic SQL:

select refcon from HISTOMVTS where infosbackoffice = :refext

There is an index on text column "infosbackoffice"

execution plan in the sqplus:

SQL> select refcon from HISTOMVTS where infosbackoffice = '123';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1551545895

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |     1 |    22 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HISTOMVTS                   |     1 |    22 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_HISTOMVTS_INFOSBACKOFFICE |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("INFOSBACKOFFICE"='123')

The same SQL with FULL table scan in the EM:



I tried to apply Stored Outlines from the other server and using execution plan produced in the sqlplus, but result is the same.



AWR report is attached here:
http://www.dbasupport.com/forums/att...1&d=1292928685