Oracle绑定变量和索引使用总结.pdf

Oracle绑定变量和索引使用总结.pdf

《Oracle绑定变量和索引使用总结.pdf》由会员分享，可在线阅读，更多相关《Oracle绑定变量和索引使用总结.pdf（9页珍藏版）》请在冰豆网上搜索。

OracleOracle绑定变量和索引使用总结绑定变量和索引使用总结1.Oracle里的绑定变量.21.1绑定变量是什么？

.21.2为什么使用绑定变量？

.21.3怎么样使用绑定变量.31.4绑定变量使用限制条件是什么.51.5将未使用绑定变量的语句改造成使用绑定变量时，需要做那些必要工作.62.索引使用的相关总结.72.1哪些条件下适合建索引.72.2建复合索引需要把握的注意事项和原则.72.2.1不得存在冗余索引.72.2.2当单个字段无法过滤大量记录时应建立复合索引,非唯一索引中包含的字段不超过5个.72.2.3使用异常状态类索引的查询语句必须在SQL文本中增加提示（hint）明确指定使用该索引.72.2.4优先将选择性最高字段放在第一位，选择性接近，在Where过滤条件中为等值查询的字段应该放在范围查询字段前面.72.3建有索引未走上索引的情况（在sql编写中需注意）.72.3.1表的统计信息过旧.72.3.2Where条件中的索引字段带有函数或数学运算.82.3.3包含了隐式转换.82.3.4LIKE前通配或全通配的查询，走不了索引.82.3.5谓词使用了不等于（,!

=），走不了索引.82.3.6索引列的值为null.92.4如何查看sql执行计划（判别sql是否走上正确的索引）.92.5.如何加快建索引的效率.91.Oracle里的绑定变量1.1绑定变量是什么？

绑定变量是一种特殊类型的变量，实际上是查询语句用的一个占位符,通常出现在目标SQL文本中，用于替换SQL文本中的where条件或者value子句中的具体输入值。

绑定变量可以被理解成为指针，这些指针指向内存中的一段，这段内存在语句执行时会被替换为用户给定的具体值。

Oracle中的绑定变量只会在优化器为CBO（Cost-Based）模式下才会影响查询语句的执行计划，因为当优化器在CBO模式下时，优化器生成语句的执行计划时，会参考数据表上列的统计信息，而在RBO情况下，是否采用绑定变量都不会影响语句的执行计划（不过，Oracle自9I以来都是默认采用CBO模式优化器）。

至于绑定变量会如何影响查询语句的执行计划生成，会在文章的后面章节中描述。

1.2为什么使用绑定变量？

使用绑定变量的好处主要有两个方面，一是从数据库的安全性考虑，使用绑定变量能够较好的防范SQL注入攻击；二是从性能方面考虑，使用绑定变量，可以有效减少SQL的硬解析工作，使用绑定变量替换查询语句中的固定值（所谓“写死”的值），每次调用该SQL时，Oracle无需将该SQL作为一条新语句进行再次解析。

Oracle在解析和执行目标SQL时，会根据目标SQL的SQL文本的哈希值去SGA（sharedglobalarea,共享内存区）中的库缓冲区查找匹配的cursor，只要待执行的目标SQL文本稍有不同，那么计算出来的哈希值极有可能不同，对于SQL文本不完全相同的目标SQL之间是没法重用解析树和执行计划，需要重新解析。

无论是硬解析还是软解析数据库都会持有轻量级librarycachelatch。

硬解析硬解析：

Oracle在执行目标SQL时，在库缓冲区找不到可以重用的解析树和执行计划，不得不从头开始解析目标SQL，并生成相应的cursor。

硬解析硬解析的的危害危害主要体现在：

主要体现在：

可能会导致sharedpoolLatch争用。

硬解析是会在SGA中的sharedpool中分配一块内存区域，latch的作用之一就是保护共享内存的分配。

如果有一定数量的并发硬解析，可能就会导致严重的sharedpoollatch争用。

对于高并发的OLTP类型的系统，硬解析会严重影响系统的性能和可扩展性。

软解析：

软解析：

是指在Oracle执行目标SQL时，在库缓冲区找到了匹配cursor,并将缓存在cursor中的解析树和执行计划直接拿来重用而无须从头开始解析。

软解析不持有sharedpoollatch仅持有librarycachelatch，且持有时间会比硬解析短。

使用绑定变量是解决Oracle应用程序可伸缩性的一个关键环节;如果想要获得并发性能并且共享池资源不会成为瓶颈，推荐开发人员使用绑定变量。

使用绑定变量的SQL，即使每次调用的时候都带入不同的值，Oracle也会将其视为一个统一维度（相同的SQL_ID）来收集该SQL的性能统计信息，这也为后期测试、运维工作创造了便利条件，比起分析上百条不同的SQL来，分析一条重复上百次的单一SQL更加容易，存在的性能问题也更容易被诊断。

进一步来讲，如果需要使用创建SQLPROFILE来绑定语句的执行计划，那么针对一条未使用绑定变量的SQL，每次查询条件中的变量值发生变化，都会被标记为一个新的SQLID，如果变量值多达十几个到数十、数百个，使用SQLPROFIE来绑定执行计划几乎是不可能的。

一般情况下，当SQL过滤条件中某字段的输入值非常多，且分布较为均匀时，强烈建议使用绑定变量，比如：

账户号、姓名、时间等字段。

举个例子：

select*fromempwhereempno=123;大多数OLTP系统，同一类型的SQL都可能会并发被不同的用户反复执行，这里的同一类型是指除SQL文本中对应的输入值不同外，其他部分都一模一样的目标SQL。

使用绑定变量，意味着当SQL语句被送给Oracle优化器编译时，并没有包含过滤条件中的“特征值”。

如果用户想查empno，值可能是123，也可能是456或者其他。

对于ORACLE而言，即使select*fromempwhereempno部分内容一样，输入的值不同，也会被判断为不同的SQL（SQL_ID不同）。

所以，oracle会判断SQL1和SQL2就是不同的SQL，第一次执行SQL1时，直接硬解析，再次执行SQL1为软解析，接着执行SQL2还是需要硬解析。

没有什么捷径，就是ORACLE不认识，遇到新的SQL需要再调查了解。

比如下面SQL，SQL1:

selectempnamefromempwhereempno=123;SQL2:

SQL2:

selectselectempnameempnamefromfromempempwherewhereempno=456empno=456；如果使用绑定变量：

SQL3:

selectSQL3:

selectempnameempnamefromfromempempwherewhereempno=:

empnoempno=:

empno；查询语句提供了绑定变量:

empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用。

SQL1/SQL2查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量；会把优化好的其它查询语句从共享池中踢出，系统的整体性能降低；而执行绑定变量,提交相同对象的完全相同的查询的用户（这句话,听起来比较难理解），一次性使用就可重复使用,其效率不言而喻。

打个形象的比喻来说,第一个查询就像一次性使用的筷子,而第三个查询像是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源。

再举个例子进一步解释：

语句1：

Selectcount（*）fromuser_accountswherestatus=异常；语句2：

Selectcount（*）fromuser_accountswherestatus=正常；语句3：

Selectcount（*）fromuser_accountswherestatus=:

bind_1；“账户状态”这类的值，可能一万条里有九千多条都是“正常”，有几百条是“异常”，假设status列上创建了索引，如果我们需要查询“异常”状态的账户，那么走索引无疑是更好的选择，要查询“正常”状态的账户，走全表扫描就更好，当语句中的过滤条件没有使用绑定变量替代时，语句1和2会被当成两条SQL处理，当STATUS列上有准确的直方图统计信息的情况下，Oracle会在解析SQL时，分别为两个不同的条件生成两种执行计划，一个走全表，一个走索引。

而当我们使用了绑定变量替换过滤条件后，无论将来执行SQL时传入的值是“正常”还是“异常”，Oracle都只会生成一个执行计划，或者走全表或者走索引，无论选择哪一种访问路径，都会降低某一个查询条件的执行效率。

OracleOracle开发规范开发规范中明确建议中明确建议，SQLSQL过滤条件中该字段的输入值可变范围小于过滤条件中该字段的输入值可变范围小于200200，且分布很不均匀的且分布很不均匀的情况下情况下，建议不建议不采用绑定变量，常见如：

多法人实体、状态、类型等字段采用绑定变量，常见如：

多法人实体、状态、类型等字段。

（请注意：

（请注意：

1.1.200200这个值不是硬性的，只是一个建议值这个值不是硬性的，只是一个建议值;2.2.直方图直方图统计信息收集在建行的数据库部署规范中是禁用的统计信息收集在建行的数据库部署规范中是禁用的，因为表上的数据总是在活动变化因为表上的数据总是在活动变化，统计信息可能出现偏差统计信息可能出现偏差，为优化器提供为优化器提供错误信息，反而造成了低效执行计划，所以数据中心禁用了该特性）错误信息，反而造成了低效执行计划，所以数据中心禁用了该特性）1.3怎么样使用绑定变量先先给一个未使用绑定变量的例子给一个未使用绑定变量的例子:

setechoon;-把执行结果显示出来altersystemflushshared_pool;-这条语句是清空共享池,每次都必须使用,确保共享池是空的,以提高执行效率;settimingon-打开记时器declaretypercisrefcursor;l_rcrc;l_dummyempno.empname%type;l_startnumberdefaultdbms_utility.get_time;beginforiin1.1000loopopenl_rcforselectempnamefromempwhereempno=|i;fetchl_rcintol_dummy;closel_rc;endloop;dbms_output.put_line（round（dbms_utility.get_time-l_start）/100,2）|seconds.）;end;/还有一个迁移区的案例，其思路一样，都是内层的SQL通过拼接产生，条件筛选语句的值直接带入（标色语句）。

这样就容易发生多次硬解析，是一种不太好的书写习惯。

-取系统日期和起止日期selectto_char（sysdate,yyyymmdd）intosys_datefromdual;iflen_date=8thensqlstr1:

=altersessionsetnls_date_format=SYYYYMMDDHH24:

MI:

SS;executeimmediatesqlstr1;selectto_date（beg_date|00:

00:

00,SYYYYMMDDHH24:

MI:

SS,NLS_CALENDAR=GREGORIAN）intobeg_date1fromdual;selectto_date（end_date|00:

00:

00,SYYYYMMDDHH24:

MI:

SS,NLS_CALENDAR=GREGORIAN）intoend_date1fromdual;elseselectselectto_date（beg_date|00:

00:

00,SYYYYMMto_date（beg_date|00:

00:

00,SYYYYMMHH24:

MI:

SS,NLS_CALENDAR=GREGORIAN）HH24:

MI:

SS,NLS_CALENDAR=GREGORIAN）intointobeg_date1beg_date1fromfromdual;dual;selectto_date（end_date|00:

00:

00,SYYYYMMHH24:

MI:

SS,NLS_CALENDAR=GREGORIAN）intoend_date1fromdual;endif;-游标字符串ifi_date_flag=Ythenlg.init（PC_S61T1_Cst_APAcc_Dtl_Inf_|beg_date|_|end_date|.log,PC_S61T1_Cst_APAcc_Dtl_Inf）;-Before3.1-sqlstr:

=selectsqlstr:

=selecta.*,b.cst_id,b.basketno,c.DB_NMBRa.*,b.cst_id,b.basketno,c.DB_NMBRfromfrom-（select-（select*fromfromhist_p201401hist_p201401wherewherestampstampbetweenbetween|beg_date1|beg_date1|andand|end_date1|）|end_date1|）aa-left-leftjoinjoins02_inner_ecifs02_inner_ecifbbonona.ACCTNBR=b.CARD_NMBRa.ACCTNBR=b.CARD_NMBR-left-leftjoinjoinS11T1_BKTDBMAPS11T1_BKTDBMAPccononb.basketnob.basketno=c.BKT_NMBRc.BKT_NMBRwherewhereb.cst_idb.cst_idisisnotnotnull;null;-After3.1sqlstr:

=selectsqlstr:

=selecta.*,b.CUSTOMER_NMBR,b.BRANCH_ID,c.basketno,d.DB_NMBR,e.zxflag,e.kscpbma.*,b.CUSTOMER_NMBR,b.BRANCH_ID,c.basketno,d.DB_NMBR,e.zxflag,e.kscpbmfromfrom（select（select*fromfromhist_|v_part|hist_|v_part|wherewherestampstamp=|beg_date1|=|beg_date1|andandstampstamp|end_date1|）|end_date1|）aa下面开始介绍几种绑定变量的典型用法：

（11）简单简单替换替换varxvarchar2（100）;exec:

x:

=456;selectempnamefromempwhereempno=:

x;（22）PL/SQLPL/SQL中中selectselect语句的语句的绑定变量用法：

绑定变量用法：

declarevc_empnovarchar2（100）;beginexecimmediateselectempnamefromempwhereempno=:

xintovc_empnousing456;dbms_output.put_line（vc_empno）;end;/PL/SQL中使用绑定变量的语法示意：

executeimmediate带绑定变量的目标SQLusing对应绑定变量的具体输入值；1.4绑定变量使用限制条件是什么提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同（包括空格,换行等）.共享的语句必须满足三个条件:

A.A.字符级的比较字符级的比较当前被执行的语句和共享池中的语句必须完全相同.例如:

SELECT*FROMEMP;和下列每一个都不同SELECT*fromEMP;Select*FromEmp;SELECT*FROMEMP;B.B.两个语句所指的对象必须完全相同两个语句所指的对象必须完全相同例如:

用户对象名如何访问Jacksal_limitprivatesynonymWork_citypublicsynonymPlant_detailpublicsynonymJillsal_limitprivatesynonymWork_citypublicsynonymStringPlant_detailtableowner考虑一下下列SQL语句能否在这两个用户之间共享.SQL能否共享原因selectmax（sal_cap）fromsal_limit;/不能，每个用户都有一个privatesynonym的sal_limit，它们是不同的对象selectcount（*）fromwork_citywheresdesclikeNEW%;/能,两个用户访问相同的对象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id;/不能,用户jack通过privatesynonym访问plant_detail而jill是表的所有者,对象不同。

C.C.两个两个SQLSQL语句中必须使用相同的名字的绑定变量语句中必须使用相同的名字的绑定变量（bind（bindvariables）variables）例如：

第一组的两个SQL语句是相同的（可以共享）,而第二组中的两个语句是不同的（即使在运行时,赋于不同的绑定变量相同的值）第一组：

selectpin,namefrompeoplewherepin=:

blk1.pin;selectpin,namefrompeoplewherepin=:

blk1.pin;第二组：

selectpin,namefrompeoplewherepin=:

blk1.ot_ind;selectpin,namefrompeoplewherepin=:

blk1.ov_ind;1.5将未使用绑定变量的语句改造成使用绑定变量时，需要做那些必要工作

（1）事先评估语句是否需要更改为绑定变量模式；需要考虑语句的执行频次是否很高，变量的非唯一值是否很多（比如大于200），变量值分布是否均匀；

（2）检查变更前的语句是否存在SQLPROFILE绑定情况；更改语句采用绑定变量，会使得SQL_ID改变，SQLPROFILE发生改变；（3）必要时在新的语句中增加HINT，强制新语句采用期望的访问路径；（4）检查SQL语句更改前后的执行计划和统计信息；更改前的语句由于采用了固定值作为过滤条件值，所以SQLID有很多个，较难按SQL_ID维度来统计执行效率，可以单个SQL对比，也可以考虑用SQLPLANHASHVALUE作为统计维度，因为hashvalue不会受变量值变化影响，决定hashvalue的一般只有SQL执行计划中表的访问方式、Join方式，统计信息方面需要重点关注的就是SQL的逻辑读、物理读等指标。

（此处可参考arsenal脚本中的sqlhist.sql脚本）2.索引使用的相关总结2.1哪些条件下适合建索引索引扫描特点是先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据。

一个rowid唯一的表示一行数据，该行对应的数据块是通过一次I/O得到，索引扫描的特点一次只会读取一个数据库块；全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件，全表扫描一次I/O可以读取多个块数据块（数据块个数由db_block_multiblock_read_count参数设定，默认128，block_size为8k的数据库，全表扫描一次I/O读取的数据量为1M），这极大的减少了I/O总次数，提高了系统的吞吐量。

考虑到索引扫描和全表扫描的特点，在表的比较小，走全表扫描可能效率要比走索引要高（比如一次I/O就可以读取完表的数据）。

当表比较大时，通过走索引去访问数据可以比较大的提升效率。

数据库开发要求里面把大小超过10M定义为大表，当表的大小超过10M，我们可以考虑通过建索引来提升访问数据效率。

2.2建复合索引需要把握的注意事项和原则（可以参考可以参考“数据库开发要求数据库开发要求”以及开发要求解读材料以及开发要求解读材料）2.2.1不得存在冗余索引例如A,B为table的两个字段，有复合索引index1（A,B）和单列索引index2（A）,可以判断index2为冗余索引,index1实际上已经包含了index2的功能，此时再建index2会增加数据库的空间开销和维护索引的开销。

2.2.2当单个字段无法过滤大量记录时应建立复合索引,非唯一索引中包含的字段不超过5个当单个字段的筛选性不是很好的情况，建议建立复合索引来增强索引的过滤效果，非唯一索引中包含字段建议不要超过5个，字段个数过多会使索引的结构更加复杂，增大索引维护的成本。

建议选择最能过滤得到所需记录的字段放入索引，而不是把查询中用到的所有字段都放入索引。

2.2.3使用异常状态类索引的查询语句必须在SQL文本中增加提示（hint）明确指定使用该索引异常状态类索引：

字段唯一值较少、少数记录有不同的特殊值例如：

state、type、status示例：

Select*fromtablewherestatus=2;Table表中staus字段值的分布特别不均匀，存在数据倾斜，status=5的记录数特别多，status=2,3,4的记录数很少，当查询status=2,3,4这些特殊的值，需要在sql文本中加hint提示，强制走status字段的索引count（*）status-323434824999999952.2.4优先将选择性最高字段放在第一位，选择性接近，在Where过滤条件中为等值查询的字段应该放在范围查询字段前面选择性高的字段为列的唯一值比较多的字段，比如1万记录数的表，某列A的唯一值有一万个，我们可以认为A字段的选择性超级好，当建立复合索引时，我们优先将这样选择性好的字段放在复合索引的前面；当两个字段选择性相近时，我们应当把等值查询的字段放在范围查询字段前面，比如有select*fromtablewhereA=2andB,tabname=,cascade=true,estimate_percent=XXX,method_opt=forallcolumnssize1）;注：

estimate_percent为采样率，XXX为10-100的数字，一般情况下建议200GB以下的表均选用XXX=100的采样率；Method_opt选项中forallcolumnssize1表示对所有字段不收集直方图。

2.3.2Where条件中的索引字段带有函数或数学运算例如：

test表ID字段建有索引，下面这些情况会走不上索引

（1）Selectstatusfromtablewheretrim（ID）=1234;-字段带有函数解决方法解决方法：

ID上面建函数索引createindexindx_nameontable（trim（ID）;

（2）Selec