Oracle SQL调优系列之绑定变量用法简介

迈不过友情╰ 2021-08-19 19:35 393阅读 0赞

最近在看《基于Oracle的SQL优化一书》，并做了笔记，作者的个人博客：[http://www.dbsnake.net/][http_www.dbsnake.net]

### 文章目录 ###

*   *  一、SQL执行过程简介
     *  二、绑定变量典型用法
     *   *  2.1、在SQL中绑定变量
         *  2.2、在PL/SQL中使用绑定变量
         *  2.3、PL/SQL批量绑定变量
         *  2.4、Java代码里使用绑定变量

## 一、SQL执行过程简介 ##

继上一篇博客Oracle的cursor学习笔记：[Oracle的游标Cursor原理简介][Oracle_Cursor]，再介绍oracle的绑定变量

介绍绑定变量之前，先介绍SQL执行过程和硬解析的概念：

执行sql的过程，会将sql的文本进行hash运算，得到对象的hash值，然后拿hash值，去Hash Buckets里遍历缓存对象句柄链表，找到对应的缓存对象句柄，然后就可以得到缓存对象句柄里对应sql执行计划、解析树等对象，所以执行相同的sql第二次执行时是会比较快的，因为不需要解析获取执行计划，解析树等对象，如果找不到库缓存对象句柄，就需要重新解析，这个过程解析过多，容易造成硬解析问题

> 硬解析：是指Oracle在执行目标SQL时，在库缓存中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标SQL并生成相应的Parent Cursor和Child Cursor的过程。  
>   
> 软解析：是指Oracle在执行目标SQL时，在Library Cache中找到了匹配的Parent Cursor和Child Cursor，并将存储在Child Cursor中的解析树和执行计划直接拿过来重用，无须从头开始解析的过程。

ok，上面是SQL执行过程的简单介绍，由此可知，假如sql执行过程，在共享池里找不到执行计划、解析树等就会重现解析sql，生成执行计划和解析树等，这个过程是比较耗时间的，所以要想办法尽量不要重现解析sql，需要执行计划直接去共享池拿已经生成的

举个例子，`select * from sys_user where userid='u10001';`和`select * from sys_user where userid='u10002';`，这两个很类似的sql在执行过程，生成的执行计划很有可能是不一样的，也就是说第一条sql执行后，第二条sql继续执行，假如发现找不到对应执行计划，就会再解析sql，重现生成session cursor和一对shared cursor(parent cursor和child cursor)

然后，我们不想重新解析sql，有什么方法？方法就是用绑定变量的方法

## 二、绑定变量典型用法 ##

### 2.1、在SQL中绑定变量 ###

绑定变量的典型用法就是用 :variable\_name的形式，variable\_name是自定义的变量名称，variabl\_name可以是字母、数字或者字母和数字的组合

ok，上面的那种类型的sql，就可以用一条带绑定变量的sql来表示：

select * from sys_user where userid = :u;

这样这种类型的一堆sql都只会解析一次，不用每条sql都解析一遍，可以很好的提高系统处理能力

ok，举个例子说明

环境准备：

/* 随便建一张表*/
    create table t as select * from dba_objects;

注意，这些脚本只能在sqlplus或者PLSQL客户端的命令窗口执行

/* 定义绑定变量vid */
    SQL> variable vid number;
    /* 给绑定变量赋值为2 */
    SQL> exec :vid := 2;

在sqlplus或者PLSQL客户端的命令窗口执行

/* 通过绑定变量查询 */
    SQL> select * from t where object_id = :vid;

/*通过性能视图查询SQL解析情况*/
    select a.*, b.name
      from v$sesstat a, v$statname b
     where a.statistic# = b.statistic#
       and a.sid = (select distinct sid from v$mystat)
       and b.name like '%parse%';

/* 去共享池查询一下这种类型的SQL信息*/
    select sql_text, parse_calls, executions
      from v$sql
     where sql_text like 'select * from t where object_id=%';

/* 通过共享池查询查询最慢的10条sql*/
    SELECT *
      FROM (select PARSING_USER_ID,
                   EXECUTIONS,
                   SORTS,
                   COMMAND_TYPE,
                   DISK_READS,
                   sql_text
              FROM v$sqlarea
             order BY disk_reads DESC)
     where ROWNUM < 10;

### 2.2、在PL/SQL中使用绑定变量 ###

/* SQL语句使用绑定变量*/
    declare
      vc_empname varchar2(10);
    begin
      execute immediate 'select ename from t_emp where empno = :1'
        into vc_empname
        using 7369;
      dbms_output.put_line(vc_empname);
    end;
    /

![在这里插入图片描述][20190919193400518.png]

往t\_emp表写入一条数据，并统计是否执行成功，返回数值

/*DML语句使用绑定变量*/
    declare
     vc_sql varchar2(2000);
     vc_number number;
    begin 
      vc_sql := 'insert into t_emp(empno,ename,job) values(:1,:2,:3)';
      execute immediate vc_sql using 7990,'SMITH','HR';
      vc_number := sql%rowcount;
      dbms_output.put_line(to_char(vc_number));
      commit;
    end;
    /

所以绑定变量在pl/sql里的核心语法为：

execute immediate [sql语句] using [变量]

### 2.3、PL/SQL批量绑定变量 ###

例子来自《基于Oracle的SQL优化》一书，要实现的的是批量绑定变量，fetch关键字，将empno大于7900的职员信息打印出来

declare
     cur_emp sys_refcursor;
     vc_sql varchar2(2000);
     type namelist is table of varchar2(10);
     enames namelist;
     CN_BATCH_SIZE constant pls_integer := 1000;
     begin
      vc_sql:= 'select ename from t_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;
    /

### 2.4、Java代码里使用绑定变量 ###

不用绑定变量的写法：

String empno = '7369';
    String query_sql = 'select ename from t_emp where empno = 7369 '; 
    stmt = con.prepareStatement( query_sql );
    stmt.executeQuery();

使用绑定变量的写法：

String empno = 'xxxxx';
    String query_sql = 'select ename from t_emp where empno = ? '; //嵌入绑定变量
    stmt = con.prepareStatement( query_sql );
    stmt.setString(1, empno ); //为绑定变量赋值
    stmt.executeQuery();

批量绑定变量写法：  
此例子来自《基于Oracle的SQL优化》一书：

String vc_sql = 'update t_emp set sal = ? where empno = ?';
    pstmt = connection.prepareStatement(dml);
    pstmt.clearBatch();
    for (int i = 0; i < UPDATE_COUNT; ++ i) {
    	pstmt.setInt(1, generateEmpno(i));
    	pstms.setInt(2, generateSal(i));
    	pstmt.addBatch();
    }
    pstmt.executeBatch();
    connection.commit();

[http_www.dbsnake.net]: http://www.dbsnake.net/
[Oracle_Cursor]: https://smilenicky.blog.csdn.net/article/details/99946785
[20190919193400518.png]: /images/20210813/b6697aea68014aa2963dee0d75752d81.png