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
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.
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
