oracle 测试用相关函数

语句基本来自tom的书。

• 按照个人习惯做了一些小修改
• 所有测试全部在PL/SQL Developer中进行

runstats 比较两个方法的优劣

通过几个监视动态性能的表来建立一个新的视图以及一个存储中间过程的表

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; 

create global temporary table run_stats(
runid varchar2(15),
name varchar2(80),
value int
)
on commit preserve rows;

创建一套存储过程来监控运行状态

create or replace package runstats_pkg
as
       procedure rs_start;
       procedure rs_middle;
       procedure rs_end;
       procedure rs_report(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
           execute immediate 'truncate table run_stats';
           
           insert into run_stats
           select 'before', stats.* from stats;
           
           commit;
           
           g_start := dbms_utility.get_cpu_time;
       end;
       
       procedure rs_middle
       is
       begin
           g_run1 := (dbms_utility.get_cpu_time - g_start);
           
           delete from run_stats t1 where t1.runid = 'after 1';
           insert into run_stats
           select 'after 1', stats.* from stats;
           commit;
           
           g_start := dbms_utility.get_cpu_time;
       end;
       
       procedure rs_end
       is
       begin
           g_run2 := (dbms_utility.get_cpu_time - g_start);
           
           delete from run_stats t where t.runid = 'after 2';
           insert into run_stats
           select 'after 2', stats.* from stats;
           commit;
           
           g_start := dbms_utility.get_cpu_time;
       end;
       
       procedure rs_report(p_difference_threshold in number default 0)
       is 
       begin
           
           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));
           
           
           
           dbms_output.put_line
           (rpad('Name', 30) || lpad('Run1', 12) || lpad('Run2', 12) || lpad('Diff', 12) || lpad('Per', 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') ||
                  to_char(round(decode((c.value - b.value), 0, 0, (b.value - a.value) / (c.value - b.value)) * 100, 2), '999,990.99') || '%' 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;

使用示例

按顺序逐条执行下列语句

create table test_big1 as select*from all_objects where 1 = 2;
create table test_big2 as select*from all_objects where 1 = 2;



begin 
    runstats_pkg.rs_start;
end;

insert into test_big1 select*from all_objects where rownum < 100000;
commit;

begin 
    runstats_pkg.rs_middle;
end;

begin 
  for x in (select*from all_objects where rownum < 100000) loop
      insert into test_big2 values x;
  end loop;
  commit;
end;

begin 
    runstats_pkg.rs_end;
end;

begin 
    runstats_pkg.rs_report(100000);--只显示大于传入参数的差异指标，需要根据自身硬件配置与所执行语句进行调整
end;

最后会输出这样一个报告（具体信息会根据硬件的差异而有所不同）。

Run1 ran in 246 cpu hsecs
Run2 ran in 816 cpu hsecs
run 1 ran in 30.15% of the time
	
Name                                  Run1        Run2        Diff         Per
STAT...db block gets from cach       4,660     104,815     100,155       4.45%
STAT...db block gets                 4,660     104,815     100,155       4.45%
STAT...recursive calls               7,648     108,648     101,000       7.04%
STAT...session logical reads       142,706     243,756     101,050      58.54%
LATCH.KJCT flow control latch      102,728     209,068     106,340      49.14%
STAT...session pga memory           65,536     -65,536    -131,072    -100.00%
LATCH.simulator hash latch         166,859     351,551     184,692      47.46%
STAT...db block changes              3,929     203,297     199,368       1.93%
LATCH.row cache objects             64,703     286,419     221,716      22.59%
LATCH.KCL gc element parent la     208,279     496,675     288,396      41.93%
STAT...table scan rows gotten      967,049   1,272,457     305,408      76.00%
LATCH.gcs remastering latch         70,303     400,166     329,863      17.57%
LATCH.gcs resource hash            145,950     519,769     373,819      28.08%
LATCH.cache buffers lru chain      383,463     907,621     524,158      42.25%
LATCH.object queue header oper     458,659   1,078,847     620,188      42.51%
LATCH.gcs partitioned table ha     355,457   2,275,525   1,920,068      15.62%
LATCH.process queue reference    2,219,518   4,352,024   2,132,506      51.00%
LATCH.cache buffers chains       4,934,294  11,119,628   6,185,334      44.37%
STAT...undo change vector size     274,832   6,818,300   6,543,468       4.03%
STAT...redo size                12,433,288  38,792,164  26,358,876      32.05%
	
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
  10,046,208  23,515,003  13,468,795     42.72%

上述例子所得出的结论

将一个功能用一条语句执行与用多条语句执行相比，在效率上有压倒性的优势。参考block、redo、undo以及更关键的latch指标。
当然，这套功能的实际作用远不止于此。

修改记录

对比原书所进行的改造

• 添加了一些commit
• 添加了一些delete语句
• 将stop拆成了end与report

mystat

用于展示完成某些操作前后的某些统计结果的变化情况。功能上看，应该属于上面的runstats的子集。书中使用sqlplus写的，我这边改成了存储过程。

create or replace package mystat
as
       procedure mystat1(item in varchar2);
       procedure mystat2;
end;

create or replace package body mystat as

       procedure mystat1(item in varchar2)
       is
       begin
           delete from mystat_temp;
           insert into mystat_temp 
           select t1.NAME, t2.VALUE
             from v$statname t1, v$mystat t2
            where t1.STATISTIC# = t2.STATISTIC#
              and lower(t1.NAME) like '%' || lower(item) || '%'
           ;
           commit;
           
       end;

       procedure mystat2
       is
       begin
           dbms_output.put_line(rpad('name', 40) || lpad('value', 20));
       
           for x in (
               select t1.NAME, to_char(t2.VALUE - t3.value, '999,999,990.99') diff
                 from v$statname t1, v$mystat t2, mystat_temp t3
                where t1.STATISTIC# = t2.STATISTIC#
                  and lower(t1.NAME) = lower(t3.name)
                order by abs(t2.VALUE - t3.value) desc
           ) loop
           
             dbms_output.put_line(rpad(x.NAME, 40) || lpad(x.diff, 20));
           end loop;
           
       end;

end mystat;

调用

begin
       mystat.mystat1('redo');
end;

insert into test_big1 select*from all_objects where rownum < 100000;
commit;

begin
       mystat.mystat2;
end;

show_space

又是一个NB的家伙，主要用于打印段的空间利用率信息。

create or replace procedure show_space
(
p_segname in varchar2,
p_owner   in varchar2 default user,
p_type    in varchar2 default 'TABLE',
p_partition in varchar2 default null
) as
  l_free_blks           number;
  l_total_blocks        number;
  l_total_bytes         number;
  l_unused_blocks       number;
  l_unused_bytes        number;
  l_lastUsedExtFileId   number;
  l_lastUsedExtBlockId  number;
  l_last_used_block     number;
  l_segment_space_mgmt  varchar2(255);
  l_unformatted_blocks  number;
  l_unformatted_bytes   number;
  l_fs1_blocks number;  l_fs1_bytes number;
  l_fs2_blocks number;  l_fs2_bytes number;
  l_fs3_blocks number;  l_fs3_bytes number;
  l_fs4_blocks number;  l_fs4_bytes number;
  l_full_blocks number;  l_full_bytes number;
  
  procedure p(p_label in varchar2, p_num in number) 
  is
  begin
    dbms_output.put_line(rpad(p_label, 40, '.') || to_char(p_num, '999,999,999,999'));
  end;
begin
  begin
  --this query determines if the object is an ASSM or not
  execute immediate '
      select ts.segment_space_management
        from dba_segments seg, dba_tablespaces ts
       where seg.segment_name = :p_segment
         and (:p_partition is null or seg.partition_name = :p_partition)
         and seg.owner = :p_owner
         and seg.tablespace_name = ts.tablespace_name
  ' into l_segment_space_mgmt
  using p_segname, p_partition, p_partition, p_owner;
  
  exception 
      when too_many_rows then
           dbms_output.put_line('this must be a partitioned table, use p_partition => ');
           return;
  end;
  
  dbms_output.put_line('test:  ASSM flag = ' || l_segment_space_mgmt);
  
  if l_segment_space_mgmt = 'AUTO'
  then
    dbms_space.space_usage(
      p_owner, p_segname, p_type, l_unformatted_blocks,
      l_unformatted_bytes, 
      l_fs1_blocks, l_fs1_bytes,
      l_fs2_blocks, l_fs2_bytes,
      l_fs3_blocks, l_fs3_bytes,
      l_fs4_blocks, l_fs4_bytes,
      l_full_blocks, l_full_bytes,
      p_partition
    );
    
    p('Unformatted Blocks', l_unformatted_blocks);
    p('FS1 Blocks (0-25)', l_fs1_blocks);
    p('FS1 Blocks (25-50)', l_fs2_blocks);
    p('FS1 Blocks (50-75)', l_fs3_blocks);
    p('FS1 Blocks (75-100)', l_fs4_blocks);
    p('Full Blocks', l_full_blocks);
    
  else 
    
    dbms_space.free_blocks(
      segment_owner    => p_owner,
      segment_name     => p_segname,
      segment_type     => p_type,
      freelist_group_id => 0,
      free_blks        => l_free_blks
    );
    
    p('Free Blocks', l_free_blks);
  
  end if;
  
  --and then the unused space API call to get the rest of the information
  
  dbms_space.unused_space(
    segment_owner        => p_owner,
    segment_name         => p_segname,
    segment_type         => p_type,
    partition_name       => p_partition,
    total_blocks         => l_total_blocks,
    total_bytes          => l_total_bytes,
    unused_blocks        => l_unused_blocks,
    unused_bytes         => l_unused_bytes,
    last_used_extent_file_id  => l_lastUsedExtFileId,
    last_used_extent_block_id => l_lastUsedExtBlockId,
    last_used_block           => l_last_used_block
  );
  
  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_lastUsedExtFileId);
  p('Last Used Ext BlockId', l_lastUsedExtBlockId);
  p('Last Used Block', l_last_used_block);
  
end show_space;

调用

begin 
  show_space('TEST_BIG1');
end;

输出

test:  ASSM flag = AUTO
Unformatted Blocks......................               0
FS1 Blocks (0-25).......................               0
FS1 Blocks (25-50)......................               0
FS1 Blocks (50-75)......................               1
FS1 Blocks (75-100).....................               8
Full Blocks.............................             397
Total Blocks............................             416
Total Bytes.............................      13,631,488
Total MBytes............................              13
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................           3,334
Last Used Ext BlockId...................           4,101
Last Used Block.........................              32

big_table

创建大数据量表的一个方法，比较简单，直接自己写了一个存储过程版本的。

create or replace procedure p_insert_big_data(p_rows in number, p_table_name in varchar2) is
  
  l_cnt   number;
  l_rows  number := p_rows;

begin

  execute immediate '
  insert /*+ apppend*/
  into ' || p_table_name || '
  select rownum, t1.*
    from all_objects t1
   where rownum <= ' || p_rows || '
  ';
  
  l_cnt := sql%rowcount;
  commit;
  
  while(l_cnt < l_rows)
  loop
      execute immediate '
      insert /*+ apppend*/
      into ' || p_table_name || '
      select ' || l_cnt || ' + rownum, t1.*
        from all_objects t1
       where rownum <= ' || (l_rows - l_cnt) || '
      ';
      
      dbms_output.put_line(sql%rowcount);
      l_cnt := l_cnt + sql%rowcount;
      commit;
  end loop;
  
end p_insert_big_data;

调用

create table test_big_table1 as 
select rownum id, t1.* from all_objects t1 where 1 = 2;

begin 
  p_insert_big_data(321, 'TEST_BIG_TABLE1');
end;