周一收到生成支持人员的报告，系统上一个作业启动后很长时间没有完成，其执行时间远远大于上周的正常执行时间。接到报告后，首先检查了系统，不存在锁队列的问题。然后查询V$SESSION_LONGOPS，立即发现下面的语句正在进行长操作：
SELECT *
FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT
WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1
AND BKGSHMTRESULT.COMP_ID = :B2
从V$SESSION_LONGOPS看，它正在对表CR_BKG_INTMD_SHMT_PARTITION做FULL TABLE SCAN。而表CR_BKG_INTMD_SHMT_PARTITION是一张非常大的分区表，是我们之前做的优化建立的分区表(该案例我有在《11g新特性 ——更加灵活的分区策略》中提到，Partition Key是COMP_ID，分区策略是每个VIP用户一个分区，所有非VIP用户在DEFAULT分区)。
这条语句的查询条件很简单，且在(BKG_CFM_ID，COMP_ID)上有建一个Global Index。通过直接对其解析查询计划，发现它能正确命中索引：
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT
4 WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1
5 AND BKGSHMTRESULT.COMP_ID = :B2;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 772272200
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 880 | 5 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| CR_BKG_INTMD_SHMT_PARTITION | 1 | 880 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | CR_BKG_INTMD_PARTITION_IDX03 | 1 | | 4 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BKGSHMTRESULT"."BKG_CFM_ID"=TO_NUMBER(:B1) AND "BKGSHMTRESULT"."COMP_ID"=:B2)
但是，通过SQL_ID查询，实际的查询计划却是全表扫描：
SQL> select lpad(' ', 2 * (level - 1)) || operation || ' ' ||
2 decode(id, 0, 'Cost = ' || position) "OPERATION",
3 options,
4 object_name
5 from v$sql_plan
6 start with (sql_id = 'f0mwuqfxxmtmf' and hash_value = 3151619694 and id = 0)
7 connect by prior id = parent_id
8 and prior sql_id = sql_id
9 and prior hash_value = hash_value
10 order by id, position;
OPERATION OPTIONS OBJECT_NAME
---------------------------- ------------------------------------- ------------------------
SELECT STATEMENT Cost = 265
PARTITION LIST SINGLE
TABLE ACCESS FULL CR_BKG_INTMD_SHMT_PARTITION
这一现象通常是由于绑定变量窥视(Bind Variable Peeking)造成的：Peeking的变量值比较特殊，造成计算出的全表扫描代价低于索引扫描代价。为了确认问题，我们找到解析查询计划所“窥视”到的数据：
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f0mwuqfxxmtmf', 0, 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID f0mwuqfxxmtmf, child number 0
-------------------------------------
SELECT * FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID
AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID
Plan hash value: 3035855418
责任编辑：小草
您现在的位置： 