Different Execution Plan for Same SQL Statement on Oracle 11g

Oracle Database - 11.2.0.4

SYMPTOMS

用户反馈同一条 SQL 语句，在应用程序和 plsql 执行产生的执行计划不一样，应用程序执行不走索引，plsql 执行走索引。
尝试使用 hint index，表现一样。

SQL_ID 9p4v8fwfyjxqp
--------------------
select distinct t.CODE_NAME,t.CODE_ENROUTE,t.CODE_STARTAD,t.CODE_ENDTAD,
t.VAL_STATUS,t.TXT_DESIG_CFP,t.CODE_GROUP,t.COMPANY_ENROUTE_ID from
COMPANY_ENROUTE t,MAP_OCEAN t1
where SDO_RELATE(t.geometry,t1.geometry, 'mask=anyinteract')='TRUE'

Plan hash value: 476608943

----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |       |       |     8 (100)|          |
|   1 |  HASH UNIQUE                      |                      |     1 |  4012 |     8  (13)| 00:00:01 |
|   2 |   NESTED LOOPS                    |                      |     1 |  4012 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL              | MAP_OCEAN            |     1 |  3819 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID    | COMPANY_ENROUTE      |     1 |   193 |     7   (0)| 00:00:01 |
|   5 |     DOMAIN INDEX (SEL: 0.010000 %)| COMPANY_ENROUTE_SIDX |       |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

SQL_ID 9p4v8fwfyjxqp
--------------------
select distinct t.CODE_NAME,t.CODE_ENROUTE,t.CODE_STARTAD,t.CODE_ENDTAD,
t.VAL_STATUS,t.TXT_DESIG_CFP,t.CODE_GROUP,t.COMPANY_ENROUTE_ID from
COMPANY_ENROUTE t,MAP_OCEAN t1
where SDO_RELATE(t.geometry,t1.geometry, 'mask=anyinteract')='TRUE'

Plan hash value: 4193672310

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |       |       |       |  1791 (100)|          |
|   1 |  HASH UNIQUE        |                 |   154 |   603K|  1080K|  1791   (1)| 00:00:22 |
|   2 |   NESTED LOOPS      |                 |   268 |  1050K|       |  1564   (1)| 00:00:19 |
|   3 |    TABLE ACCESS FULL| COMPANY_ENROUTE |  4993 |   941K|       |   207   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| MAP_OCEAN       |     1 |  3819 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

CAUSE

在应用程序的执行计划中，可以看到使用了基数反馈（cardinality feedback），可能由以下情况造成：

  1. 相关表的统计信息过旧（没有直方图的信息），当在查询 SDO_RELATE(t.geometry,t1.geometry, 'mask=anyinteract')='TRUE' 的 WHERE 子句中对列应用函数时，优化器无法知道该函数如何影响列的选择性。
  2. 未开启动态采样。

SOLUTION

  1. 对相关表进行统计信息收集。

    Managing Optimizer Statistics

  2. 关闭基数反馈特性。

    Cardinality Feedback