ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 第85期 为什么直方图和绑定变量一起使用时，有时会引发性能问题

第85期 为什么直方图和绑定变量一起使用时，有时会引发性能问题

原创 Oracle 作者：pxbibm 时间：2026-10-20 10:58:54 0 删除 编辑

前段时间，给大家做Oracle 培训时，有同学提出了几个很有价值的问题：为什么直方图和绑定变量一起使用时，

有时会引发性能问题。

同学们希望好好学习一下借助直方图来解决绑定变量窥视（bind peeking）带来的问题。

我对直方图和绑定变量做了一下具体问题的总结，总结如下：

1. 当 SQL 使用绑定变量时，直方图会对执行计划产生怎样的影响？

2. 为什么直方图和绑定变量无法很好地协同工作？

3. 使用绑定变量时不要建直方图，背后的原因是什么？

如果大家对以上有疑问，我们分几次对这3个问题进行详细讲述。

当 WHERE 条件里和绑定变量做对比的列上存在直方图时，数据库内部会发生什么。

直方图并非完全不能和绑定变量一起工作，但结果通常不是我们想要的。

使用绑定变量的初衷， 是为了减少执行计划的数量，实现游标共享和复用。

而直方图的作用， 则是帮助优化器针对传入的谓词条件选出理论最优的执行计划；

在绑定变量场景下，这个谓词就是**被窥视到的绑定变量值**。

所以，如果某一列建有直方图：

假如这条 SQL 第一次硬解析时，传入的绑定变量是该列中**高频主流值**，优化器就会生成一份适配这个主流值的、

它认为是 “最优” 的执行计划。

反过来，如果第一次硬解析传入的是该列里**极少出现的小众值**，优化器会为同一条 SQL 生成一套完全不一样的执行计划，

这套计划是专门针对小众值优化的（比如会选择索引范围扫描，而不走全表扫描）。

在后续执行这条 SQL 时，即便绑定变量传入的值发生变化，**执行计划却不会随之改变**。

举个场景：表中存在一个占绝大多数的热门值，同时还有大量冷门值。

如果这条 SQL 第一次硬解析时传入的是那个热门值，那么后续所有执行都会沿用硬解析生成的全表扫描计划 ——

 哪怕后续查询只需要返回寥寥几行数据，也依然会走全表扫描。

我搭建了测试用例，用于演示上述观点：演示环境使用的是oracle 19c

[oracle@zbyl ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 20 10:10:24 2026
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> 
--创建测试表
SQL> CREATE TABLE TEST (
  C1 NUMBER,
  C2 NUMBER,
  C3 VARCHAR2(300));  2    3    4  
Table created.
--构建测试数据，分热门值，冷门值，两个值倾斜严重。
SQL> INSERT INTO
  TEST
SELECT
  *
FROM
  (SELECT
    ROWNUM C1,
    DECODE(MOD(ROWNUM,100),99,99,1) C2,
    RPAD('A',300,'A') C3
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 1000000)
ORDER BY
  C2;  2    3    4    5    6    7    8    9   10   11   12   13   14   15  
1000000 rows created.
--C2为99的值，有10000条，值为1的有990000条。
SQL> select c2,count(*)  from test group by c2;
	C2   COUNT(*)
---------- ----------
	99	10000
	 1     990000
--创建索引
SQL> CREATE INDEX IND_T1_C2 ON TEST(C2);
Index created.
--收集直方图信息。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'TEST',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254') 
PL/SQL procedure successfully completed.

上面的语句创建了一张包含 100 万行的TEST表：其中 99% 的行，C2 列值为 1；1% 的行值为 99。

因为插入时带了 `ORDER BY`，数据的**聚簇因子（clustering factor）非常理想**。

我们在这个建有索引的列上收集了直方图。


我们来做个测试：这条查询**第一次硬解析**时，给绑定变量传入一个冷门值 2。其实值2的没有行，查询不到值的。

SQL> VARIABLE N1 NUMBER
SQL> EXEC :N1:=2
PL/SQL procedure successfully completed.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7  
no rows selected
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 3318186407
-----------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		|      1 |	  |	 0 |00:00:00.01 |	3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST	|      1 |   5000 |	 0 |00:00:00.01 |	3 |
|*  2 |   INDEX RANGE SCAN		    | IND_T1_C2 |      1 |   5000 |	 0 |00:00:00.01 |	3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.

因此，本次查询没有返回任何行；优化器预估会返回 5000 行，并且选择了索引访问路径来获取数据，使用的索引范围扫INDEX RANGE SCAN。

那这个索引访问路径，对于绑定变量值 1 来说还合适吗？我们继续测试，这次绑定变量传入值 99：

SQL> EXEC :N1:=99
PL/SQL procedure successfully completed.
SQL> SET TIMING ON
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7
  ......
    908799	   99
    896099	   99
    896199	   99
    896299	   99
    896399	   99
    896499	   99
10000 rows selected.
Elapsed: 00:00:00.11
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 3318186407
-----------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		|      1 |	  |  10000 |00:00:00.01 |    1781 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST	|      1 |   5000 |  10000 |00:00:00.01 |    1781 |
|*  2 |   INDEX RANGE SCAN		    | IND_T1_C2 |      1 |   5000 |  10000 |00:00:00.01 |     689 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.
Elapsed: 00:00:00.02
SQL>

次执行，执行计划显示：优化器依旧预估返回 5000 行，而实际返回了 10000 行。同时留意到子游标编号仍然是 0，

说明没有发生硬解析。

我们继续测试，这次绑定变量传入值 1：

SQL> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7  
  ......
    999922	    1
    999923	    1
    999924	    1
990000 rows selected.
Elapsed: 00:00:11.62
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 3318186407
-----------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		|      1 |	  |  10000 |00:00:00.01 |    1781 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST	|      1 |   5000 |  10000 |00:00:00.01 |    1781 |
|*  2 |   INDEX RANGE SCAN		    | IND_T1_C2 |      1 |   5000 |  10000 |00:00:00.01 |     689 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.
Elapsed: 00:00:00.02
SQL>

这并不是实际使用的执行计划，因为它仍然显示上一次执行返回 10000 行，使用是上次的执行计划。

而 执行C2=1值时表明实际返回了 990000 行。我们再试执行一次。

SQL> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7  
  ......
    999922	    1
    999923	    1
    999924	    1
990000 rows selected.
Elapsed: 00:00:11.62
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 1357081020
------------------------------------------------------------------------------------
| Id  | Operation	  | Name | Starts | E-Rows | A-Rows |	A-Time	 | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	1 |	   |	990K|00:00:00.16 |     108K|
|*  1 |  TABLE ACCESS FULL| TEST |	1 |    990K|	990K|00:00:00.16 |     108K|
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:N1)
19 rows selected.
Elapsed: 00:00:00.03

上面展示的才是真正执行时用到的执行计划。

**自适应游标共享（ACS，Oracle 11.1 版本引入）**在这里生效了：它触发了执行计划的重新评估，

避免继续走索引导致查询性能极差。

在 11.1 之前的版本不会有这个重新评估机制，（在旧版本 Oracle 中，如果 SQL 里用的是字面值，设置 `CURSOR_SHARING='SIMILAR'` 有可能达到类似效果）。

使用低版本的数据库进行测试：

SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';
Session altered.
Elapsed: 00:00:00.00
SQL> VARIABLE N1 NUMBER
SQL> EXEC :N1:=2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7  
no rows selected
Elapsed: 00:00:00.00
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 4126779481
---------------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		|      1 |	  |	 0 |00:00:00.01 |	3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST	|      1 |   5000 |	 0 |00:00:00.01 |	3 |
|*  2 |   INDEX RANGE SCAN	    | IND_T1_C2 |      1 |   5000 |	 0 |00:00:00.01 |	3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.
Elapsed: 00:00:00.09

注意，上面示例中 **CHILD_NUMBER（子游标编号）变成了 2**，原因是我们修改了优化器的执行环境

（大家可以思考一下修改数据库的哪些参数，哪些因素会触发优化器执行环境发生变化）：

SQL> EXEC :N1:=99
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7 
  ....... 
    896199	   99
    896299	   99
    896399	   99
    896499	   99
10000 rows selected.
Elapsed: 00:00:00.11
SQL>   
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 4126779481
---------------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		|      1 |	  |  10000 |00:00:00.01 |    1781 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST	|      1 |   5000 |  10000 |00:00:00.01 |    1781 |
|*  2 |   INDEX RANGE SCAN	    | IND_T1_C2 |      1 |   5000 |  10000 |00:00:00.03 |     689 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.
Elapsed: 00:00:00.02

子游标编号仍然是 2，因此没有发生硬解析。

我们继续测试

SQL> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2
FROM
  TEST
WHERE
  C2 = :N1;  2    3    4    5    6    7
 .......  
    909255	    1
    909256	    1
    909257	    1
990000 rows selected.
Elapsed: 00:00:11.98
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	fgsshbz6rhybs, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   TEST WHERE   C2
= :N1
Plan hash value: 4126779481
---------------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	| Starts | E-Rows | A-Rows |   A-Time	| Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		|      1 |	  |    990K|00:00:01.04 |     175K|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST	|      1 |   5000 |    990K|00:00:01.04 |     175K|
|*  2 |   INDEX RANGE SCAN	    | IND_T1_C2 |      1 |   5000 |    990K|00:00:00.65 |   67807 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:N1)
20 rows selected.
Elapsed: 00:00:00.03

以上就是子游标编号 2 对应的执行计划。可以看到，这次它实际执行返回行数是 990000 行，

说明**这正是针对表中占比 99% 的那个绑定变量值所使用的执行计划**。

自适应游标共享（ACS）并没有生效，没有触发执行计划的重新评估，执行计划也**没有切换成全表扫描**。

这就是你要承担的风险：如果在数据分布不均匀的列上保留直方图，WHERE 条件中使用绑定变量引用该列，

并且开启了绑定变量窥视（Oracle 9i 及以上版本默认开启；绑定变量窥视由隐藏参数 `_OPTIM_PEEK_USER_BINDS` 控制，

默认值为 TRUE）。 很有可能它会选错执行计划。高版本已经避免了这个 自适应游标共享（ACS），如果你还在使用oracle 11.1 之前的版本就要注意了。


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3186063/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 第84期 查询RAC之间私网的流量信息，监控 RAC 私网流量变化
下一篇： 没有了~
请登录后发表评论 登录
全部评论
pxbibm
专家姓名：彭小波
【DBAplus社群】联合发起人。ACOUG核心成员，Oracle用户组年轻专家。熟悉Oracle的体系结构，擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    342
  • 访问量
    2470350

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593