--查询度量参数的含义
SQL> select * from v$metricname mt where mt.METRIC_NAME='Database CPU Time Ratio';
GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT
---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
2 System Metrics Long Duration 2108 Database CPU Time Ratio % Cpu/DB_Time
3 System Metrics Short Duration 2108 Database CPU Time Ratio % Cpu/DB_Time
SQL>
--上述的度量值>100%,因为是4个cpu，
SQL> select value/4 from v$sysmetric_history uh where uh.METRIC_NAME like '%Database CPU Time Ratio%' and uh.GROUP_ID=3;
VALUE/4
----------
0
0
0
0
0
0
0
0
0
0
0
36.1781076
0
13 rows selected
Virtual Memory Statistics
1,检查系统是否存在分页和交换活动；
2,这种问题在中间层计算机的共享服务器应用更为严重；
在这里会话状态会持久化存在于多个用户交互中；而完成状态的信息
并非完全释放
3,v$osstat
disk i/0 statistics
1,最重要的统计信息即当前的响应时间和磁盘队列的长度；
2，这些统计可以判断磁盘是否处理最佳；磁盘是否过载
3，每次磁盘数据块的读取的时间为 5-20 毫秒,这和具体的硬件有关
4，如果磁盘响应时间长于正常的范围，这可能是性能更差或过载
5，如果磁盘队列长度超过2,表明磁盘有潜在的瓶颈
oracle维护io调用的一系列io统计信息，会根据如下维度自单一多多个
数据块读写操作获取统计信息：
1,consumer group,v$iostat_consumer_group;(前提：开启resource manager)
2,database file,v$iostat_file
3,database function（比如：lgwr and dbwr）,存储在v$iostat_function;
--示例：
SQL> desc v$iostat_consumer_group;
Name Type Nullable Default Comments
--------------------- ------ -------- ------- --------
CONSUMER_GROUP_ID NUMBER Y
SMALL_READ_MEGABYTES NUMBER Y
SMALL_WRITE_MEGABYTES NUMBER Y
LARGE_READ_MEGABYTES NUMBER Y
LARGE_WRITE_MEGABYTES NUMBER Y
SMALL_READ_REQS NUMBER Y
SMALL_WRITE_REQS NUMBER Y
LARGE_READ_REQS NUMBER Y
LARGE_WRITE_REQS NUMBER Y
NUMBER_OF_WAITS NUMBER Y
WAIT_TIME NUMBER Y
SQL> desc v$iostat_file;
Name Type Nullable Default Comments
----------------------- ------------ -------- ------- --------
FILE_NO NUMBER Y
FILETYPE_ID NUMBER Y
FILETYPE_NAME VARCHAR2(28) Y
SMALL_READ_MEGABYTES NUMBER Y
SMALL_WRITE_MEGABYTES NUMBER Y
LARGE_READ_MEGABYTES NUMBER Y
LARGE_WRITE_MEGABYTES NUMBER Y
SMALL_READ_REQS NUMBER Y
SMALL_WRITE_REQS NUMBER Y
SMALL_SYNC_READ_REQS NUMBER Y
LARGE_READ_REQS NUMBER Y
LARGE_WRITE_REQS NUMBER Y
SMALL_READ_SERVICETIME NUMBER Y
SMALL_WRITE_SERVICETIME NUMBER Y
SMALL_SYNC_READ_LATENCY NUMBER Y
LARGE_READ_SERVICETIME NUMBER Y
LARGE_WRITE_SERVICETIME NUMBER Y
ASYNCH_IO VARCHAR2(9) Y
ACCESS_METHOD VARCHAR2(11) Y
RETRIES_ON_ERROR NUMBER Y
SQL> select * from v$iostat_file;
FILE_NO FILETYPE_ID FILETYPE_NAME SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
0 0 Other 0 0 0 0 12 20 12 0 0 16 93 16 0 0 ASYNC_OFF OS_LIB 0
0 1 Control File 226 152 0 0 14389 9716 14331 0 0 39598 133603 39086 0 0 ASYNC_OFF 0
0 3 Log File 0 3 0 4 0 1117 0 0 11 0 2074 0 0 46 ASYNC_OFF 0
0 4 Archive Log 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 9 Data File Backup 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 10 Data File Incremental Backup 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 11 Archive Log Backup 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 12 Data File Copy 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 17 Flashback Log 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
0 18 Data Pump Dump File 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_OFF 0
1 2 Data File 60 3 17 0 6681 329 8252 69 0 91130 983 89926 313 0 ASYNC_ON OS_LIB 0
1 6 Temp File 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ASYNC_ON OS_LIB 0
2 2 Data File 17 13 3 1 1556 1279 1415 14 5 8912 5221 8096 61 16 ASYNC_ON OS_LIB 0
2 6 Temp File 4 11 0 0 30 94 0 0 0 63 391 0 0 0 ASYNC_ON OS_LIB 0
3 2 Data File 0 4 0 2 47 425 47 0 12 639 1509 639 0 31 ASYNC_ON OS_LIB 0
4 2 Data File 0 0 0 0 5 0 5 0 0 92 0 92 0 0 ASYNC_ON OS_LIB 0
5 2 Data File 0 0 0 0 5 0 5 0 0 234 0 234 0 0 ASYNC_ON OS_LIB 0
6 2 Data File 0 0 0 0 5 0 5 0 0 312 0 312 0 0 ASYNC_ON OS_LIB 0
7 2 Data File 0 0 0 0 5 0 5 0 0 125 0 125 0 0 ASYNC_ON OS_LIB 0
8 2 Data File 0 0 0 0 5 0 5 0 0 125 0 125 0 0 ASYNC_ON OS_LIB 0
FILE_NO FILETYPE_ID FILETYPE_NAME SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
9 2 Data File 0 0 0 0 5 0 5 0 0 124 0 124 0 0 ASYNC_ON OS_LIB 0
10 2 Data File 0 0 0 0 7 2 7 0 0 266 0 266 0 0 ASYNC_ON OS_LIB 0
11 2 Data File 0 0 0 0 5 0 5 0 0 93 0 93 0 0 ASYNC_ON OS_LIB 0
12 2 Data File 0 0 0 0 4 0 4 0 0 141 0 141 0 0 ASYNC_ON OS_LIB 0
13 2 Data File 0 0 0 0 4 0 4 0 0 62 0 62 0 0 ASYNC_ON OS_LIB 0
14 2 Data File 0 0 0 0 4 0 4 0 0 48 0 48 0 0 ASYNC_ON OS_LIB 0
15 2 Data File 0 0 0 0 4 0 4 0 0 61 0 61 0 0 ASYNC_ON OS_LIB 0
16 2 Data File 0 0 0 0 4 0 4 0 0 63 0 63 0 0 ASYNC_ON OS_LIB 0
17 2 Data File 0 0 0 0 4 0 4 0 0 63 0 63 0 0 ASYNC_ON OS_LIB 0
18 2 Data File 0 0 0 0 4 0 4 0 0 62 0 62 0 0 ASYNC_ON OS_LIB 0
19 2 Data File 0 0 0 0 4 0 4 0 0 31 0 31 0 0 ASYNC_ON OS_LIB 0
20 2 Data File 0 0 0 0 4 0 4 0 0 32 0 32 0 0 ASYNC_ON OS_LIB 0
32 rows selected
SQL> desc v$iostat_function;
Name Type Nullable Default Comments
--------------------- ------------ -------- ------- --------
FUNCTION_ID NUMBER Y
FUNCTION_NAME VARCHAR2(18) Y
SMALL_READ_MEGABYTES NUMBER Y
SMALL_WRITE_MEGABYTES NUMBER Y
LARGE_READ_MEGABYTES NUMBER Y
LARGE_WRITE_MEGABYTES NUMBER Y
SMALL_READ_REQS NUMBER Y
SMALL_WRITE_REQS NUMBER Y
LARGE_READ_REQS NUMBER Y
LARGE_WRITE_REQS NUMBER Y
NUMBER_OF_WAITS NUMBER Y
WAIT_TIME NUMBER Y
SQL> select * from v$iostat_function;
FUNCTION_ID FUNCTION_NAME SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS NUMBER_OF_WAITS WAIT_TIME
----------- ------------------ -------------------- --------------------- -------------------- --------------------- --------------- ---------------- --------------- ---------------- --------------- ----------
0 RMAN 0 0 0 0 0 0 0 0 0 0
4 XDB 0 0 0 0 0 0 0 0 0 0
6 Data Pump 0 0 0 0 0 0 0 0 0 0
9 Direct Reads 4 0 0 0 30 0 0 0 0 0
10 Direct Writes 0 11 0 0 0 103 0 0 0 0
8 Buffer Cache Reads 77 0 21 0 8202 0 83 0 6425 37132
2 LGWR 0 3 0 4 0 1117 0 11 0 0
3 ARCH 0 0 0 0 0 0 0 0 0 0
13 Others 227 152 0 0 14541 9787 0 0 19435 101387
1 DBWR 0 20 0 2 0 2007 0 17 0 0
5 Streams AQ 1 0 0 0 68 0 0 0 68 421
11 Smart Scan 0 0 0 0 0 0 0 0 0 0
7 Recovery 0 0 0 0 0 0 0 0 0 0
12 Archive Manager 0 0 0 0 0 0 0 0 0 0
14 rows selected
SQL>
Network Statistics
--网络的延迟也是响应时间组成的一部分；
SQL> desc v$iostat_network;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
CLIENT VARCHAR2(32) Y
READS# NUMBER Y
WRITES# NUMBER Y
KBYTES_READ NUMBER Y
KBYTES_WRITTEN NUMBER Y
READ_LATENCY NUMBER Y
WRITE_LATENCY NUMBER Y
SQL> select * from v$iostat_network;
CLIENT READS# WRITES# KBYTES_READ KBYTES_WRITTEN READ_LATENCY WRITE_LATENCY
-------------------------------- ---------- ---------- ----------- -------------- ------------ -------------
RMAN 0 0 0 0 0 0
PLSQL 0 0 0 0 0 0
如何理解或使用这些统计信息：
1，查看或计算命中率：比如buffer hit,latch hit,soft parse hit ratio;可采用v$sysstat
2,如配置timed_statistitics=true,则采集等待事件的等待时间
3,如果在采样2个小时等待时间占用比例很高，则需要进一步分析等待事件，如等待时间占采样时间很小；则没有必要再分析
3，配置参数 statistics_level=typical or all，自动收集基于时间的统计；
如参数值为basic,必须配置timed_statistics=true;
4,statistics_level=basic禁用一些自动化特性，这不受推荐
5,综合比较各个统计信息，即评估一个统计指标也要考虑下相关的指标；比如用户负荷和硬件容量；45分的采样产生了30分的等待；
并不表明系统出现了性能问题；因为系统有2000多个用户；主机运行于64节点
6,处理未配置timed_statiscs=false的等待事件；按照等待次数逆向排序；
注意：如某事件的总等待次数很大，未必有问题；比如总的等待时间很短；所以要看等待时间每次等待的时间；
相反；如果某等待事件的总等待次数很小，也可能出现性能问题；它占用系统总等待时间的大部分比例；
所以如没有等待时间每次等待时间很难判断
7，空等待事件：
标明oracle进程正处于空闲状态
8，计算性的统计
比如：比率，多个事务的一些统计值等；交叉与其它实际的统计值参考验证相当重要；以验证些统计信息是否值得分析；
例：soft parse ratio为50%,可能表明系统出问题了；但可能采样期间仅仅有一次硬解析和一次软解析；所以为50%;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755736/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-755736/
86
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
