Oracle SQL調優系列之綁定變量用法簡介

文章目錄

一、SQL執行過程簡介

二、綁定變量典型用法

2.1、在SQL中綁定變量

2.2、在PL/SQL中使用綁定變量

2.3、PL/SQL批量綁定變量

2.4、Java代碼里使用綁定變量

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

1

這樣這種類型的一堆sql都只會解析一次，不用每條sql都解析一遍，可以很好的提高系統處理能力

ok，舉個例子說明

環境準備：

/* 隨便建一張表*/ create table t as select * from dba_objects;

1

2

注意，這些腳本只能在sqlplus或者PLSQL客戶端的命令窗口執行

/* 定義綁定變量vid */ SQL> variable vid number; /* 給綁定變量賦值為2 */ SQL> exec :vid := 2;

1

2

3

4

在sqlplus或者PLSQL客戶端的命令窗口執行

/* 通過綁定變量查詢 */ SQL> select * from t where object_id = :vid;

1

2

/*通過性能視圖查詢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%';

1

2

3

4

5

6

/* 去共享池查詢一下這種類型的SQL信息*/ select sql_text, parse_calls, executions from v$sql where sql_text like 'select * from t where object_id=%';

1

2

3

4

/* 通過共享池查詢查詢最慢的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;

1

2

3

4

5

6

7

8

9

10

11

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; /

1

2

3

4

5

6

7

8

9

10

往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; /

1

2

3

4

5

6

7

8

9

10

11

12

所以綁定變量在pl/sql里的核心語法為：

execute immediate [sql語句] using [變量]

1

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; /

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

2.4、Java代碼里使用綁定變量

不用綁定變量的寫法：

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

1

2

3

4

使用綁定變量的寫法：

String empno = 'xxxxx'; String query_sql = 'select ename from t_emp where empno = ? '; //嵌入綁定變量 stmt = con.prepareStatement( query_sql ); stmt.setString(1, empno ); //為綁定變量賦值 stmt.executeQuery();

1

2

3

4

5

批量綁定變量寫法：

此例子來自《基于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();

1

2

3

4

5

6

7

8

9

10

Oracle SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。