### Oracle里的绑定变量 使用绑定变量，是可以重用解析树和执行计划基础条件. #### 绑定变量的典型写法 ``` --sql var x number; exec :x := 7369; select ename from emp where empno= :x; --plsql declare vc_name varchar2(10); begin execute immediate 'select ename from emp whre empno= :1' into vc_name using 7369; dbms_output.put_line(vc_name); end; / --execute immediate [带绑定变量的sql] using [对应绑定变量的具体输入值] --dml语句的绑定变量 declare vc_sql_1 varchar2(4000); vc_sql_2 varchar2(4000); n_temp_1 number; n_temp_1 number; begin vc_sql_1 := 'insert into emp(empno,ename,job) values(:1,:2,:3)'; execute immediate vc_sql_1 using 7370,'CUIHUA2','DBA'; n_temp_1 := sql%rowcount; vc_sql_2 := 'insert into emp(empno,enmae,job) values(:1,:1,:1)'; execute immediate vc_sql_2 using 7371,'CUIHUA3','DBA'; n_temp_2 := sql%rowcount; dbms_output.put_line(to_char(n_temp_1+n_temp_2)); commit; end; / --using 根据位置传入相关变量参数值 --动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用，目的把受该sql影响的行记录的对应字段值给取出来. --eg declare vc_column varchar2(10); vc_sql varchar2(4000); n_temp number; vc_name varchar2(10); begin vc_column := 'empno'; vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2'; execute immediate vc_sql using 7369 returning into vc_name; dbms_output.put_line(vc_ename); commit; end; / ``` ###### PL/SQL中批量绑定的典型用法 > 主要优势，一次处理一批数据。 可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统，SQL引擎用来处理sql语句的子系统。 这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。 ``` --减少交互，提高性能 fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE> --eg "forall" 表示一次执行一批sql declare cur_emp sys_refcursor; vc_sql varchar2(4000); type namelist is table of varchar2(10); enames namelist; CN_BATCH_SIZE constant pls_integer :=1000; begin vc_sql := 'select ename from emp where empno > :1'; open cur_emp for vc_sql using 7900; loop fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE; for i in 1..enames.count loop dbms_output.put_line(enames(i)); end loop; exit when enames.count < CN_BATCH_SIZE; end loop; close cur_emp; end; / ``` ##### 绑定变量的使用原则和最佳实践 - OLTP系统，建议使用绑定变量，最好是批量使用，前台代码和后台PL/SQL代码都使用批量绑定。 - 对于OLTP/OLAP混合型，如果有循环，循环内的SQL腰使用批量绑定变量。 page 245 ### Oracle里的游标共享 未使用绑定变量再整改代价大，绑定变量的窥探影响 常规游标解决第一个问题，自适应游标共享解决第二个问题 #### 常规游标共享 ``` --参数cursor_sharing控制 --exact 默认不会自动替换 --similar 会被替换，12c之后过时，问题较多，对于它认为不安全的，都会执行一次硬解析 --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，其他只用经历一次。