Oracle_绑定变量窥探测试

最新推荐文章于 2024-09-10 16:46:08 发布
原创 最新推荐文章于 2024-09-10 16:46:08 发布 · 2k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#测试 #优化

本文通过创建测试表、建立索引并分析，演示了如何利用绑定变量窥探提升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 1034
搭建环境 构建一个表 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
这个问题通常与Oracle的**绑定变量窥探（Bind Peeking）**和**执行计划固化**机制有关。以下是分步解释和解决方案： --- ### **1. 绑定变量窥探（Bind Peeking）** - **现象**： 当第一次执行带有绑定变量的SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值