linux loadavg 笔记整理
绑定变量和非绑定变量的资源消耗对比
首先初始化数据
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 stats
as select ‘STAT...‘ || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select ‘LATCH.‘ || name, gets
from v$latch
union all
select ‘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 as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_threshold in number default 0);
end;
/
create or replace package body runstats_pkg as
g_start number;
g_run1 number;
g_run2 number;
procedure rs_start is
begin
delete from run_stats;
insert into run_stats
select ‘before‘, stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_middle is
begin
g_run1 := (dbms_utility.get_cpu_time - g_start);
insert into run_stats
select ‘after 1‘, stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_stop(p_difference_threshold in number default 0) is
begin
g_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) then
dbms_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_stats
select ‘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‘) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and 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_threshold
order by abs((c.value - b.value) - (b.value - a.value))) loop
dbms_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‘) || ‘%‘ data
from (select sum(b.value - a.value) run1,
sum(c.value - b.value) run2,
sum((c.value - b.value) - (b.value - a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = ‘before‘
and b.runid = ‘after 1‘
and c.runid = ‘after 2‘
and a.name like ‘LATCH%‘)) loop
dbms_output.put_line(x.data);
end loop;
end;
end;
/
4. 用法
exec runstats_pkg.rs_start;
p1
exec runstats_pkg.rs_middle;
p2
exec 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 <= 10000 ;
已创建 10000 行。
dexter@REPO>exec runstats_pkg.rs_stop(100);
Run1 ran in 1 cpu hsecs
Run2 ran in 4 cpu hsecs
run 1 ran in 25% of the time
Name Run1 Run2 Diff
LATCH.session idle bit 331 207 -124
STAT...redo entries 15 167 152
LATCH.SQL memory manager worka 1,012 807 -205
STAT...db block changes 77 300 223
LATCH.enqueue hash chains 1,035 794 -241
LATCH.cache buffers chains 2,182 2,425 243
STAT...db block gets 60 304 244
STAT...db block gets from cach 60 304 244
STAT...session logical reads 89 374 285
LATCH.shared pool 1,016 515 -501
STAT...Elapsed Time 4,370 3,681 -689
LATCH.row cache objects 2,063 1,082 -981
STAT...undo change vector size 3,908 29,856 25,948
STAT...physical read bytes 8,192 40,960 32,768
STAT...physical read total byt 8,192 40,960 32,768
STAT...cell physical IO interc 8,192 40,960 32,768
STAT...session uga memory max 123,512 65,488 -58,024
STAT...session uga memory 65,488 0 -65,488
STAT...file io wait time 8,767 113,342 104,575
STAT...redo size 5,220 167,168 161,948
STAT...logical read bytes from 729,088 3,063,808 2,334,720
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
11,252 8,792 -2,460 127.98%
PL/SQL 过程已成功完成。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
