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

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

一、SQL执行过程简介

继上一篇博客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;
/

在这里插入图片描述

往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();
smileNicky CSDN认证博客专家 分布式 Java Spring
java程序员,软件工程专业,专注于sql调优,SpringBoot,spring框架学习,个人邮箱nickypm@foxmail.com,公众号nickymp
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 岁月 设计师:pinMode 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值