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

2022-10-11 08:52:42

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

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

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

  1. --减少交互，提高性能
  2. fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>
  4. --eg "forall" 表示一次执行一批sql
  5. declare
  6. cur_emp sys_refcursor;
  7. vc_sql varchar2(4000);
  8. type namelist is table of varchar2(10);
  9. enames namelist;
  10. CN_BATCH_SIZE constant pls_integer :=1000;
  11. begin
  12. vc_sql := 'select ename from emp where empno > :1';
  13. open cur_emp for vc_sql using 7900;
  14. loop
  15. fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
  17. for i in 1..enames.count loop
  18. dbms_output.put_line(enames(i));
  19. end loop;
  20. exit when enames.count < CN_BATCH_SIZE;
  21. end loop;
  22. close cur_emp;
  23. end;
  24. /
绑定变量的使用原则和最佳实践
  • 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，其他只用经历一次。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29487349/viewspace-2786117/

上一篇：【ORACLE】Oracle绑定变量知识梳理


下一篇：Vue组件化编程