RMAN备份恢复系列1: Oracle 10g rac asm数据库恢复到10g单实例数据库


绑定变量和非绑定变量的资源消耗对比

首先初始化数据

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 过程已成功完成。



RMAN备份恢复系列1: Oracle 10g rac asm数据库恢复到10g单实例数据库,布布扣,bubuko.com

RMAN备份恢复系列1: Oracle 10g rac asm数据库恢复到10g单实例数据库

上一篇:mysql5.5异常: java.sql.SQLException: Packet for query is too large (1055975 > 1048576).


下一篇:Oracle触发器修改自身字段解决方案