runstats相关

V$STATNAME

显示 V$SESSTAT 和 V$SYSSTAT 表中的统计信息名称

This view displays decoded statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT tables.

On some platforms, the NAME and CLASS columns contain additional operating system-specific statistics.

Column	Datatype	Description
STATISTIC#	NUMBER	Statistic number ( 统计编号 ) Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
NAME	VARCHAR2(64)	统计名
CLASS	NUMBER	A number representing one or more statistics classes. The following class numbers are additive: 1 - User 2 - Redo 4 - Enqueue 8 - Cache 16 - OS 32 - Real Application Clusters 64 - SQL 128 - Debug
STAT_ID	NUMBER	Identifier of the statistic ( 统计标识符 )

V$MYSTAT

显示当前 session 的统计信息

Column	Datatype	Description
SID	NUMBER	当前 session ID
STATISTIC#	NUMBER	统计编号
VALUE	NUMBER	统计值

-- 显示当前session 的统计信息

SELECT 'STAT...' || a.NAME NAME, b.VALUE

FROM v$statname a, v$mystat b

WHERE a.STATISTIC# = b.STATISTIC#

V$LATCH

显示总的以 latch 名称分组的 latch 统计信息，包括父 latch 与子 latch, 单独的父 latch 与子 latch 统计信息分别在 V$LATCH_PARENT , V$LATCH_CHILDREN 视图

V$LATCH shows aggregate latch statistics for both parent and child latches, grouped by latch name. Individual parent and child latch statistics are broken down in the views V$LATCH_PARENT and V$LATCH_CHILDREN .

Column	Datatype	Description
ADDR	RAW(4 | 8)	Address of the latch object
LATCH#	NUMBER	Latch number
LEVEL#	NUMBER	Latch level
NAME	VARCHAR2(50)	Latch name
HASH	NUMBER	Latch hash
GETS	NUMBER	Number of times the latch was requested in willing-to-wait mode latch 被请求的次数
MISSES	NUMBER	Number of times the latch was requested in willing-to-wait mode and the requestor had to wait
SLEEPS	NUMBER	Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch
IMMEDIATE_GETS	NUMBER	Number of times a latch was requested in no-wait mode
IMMEDIATE_MISSES	NUMBER	Number of times a no-wait latch request did not succeed (that is, missed)
WAITERS_WOKEN	NUMBER	This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.
WAITS_HOLDING_LATCH	NUMBER	This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero.
SPIN_GETS	NUMBER	Willing-to-wait latch requests which missed the first try but succeeded while spinning
SLEEP[1 | 2 | 3]	NUMBER	These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns; they will always have a value of zero. As a substitute for this column you can query the appropriate rows of the V$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:% .
SLEEP4	NUMBER	This column has been deprecated and is present only for compatibility with previous releases of Oracle. No data is accumulated for this column; it will always have a value of zero. As a substitute for this column you can query the appropriate rows of the V$EVENT_HISTOGRAM view where the EVENT column has a value of latch free or latch:% .
SLEEP[5 | 6 | 7 | 8 | 9 | 10 | 11]	NUMBER	These columns have been deprecated and are present only for compatibility with previous releases of Oracle. No data is accumulated for these columns.
WAIT_TIME	NUMBER	Elapsed time spent waiting for the latch (in microseconds)

$TIMER

This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days).

Column	Datatype	Description
HSECS	NUMBER	Elapsed time in hundredths of a second

DBMS_UTILITY

GET_TIME Function

This function determines the current time in 100th's of a second. This subprogram is primarily used for determining elapsed time. The subprogram is called twice – at the beginning and end of some process – and then the first (earlier) number is subtracted from the second (later) number to determine the time elapsed.

Syntax

DBMS_UTILITY.GET_TIME

RETURN NUMBER;

Return Values

Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.

Usage Notes

Numbers are returned in the range -2147483648 to 2147483647 depending on platform and machine, and your application must take the sign of the number into account in determining the interval. For instance, in the case of two negative numbers, application logic must allow that the first (earlier) number will be larger than the second (later) number which is closer to zero. By the same token, your application should also allow that the first (earlier) number be negative and the second (later) number be positive.

GET_CPU_TIME Function

This function returns the current CPU time in 100th's of a second. The returned CPU time is the number of 100th's of a second from some arbitrary epoch.

Syntax

DBMS_UTILITY.GET_CPU_TIME

RETURN NUMBER;

Return Values

Time is the number of 100th's of a second from some arbitrary epoch.

RunStats
赋于scott用户访问v$视图的权限
GRANT SELECT ON v_$statname to scott;
GRANT SELECT ON v_$mystat to scott;
GRANT SELECT ON v_$latch to scott;
GRANT CREATE VIEW to scott;
创建视图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;
创建存放统计信息的临时表：
drop table run_stats;
create global temporary table run_stats 
( runid varchar2(15), 
  name varchar2(80), 
  value int )
on commit preserve rows;
创建runstats包：
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 (c.value-a.value) > 0
         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;
/
使用方式为
/*
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;
*/
p_difference_threshold用来控制只查看大于这个数的统计结果与latch信息，默认为0，表示可以看到全部的输出结果。
测试例子：
SQL> CREATE TABLE t1
  2  AS
  3  SELECT * FROM All_Objects WHERE 1=0;
Table created
SQL> CREATE TABLE t2
  2  AS
  3  SELECT * FROM All_Objects WHERE 1=0;
Table created
SQL> 
SQL> EXEC runstats_pkg.rs_start;
PL/SQL procedure successfully completed
SQL> INSERT INTO t1
  2  SELECT * FROM all_objects WHERE ROWNUM<40000;
39999 rows inserted
SQL> COMMIT;
Commit complete
SQL> EXEC runstats_pkg.rs_middle;
PL/SQL procedure successfully completed
SQL> 
SQL> BEGIN
  2    FOR x IN (SELECT * FROM All_Objects WHERE ROWNUM<40000) LOOP
  3        INSERT INTO t2 VALUES x;
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /
PL/SQL procedure successfully completed
SQL> EXEC runstats_pkg.rs_stop(100000);
EXEC runstats_pkg.rs_stop(100000);
Run1 ran in 97 cpu hsecs
Run2 ran in 225 cpu hsecs
run 1 ran in 43.11% of the time
	
Name                                  Run1        Run2        Diff
LATCH.cache buffers chains         149,900     349,138     199,238
STAT...physical read total byt           0     327,680     327,680
STAT...undo change vector size     161,488   2,572,016   2,410,528
STAT...physical write total by           0   5,398,528   5,398,528
STAT...redo size                 4,545,684  14,553,780  10,008,096
	
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     225,405     603,915     378,510     37.32%
PL/SQL procedure successfully completed
Mystat
显示某操作之前与之后的统计结果变化情况
SQL> @d:/mystat.sql "redo size"
SQL> set echo off
NAME                                               VALUE
--------------------------------------------- ----------
redo size                                         516048
SQL> update t1 set object_name=lower(object_name) where rownum<1000;
已更新999行。
SQL> @d:/mystat2.sql
SQL> set echo off
NAME                                               VALUE DIFF
--------------------------------------------- ---------- ------------------
redo size                                         650224          134,176
mystat.sql:
set echo off
set verify off
column value new_val V
define S="&1"
column name format a45
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')||'%'
-- and lower(a.name) = lower('&S')
/
set echo on
mystat2.sql:
set echo off
set verify off
column diff format a18
select a.name, b.value Value, 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