一个隐式转换引发的执行计划错误

查看awr报告过程发现一个隐式转换导致的索引失效问题，做做记录

awr中语句实际消耗大40多w逻辑读

代入变量后测试很快

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '125924325287';

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4287492176

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    30 |     4   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                       |             |     1 |    30 |     4   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| user_sess   |     1 |    21 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX UNIQUE SCAN                | SYS_C008504 |     1 |       |     2   (0)| 00:00:01 |       |       |
|   4 |   TABLE ACCESS BY INDEX ROWID       | PR_DO_T     |  2983 | 26847 |     1   (0)| 00:00:01 |       |       |
|*  5 |    INDEX UNIQUE SCAN                | SYS_C008459 |     1 |       |     0   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"."ORDER_NO"='125924325287')
   5 - access("A"."PROD_ID"="B"."PROD_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          2  physical reads
          0  redo size
        489  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

按语句sqlid查看历史执行计划

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '871314                 



SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor('88f4paj5t26xb'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  88f4paj5t26xb, child number 0
-------------------------------------
SELECT a.order_prod_amount,b.prod_cost_price                        FROM user_sess a, PR_DO_T b
                  WHERE a.prod_id  = b.prod_id                         AND a.order_no = :"SYS_B_0"

Plan hash value: 4105848458

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |   106K(100)|          |       |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       |
|   2 |   PARTITION RANGE ALL        |             |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 |
|*  3 |    TABLE ACCESS FULL         | user_sess  |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 |
|   4 |   TABLE ACCESS BY INDEX ROWID| PR_DO_T     |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C008459 |     1 |       |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------------

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

   3 - filter(TO_NUMBER("A"."ORDER_NO")=:SYS_B_0)

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
   5 - access("A"."PROD_ID"="B"."PROD_ID")


24 rows selected.

 user_sess明显走错了执行计划，使用了全表扫描

突然想到user_sess表字段order_no为varchar2猜测为隐式转换
去掉单引号测试

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = 125924325287;

Elapsed: 00:00:18.46

Execution Plan
----------------------------------------------------------
Plan hash value: 4105848458

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       |
|   1 |  NESTED LOOPS                |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       |
|   2 |   PARTITION RANGE ALL        |             |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 |
|*  3 |    TABLE ACCESS FULL         | user_sess  |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 |
|   4 |   TABLE ACCESS BY INDEX ROWID| PR_DO_T     |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C008459 |     1 |       |     0   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------

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

   3 - filter(TO_NUMBER("A"."ORDER_NO")=125924325287)
   5 - access("A"."PROD_ID"="B"."PROD_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     484974  consistent gets
     477021  physical reads
          0  redo size
        489  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

找开发人员加回蛋引号避免隐式转换后恢复

注：隐式类型转换number转换成varchar2时，索引会失效。varchar2转换为number时，索引并不失效