目录
一, SQL 执行过程简介
二, 绑定变量典型用法
2.1, 在 SQL 中绑定变量
2.2, 在 PL/SQL 中使用绑定变量
2.3,PL/SQL 批量绑定变量
2.4,Java 代码里使用绑定变量
最近在看《基于 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();
来源: https://www.cnblogs.com/mzq123/p/11556059.html