CPU Statistics
1,获取整个系统的cpu使用情况
2,获取多处理器环境的每个独立cpu的使用情况
3,大多os关于cpu usage用时在user space or mode and kernel space or mode(即用户和内核模式)
4,当然还有一些额外的信息体现,进一步分析cpu正在作什么工作
5,在oracle运行的os中,以一个应用为例;系统活动基本运行在用户模式下
6,为了处理数据库请求的活动,比如调度,同步,io,内存管理,进线程分配和释放,则运行于内核模式下
7,oracle建议对于一个充分利用cpu的系统,65-95% cpu运行于用户模式
--示例:
---处于空闲和繁忙状态的时间,注意:是累计;user_time为用户模式;avg前缀为平均空闲相关的时间;非累计;
--num_cpu_cores为cpu核数;物理内存大小;vm前缀为交换产生页出和页入的大小
--调优原则:user_time/(user_time+sys_time) 位于65-95%之间,说明合理
-----------如果busy_time远小于idle_time说明系统很空闲,否则说明系统很忙
-----------如果vm太多,说明交换活动太多,增加交换分区
SQL> select * from v$osstat;
STAT_NAME VALUE OSSTAT_ID COMMENTS CUMULATIVE
---------------------------------------------------------------- ---------- ---------- ---------------------------------------------------------------- ----------
NUM_CPUS 4 0 Number of active CPUs NO
IDLE_TIME 20361 1 Time (centi-secs) that CPUs have been in the idle state YES
BUSY_TIME 1368 2 Time (centi-secs) that CPUs have been in the busy state YES
USER_TIME 919 3 Time (centi-secs) spent in user code YES
SYS_TIME 449 4 Time (centi-secs) spent in the kernel YES
AVG_IDLE_TIME 5088 7 Average time (centi-secs) that CPUs have been in the idle state NO
AVG_BUSY_TIME 340 8 Average time (centi-secs) that CPUs have been in the busy state NO
AVG_USER_TIME 229 9 Average time (centi-secs) spent in user code NO
AVG_SYS_TIME 111 10 Average time (centi-secs) spent in the kernel NO
RSRC_MGR_CPU_WAIT_TIME 0 14 Time (centi-secs) processes spent in the runnable state waiting YES
NUM_CPU_CORES 2 16 Number of CPU cores NO
NUM_CPU_SOCKETS 1 17 Number of physical CPU sockets NO
PHYSICAL_MEMORY_BYTES 4196536320 1008 Physical memory size in bytes NO
VM_IN_BYTES 3.56224175 1009 Bytes paged in due to virtual memory swapping YES
VM_OUT_BYTES 1.84451431 1010 Bytes paged out due to virtual memory swapping YES
15 rows selected
--v$sysmetric_history存储1小时的历史数据,每1分钟采样一次;采样各个度量的指标数据
SQL> select max(to_char(begin_time,'yyyymmdd hh24:mi:ss')),min(to_char(begin_time,'yyyymmdd hh24:mi:ss')) from v$sysmetric_history;
MAX(TO_CHAR(BEGIN_TIME,'YYYYMM MIN(TO_CHAR(BEGIN_TIME,'YYYYMM
------------------------------ ------------------------------
20130311 13:07:56 20130311 12:06:56
--度量分组
SQL> select count(*) from v$metricgroup;
COUNT(*)
----------
14
--度量隶属于上述的度量分组
SQL> select count(distinct metric_name) from v$metricname;
COUNT(DISTINCTMETRIC_NAME)
--------------------------
220
--度量历史表
SQL> select count(distinct metric_name) from v$sysmetric_history;
COUNT(DISTINCTMETRIC_NAME)
--------------------------
158
--查询一个时间范围主机cpu的使用率,如下说明cpu很idle
SQL> select * from v$sysmetric_history uh where uh.METRIC_NAME like '%Host CPU Utilization (%)%';
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.71051312 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.47377878 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 3.60650257 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.61167523 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.70215610 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6084 2 2057 Host CPU Utilization (%) 2.27235371 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 4.87672377 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 1.96381565 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.53919438 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 2.71591526 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6064 2 2057 Host CPU Utilization (%) 4.06926406 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5980 2 2057 Host CPU Utilization (%) 3.23592123 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5960 2 2057 Host CPU Utilization (%) 6.91620907 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 4.48559842 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6084 2 2057 Host CPU Utilization (%) 2.35874255 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.17209690 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 2.41190486 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 0.51770207 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 0.48888517 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 0.45587620 % Busy/(Idle+Busy)
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME
--如下查询oracle使用的cpu比率,用如上和如下可以判断到底是什么原因导致cpu出现问题;即到底是主机活动问题或是oracle导致
SQL> select * from v$sys_time_model;
STAT_ID STAT_NAME VALUE
---------- ---------------------------------------------------------------- ----------
3649082374 DB time 5365952069
2748282437 DB CPU 12230459
4157170894 background elapsed time 166631594
2451517896 background cpu time 9812454
4127043053 sequence load elapsed time 66275
1431595225 parse time elapsed 18772863
372226525 hard parse elapsed time 18220556
2821698184 sql execute elapsed time 50648806
1990024365 connection management call elapsed time 205212
1824284809 failed parse elapsed time 160366
4125607023 failed parse (out of shared memory) elapsed time 0
3138706091 hard parse (sharing criteria) elapsed time 177374
268357648 hard parse (bind mismatch) elapsed time 5091
2643905994 PL/SQL execution elapsed time 3313276
290749718 inbound PL/SQL rpc elapsed time 0
1311180441 PL/SQL compilation elapsed time 4685801
751169994 Java execution elapsed time 0
1159091985 repeated bind elapsed time 27650
2411117902 RMAN cpu time (backup/restore) 0
19 rows selected
--查询数据库cpu time比率,即cpu/db_time;而db_time为cpu time+等待非空闲事件的等待时间
SQL> select * from v$sysmetric_history uh where uh.METRIC_NAME like '%Database CPU Time Ratio%';
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 132.944725 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 64.9215531 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5981 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 78.2051564 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 63.0991384 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 181.930029 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 52.3636363 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 80.7955251 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 126.985603 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 45.1412449 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6064 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5980 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5960 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 60.2107375 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 83.6541864 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 153.001176 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 90.2229548 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 151.883945 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 129.310344 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 134.401654 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 54.6831183 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1420 3 2108 Database CPU Time Ratio 207.013236 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1420 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 66.3886288 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
74 rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-755734/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-755734/