跟大家講解下有關綁定變量與非綁定變量資源消耗對比，相信小伙伴們對這個話題應該也很關注吧，現在就為小伙伴們說說綁定變量與非綁定變量資源消耗對比，小編也收集到了有關綁定變量與非綁定變量資源消耗對比的相關資料，希望大家看到了會喜歡。

綁定變量和非綁定變量的資源消耗對比 首先初始化數據 Create table tsts as select level as col1 ,rdbms_random.string(p,10) as col2 from dual connect by level = 10000 ; create or replace procedure p1 as rec_tsts%rowtype ; begin for i in1 .. 100

綁定變量和非綁定變量的資源消耗對比首先初始化數據

Create table tsts as select level as col1 ,rdbms_random.string(‘p’,10) as col2 from dual connect by level <= 10000 ;

create or replace procedure p1 as

rec_tsts%rowtype ;

begin

for i in1 .. 10000 loop

execute immediate 'select * from tsts where col1='||i into rec_ ;

end loop;

end ;

create or replace procedure p2 as

rec_tsts%rowtype ;

begin

for i in1 .. 10000 loop

execute immediate 'select * from tsts where col1=:1 ' into rec_ using i;

end loop;

end ;

開始比較（使用tom的runstat腳本）

exec runstats_pkg.rs_start ;

exec p1 ;

exec runstats_pkg.rs_middle ;

exec p2 ;

exec runstats_pkg.rs_stop(1000) ;

_dexter@FAKE> exec runstats_pkg.rs_start ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec p1 ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec runstats_pkg.rs_middle ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec p2 ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec runstats_pkg.rs_stop(1000);

Run1 ran in 1163 cpu hsecs

Run2 ran in 64 cpu hsecs

run 1 ran in 1817.19% of the time

Name Run1 Run2 Diff

STAT...recursive cpu usage 1,119 59 -1,060

STAT...DB time 1,150 75 -1,075

STAT...CPU used when call star 1,173 72 -1,101

STAT...CPU used by this sessio 1,172 64 -1,108

STAT...buffer is not pinned co 31,411 30,087 -1,324

STAT...consistent gets 41,986 40,457 -1,529

STAT...consistent gets from ca 41,986 40,457 -1,529

STAT...session logical reads 42,047 40,494 -1,553

STAT...sorts (rows) 1,676 3 -1,673

LATCH.cache buffers chains 66,737 61,883 -4,854

STAT...sql area evicted 9,519 4 -9,515

STAT...session cursor cache hi 360 10,018 9,658

STAT...parse count (hard) 10,053 4 -10,049

STAT...enqueue releases 10,053 4 -10,049

STAT...enqueue requests 10,053 4 -10,049

STAT...parse count (total) 10,057 4 -10,053

LATCH.enqueue hash chains 20,379 110 -20,269

STAT...recursive calls 41,827 10,157 -31,670

LATCH.shared pool simulator 38,862 40 -38,822

LATCH.shared pool 455,526 10,683 -444,843

LATCH.row cache objects 513,487 441 -513,046

STAT...logical read bytes from 344,449,024331,726,848 -12,722,176

Run1 latches total versus runs -- difference andpct

Run1 Run2 Diff Pct

1,097,601 74,729 -1,022,872 1,468.78%

PL/SQL procedure successfully completed.

分析

可以看到資源使用相差是非常巨大的。我們示例中的sql語句是典型的應用在oltp系統中的，可以看到使用綁定變量消耗更少的資源。我們觀察相差比較大的事件。執行可以完全肯定，綁定變量優于非綁定變量。主要由于下面幾個相差較大的事件影響。

Binding

No binding

Desc

session cursor cache hits

10,018

360

cursor命中率

sql area evicted

4

9,519

Shared pool 不足引起的ageout

parse count (hard)

4

10,053

硬解析

enqueue releases

4

10,053

Enqueue鎖釋放

enqueue requests

4

10,053

Enqueue鎖請求

parse count (total)

4

10,057

解析總次數

enqueue hash chains

110

20,379

獲取hash chain次數

recursive calls

10,157

41,827

讀取數據字典信息可能引發recursive calls

shared pool simulator

40

38,862

Advice信息收集

shared pool

10,683

455,526

Shared pool使用

row cache objects

441

513,487

硬解析會讀取更多的統計信息

logical read byte

331,726,848

344,449,024

邏輯讀

可以看到，因為硬解析，oracle數據庫優化器需要重新對語句進行優化操作，需要獲取更多的latch并且執行更多的操作，而且對于oltp系統中的語句，優化期間所做的操作甚至比查詢數據需要更多的時間，所以在OLTP系統中，更多的使用綁定變量，是非常有必要的。

附錄：tom runstat 腳本的使用

1. 創建臨時表create or replace view statsas select 'STAT...' || a.name name, b.valuefrom v$statname a, v$mystat bwhere a.statistic# = b.statistic#union allselect 'LATCH.' || name, getsfrom v$latchunion allselect 'STAT...Elapsed Time', hsecs from v$timer;需要授權相應的視圖權限grant select on v_$statname to &&username ;grant select on v_$mystat to &&username ;grant select on v_$latch to &&username ;grant select on v_$timer to &&username ;2. 創建臨時表create global temporary table run_stats( runid varchar2(15),name varchar2(80),value int )on commit preserve rows;3. 創建相應的runstat包create or replace package runstats_pkg asprocedure rs_start;procedure rs_middle;procedure rs_stop(p_difference_threshold in number default 0);end;/create or replace package body runstats_pkg asg_start number;g_run1 number;g_run2 number;procedure rs_start isbegindelete from run_stats;insert into run_statsselect 'before', stats.* from stats;g_start := dbms_utility.get_cpu_time;end;procedure rs_middle isbeging_run1 := (dbms_utility.get_cpu_time - g_start);insert into run_statsselect 'after 1', stats.* from stats;g_start := dbms_utility.get_cpu_time;end;procedure rs_stop(p_difference_threshold in number default 0) isbeging_run2 := (dbms_utility.get_cpu_time - g_start);dbms_output.put_line('Run1 ran in ' || g_run1 || ' cpu hsecs');dbms_output.put_line('Run2 ran in ' || g_run2 || ' cpu hsecs');if (g_run2 0) thendbms_output.put_line('run 1 ran in ' ||round(g_run1 / g_run2 * 100, 2) ||'% of the time');end if;dbms_output.put_line(chr(9));insert into run_statsselect 'after 2', stats.* from stats;dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 12) ||lpad('Run2', 12) || lpad('Diff', 12));for x in (select rpad(a.name, 30) ||to_char(b.value - a.value, '999,999,999') ||to_char(c.value - b.value, '999,999,999') ||to_char(((c.value - b.value) - (b.value - a.value)),'999,999,999') datafrom run_stats a, run_stats b, run_stats cwhere a.name = b.nameand b.name = c.nameand a.runid = 'before'and b.runid = 'after 1'and c.runid = 'after 2'and abs((c.value - b.value) - (b.value - a.value)) >p_difference_thresholdorder by abs((c.value - b.value) - (b.value - a.value))) loopdbms_output.put_line(x.data);end loop;dbms_output.put_line(chr(9));dbms_output.put_line('Run1 latches total versus runs -- difference and pct');dbms_output.put_line(lpad('Run1', 12) || lpad('Run2', 12) ||lpad('Diff', 12) || lpad('Pct', 10));for x in (select to_char(run1, '999,999,999') ||to_char(run2, '999,999,999') ||to_char(diff, '999,999,999') ||to_char(round(run1 /decode(run2, 0, to_number(0), run2) * 100,2),'99,999.99') || '%' datafrom (select sum(b.value - a.value) run1,sum(c.value - b.value) run2,sum((c.value - b.value) - (b.value - a.value)) difffrom run_stats a, run_stats b, run_stats cwhere a.name = b.nameand b.name = c.nameand a.runid = 'before'and b.runid = 'after 1'and c.runid = 'after 2'and a.name like 'LATCH%')) loopdbms_output.put_line(x.data);end loop;end;end;/4. 用法exec runstats_pkg.rs_start;p1exec runstats_pkg.rs_middle;p2exec runstats_pkg.rs_stop(1000);示例：dexter@REPO>exec runstats_pkg.rs_start;PL/SQL 過程已成功完成。dexter@REPO>delete from t1 ;已刪除 1 行。dexter@REPO>exec runstats_pkg.rs_middle;PL/SQL 過程已成功完成。dexter@REPO>insert into t1 select level from dual connect by level exec runstats_pkg.rs_stop(100);Run1 ran in 1 cpu hsecsRun2 ran in 4 cpu hsecsrun 1 ran in 25% of the timeName Run1 Run2 DiffLATCH.session idle bit 331 207 -124STAT...redo entries 15 167 152LATCH.SQL memory manager worka 1,012 807 -205STAT...db block changes 77 300 223LATCH.enqueue hash chains 1,035 794 -241LATCH.cache buffers chains 2,182 2,425 243STAT...db block gets 60 304 244STAT...db block gets from cach 60 304 244STAT...session logical reads 89 374 285LATCH.shared pool 1,016 515 -501STAT...Elapsed Time 4,370 3,681 -689LATCH.row cache objects 2,063 1,082 -981STAT...undo change vector size 3,908 29,856 25,948STAT...physical read bytes 8,192 40,960 32,768STAT...physical read total byt 8,192 40,960 32,768STAT...cell physical IO interc 8,192 40,960 32,768STAT...session uga memory max 123,512 65,488 -58,024STAT...session uga memory 65,488 0 -65,488STAT...file io wait time 8,767 113,342 104,575STAT...redo size 5,220 167,168 161,948STAT...logical read bytes from 729,088 3,063,808 2,334,720Run1 latches total versus runs -- difference and pctRun1 Run2 Diff Pct11,252 8,792 -2,460 127.98%PL/SQL 過程已成功完成。

來源：php中文網