不均衡分区和绑定变量窥视导致的查询计划错误

最新推荐文章于 2026-02-26 14:24:58 发布
转载最新推荐文章于 2026-02-26 14:24:58 发布 · 1.1k 阅读
收录于
Oracle 日记
文章详细介绍了如何解决由于分区数据不平衡和绑定变量窥视导致的查询性能问题，包括分析问题原因、确认解决方案以及采取措施避免类似问题再次发生。
不均衡分区和绑定变量窥视导致的查询计划错误
周一收到生成支持人员的报告，系统上一个作业启动后很长时间没有完成，其执行时间远远大于上周的正常执行时间。接到报告后，首先检查了系统，不存在锁队列的问题。然后查询V$SESSION_LONGOPS，立即发现下面的语句正在进行长操作：
SQL代码
1.	SELECT *   
2.	  FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
3.	 WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1   
4.	   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代码
1.	SQL> EXPLAIN PLAN FOR  
2.	  2  SELECT *   
3.	  3    FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
4.	  4   WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1   
5.	  5     AND BKGSHMTRESULT.COMP_ID = :B2;   
6.	  
7.	Explained.   
8.	  
9.	SQL> select * from table(dbms_xplan.display());   
10.	  
11.	PLAN_TABLE_OUTPUT   
12.	-----------------------------------------------------------------------------------------------------------------------------------   
13.	Plan hash value: 772272200   
14.	  
15.	-----------------------------------------------------------------------------------------------------------------------------------   
16.	| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
17.	-----------------------------------------------------------------------------------------------------------------------------------   
18.	|   0 | SELECT STATEMENT                   |                              |     1 |   880 |     5   (0)| 00:00:01 |       |       |   
19.	|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| CR_BKG_INTMD_SHMT_PARTITION  |     1 |   880 |     5   (0)| 00:00:01 | ROWID | ROWID |   
20.	|*  2 |   INDEX RANGE SCAN                 | CR_BKG_INTMD_PARTITION_IDX03 |     1 |       |     4   (0)| 00:00:01 |       |       |   
21.	-----------------------------------------------------------------------------------------------------------------------------------   
22.	  
23.	Predicate Information (identified by operation id):   
24.	---------------------------------------------------   
25.	  
26.	   2 - access("BKGSHMTRESULT"."BKG_CFM_ID"=TO_NUMBER(:B1) AND "BKGSHMTRESULT"."COMP_ID"=:B2)   
    但是，通过SQL_ID查询，实际的查询计划却是全表扫描：
SQL代码
1.	SQL> select lpad(' ', 2 * (level - 1)) || operation || ' ' ||   
2.	  2         decode(id, 0, 'Cost = ' || position) "OPERATION",   
3.	  3         options,   
4.	  4         object_name   
5.	  5    from v$sql_plan   
6.	  6   start with (sql_id = 'f0mwuqfxxmtmf' and hash_value = 3151619694 and id = 0)   
7.	  7  connect by prior id = parent_id   
8.	  8         and prior sql_id = sql_id   
9.	  9         and prior hash_value = hash_value   
10.	 10   order by id, position;   
11.	  
12.	OPERATION                    OPTIONS                               OBJECT_NAME   
13.	---------------------------- ------------------------------------- ------------------------   
14.	SELECT STATEMENT Cost = 265   
15.	  PARTITION LIST             SINGLE   
16.	    TABLE ACCESS             FULL                                  CR_BKG_INTMD_SHMT_PARTITION   
    这一现象通常是由于绑定变量窥视（Bind Variable Peeking）造成的：Peeking的变量值比较特殊，造成计算出的全表扫描代价低于索引扫描代价。为了确认问题，我们找到解析查询计划所“窥视”到的数据：
SQL代码
1.	SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f0mwuqfxxmtmf', 0, 'ADVANCED'));   
2.	  
3.	PLAN_TABLE_OUTPUT   
4.	--------------------------------------------------   
5.	SQL_ID  f0mwuqfxxmtmf, child number 0   
6.	-------------------------------------   
7.	SELECT *   FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT  WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID   
8.	  AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID   
9.	  
10.	Plan hash value: 3035855418   
11.	  
12.	---------------------------------------------------------------------------------------------------------------------   
13.	| Id  | Operation             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
14.	---------------------------------------------------------------------------------------------------------------------   
15.	|   0 | SELECT STATEMENT      |                             |       |       |   265 (100)|          |       |       |   
16.	|   1 |  PARTITION LIST SINGLE|                             |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
17.	|*  2 |   TABLE ACCESS FULL   | CR_BKG_INTMD_SHMT_PARTITION |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
18.	---------------------------------------------------------------------------------------------------------------------   
19.	  
20.	Query Block Name / Object Alias (identified by operation id):   
21.	-------------------------------------------------------------   
22.	  
23.	   1 - SEL$1   
24.	   2 - SEL$1 / BKGSHMTRESULT@SEL$1   
25.	  
26.	Outline Data   
27.	-------------   
28.	  
29.	  /*+   
30.	      BEGIN_OUTLINE_DATA   
31.	      IGNORE_OPTIM_EMBEDDED_HINTS   
32.	      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')   
33.	      OPT_PARAM('_complex_view_merging' 'false')   
34.	      ALL_ROWS   
35.	      OUTLINE_LEAF(@"SEL$1")   
36.	      FULL(@"SEL$1" "BKGSHMTRESULT"@"SEL$1")   
37.	      END_OUTLINE_DATA   
38.	  */   
39.	  
40.	Peeked Binds (identified by position):   
41.	--------------------------------------   
42.	  
43.	   1 - :V_BKG_CFM_ID (NUMBER): 592533   
44.	   2 - :V_COMP_ID (VARCHAR2(30), CSID=873): 'BANDHK270600815'  
    可以看到，两个变量的值分别为V_BKG_CFM_ID : 592533、V_COMP_ID : 'BANDHK270600815'。使用这2个值，再重新解析查询计划，果然是全表扫描：
SQL代码
1.	SQL> explain plan for  
2.	  2  SELECT *   
3.	  3    FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT   
4.	  4   WHERE BKGSHMTRESULT.BKG_CFM_ID = 592533   
5.	  5     AND BKGSHMTRESULT.COMP_ID = 'BANDHK270600815';   
6.	  
7.	Explained.   
8.	  
9.	SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());   
10.	  
11.	PLAN_TABLE_OUTPUT   
12.	---------------------------------------------------------------------------------------------------------------------   
13.	Plan hash value: 554208192   
14.	  
15.	---------------------------------------------------------------------------------------------------------------------   
16.	| Id  | Operation             | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |   
17.	---------------------------------------------------------------------------------------------------------------------   
18.	|   0 | SELECT STATEMENT      |                             |     1 |   756 |   265   (1)| 00:00:04 |       |       |   
19.	|   1 |  PARTITION LIST SINGLE|                             |     1 |   756 |   265   (1)| 00:00:04 |   KEY |   KEY |   
20.	|*  2 |   TABLE ACCESS FULL   | CR_BKG_INTMD_SHMT_PARTITION |     1 |   756 |   265   (1)| 00:00:04 |    14 |    14 |   
21.	---------------------------------------------------------------------------------------------------------------------   
22.	  
23.	Predicate Information (identified by operation id):   
24.	---------------------------------------------------   
25.	  
26.	   2 - filter("BKGSHMTRESULT"."BKG_CFM_ID"=592536)   
    注意到在查询条件中存在Partition Key：COMP_ID，因此查询计划中存Partition List Single，仅对所在分区（14）查询。，这里的Full Table Scan实际上是对一个分区的Full Scan，而'BANDHK270600815'正是一个VIP用户。我们再看该分区上的statistics数据：
SQL代码
1.	SQL> select num_rows, blocks from dba_tab_statistics where table_name = 'CR_BKG_INTMD_SHMT_PARTITION' and owner = 'CS2_PARTY_OWNER' and partition_name = 'P_COMP_BANDHK270600815';   
2.	  
3.	  NUM_ROWS     BLOCKS   
4.	---------- ----------   
5.	        18          8   
    分区上的数据非常少，因此这个Full Scan的Cost不高，解析出的查询计划为Full Table Scan，当数据落入DEFAULT分区（最大分区），其查询计划仍为Full Scan，从而导致了性能问题！继续查询，发现还存在少数几个分区的数据也非常少。这一结果和当初我们做POC时的有出入：在POC中，所有VIP用户的数据都在10K以上，但生产环境上却出现数据量极少的VIP用户。这一问题恐怕需要从开发那边BA/SA找答案了。作为DBA，我们的当前的任务就是如何避免再次发生由此引发的性能问题。
    由于我们的系统是工作日（周一到周五）运行，每周系统都会重启。因此，在周一很多语句都会被硬解析。这样的话，很难避免在硬解析时，窥视的数据再次落入这些小分区内。要避免再次造成性能问题，可以考虑以下方法：
1.	相关语句上加HINT，强制使用索引。但是这样的修改涉及面太大，且如果将来Schema发生变化，代码维护更新困难；
2.	用Stored Outline为语句固定查询计划。其缺点和第一点差不多；
3.	禁用Bind Variable Peeking。因为我们的系统会每周重启，如果在db level禁用，风险较大，所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的，所以修改的代码量非常少：在入口函数上加上以下语句。 
SQL代码
1.	execute immediate 'alter session set "_OPTIM_PEEK_USER_BINDS" = false';  
后记
    此案例涉及两个问题值得注意：
1.	分区的平衡问题。如果分区之间的数据量存在很大差异，在绑定变量窥视被启用（默认）的情况下，硬解析出来的查询计划在不同分区上的性能差异可能非常大；
2.	绑定变量窥视其目的主要是帮助CBO下更加精确的计算出查询计划代价。但是，因为这依赖于被“窥视”的变量值，因而也为查询计划带来了不稳定性。数据的不平衡、分区的不平衡都可能会因为这种不稳定性而导致性能风险。