Oracle_绑定变量窥探测试

最新推荐文章于 2024-09-10 16:46:08 发布
最新推荐文章于 2024-09-10 16:46:08 发布
阅读量2k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： Oracle 技术分享 文章标签： 测试 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xuliq/article/details/47625053
本文通过创建测试表、建立索引并分析，演示了如何利用绑定变量窥探提升SQL执行效率，以及展示SQL执行计划的变化，包括使用绑定变量前后的执行路径和性能对比。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

绑定变量窥探受隐含参数:_optim_peek_user_binds的限制，自9i之后改参数默认为True,为自动开启状态：

查询sql

SQL> col name for a50
SQL> col value for a10
SQL> col description for a100
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx
  2  and a.ksppinm like '%_optim_peek_user_binds%';


NAME                                               VALUE      DESCRIPTION
-------------------------------------------------- ---------- ---------------------------------------------------------
_optim_peek_user_binds                             TRUE       enable peeking of user binds

接下来，我们测试一个绑定变量窥探的示例：

1、创建测试表t1

      create table sdxj.t1 as select * from dba_objects;
2、创建索引
      create index idx_t1 on sdxj.t1 (object_id);
3、对表进行分析
       exec dbms_stats.gather_table_stats(ownname => 'SDXJ',tabname => 'T1',estimate_percent => 100,CASCADE => TRUE,method_opt => 'for all columns size 1',degree => 8,no_invalidate    => FALSE);

4、执行以下两个sql

      4.1、SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999  AND  1000;

      4.2、SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999  AND  6000;

      4.3、查看sql执行情况

SQL> SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.T1%';

SQL_TEXT                                                               SQL_ID               VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------- -------------------- ------------- ----------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 1000      gxgb577ncwrm2                 1     1
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 60000     6d2d6y357kfws                 1     1

SQL> col plan_table_output for a200
SQL> set long 900
SQL> set pages 800
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('gxgb577ncwrm2','0','advanced'));

----sql1 执行计划----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  gxgb577ncwrm2, child number 0
-------------------------------------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 1000

Plan hash value: 1970818898

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">=999 AND "OBJECT_ID"<=1000)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - SQL plan baseline SQL_PLAN_f02470f3q2mcga9a6a0a8 used for this statement

已选择48行。

---sql2 执行计划---

Plan hash value: 2101382132
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    46 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 54911 |   268K|    46   (3)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("OBJECT_ID"<=60000 AND "OBJECT_ID">=999))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

已选择44行。


现在我们将上述两个SQL进行改造，使其可以用绑定变量的形式。

SQL> var x number;
SQL> var y number;
SQL> exec :x :=999
    PL/SQL 过程已成功完成。
SQL> exec :y :=1000
   PL/SQL 过程已成功完成。

---执行查询sql

SQL> SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y;

  COUNT(*)
----------
         2
---查询sql执行情况
SQL> SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.T1%';

SQL_TEXT                                                               SQL_ID               VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------- -------------------- ------------- ----------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y         65r6vbdf4n1vk                            1     1
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 1000      gxgb577ncwrm2                 1     1
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 60000     6d2d6y357kfws                 1     1

----查看执行计划
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('65r6vbdf4n1vk','0','advanced'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  65r6vbdf4n1vk, child number 0
-------------------------------------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y

Plan hash value: 2351893609

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   FILTER           |        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 999
   2 - :Y (NUMBER): 1000

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X<=:Y)
   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - SQL plan baseline SQL_PLAN_b0hdzdb9t6f6911bbd8aa used for this statement

已选择56行。


下面执行第二个sql语句，将y改为60000

SQL> exec :y :=60000

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y;

  COUNT(*)
----------
     58180

SQL> SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.T1%';

SQL_TEXT                                                               SQL_ID               VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------- -------------------- ------------- ----------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y         65r6vbdf4n1vk                            1     2
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 1000      gxgb577ncwrm2                 1     1
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 60000     6d2d6y357kfws                 1     1

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('65r6vbdf4n1vk','0','advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  65r6vbdf4n1vk, child number 0
-------------------------------------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN :x AND :y

Plan hash value: 2351893609

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   FILTER           |        |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------
   1 - :X (NUMBER): 999
   2 - :Y (NUMBER): 1000

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:X<=:Y)
   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]

Note
-----
   - SQL plan baseline SQL_PLAN_b0hdzdb9t6f6911bbd8aa used for this statement

已选择56行。

SQL>


从上面执行计划可以看出，依然走索引：INDEX RANGE SCAN| IDX_T1 并且绑定变量值仍然为999~1000；

现在，我们在进行另外一个测试。让其走 INDEX FAST FULL SCAN| 索引，测试方法同上，首先创建表T2

采用绑定变量时，首先带入只999 和60000 然后在测试999 h和1000 查看执行计划情况。







绑定变量窥探和直方图
zhaoyangjian724的专栏
01-18 1837
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM
开启和关闭绑定变量窥探
zhaoyangjian724的专栏
05-14 712
绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只 是无法使用直方图信息而已: SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM...
Oracle 绑定变量窥探
anjichan4261的博客
11-01 390
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值 ，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。 一、绑定变量窥探 使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次...
Oracle绑定变量窥探
weixin_33736832的博客
03-07 379
随着具体输入值的不同，SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化，而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对SQL执行计划的选择。这就意味着随着具体输入值的不同，目标SQL执行计划可能会发生变化。对于不使用绑定变量的SQL而言，具体...
Oracle 绑定变量窥视
weixin_30329623的博客
08-09 741
绑定变量窥视功能是数据库的一个特性，自ORACLE9i版本开始引入，默认是开启的。 “绑定变量窥视”表示，查询优化器在第一次调用游标时，会观察用户定义的绑定变量的值，允许优化器来确认过滤条件的选择性，以及是否使用绑定变量代替了常量。之后调用游标时不会出现窥视，且会根据指针共享标准来共享游标，即使随后的调用使用不同的绑定值。 使用绑定变量窥视，第一次解析包含绑定谓词的SQL...
PostgreSQL 绑定变量窥探
lyu1026的博客
05-20 1266
今天我们要探讨的是 custom执行计划和通用执行计划。这一技术在 Oracle中被称为绑定变量窥视。但 Kingbase中并没有这样的定义，更严格地说，Kingbase叫做custom执行计划和通用执行计划。 什么是custom执行计划，什么是通用执行计划，我们先来看一个例子，我创建了一个100011行的表，其中有两列分别为 id、 name。在name列就2种类型的值，一种值为“aaa”，有...
直方图；绑定变量窥探；自适应游标
wbaug20th的专栏
11-19 1035
搭建环境 构建一个表 CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS; UPDATE SET OBJECT_ID = 11111 WHERE ROWNUM <=50000; 建立索引： CREATE INDEX IND_TEST2_OBJECT_ID ON TEST2(OBJECT_ID); 收集统计信息，不收集直方图 EXEC DBMS_STAT
Oracle数据倾斜问题,Oracle数据倾斜导致的问题-有绑定变量
weixin_34957608的博客
04-03 1460
Oracle数据倾斜导致的问题-有绑定变量参考整理---<<恩墨年货-SQL与性能优化>>场景二：在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增...
Oracle 变量绑定与变量窥视合集系列五
weixin_33908217的博客
02-03 137
五 演示ACS（adaptiver cursor sharing)的效果 变量窥视bind peeking：当一条SQL语句在硬解析时如果有绑定变量，会查看这个变量值，有了变量值便于得出正确的执行计划，在后续软解析过程中将不再查看这个变量值了。 变量窥视目的：就是为了得出正确的执行计划，第一条sql的执行计划的准确性是至关重要的，后面反复执行即可 变量窥视发生时刻：只发生在SQL硬解析的时候...
绑定变量窥视 oracle,Oracle bind peeking(绑定变量窥视)
weixin_39992957的博客
04-04 163
1.创建测试表SQL> create table system.wh(num1 number(10),num2 number(10));Table created.SQL> begin2 for i in 1 .. 10000 loop3 if i < 9997 then4 insert int...
绑定变量窥探
weixin_42575078的博客
03-29 709
Cost(%CPU)等都与首次生存执行计划得值相同。由此可知，尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在Oracle 11g 中，自适。从上面的演示可以，由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,应特性从一定程度解决了绑定变量窥探所导致的问题。
oracle早期绑定和晚期绑定,Oracle 绑定变量窥探
weixin_39880895的博客
04-04 124
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。一、绑定变量窥探 使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。影响的版本：Ora...
Oralce性能优化-绑定变量窥视
oradbm的博客
09-11 396
查看Oracle SQL绑定变量方bai法如下： alter session set nls_date_format = ‘yyyy-mm-dd,hh24:mi:ss’; set linesize 400 col sql_Id format a20 col name format a20 col datatype_string format a14 col value_string format a20 –这个dusql从内存中读zhi取绑定变量值信息，dao若是不在内存中，则使用下一个sql select
绑定变量窥视 oracle,不均衡分区和绑定变量窥视导致的查询计划错误
weixin_39999025的博客
04-04 238
周一收到生成支持人员的报告，系统上一个作业启动后很长时间没有完成，其执行时间远远大于上周的正常执行时间。接到报告后，首先检查了系统，不存在锁队列的问题。然后查询V$SESSION_LONGOPS，立即发现下面的语句正在进行长操作：SQL代码SELECT *FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULTWHERE BKGSHMTRESULT.BKG_CF...
Oracle绑定变量窥视与自适应游标共享
dnuiking的博客
09-10 1639
创建test表，列status存在2个值，有数据倾斜，在列st问题：如果SQL硬解析生成执行计划时“窥视”到的绑定变量刚好只适合少部分数据，不适合其他大多数数据，那么直到下一次硬解析前（一般是收集统计信息后才会触发硬解析），都会使用这个不优的执行计划。解决：1.收集直方图，当查询Active的时候，直方图观察到绝大多数都是Active，则会走全表扫描，当查询Inactive的时候，直方图观察到绝大多数都是Inactive，则会走索引扫描。
绑定变量和游标 ，及绑定变量窥探
文档搬运工
03-18 337
原文地址：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C -- 进行3个查询，不使用绑定变量 SYS@test&gt;SELECT SUM(salar...
一次关闭绑定变量窥探_optim_peek_user_binds导致的存储过程缓慢故障
Brent的专栏
06-16 4726
一个客户联系我,他写了一个存储过程,其中有一条insert into select的sql语句运行非常缓慢,平均处理每天的数据量需要4分钟,而如果单独执行此sql语句,其实很快,大概6s左右即可完成,返回的条数也不多,总共500条左右,希望帮忙优化一下. 1.先看一下正常的执行sql效率和执行计划 此sql非常长,有多个union all连接而成,大概sql如下: ... 我们看到这个sql通过时间字段enter_date进行了限制,取一天的数据,而且时间字段上面有索引,所以直接执行的时候肯
Oracle 某一天绑定变量的语句，在第一次传入不同的参数值时，会执行很慢，是怎么回事
最新发布
03-30
<think>好的，用户问的是Oracle数据库中某一天绑定变量的语句，在第一次传入不同的参数值时执行很慢，是怎么回事。我需要分步骤分析这个问题。 首先，绑定变量在Oracle中的作用是减少硬解析，通过共享执行计划来提高效率。但有时候第一次使用不同参数时变慢，可能涉及到执行计划的问题。这时候要考虑绑定变量窥探（Bind Peeking）的影响。当第一次执行带有绑定变量的语句时，Oracle会窥探传入的值，生成一个执行计划。如果这个值不具有代表性，比如极端值，可能会导致后续查询使用不合适的计划，特别是当数据分布不均匀时。例如，如果第一次传入的值返回的数据量很大，优化器可能选择全表扫描，而实际后续查询可能参数对应的数据量很小，更适合索引扫描，这时候第一次执行就会变慢。 接下来要考虑的是直方图统计信息。Oracle的直方图帮助优化器了解数据分布，但如果没有收集或者收集不准确，优化器可能无法正确估计选择率，导致生成低效的执行计划。当第一次传入的参数对应的数据分布与直方图信息不符时，执行计划可能不佳，导致性能问题。 另外，硬解析的发生也可能导致延迟。第一次执行绑定变量语句时，Oracle需要做硬解析，生成执行计划，这过程本身比较耗时。如果系统资源紧张，比如高并发或内存不足，硬解析时间会更长，从而影响第一次执行的速度。 还有自适应游标共享（ACS）的问题。在Oracle 11g之后引入的ACS功能，允许对不同绑定值使用不同的执行计划。但如果ACS没有正确触发，或者游标没有被标记为绑定敏感，可能会导致重复使用不合适的计划，影响性能。 此外，数据倾斜和索引使用不当也是可能的原因。如果某个绑定值对应的数据量极大，而执行计划没有正确使用索引，或者索引统计信息不准确，也会导致第一次执行缓慢。 针对这些可能性，解决办法可能包括检查绑定变量窥探的设置，确保统计信息准确，特别是直方图的收集。可能需要使用SQL Profile或SQL Plan Management来固定更优的执行计划。另外，升级到更高版本的Oracle，利用自适应执行计划等新特性，或者手动使用提示（hint）来指导优化器选择正确的计划也是可行的方法。 最后，用户可能需要具体分析当时的执行计划，查看统计信息是否最新，是否存在数据倾斜，以及绑定变量的值是否导致了不合适的计划选择。通过这些步骤，可以定位问题所在并采取相应的优化措施。</think>这个问题通常与Oracle的**绑定变量窥探（Bind Peeking）**和**执行计划固化**机制有关。以下是分步解释和解决方案： --- ### **1. 绑定变量窥探（Bind Peeking）** - **现象**： 当第一次执行带有绑定变量的SQL语句时，Oracle会窥探（peek）传入的绑定变量值，并根据该值生成一个执行计划。如果该值具有极端性（例如极小的值或极多的重复值），可能导致生成的执行计划不适合后续其他参数值。 - **问题场景**： 例如，表 `orders` 中 `status` 字段数据分布不均： - 值 `'COMPLETED'` 占99%的数据（适合全表扫描）， - 值 `'PENDING'` 占1%的数据（适合索引扫描）。 若第一次传入 `status = 'COMPLETED'`，Oracle可能生成全表扫描计划；后续传入 `'PENDING'` 时，仍强制使用全表扫描，导致性能下降。 --- ### **2. 执行计划固化** - **原因**： 生成的执行计划会被缓存到共享池（Shared Pool）中，后续相同SQL（即使绑定变量值不同）会复用该计划。若初始计划不适用于新参数，性能问题会持续。 --- ### **3. 直方图（Histogram）缺失** - **关键影响**： 若表的列未收集直方图统计信息，优化器无法感知数据分布不均，可能错误估计选择率（Selectivity）。例如： ```sql SELECT * FROM orders WHERE status = :status; -- 未收集直方图时，优化器默认均匀分布 ``` --- ### **4. 自适应游标共享（Adaptive Cursor Sharing, ACS）** - **11g+ 特性**： Oracle 11g引入ACS，允许为不同绑定变量值生成不同执行计划。但需满足以下条件： - 游标被标记为**绑定敏感（Bind Sensitive）**， - 执行计划需根据变量值变化动态调整。 **若ACS未生效**，可能导致计划固化问题。 --- ### **解决方案** #### **方法1：收集统计信息与直方图** ```sql -- 收集表统计信息并生成直方图 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SCHEMA_NAME', tabname => 'ORDERS', method_opt => 'FOR COLUMNS SIZE 254 STATUS' -- 为列生成直方图 ); END; ``` #### **方法2：强制优化器重新生成计划** - 使用 `REOPT` 提示（12c+）： ```sql SELECT /*+ REOPT */ * FROM orders WHERE status = :status; ``` - 清空共享池中的缓存计划（谨慎使用）： ```sql ALTER SYSTEM FLUSH SHARED_POOL; ``` #### **方法3：使用SQL Profile或SPM固定计划** - 通过SQL Tuning Advisor生成更优的SQL Profile： ```sql DECLARE ret VARCHAR2(100); BEGIN ret := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT * FROM orders WHERE status = :status', scope => 'COMPREHENSIVE', time_limit => 60 ); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(ret); END; ``` - 使用SQL Plan Management（SPM）固定最佳计划： ```sql -- 从AWR或游标缓存加载计划 DECLARE plans PLS_INTEGER; BEGIN plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'g4w4hj5m6k7uj' -- 替换为实际SQL_ID ); END; ``` #### **方法4：升级或使用自适应特性** - 若使用Oracle 12c+，开启**自适应执行计划**： ```sql ALTER SESSION SET optimizer_adaptive_plans = TRUE; ``` --- ### **验证步骤** 1. 检查执行计划变化： ```sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'g4w4hj5m6k7uj')); ``` 2. 确认直方图收集： ```sql SELECT COLUMN_NAME, HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'ORDERS' AND COLUMN_NAME = 'STATUS'; ``` --- 通过以上方法，可以定位是绑定变量窥探、统计信息缺失还是执行计划固化导致的问题，并针对性优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值