oracle 测试用相关函数

柔情只为你懂 2021-09-27 23:46 286阅读 0赞

### 文章目录 ###

*  语句基本来自tom的书。
 *  runstats 比较两个方法的优劣
 *   *  通过几个监视动态性能的表来建立一个新的视图以及一个存储中间过程的表
     *  创建一套存储过程来监控运行状态
     *  使用示例
     *   *  上述例子所得出的结论
     *  修改记录
 *  mystat
 *  show\_space
 *  big\_table

# 语句基本来自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;