runstats相关

 

V$STATNAME

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

Thisview displays decoded statistic names for the statistics shown inthe V$SESSTAT and V$SYSSTAT tables.

Onsome platforms, the NAME and CLASS columnscontain additional operating system-specific statistics.

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

V$MYSTAT

显示当前session的统计信息

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

 

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

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

FROMv$statname a, v$mystat b

WHEREa.STATISTIC# = b.STATISTIC#

V$LATCH

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

 

V$LATCH showsaggregate latch statistics for both parent and child latches, groupedby latch name. Individual parent and child latch statistics arebroken down in the views V$LATCH_PARENT and V$LATCH_CHILDREN.

Column	Datatype	Description
ADDR	RAW(4| 8)	Addressof the latch object
LATCH#	NUMBER	Latchnumber
LEVEL#	NUMBER	Latchlevel
NAME	VARCHAR2(50)	Latchname
HASH	NUMBER	Latchhash
GETS	NUMBER	Numberof times the latch was requested in willing-to-wait modelatch被请求的次数
MISSES	NUMBER	Numberof times the latch was requested in willing-to-wait mode and therequestor had to wait
SLEEPS	NUMBER	Numberof times a willing-to-wait latch request resulted in a sessionsleeping while waiting for the latch
IMMEDIATE_GETS	NUMBER	Numberof times a latch was requested in no-wait mode
IMMEDIATE_MISSES	NUMBER	Numberof times a no-wait latch request did not succeed (that is,missed)
WAITERS_WOKEN	NUMBER	Thiscolumn has been deprecated and is present only for compatibilitywith previous releases of Oracle. No data is accumulated for thiscolumn; it will always have a value of zero.
WAITS_HOLDING_LATCH	NUMBER	Thiscolumn has been deprecated and is present only for compatibilitywith previous releases of Oracle. No data is accumulated for thiscolumn; it will always have a value of zero.
SPIN_GETS	NUMBER	Willing-to-waitlatch requests which missed the first try but succeeded whilespinning
SLEEP[1| 2 | 3]	NUMBER	Thesecolumns have been deprecated and are present only forcompatibility with previous releases of Oracle. No data isaccumulated for these columns; they will always have a value ofzero. As a substitute for this column you can query theappropriate rows of theV$EVENT_HISTOGRAM viewwhere the EVENT columnhas a value of latchfree or latch:%.
SLEEP4	NUMBER	Thiscolumn has been deprecated and is present only for compatibilitywith previous releases of Oracle. No data is accumulated for thiscolumn; it will always have a value of zero. As a substitute forthis column you can query the appropriate rows ofthe V$EVENT_HISTOGRAMviewwhere the EVENT columnhas a value of latchfree or latch:%.
SLEEP[5| 6 | 7 | 8 | 9 | 10 | 11]	NUMBER	Thesecolumns have been deprecated and are present only forcompatibility with previous releases of Oracle. No data isaccumulated for these columns.
WAIT_TIME	NUMBER	Elapsedtime spent waiting for the latch (in microseconds)

 

$TIMER

Thisview lists the elapsed time in hundredths of seconds. Time ismeasured since the beginning of the epoch, which is operating systemspecific, and wraps around to 0 again whenever the value overflowsfour bytes (roughly 497 days).

Column	Datatype	Description
HSECS	NUMBER	Elapsedtime in hundredths of a second

DBMS_UTILITY

GET_TIMEFunction

Thisfunction determines the current time in 100th's of a second. Thissubprogram is primarily used for determining elapsed time. Thesubprogram is called twice – at the beginning and end of someprocess – and then the first (earlier) number is subtracted fromthe second (later) number to determine the time elapsed.

Syntax

DBMS_UTILITY.GET_TIME

RETURNNUMBER;

ReturnValues

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

UsageNotes

Numbersare returned in the range -2147483648 to 2147483647 depending onplatform and machine, and your application must take the sign of thenumber into account in determining the interval. For instance, in thecase of two negative numbers, application logic must allow that thefirst (earlier) number will be larger than the second (later) numberwhich is closer to zero. By the same token, your application shouldalso allow that the first (earlier) number be negative and the second(later) number be positive.

GET_CPU_TIMEFunction

Thisfunction returns the current CPU time in 100th's of a second. Thereturned CPU time is the number of 100th's of a second from somearbitrary epoch.

Syntax

DBMS_UTILITY.GET_CPU_TIME

RETURNNUMBER;

ReturnValues

Timeis 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