Databases Reference
In-Depth Information
With an Exadata capacity-planning exercise, however, you will also have a large number of processors on the
Exadata Storage Servers. Thus, it is important to differentiate between
processor requirements for I/O
and
processor
requirements for non-I/O
wait-related database operations. You can calculate this by subtracting
DB CPU time
from
AAS to arrive at I/O-related CPU numbers. For this type of analysis, we recommend adding a margin of error for the
DB CPU
time metrics, assuming that I/O requests will be satisfied faster with Exadata and potentially place greater CPU
demands on the database tier nodes. In the query below we will assume the following:
•
I/O-related CPU demand = (Average Active Sessions) - ((DB CPU + background CPU))
•
No I/O-related CPU demand = (DB CPU + background CPU) + (50% * I/O-related CPU)
Next, run the script in Listing 5-3 to account for I/O versus non-I/O processor requirements:
Listing 5-3.
lst05-03-awr-cpu-corecnt-ioadj.sql
SYS @ PROD> select id,tm,dur,CPUs,
aas, oracpupct,
(case
when (greatest(0,aas - (oracpupct * CPUs))) > 0 then
((oracpupct * CPUs) + (.5 * (aas - (oracpupct * CPUs))))
else (oracpupct * CPUs)
end) OraCPU,
greatest(0,aas - (oracpupct * CPUs)) OraIOCPU,
(( oracpupct * CPUs) + greatest(0,aas - (oracpupct * CPUs))) OraCPUTot
from
(
{ ... Code from LIsting 5-2 ...}
)
/
Snap|Snap |Duration|Current| | Ora|Non-IO | IO|Total CPU
ID|Start | Mins| CPUs| AAS| CPU%| CPU Req|CPU Req| Req
------|---------------|--------|-------|-------|------|--------|-------|---------
22006|24-JUN-12 02:00| 60.67| 12| 6.70| 0.1| 4.01| 5.38| 6.70
22007|24-JUN-12 03:00| 59.57| 12| 2.33| 0.1| 1.48| 1.71| 2.33
22008|24-JUN-12 04:00| 60.62| 12| 1.34| 0.1| 0.97| 0.73| 1.34
22009|24-JUN-12 05:00| 59.58| 12| 2.48| 0.1| 1.94| 1.09| 2.48
... output omitted
Now, calculate the average, maximum, and standard deviations for the CPU numbers in Listing 5-3,
as in Listing 5-4:
Listing 5-4.
lst05-04-awr-cpu-corecnt-sum.sql
SYS @ PROD> select avg(OraCPU) AvgOraCPU, max(OraCPU) MaxOraCPU, stddev(OraCPU) StdOraCPU,
avg(OraIOCPU) AvgOraIOCPU, max(OraIOCPU) MaxOraIOCPU, stddev(OraIOCPU) StdOraIOCPU,
avg(OraCPUTot) AvgOraCPUTot, max(OraCPUTot) MaxOraCPUTot, stddev(OraCPUTot) StdOraCPUTot
from (
{... Code from Listing 5-3 ...}
)
/
Avg NonIO| Max Non IO|StdDev Non IO| Avg IO| Max IO| StdDev IO| Avg Total| Max Total| StdDev
CPU Req| CPU Req| CPU Req| CPU Req|CPU Req| CPU Req| CPU Req| CPU Req| Total Req
---------|-----------|-------------|--------|-------|----------|----------|----------|----------
3.22| 10.38| 1.75| 1.99| 14.47| 1.78| 4.21| 17.52| 2.56
Search WWH ::
Custom Search