Oracle SQL調優之綁定變數用法簡介

2019 年 10 月 3 日
筆記

一、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();