CPU/IO information collection for oracle databases

Oracle Database CPU/IO Information Collection CPU time collection starts from 10 Gb, oracle introduces a time model, from the perspective of oracle, we can look at the CPU usage. oracle training first describes several concepts: db time: the time consumed by the oracle database. This range is large, including CPU usage, wait for the IO subsystem to return, network processing, and other db CPUs: refers to the time when oracle consumes cpu and performs CPU operations. IO and network waits are not in this range, use it to calculate the actual CPU consumption more accurate cpu time: This is my name, indicating the maximum TIME the CPU can provide you, for example, you have 4 cpu/core, in one hour, the cpu time is 4x60 Minutes. I used a test script to check the CPU TIME statistics in oracle.

　　#killcpu.sh　　#!/bin/sh　　export ORACLE_SID=innodb　　export ORACLE_HOME=/opt/oracle/products/11.2.0　　$ORACLE_HOME/bin/sqlplus /nolog <<_kof_　　connect iops/iops@innodb　　declare v_count pls_integer := 0;　　begin　　for x in 1..400000000 loop　　for d in 1..400000000 loop　　for c in 1..400000000 loop　　v_count :=mod(c, mod(x,d));　　end loop;　　end loop;　　end loop;　　end;　　/　　_kof_

 

The test machine is an Intel (R) Xeon (R) CPU E7530 4C6 core CPU. A total of 48 core killcpus are virtualized and tested with 24 concurrency, 48 concurrency, and 60 concurrency, every 10 minutes, information is collected through statspack. According to the formula above, the final result of cpu time = 10 min * 60*48 core = 28800 seconds is as follows (the time unit is second ): concurrency db time db cpu time top Display CPU usage LOAD 24 14066 14064 28800 50 24 48 27984 27401 28800 100 48 60 34556 27179 28800 100 57 from the results we can see: 24 concurrency, oracle uses 24 cores, the overall CPU usage is 50%, and the load is 24, which is very accurate. At this time, the db time is basically the same as the db cpu, because everything you do, all of them are 48 concurrent CPUs. oracle uses 48 cores and the CPU usage reaches 100%. In multi-CPU environments, the use of resources in oracle is indeed high. At this time, db time, db cpu, the cpu time is the same, and the CPU runs 60 concurrently at full capacity. At this time, we will find that the db cpu has not changed because the CPU has been exhausted and has not increased, db time is far greater than cpu time and db cpu, which means that some of our programs cannot grab the CPU at all, enter the wait, and load also exceeds the number of cores, when the throughput reaches 57, we can see whether the database is a cpu-intensive application, such as a large number of operations and latch contention. If it is an IO-intensive application, we will find that the proportion of db cpu stations will be very small I/O load statistics I/O performance in two aspects: IOPS and throughput, our OLTP system, generally more concerned about IOPS, and the response time of each IO, in the past, for IOPS statistics, we had been collecting statistics based on statspack's physical reads + physical writes. This is not accurate according to the document's explanation: physical reads: total number of data blocks read from disk: This is based on the number of block reads. There are many oracle IO types. If it is scatter read or parallel read, one IO will read multiple blocks, so that the computing IOPS will be too large. After the adjustment, we will find that the statistical curves are consistent with those observed from the storage segment.

　　9I:　　–IOPS&MBPS　　select sum(iops) as iops,sum(mbps) as mbps　　from (　　select sum(phyrds + phywrts) as IOPS,　　sum(phyblkrd + phyblkwrt) as MBPS　　from (select a.phyrds,a.phywrts,a.phyblkrd * b.block_size / 1024 / 1024 as phyblkrd,a.phyblkwrt * b.BLOCK_SIZE / 1024 / 1024 as phyblkwrt　　from v$filestat a,v$datafile b　　where a.file# = b.file#)　　union all　　select sum(decode(name,’redo writes’,value,’0′)) as IOPS,　　sum(decode(name,’redo size’,value,’0′)) / 1024 / 1024 as MBPS　　from v$sysstat where name in( ‘redo writes’,'redo size’));　　10G/11G　　–IOPS&MBPS　　select sum(decode(name,’physical read IO requests’,value,’physical write IO requests’,value,0)) as iops,　　sum(decode(name,’physical read bytes’,value,’physical write bytes’,value,0)) / 1024 / 1024 as mbps　　from v$sysstat　　where name in (’physical read IO requests’,'physical write IO requests’,　　‘physical read bytes’,'physical read total bytes’,　　‘physical write bytes’,'physical write total bytes’,'physical read total IO requests’,'physical write total IO requests’　　);

 

Recently, these statistical indicators have been added to the monitoring system to see how they work.