查询计划错误的原因分析
undefined undefined SELECT *
FROM CR_BKG_IN易做图_SHMT_PARTITION BKGSHMTRESULT
WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1
AND BKGSHMTRESULT.COMP_ID = :B2
从V$SESSION_LONGOPS看，它正在对表CR_BKG_IN易做图_SHMT_PARTITION做FULL TABLE SCAN。而表CR_BKG_IN易做图_SHMT_PARTITION是一张非常大的分区表，是我们之前做的优化建立的分区表(该案例我有在《11g新特性 ——更加灵活的分区策略》中提到，Partition Key是COMP_ID，分区策略是每个VIP用户一个分区，所有非VIP用户在DEFAULT分区)。
这条语句的查询条件很简单，且在(BKG_CFM_ID，COMP_ID)上有建一个Global Index。通过直接对其解析查询计划，发现它能正确命中索引：
undefined undefined SQL > EXPLAIN PLAN FOR
2 SELECT *
3 FROM CR_BKG_IN易做图_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_IN易做图_SHMT_PARTITION | 1 | 880 | 5 ( 0 ) | 00 : 00 : 01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | CR_BKG_IN易做图_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)undefined undefined 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_IN易做图_SHMT_PARTITION
这一现象通常是由于绑定变量窥视(Bind Variable Peeking)造成的：Peeking的变量值比较特殊，造成计算出的全表扫描代价低于索引扫描代价。为了确认问题，我们找到解析查询计划所“窥视”到的数据：
undefined undefined SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR( ' f0mwuqfxxmtmf ' , 0 , ' ADVANCED ' ));
PLAN_TABLE_OUTPUT
-- ------------------------------------------------
SQL_ID f0mwuqfxxmtmf, child number 0
-- -----------------------------------
SELECT * FROM CR_BKG_IN易做图_SHMT_PARTITION BKGSHMTRESULT WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID
AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID
Plan hash value: 3035855418
-- -------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |
-- -------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 265 ( 100 ) | | | |
| 1 | PARTITION LIST SINGLE | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | CR_BKG_IN易做图_SHMT_PARTITION | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |
-- -------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-- -----------------------------------------------------------
1 - SEL$ 1
2 - SEL$ 1 / BKGSHMTRESULT @SEL $ 1
Outline Data
-- -----------
/* +
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
OPT_PARAM('_complex_view_merging' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BKGSHMTRESULT"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
-- ------------------------------------
1 - :V_BKG_CFM_ID ( NUMBER ): 592533
2 - :V_COMP_ID ( VARCHAR2 ( 30 ), CSID = 873 ): ' BANDHK270600815 '可以看到，两个变量的值分别为V_BKG_CFM_ID : 592533、V_COMP_ID : 'BANDHK270600815'。使用这2个值，再重新解析查询计划，果然是全表扫描：
undefined undefined SQL > explain plan for
2 SELECT *
3 FROM CR_BKG_IN易做图_SHMT_PARTITION BKGSHMTRESULT
4 WHERE BKGSHMTRESULT.BKG_CFM_ID = 592533
5 AND BKGSHMTRESULT.COMP_ID = ' BANDHK270600815 ' ;
Explained.
SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-- -------------------------------------------------------------------------------------------------------------------
Plan hash value: 554208192
-- -------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |
-- -------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | | |
| 1 | PARTITION LIST SINGLE | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | CR_BKG_IN易做图_SHMT_PARTITION | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | 14 | 14 |
-- -------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-- -------------------------------------------------
2 - filter("BKGSHMTRESULT"."BKG_CFM_ID" = 592536 )
注意到在查询条件中存在Partition Key：COMP_ID，因此查询计划中存Partition List Single，仅对所在分区(14)查
询。，这里的Full Table Scan实际上是对一个分区的Full Scan，而'BANDHK270600815'正是一个VIP用户。我们再看该分区上的statistics数据：
undefined undefined SQL > select num_rows, blocks from dba_tab_statistics where table_name = ' CR_BKG_IN易做图_SHMT_PARTITION ' and owner = ' CS2_PARTY_OWNER ' and partition_name = ' P_COMP_BANDHK270600815 ' ;
NUM_ROWS BLOCKS
-- -------- ----------
18 8
分区上的数据非常少，因此这个Full Scan的Cost不高，解析出的查询计划为Full Table Scan，当数据落入DEFAULT分区(最大分区)，其查询计划仍为Full Scan，从而导致了性能问题!继续查询，发现还存在少数几个分区的数据也非常少。这一结果和当初我们做POC时的有出入：在POC中，所有VIP用户的数据都在10K以上，但生产环境上却出现数据量极少的VIP用户。这一问题恐怕需要从开发那边BA/SA找答案了。作为DBA，我们的当前的任务就是如何避免再次发生由此引发的性能问题。
由于我们的系统是工作日(周一到周五)运行，每周系统都会重启。因此，在周一很多语句都会被硬解析。这样的话，很难避免在硬解析时，窥视的数据再次落入这些小分区内。要避免再次造成性能问题，可以考虑以下方法：
相关语句上加HINT，强制使用索引。但是这样的修改涉及面太大，且如果将来Schema发生变化，代码维护更新困难;
用Stored Outline为语句固定查询计划。其缺点和第一点差不多;
禁用Bind Variable Peeking。因为我们的系统会每周重启，如果在db level禁用，风险较大，所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的，所以修改的代码量非常少：在入口函数上加上以下语句。
undefined undefined execute immediate ' alter session set "_OPTIM_PEEK_USER_BINDS" = false ' ;
后记
补充：数据库,Mssql
