（tom大师的）定制脚本runstat和mystat和show_space

Runstat需要访问的几个视图：
stats视图

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;

runstat临时小表，用于收集统计信息：

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

最后创建runstat包，

It contains three simple API calls:
• RS_START (Runstats Start) to be called at the beginning of a Runstats test
• RS_MIDDLE to be called in the middle, as you might have guessed
• RS_STOP to finish off and print the report

 

create or replace package runstats_pkg as
  procedure rs_start;
  procedure rs_middle;
  procedure rs_stop(p_difference_threshold in number default 0);
end;
/
Package created.


下面是包体：


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;

Mystat脚本：
Mystat.sql和相应的mystat2.sql用于展示完成操作之前和之后的某些oracle“统计结果”的变化情况。Mystat.sql只是获得统计结果的开始值：
Mystat.sql

set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

mystat2.sql用于报告差值（通过运行第一个脚本mystat.sql来填充&V，为此它使用了SQL*PLUS NEW_VAL特性，其中包含由上一个查询选择的最后一个value）
mystat2.sql

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

例如：要查看某个UPDATE生成的redo数，可以使用以下命令：

big_table@ORA11GR2> @mystat "redo size"
big_table@ORA11GR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 496
big_table@ORA11GR2> update big_table set owner = lower(owner)
2 where rownum <= 1000;
1000 rows updated.
big_table@ORA11GR2> @mystat2
big_table@ORA11GR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------------
redo size 89592 89,096

This shows our UPDATE of 1,000 rows generated 89,096 bytes of redo.
1000行记录的update会产生89096字节的redo；

SHOW SPACE
用于打印数据库段的空间利用率信息，其接口如下：

ops$tkyte@ORA11GR2> desc show_space
PROCEDURE show_space
Argument  Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SEGNAME VARCHAR2   IN
P_OWNER   VARCHAR2   IN    DEFAULT
P_TYPE    VARCHAR2   IN    DEFAULT
P_PARTITION VARCHAR2 IN    DEFAULT


The arguments are as follows:
• P_SEGNAME - Name of the segment—the table or index name, for example.
• P_OWNER - Defaults to the current user, but you can use this routine to look at some other schema.
• P_TYPE - Defaults to TABLE and represents the type of object you are looking at. For example,
select distinct segment_type from dba_segments lists valid segment types.
• P_PARTITION - Name of the partition when you show the space for a partitioned object. SHOW_SPACE
shows space for only a partition at a time.

一个例子的输出如下：这里段位于一个自动段空间管理（Automatic Segment
Space Management,ASSM）（下面的big_table在后面有脚本）

big_table@ORA11GR2> exec show_space('BIG_TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 14,469
Total Blocks............................ 15,360
Total Bytes............................. 125,829,120
Total MBytes............................ 120
Unused Blocks........................... 728
Unused Bytes............................ 5,963,776
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 43,145
Last Used Block......................... 296




The items reported are as follows:
• Unformatted Blocks – The number of blocks that are allocated to the table below the high water
mark, but have not been used. Add unformatted and unused blocks together to get a total count
of blocks allocated to the table but never used to hold data in an ASSM object.
• FS1 Blocks-FS4 Blocks – Formatted blocks with data. The ranges of numbers after their name
represent the emptiness of each block. For example, (0-25) is the count of blocks that are between
0 and 25 percent empty.
• Full Blocks – The number of blocks that are so full that they are no longer candidates for future
inserts.
• Total Blocks, Total Bytes, Total Mbytes - The total amount of space allocated to the segment
measured in database blocks, bytes, and megabytes.
• Unused Blocks, Unused Bytes – Represents a portion of the amount of space never used. These are
blocks allocated to the segment but are currently above the high water mark of the segment
• Last Used Ext FileId – The file ID of the file that contains the last extent that contains data.
• Last Used Ext BlockId – The block ID of the beginning of the last extent; the block ID within the
last-used file.
• Last Used Block – The block ID offset of the last block used in the last extent.


如果对象在用户空间管理的表空间中，使用show_space查看时：


big_table@ORA11GR2> exec show_space( 'BIG_TABLE' )
Free Blocks............................. 1
Total Blocks............................ 147,456
Total Bytes............................. 1,207,959,552
Total MBytes............................ 1,152
Unused Blocks........................... 1,616
Unused Bytes............................ 13,238,272
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 139,273
Last Used Block......................... 6,576
PL/SQL procedure successfully completed.

这里唯一的区别是报告中最前面的free blocks项，这是段的第一个freelist（自由列表）组中的块数，

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)
-- this procedure uses authid current user so it can query DBA_*
  -- views using privileges from a ROLE and so it can be installed
  -- once per database, instead of once per user that wants to use it
authid current_user 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;
  -- inline procedure to print out numbers nicely formatted
  -- with a simple label
  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
  -- this query is executed dynamically in order to allow this procedure
  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
  -- via a role as is customary.
  -- NOTE: at runtime, the invoker MUST have access to these two
  -- views!
  -- this query determines if the object is an ASSM object or not
  begin
    execute immediate 'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
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;
  -- if the object is in an ASSM tablespace, we must use this API
  -- call to get space information, else we use the FREE_BLOCKS
  -- API for the user managed segments
  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('FS1 Blocks (0-25) ', l_fs1_blocks);
    p('FS2 Blocks (25-50) ', l_fs2_blocks);
    p('FS3 Blocks (50-75) ', l_fs3_blocks);
    p('FS4 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;
/

Big_table脚本：

create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

 

alter table big_table add constraint
big_table_pk primary key(id);


exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);