显示 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:
|
| STAT_ID | NUMBER | Identifier of the statistic ( 统计标识符 ) |
显示当前 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#
显示总的以 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) |
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.
DBMS_UTILITY.GET_TIME
RETURN NUMBER;
Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.
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.
DBMS_UTILITY.GET_CPU_TIME
RETURN NUMBER;
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
