Oracle认证：分析查询计划错误的原因

会计考友

周一收到生成撑收支员的陈述，系统上一个功课启动后很长时刻没有完成，其执行时刻远弘远于上周的正常执行时刻。接到陈述后，首先搜检了系统，不存在锁队列的问题。然后发芽V$SESSION_LONGOPS，当即发现下面的语灸正在进行长操作：
　　SELECT *
4 D$ w1 z% }) }9 s6 C1 ?* G- P, ~　　FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT
　　WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1
1 R% A0 }1 G0 [' W" z3 O4 n0 D- p　　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。经由过程直接对其解析发芽打算，发现它能正确射中索引：
" f7 z$ d# s# A  k  d　　SQL> EXPLAIN PLAN FOR
% g0 f  q/ M2 h) }; a2 i1 P4 \　　2 SELECT *
　　3 FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT
& A0 A# r% V& O　　4 WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1
- A; H% R8 p8 D　　5 AND BKGSHMTRESULT.COMP_ID = :B2;
- U& j" q& H' \　　Explained.
　　SQL> select * from table(dbms_xplan.display());
　　PLAN_TABLE_OUTPUT
　　-----------------------------------------------------------------------------------------------------------------------------------
5 `4 X& @5 n. e1 I0 J/ C* y　　Plan hash value: 772272200
　　-----------------------------------------------------------------------------------------------------------------------------------
( W; `  v4 _& |5 v2 {) K# p& T　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
　　-----------------------------------------------------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 1 | 880 | 5 (0)| 00:00:01 | | |
# [6 |, ~5 E# V0 W+ {　　| 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):
! g* s0 _$ p+ Z* O  [  C% u4 s　　---------------------------------------------------
; G1 E. W1 F7 E4 p$ Y　　2 - access("BKGSHMTRESULT"."BKG_CFM_ID"=TO_NUMBER(:B1) AND "BKGSHMTRESULT"."COMP_ID"=:B2)
% p$ l' Z! D& _, n4 t6 H- ~$ N　　可是，经由过程SQL_ID发芽，现实的发芽打算却是全表扫描：
# Y/ F5 U( P, \! m& I　　SQL> select lpad(' ', 2 * (level - 1)) || operation || ' ' ||
6 W- {- t% T, z! i' d2 x/ e　　2 decode(id, 0, 'Cost = ' || position) "OPERATION",
　　3 options,
% U9 _" J% W" e　　4 object_name
+ c0 v$ l. s: l: \. R- L) Z　　5 from v$sql_plan
: x/ M: [3 m& N) {　　6 start with (sql_id = 'f0mwuqfxxmtmf' and hash_value = http://www.qnr.cn/pc/ora/study/200911/3151619694 and id = 0)
7 m2 K1 z7 U7 z5 s$ C4 K　　7 connect by prior id = parent_id
　　8 and prior sql_id = sql_id
$ v' V9 W0 ?% I0 |$ K" J/ ^　　9 and prior hash_value = http://www.qnr.cn/pc/ora/study/200911/hash_value
1 C5 a0 J$ Z7 G　　10 order by id, position;
　　OPERATION OPTIONS OBJECT_NAME
  o6 h+ B' }. I% |) o/ W3 v　　---------------------------- ------------------------------------- ------------------------
　　SELECT STATEMENT Cost = 265
. h' s# u- j/ b　　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'));
3 m# l# j9 p4 n; H' L1 |. @　　PLAN_TABLE_OUTPUT
% g! s/ |9 I2 O; L% K3 ^0 Z+ p　　--------------------------------------------------
　　SQL_ID f0mwuqfxxmtmf, child number 0
+ k7 ?1 |0 h! h9 J& [7 M　　-------------------------------------
% l6 t/ j, R- E1 G! l/ G　　SELECT * FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID
+ o& }" j# e& _. {" z　　AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID