如何解决oracle绑定变量用法造成执行计划不准的问题
点击联系发帖人
时间：2016-12-11 21:07
博客访问： 1829874
博文数量： 640
博客积分： 16398
博客等级： 上将
技术积分： 6920
注册时间：
WINDOWS下的程序员出身，偶尔也写一些linux平台下小程序，
后转行数据库行业，专注于ORACLE和DB2的运维和优化。
同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。
数据库技术交流群：618606
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
这个问题是一个朋友遇到的，客户的数据库在关闭了绑定变量探测后，一些简单的SQL执行计划走错了。
SQL也相对比较特殊，带有LIKE和ROWNUM。
SQL类似如下：
&select * from
(select object_id from testwhere object_name like :v_object_name order by object_id)
&where rownum<=:v_
这个问题在10.2.0.4的数据库上遇到的，我在11.2.0.3环境上测试的结果也是一样：
SQL> select * from v$
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE & &11.2.0.3.0 & & &Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter system set "_optim_peek_user_binds"=
System altered.
SQL> create table test as select * from all_
Table created.
SQL> create index idx_test_01 on test(object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL procedure successfully completed.
首先关闭绑定变量探测，并创建测试表和索引。
上面的SQL将会导致全表扫描，而不能用到索引：
SQL> variable v_object_name varchar2(30)
SQL> variable v_num &
SQL> exec :v_object_name:='TEST%';
PL/SQL procedure successfully completed.
SQL> exec :v_num:=2;
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID &gvjrxnsd5y45d, child number 0
-------------------------------------
select * from (select object_id from test where object_name like
:v_object_name order by object_id) where rownum<=:v_num
Plan hash value:
--------------------------------------------------------------------------------
| Id &| Operation & & & & & & & | Name | Rows &| Bytes | Cost (%CPU)| Time & & |
--------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & &| & & &| & & & | & & & | & 292 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & &| & & &| & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & &| & & &| &3626 | 47138 | & 292 & (2)| 00:00:04 |
|* &3 | & &SORT ORDER BY STOPKEY| & & &| &3626 | & 106K| & 292 & (2)| 00:00:04 |
|* &4 | & & TABLE ACCESS FULL & | TEST | &3626 | & 106K| & 291 & (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &4 - filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
24 rows selected.
很多人可能认为没有进行绑定变量探测，而且对于LIKE这样的谓词选择率是5%导致的，实际上5%选择率在此处是能用到索引的。
如果单纯的查询select object_id from test where object_name like :v_object_name order by object_id 也是可以用到索引的。
SQL> select object_id from test where object_name like :v_object_name order by object_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &c4wcr4yxuzj8s, child number 0
-------------------------------------
select object_id from test where object_name like :v_object_name order
by object_id
Plan hash value:
--------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
--------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & | & & & & & & | & & & | & & & | & 289 (100)| & & & & &|
| & 1 | &SORT ORDER BY & & & & & & & | & & & & & & | &3626 | & 106K| & 289 & (1)| 00:00:04 |
| & 2 | & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &3626 | & 106K| & 288 & (0)| 00:00:04 |
|* &3 | & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 653 | & & & | & & 6 & (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &3 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
22 rows selected.
如果改写上面的SQL里面加个ROWNUM，那么也可以用到索引。
SQL> select *
& 2 & &from (select object_id,rownum rn
& 3 & & & & & &from test
& 4 & & & & & where object_name like :v_object_name
& 5 & & & & & order by object_id)
& 6 & where rn <= :v_
&OBJECT_ID & & & & RN
---------- ----------
& & &76903 & & & & &1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID &54b2pjfjnuqyp, child number 0
-------------------------------------
select * & from (select object_id,rownum rn & & & & & from test
&where object_name like :v_object_name & & & & &order by object_id)
where rn <= :v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & 289 (100)| & & & & &|
|* &1 | &VIEW & & & & & & & & & & & & &| & & & & & & | &3626 | 94276 | & 289 & (1)| 00:00:04 |
| & 2 | & SORT ORDER BY & & & & & & & &| & & & & & & | &3626 | & 106K| & 289 & (1)| 00:00:04 |
| & 3 | & &COUNT & & & & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &3626 | & 106K| & 288 & (0)| 00:00:04 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 653 | & & & | & & 6 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter("RN"<=:V_NUM)
& &5 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
26 rows selected.
如果吧SQL的LIKE改成=也是可以用到索引的。
SQL> exec :v_object_name:='TEST'; & & &
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name=:v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &gf9bj9ps4ujkm, child number 0
-------------------------------------
select * from (select object_id from test where
object_name=:v_object_name order by object_id) where rownum<=:v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & & 5 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & & & & & | & & & & & & | & & 2 | & &26 | & & 5 &(20)| 00:00:01 |
|* &3 | & &SORT ORDER BY STOPKEY & & & | & & & & & & | & & 2 | & &60 | & & 5 &(20)| 00:00:01 |
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| & & 2 | & &60 | & & 4 & (0)| 00:00:01 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & & 2 | & & & | & & 3 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &5 - access("OBJECT_NAME"=:V_OBJECT_NAME)
25 rows selected.
SQL> alter system flush shared_
System altered.
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> exec :v_object_name:='TEST%';
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=userenv('sid'));
--------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/htdb/htdb/trace/htdb_ora_3315.trc
10053 TRACEFILE也是分析走索引扫描，实际TRACE文件里走全表扫描，2个成了矛盾。
SINGLE TABLE ACCESS PATH&
& Single Table Cardinality Estimation for TEST[TEST]&
& Column (#2): OBJECT_NAME(
& & AvgLen: 25 NDV: 43428 Nulls: 0 Density: 0.000023
& Table: TEST &Alias: TEST
& & Card: Original:
&Rounded: 3626 &Computed: 3626.10 &Non Adjusted: 3626.10
& Access Path: TableScan
& & Cost: &290.61 &Resp: 290.61 &Degree: 0
& & & Cost_io: 289.00 &Cost_cpu:
& & & Resp_io: 289.00 &Resp_cpu:
kkofmx: index filter:"TEST"."OBJECT_NAME" LIKE :B1
& Access Path: index (RangeScan)
& & Index: IDX_TEST_01
& & resc_io: 342.00 &resc_cpu: 2703262
& & ix_sel: 0.009000 &ix_sel_with_filters: 0.009000&
& & Cost: 288.16 &Resp: 288.16 &Degree: 1
& Best:: AccessPath: IndexRange
& Index: IDX_TEST_01
& & & & &Cost: 288.16 &Degree: 1 &Resp: 288.16 &Card: 3626.10 &Bytes:&
Starting SQL statement dump
user_id=90 user_name=YANSP module=SQL*Plus action=
sql_id=gvjrxnsd5y45d plan_hash_value= problem_type=3
----- Current SQL Statement for this session (sql_id=gvjrxnsd5y45d) -----
select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_num
sql_text_length=121
sql=select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_num
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id &| Operation & & & & & & & &| Name & &| Rows &| Bytes | Cost &| Time & & &|
-------------------------------------------+-----------------------------------+
| 0 & | SELECT STATEMENT & & & & | & & & & | & & & | & & & | & 292 | & & & & & |
| 1 & | &COUNT STOPKEY & & & & & | & & & & | & & & | & & & | & & & | & & & & & |
| 2 & | & VIEW & & & & & & & & & | & & & & | &3626 | & 46K | & 292 | &00:00:04 |
| 3 & | & &SORT ORDER BY STOPKEY | & & & & | &3626 | &106K | & 292 | &00:00:04 |
| 4 & | & & TABLE ACCESS FULL & &| TEST & &| &3626 | &106K | & 291 | &00:00:04 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<=:V_NUM)
3 - filter(ROWNUM<=:V_NUM)
4 - filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
Content of other_xml column
===========================
& db_version & & : 11.2.0.3
& parse_schema & : YANSP
& plan_hash & & &:
& plan_hash_2 & &:
& Outline Data:
& & BEGIN_OUTLINE_DATA
& & & IGNORE_OPTIM_EMBEDDED_HINTS
& & & OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
& & & DB_VERSION('11.2.0.3')
& & & OPT_PARAM('_optim_peek_user_binds' 'false')
& & & ALL_ROWS
& & & OUTLINE_LEAF(@"SEL$2")
& & & OUTLINE_LEAF(@"SEL$1")
& & & NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
& & & FULL(@"SEL$2" "TEST"@"SEL$2")
& & END_OUTLINE_DATA
同样的问题在10.2.0.1和10.2.0.2是没问题的。
SQL> select * from v$
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE & &10.2.0.1.0 & & &Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> alter system set "_optim_peek_user_binds"=
系统已更改。
SQL> create table test as select * from all_
表已创建。
SQL> create index idx_test_01 on test(object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 过程已成功完成。
SQL> variable v_object_name varchar2(30)
SQL> variable v_num &
SQL> exec :v_object_name:='TEST%';
PL/SQL 过程已成功完成。
SQL> &exec :v_num:=2;
PL/SQL 过程已成功完成。
SQL> select * from (select object_id from test
& 2 &where object_name like :v_object_name order by object_id)
& 3 &where rownum<=:v_
&OBJECT_ID
----------
& & &40848
& & &53537
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &5pxzkba03s8g0, child number 0
-------------------------------------
select * from (select object_id from test where object_name like :v_object_name order
by object_id) where rownum<=:v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & 164 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & & & & & | & & & & & & | &2507 | 32591 | & 164 & (1)| 00:00:02 |
|* &3 | & &SORT ORDER BY STOPKEY & & & | & & & & & & | &2507 | 75210 | & 164 & (1)| 00:00:02 |
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &2507 | 75210 | & 163 & (0)| 00:00:02 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 451 | & & & | & & 4 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &5 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
已选择26行。
开启了绑定变量探测也没问题，这个问题是关闭绑定变量窥视、ROWNUM、LIKE共同导致的，可能后期版本引入的新的BUG。
阅读(872) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。绑定变量的坏处 - 炼数成金 - Powered by Discuz!
标题: 绑定变量的坏处
作者: dbcc& & 时间:
标题: 绑定变量的坏处关于绑定变量的坏处课程里面似乎还讲的不够全面，大家讨论下绑定变量的坏处 遇到的场景和案例有哪些？
作者: HDONKING& & 时间:
如果数据倾斜度较大，容易导致绑定变量窥视。
作者: zxx2403& & 时间:
邦定变量适用于 短时间，大量运行的SQL。 解析时间占用比例较大的情况。 如果是OLAP
则不需要考虑邦定变量问题
作者: neves_0& & 时间:
对于数据倾斜的情况，是一定要用柱状图的吧，11g的acs能不能解决这个问题
作者: ZALBB& & 时间:
如果数据倾斜度较大，容易导致绑定变量窥视。
什么叫容易绑定变量窥视？ 不是在第一次执行时窥视么？
作者: sir_ahua4307& & 时间:
在Oracle11g里，自适应游标解决了绑定观察的问题。
绑定变量通过减少执行计划硬解析来提高性能，绑定观察（也就是楼上说的绑定变量窥视）是怎么回事呢？对于数据分布不均的表，例如一个customer表，里面有个area字段表示客户所在地区，假如里面有100万条记录（100万个客户），其中95%都在北京地区，5%在广东地区，如果用绑定变量，Oracle会记住第一次执行的SQL语句的执行计划（一般是在一个loop中大量执行同样的SQL)，假如第一次取的记录是beijing,因beijing的记录在95%，不会用到索引而是走全表扫描，那么后面的全部SQL语句都跟着走全表扫描，这就是绑定观察（在oracle11g前是这样），所以在这种情况下就是有问题的。
这个问题，Arup Nanda在《oracle11g new feautre》里的自适应游标一章中做了阐述
作者: ninipig& & 时间:
绑定变量主要用于OLTP系统，在OLAP系统里面用得很少
作者: Aki& & 时间:
由于等高直方图的限制（255个），对于严重倾斜的列（不同的值远大于255个并且值差距很大），11g以前如果全表扫描的执行计划先执行，那么所有相似的sql均会是全表扫描的执行计划，不管访问的数据量有多小
作者: dwangw& & 时间:
加油！！！！！！！！！！！！！！！！！
欢迎光临 炼数成金 (/)
Powered by Discuz! X3.2通过重新生成执行计划解决绑定变量执行计划偏差导致SQL执行时间过长
(window.slotbydup=window.slotbydup || []).push({
id: '2611110',
container: s,
size: '240,200',
display: 'inlay-fix'
您当前位置： &
[ 所属分类
基本要素（时间、用户、问题）用户11g环境下有段SQL语句在程序中执行效率非常差，但是在plsql中执行却很快，通过查看执行计划，发现使用了不同的索引导致，程序中执行的如下： PLSQL中执行的效果如下：可以看到差别，使用门诊费用记录_IX_登记时间索引是在plsql中的执行计划，使用门诊费用记录_UQ_NO的是程序中的执行计划，两者SQL是完全相同的，唯一却别就是前者使用了绑定变量，后者是直接带参数值执行。问题分析问题很明显，由于绑定变量生成的执行计划与实际有偏差，11g本来有个绑定变量窥探的功能，但是明显在这里没有用，分析极有可能是统计信息出现了问题，需要重新对相关业务表进行统计信息收集，让相应SQL重新生成执行计划。解决步骤重新收集下相关业务表的统计信息，这里我们要注意，建议100%的收集，如果采用采样收集，可能无法准确生成直方图，同样会导致执行计划偏差，执行下面的语句：exec dbms_stats.gather_table_stats(ownname =& 'ZLHIS',tabname =&'门诊费用记录',estimate_percent =& 100,method_opt =&'for all indexed columns size 254',no_invalidate =& false,cascade =& true,force =& true,degree =&4);这里我们加了no_invalidate =& false, 该参数表示收集完统计信息后，重新生成设计该对象的SQL语句的执行计划，执行完成后，再次查看执行计划正确，系统正常运行。
本文数据库（综合）相关术语:系统安全软件
转载请注明本文标题：本站链接：
分享请点击：
1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
阅读(1287)
CodeSecTeam微信公众号
没有代价的安逸未必是幸福。
手机客户端}
博文数量： 640
博客积分： 16398
博客等级： 上将
技术积分： 6920
注册时间：
WINDOWS下的程序员出身，偶尔也写一些linux平台下小程序，
后转行数据库行业，专注于ORACLE和DB2的运维和优化。
同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。
数据库技术交流群：618606
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
这个问题是一个朋友遇到的，客户的数据库在关闭了绑定变量探测后，一些简单的SQL执行计划走错了。
SQL也相对比较特殊，带有LIKE和ROWNUM。
SQL类似如下：
&select * from
(select object_id from testwhere object_name like :v_object_name order by object_id)
&where rownum<=:v_
这个问题在10.2.0.4的数据库上遇到的，我在11.2.0.3环境上测试的结果也是一样：
SQL> select * from v$
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE & &11.2.0.3.0 & & &Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter system set "_optim_peek_user_binds"=
System altered.
SQL> create table test as select * from all_
Table created.
SQL> create index idx_test_01 on test(object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL procedure successfully completed.
首先关闭绑定变量探测，并创建测试表和索引。
上面的SQL将会导致全表扫描，而不能用到索引：
SQL> variable v_object_name varchar2(30)
SQL> variable v_num &
SQL> exec :v_object_name:='TEST%';
PL/SQL procedure successfully completed.
SQL> exec :v_num:=2;
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID &gvjrxnsd5y45d, child number 0
-------------------------------------
select * from (select object_id from test where object_name like
:v_object_name order by object_id) where rownum<=:v_num
Plan hash value:
--------------------------------------------------------------------------------
| Id &| Operation & & & & & & & | Name | Rows &| Bytes | Cost (%CPU)| Time & & |
--------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & &| & & &| & & & | & & & | & 292 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & &| & & &| & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & &| & & &| &3626 | 47138 | & 292 & (2)| 00:00:04 |
|* &3 | & &SORT ORDER BY STOPKEY| & & &| &3626 | & 106K| & 292 & (2)| 00:00:04 |
|* &4 | & & TABLE ACCESS FULL & | TEST | &3626 | & 106K| & 291 & (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &4 - filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
24 rows selected.
很多人可能认为没有进行绑定变量探测，而且对于LIKE这样的谓词选择率是5%导致的，实际上5%选择率在此处是能用到索引的。
如果单纯的查询select object_id from test where object_name like :v_object_name order by object_id 也是可以用到索引的。
SQL> select object_id from test where object_name like :v_object_name order by object_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &c4wcr4yxuzj8s, child number 0
-------------------------------------
select object_id from test where object_name like :v_object_name order
by object_id
Plan hash value:
--------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
--------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & | & & & & & & | & & & | & & & | & 289 (100)| & & & & &|
| & 1 | &SORT ORDER BY & & & & & & & | & & & & & & | &3626 | & 106K| & 289 & (1)| 00:00:04 |
| & 2 | & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &3626 | & 106K| & 288 & (0)| 00:00:04 |
|* &3 | & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 653 | & & & | & & 6 & (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &3 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
22 rows selected.
如果改写上面的SQL里面加个ROWNUM，那么也可以用到索引。
SQL> select *
& 2 & &from (select object_id,rownum rn
& 3 & & & & & &from test
& 4 & & & & & where object_name like :v_object_name
& 5 & & & & & order by object_id)
& 6 & where rn <= :v_
&OBJECT_ID & & & & RN
---------- ----------
& & &76903 & & & & &1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID &54b2pjfjnuqyp, child number 0
-------------------------------------
select * & from (select object_id,rownum rn & & & & & from test
&where object_name like :v_object_name & & & & &order by object_id)
where rn <= :v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & 289 (100)| & & & & &|
|* &1 | &VIEW & & & & & & & & & & & & &| & & & & & & | &3626 | 94276 | & 289 & (1)| 00:00:04 |
| & 2 | & SORT ORDER BY & & & & & & & &| & & & & & & | &3626 | & 106K| & 289 & (1)| 00:00:04 |
| & 3 | & &COUNT & & & & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &3626 | & 106K| & 288 & (0)| 00:00:04 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 653 | & & & | & & 6 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter("RN"<=:V_NUM)
& &5 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
26 rows selected.
如果吧SQL的LIKE改成=也是可以用到索引的。
SQL> exec :v_object_name:='TEST'; & & &
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name=:v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &gf9bj9ps4ujkm, child number 0
-------------------------------------
select * from (select object_id from test where
object_name=:v_object_name order by object_id) where rownum<=:v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & & 5 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & & & & & | & & & & & & | & & 2 | & &26 | & & 5 &(20)| 00:00:01 |
|* &3 | & &SORT ORDER BY STOPKEY & & & | & & & & & & | & & 2 | & &60 | & & 5 &(20)| 00:00:01 |
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| & & 2 | & &60 | & & 4 & (0)| 00:00:01 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & & 2 | & & & | & & 3 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &5 - access("OBJECT_NAME"=:V_OBJECT_NAME)
25 rows selected.
SQL> alter system flush shared_
System altered.
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> exec :v_object_name:='TEST%';
PL/SQL procedure successfully completed.
SQL> select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_
&OBJECT_ID
----------
& & &76903
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=userenv('sid'));
--------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/htdb/htdb/trace/htdb_ora_3315.trc
10053 TRACEFILE也是分析走索引扫描，实际TRACE文件里走全表扫描，2个成了矛盾。
SINGLE TABLE ACCESS PATH&
& Single Table Cardinality Estimation for TEST[TEST]&
& Column (#2): OBJECT_NAME(
& & AvgLen: 25 NDV: 43428 Nulls: 0 Density: 0.000023
& Table: TEST &Alias: TEST
& & Card: Original:
&Rounded: 3626 &Computed: 3626.10 &Non Adjusted: 3626.10
& Access Path: TableScan
& & Cost: &290.61 &Resp: 290.61 &Degree: 0
& & & Cost_io: 289.00 &Cost_cpu:
& & & Resp_io: 289.00 &Resp_cpu:
kkofmx: index filter:"TEST"."OBJECT_NAME" LIKE :B1
& Access Path: index (RangeScan)
& & Index: IDX_TEST_01
& & resc_io: 342.00 &resc_cpu: 2703262
& & ix_sel: 0.009000 &ix_sel_with_filters: 0.009000&
& & Cost: 288.16 &Resp: 288.16 &Degree: 1
& Best:: AccessPath: IndexRange
& Index: IDX_TEST_01
& & & & &Cost: 288.16 &Degree: 1 &Resp: 288.16 &Card: 3626.10 &Bytes:&
Starting SQL statement dump
user_id=90 user_name=YANSP module=SQL*Plus action=
sql_id=gvjrxnsd5y45d plan_hash_value= problem_type=3
----- Current SQL Statement for this session (sql_id=gvjrxnsd5y45d) -----
select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_num
sql_text_length=121
sql=select * from (select object_id from test where object_name like :v_object_name order by object_id) where rownum<=:v_num
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id &| Operation & & & & & & & &| Name & &| Rows &| Bytes | Cost &| Time & & &|
-------------------------------------------+-----------------------------------+
| 0 & | SELECT STATEMENT & & & & | & & & & | & & & | & & & | & 292 | & & & & & |
| 1 & | &COUNT STOPKEY & & & & & | & & & & | & & & | & & & | & & & | & & & & & |
| 2 & | & VIEW & & & & & & & & & | & & & & | &3626 | & 46K | & 292 | &00:00:04 |
| 3 & | & &SORT ORDER BY STOPKEY | & & & & | &3626 | &106K | & 292 | &00:00:04 |
| 4 & | & & TABLE ACCESS FULL & &| TEST & &| &3626 | &106K | & 291 | &00:00:04 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(ROWNUM<=:V_NUM)
3 - filter(ROWNUM<=:V_NUM)
4 - filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
Content of other_xml column
===========================
& db_version & & : 11.2.0.3
& parse_schema & : YANSP
& plan_hash & & &:
& plan_hash_2 & &:
& Outline Data:
& & BEGIN_OUTLINE_DATA
& & & IGNORE_OPTIM_EMBEDDED_HINTS
& & & OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
& & & DB_VERSION('11.2.0.3')
& & & OPT_PARAM('_optim_peek_user_binds' 'false')
& & & ALL_ROWS
& & & OUTLINE_LEAF(@"SEL$2")
& & & OUTLINE_LEAF(@"SEL$1")
& & & NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
& & & FULL(@"SEL$2" "TEST"@"SEL$2")
& & END_OUTLINE_DATA
同样的问题在10.2.0.1和10.2.0.2是没问题的。
SQL> select * from v$
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE & &10.2.0.1.0 & & &Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> alter system set "_optim_peek_user_binds"=
系统已更改。
SQL> create table test as select * from all_
表已创建。
SQL> create index idx_test_01 on test(object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'test');
PL/SQL 过程已成功完成。
SQL> variable v_object_name varchar2(30)
SQL> variable v_num &
SQL> exec :v_object_name:='TEST%';
PL/SQL 过程已成功完成。
SQL> &exec :v_num:=2;
PL/SQL 过程已成功完成。
SQL> select * from (select object_id from test
& 2 &where object_name like :v_object_name order by object_id)
& 3 &where rownum<=:v_
&OBJECT_ID
----------
& & &40848
& & &53537
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID &5pxzkba03s8g0, child number 0
-------------------------------------
select * from (select object_id from test where object_name like :v_object_name order
by object_id) where rownum<=:v_num
Plan hash value:
----------------------------------------------------------------------------------------------
| Id &| Operation & & & & & & & & & & &| Name & & & &| Rows &| Bytes | Cost (%CPU)| Time & & |
----------------------------------------------------------------------------------------------
| & 0 | SELECT STATEMENT & & & & & & & | & & & & & & | & & & | & & & | & 164 (100)| & & & & &|
|* &1 | &COUNT STOPKEY & & & & & & & & | & & & & & & | & & & | & & & | & & & & & &| & & & & &|
| & 2 | & VIEW & & & & & & & & & & & & | & & & & & & | &2507 | 32591 | & 164 & (1)| 00:00:02 |
|* &3 | & &SORT ORDER BY STOPKEY & & & | & & & & & & | &2507 | 75210 | & 164 & (1)| 00:00:02 |
| & 4 | & & TABLE ACCESS BY INDEX ROWID| TEST & & & &| &2507 | 75210 | & 163 & (0)| 00:00:02 |
|* &5 | & & &INDEX RANGE SCAN & & & & &| IDX_TEST_01 | & 451 | & & & | & & 4 & (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
& &1 - filter(ROWNUM<=:V_NUM)
& &3 - filter(ROWNUM<=:V_NUM)
& &5 - access("OBJECT_NAME" LIKE :V_OBJECT_NAME)
& & & &filter("OBJECT_NAME" LIKE :V_OBJECT_NAME)
已选择26行。
开启了绑定变量探测也没问题，这个问题是关闭绑定变量窥视、ROWNUM、LIKE共同导致的，可能后期版本引入的新的BUG。
阅读(872) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。绑定变量的坏处 - 炼数成金 - Powered by Discuz!
标题: 绑定变量的坏处
作者: dbcc& & 时间:
标题: 绑定变量的坏处关于绑定变量的坏处课程里面似乎还讲的不够全面，大家讨论下绑定变量的坏处 遇到的场景和案例有哪些？
作者: HDONKING& & 时间:
如果数据倾斜度较大，容易导致绑定变量窥视。
作者: zxx2403& & 时间:
邦定变量适用于 短时间，大量运行的SQL。 解析时间占用比例较大的情况。 如果是OLAP
则不需要考虑邦定变量问题
作者: neves_0& & 时间:
对于数据倾斜的情况，是一定要用柱状图的吧，11g的acs能不能解决这个问题
作者: ZALBB& & 时间:
如果数据倾斜度较大，容易导致绑定变量窥视。
什么叫容易绑定变量窥视？ 不是在第一次执行时窥视么？
作者: sir_ahua4307& & 时间:
在Oracle11g里，自适应游标解决了绑定观察的问题。
绑定变量通过减少执行计划硬解析来提高性能，绑定观察（也就是楼上说的绑定变量窥视）是怎么回事呢？对于数据分布不均的表，例如一个customer表，里面有个area字段表示客户所在地区，假如里面有100万条记录（100万个客户），其中95%都在北京地区，5%在广东地区，如果用绑定变量，Oracle会记住第一次执行的SQL语句的执行计划（一般是在一个loop中大量执行同样的SQL)，假如第一次取的记录是beijing,因beijing的记录在95%，不会用到索引而是走全表扫描，那么后面的全部SQL语句都跟着走全表扫描，这就是绑定观察（在oracle11g前是这样），所以在这种情况下就是有问题的。
这个问题，Arup Nanda在《oracle11g new feautre》里的自适应游标一章中做了阐述
作者: ninipig& & 时间:
绑定变量主要用于OLTP系统，在OLAP系统里面用得很少
作者: Aki& & 时间:
由于等高直方图的限制（255个），对于严重倾斜的列（不同的值远大于255个并且值差距很大），11g以前如果全表扫描的执行计划先执行，那么所有相似的sql均会是全表扫描的执行计划，不管访问的数据量有多小
作者: dwangw& & 时间:
加油！！！！！！！！！！！！！！！！！
欢迎光临 炼数成金 (/)
Powered by Discuz! X3.2通过重新生成执行计划解决绑定变量执行计划偏差导致SQL执行时间过长
(window.slotbydup=window.slotbydup || []).push({
id: '2611110',
container: s,
size: '240,200',
display: 'inlay-fix'
您当前位置： &
[ 所属分类
基本要素（时间、用户、问题）用户11g环境下有段SQL语句在程序中执行效率非常差，但是在plsql中执行却很快，通过查看执行计划，发现使用了不同的索引导致，程序中执行的如下： PLSQL中执行的效果如下：可以看到差别，使用门诊费用记录_IX_登记时间索引是在plsql中的执行计划，使用门诊费用记录_UQ_NO的是程序中的执行计划，两者SQL是完全相同的，唯一却别就是前者使用了绑定变量，后者是直接带参数值执行。问题分析问题很明显，由于绑定变量生成的执行计划与实际有偏差，11g本来有个绑定变量窥探的功能，但是明显在这里没有用，分析极有可能是统计信息出现了问题，需要重新对相关业务表进行统计信息收集，让相应SQL重新生成执行计划。解决步骤重新收集下相关业务表的统计信息，这里我们要注意，建议100%的收集，如果采用采样收集，可能无法准确生成直方图，同样会导致执行计划偏差，执行下面的语句：exec dbms_stats.gather_table_stats(ownname =& 'ZLHIS',tabname =&'门诊费用记录',estimate_percent =& 100,method_opt =&'for all indexed columns size 254',no_invalidate =& false,cascade =& true,force =& true,degree =&4);这里我们加了no_invalidate =& false, 该参数表示收集完统计信息后，重新生成设计该对象的SQL语句的执行计划，执行完成后，再次查看执行计划正确，系统正常运行。
本文数据库（综合）相关术语:系统安全软件
转载请注明本文标题：本站链接：
分享请点击：
1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
阅读(1287)
CodeSecTeam微信公众号
没有代价的安逸未必是幸福。
手机客户端}
我要回帖
更多关于 mysql 绑定变量 的文章
更多推荐
- ·视频苹果手机怎么关闭彩铃业务乐享会员6元产品业务订购扣费后多久生效？
- ·爽课如何提升工业品营销策划的效果？
- ·爽课在工业品网络营销具有什么特点领域有哪些竞争优势？
- ·macos10.10.5安装哪个qq2017旧版本下载的QQ？
- ·礼品卡券永辉超市永辉电子卡回收平台台国内最大的是哪个?
- ·因380v双电源转换接线图开关出现故障，380v双电源转换接线图时网电的c相与发电机电的c相并在一起，会造
- ·想办个联通联通腾讯大王卡官网 可是选那个号码都出来 手慢无 这个字 怎么办呀？
- ·《怎么做清明梦梦脑波》这个软件有人试过吗，靠不靠谱
- ·13130681195是什么号码啊 这种短信该不该信
- ·iphone已停用备份数据据时备份软件可以在刷好的rom里还原使用吗
- ·手机电池不耐用是3000容量耐用还是4000容量耐用
- ·i7华硕z170ar 6700k超频 z170主板 16g内存 1060显卡600w电源 大概用多少钱
- ·anonymousb qq气泡怎么设置弄？
- ·苹果5s微信客户端在哪怎么一个机上两个微信
- ·PS4的HDMI接口我的电脑显示器接口类型图解没有，用什么转换好？我的电脑显示器接口类型图解有DVI和VGI接口，知道的告诉下谢谢，详细点
- ·Pro/e如何根据pro e产品缩水3D设计夹具
- ·酷群中只聊天把歌关了，是不是免费斗地主不用流量量
- ·好麻烦呀，发表文章费用个文章这么费事。。
- ·deeoin双系统盘符互相隐藏中如何隐藏盘符
- ·如何解决oracle绑定变量用法造成执行计划不准的问题
- ·光纤用什么无线路由器路由器好
- ·美人为馅1百度云资源第三季百度云资源
- ·求助 怎么用Delldell 笔记本系列的内置摄像头拍照
- ·网咯七排怎么样？有些什么可以对现的vr虚拟现实游戏排行榜？
- ·微信手机号批量加好友从微信上成功解绑，该关的功能都关了，为啥好友微信还显示我微信手机号批量加好友
- ·说说可以弄成一个爱心形状说说吗
- ·女人微信头像饥荒第一天晚上怎么过换几次说明有什么启示？
- ·4选一有麦基 火箭为什么不选乔丹是小乔丹就对了
- ·星海人家点石石岩健身房怎么样
- ·中国的中国乒乓球有多强大强到什么程度
- ·斯伯丁篮球型号区别有哪些型号的球比较好看
- ·骑单车健身一小时消耗的热量多少热量
- ·国考分数什么时候出来出来了，大家考的怎么样
- ·大家转基因是什么意思基因
- ·浅谈怎样提高初中英语阅读怎么提高的技能
