C/C++教程

【ORACLE】Oracle绑定变量知识梳理

本文主要是介绍【ORACLE】Oracle绑定变量知识梳理,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

Oracle里的绑定变量

使用绑定变量,是可以重用解析树和执行计划基础条件.

绑定变量的典型写法

  1. --sql
  2. var x number;
  3. exec :x := 7369;
  4. select ename from emp where empno= :x;
  5. --plsql
  6. declare
  7. vc_name varchar2(10);
  8. begin
  9. execute immediate 'select ename from emp whre empno= :1' into vc_name using 7369;
  10. dbms_output.put_line(vc_name);
  11. end;
  12. /
  13. --execute immediate [带绑定变量的sql] using [对应绑定变量的具体输入值]
  14. --dml语句的绑定变量
  15. declare
  16. vc_sql_1 varchar2(4000);
  17. vc_sql_2 varchar2(4000);
  18. n_temp_1 number;
  19. n_temp_1 number;
  20. begin
  21. vc_sql_1 := 'insert into emp(empno,ename,job) values(:1,:2,:3)';
  22. execute immediate vc_sql_1 using 7370,'CUIHUA2','DBA';
  23. n_temp_1 := sql%rowcount;
  24. vc_sql_2 := 'insert into emp(empno,enmae,job) values(:1,:1,:1)';
  25. execute immediate vc_sql_2 using 7371,'CUIHUA3','DBA';
  26. n_temp_2 := sql%rowcount;
  27. dbms_output.put_line(to_char(n_temp_1+n_temp_2));
  28. commit;
  29. end;
  30. /
  31. --using 根据位置传入相关变量参数值
  32. --动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用,目的把受该sql影响的行记录的对应字段值给取出来.
  33. --eg
  34. declare
  35. vc_column varchar2(10);
  36. vc_sql varchar2(4000);
  37. n_temp number;
  38. vc_name varchar2(10);
  39. begin
  40. vc_column := 'empno';
  41. vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
  42. execute immediate vc_sql using 7369 returning into vc_name;
  43. dbms_output.put_line(vc_ename);
  44. commit;
  45. end;
  46. /
PL/SQL中批量绑定的典型用法

主要优势,一次处理一批数据。

可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统,SQL引擎用来处理sql语句的子系统。 这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。

  1. --减少交互,提高性能
  2. fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>
  3. --eg "forall" 表示一次执行一批sql
  4. declare
  5. cur_emp sys_refcursor;
  6. vc_sql varchar2(4000);
  7. type namelist is table of varchar2(10);
  8. enames namelist;
  9. CN_BATCH_SIZE constant pls_integer :=1000;
  10. begin
  11. vc_sql := 'select ename from emp where empno > :1';
  12. open cur_emp for vc_sql using 7900;
  13. loop
  14. fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
  15. for i in 1..enames.count loop
  16. dbms_output.put_line(enames(i));
  17. end loop;
  18. exit when enames.count < CN_BATCH_SIZE;
  19. end loop;
  20. close cur_emp;
  21. end;
  22. /
绑定变量的使用原则和最佳实践
  • OLTP系统,建议使用绑定变量,最好是批量使用,前台代码和后台PL/SQL代码都使用批量绑定。
  • 对于OLTP/OLAP混合型,如果有循环,循环内的SQL腰使用批量绑定变量。

page 245

Oracle里的游标共享

未使用绑定变量再整改代价大,绑定变量的窥探影响
常规游标解决第一个问题,自适应游标共享解决第二个问题

常规游标共享

  1. --参数cursor_sharing控制
  2. --exact 默认不会自动替换
  3. --similar 会被替换,12c之后过时,问题较多,对于它认为不安全的,都会执行一次硬解析
  4. --force 强制,不再适用于Oracle11g及后续版本

如果想在不改变一行代码的情况下,设置成force可以,虽然不是理想的方案,省事。

自适应游标

启动了绑定变量窥探的情况前提条件下,让目标SQL在其可能的多个执行计划之间“自适应”地做出选择,而不再像之前那样必须刻板地沿用该sql硬解析时所产生的解析树和执行计划。

扩展游标共享做的第一件事情就是将目标sql所对应的Child Cursor标记为Bind Sensitive。 它就是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定 变量输入值的变化而变化。

当满足如下三个条件时,目标sql所对应的Child Cursor就会被Oracle标记为Bind Sensitive

  • 启动了绑定变量窥探
  • 该SQL使用了绑定变量(不管是sql自带还是开启常规游标共享后系统产生的)
  • 该SQL使用的是不安全的为此条件(例如范围查询,目标列上有直方图统计信息的等值查询等)

自适应游标共享要做的第二件事将目标SQL所对应的Child Cursor标记为Bind Aware。指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化

当满足一下两个条件时,目标SQL所对应的子游标就会被标记为Bind Aware

  • 该SQL所对应的Child Cursor在之前已经被标记为Bind Sensitive
  • 该SQL在接下来连续两次执行时,所对应的runtime统计信息与该sql之前硬解析时所对应的runtime统计信息均存在较大差异。

对于自适应游标共享而言,v$sql中列IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE分别用来表示Child Cursor是和否,及是否共享。共享的含义是指存储在该Child Cursor中的解析树和执行计划是否能被重用。非共享不能被重用,并且也会在第一时间被age out出shared Pool.

与自适应游标共享两个重要视图,v$sql_cs_statistics(用于显示指定Child Cursor中存储的runtime统计信息) v$sql_cs_selectivity(用于显示指定的、已经被标记为Bind Aware的子游标中存储的含绑定变量的为此条件所对应的可选择率的范围)

自适应游标的整体流程
  1. 第一次执行,硬解析,根据一系列条件(如绑定变量、相关参数、是否有直方图等)来判断是否将该sql所对应的子游标标记为Bind Sensitive。标记后,对象的runtime统计信息额外地存储在该sql的子游标中。
  2. 第二次执行,软解析,重用该sql第一次子游标的解析树和执行计划
  3. 第三次执行时,如该sql对应的子游标被标记为Bind Sensitive,同时第二次和第三次记录的runtime统计信息和该sql第一次硬解析时所记录的runtime统计信息均存在较大差异,第三次会使用硬解析。产生新的子游标,且Oracle会标记新的子游标为Bind Aware。
  4. 标记为Bind Aware的子游标所对应的目标sql,当该sql再次被执行时,Oracle就会根据当前传入的绑定变量值所对应的谓词条件可选择率,来决定此时使用哪种解析方式。
  5. 如果硬解析,且所产生的执行计划和原有子游标相同,则除了生成新的子游标之外,还会把存储相同执行计划的原有子游标标记为非共享。

隐患:可能导致更多硬解析,因为需要对比可选择率。 可能导致一定数量的额外的子游标挂在同一个父游标下。这会增加软解析时查找匹配子游标的工作量。建议增大shared pool,或禁止自适应游标

一条sql绑定变量超过14个时,oracle11g不会使用绑定变量。

Oracle里的应用类型

应用类型一 硬解析

  • 没有使用绑定变量,系统硬解析的比率非常高
  • 性能和可扩展性是最差的,可用于olap类型系统
  • 将CURSOR_SHARING设置为force,可以有效降低硬解析,提高系统的性能和可扩展性

应用类型二 软解析

每次都会经历 Open Parse,Bind,Execute,Fetch Close

参数 SESSION_CACHED_CURSORS 修改为较大值,能进一步提升性能。如果为0,无法通过已缓存的Session Cursor中的父游标来建立目标sql的Session Cursor和父游标之间的联系。

  • 有效降低了硬解析
  • 执行时,其对应的Session Cursor都需要经历Open Parse,Bind,Execute,Fetch Close
  • 由于软解析不断的open,close,以及对库缓存相关Latch的争用(Oracle11g之前的版本),会影响性能
  • SESSION_CACHED_CURSORS 修改为较大值,能进一步提升性能
  • 纯粹的软解析很少见,SESSION_CACHED_CURSORS默认非0

应用类型三 软软解析

当Session Cursor对应的sql解析和执行的次数超过3次,Oracle就不会对上述sql执行Close操作,而是标记为Soft Closed.

  • open和Close只需经历一次,其他都需要经历,如Parse,Bind,Execute,Fetch

应用类型四 一次解析,多次执行

该类型通过一些手段:PL/SQL代码的循环内部执行目标SQL,使得每一条SQL语句所对应的Session Cursor会反复经历Execute和Fetch,其他只用经历一次。

这篇关于【ORACLE】Oracle绑定变量知识梳理的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!