一个查询计划错误的原因分析Oracle认证考试

文章作者 100test 发表时间 2009:10:22 22:24:18
来源 100Test.Com百考试题网

"mkhgigh"> 　　周一收到生成支持人员的报告，系统上一个作业启动后很长时间没有完成，其执行时间远远大于上周的正常执行时间。接到报告后，首先检查了系统，不存在锁队列的问题。然后查询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>. 0select * 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>. 0select 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