Databases Reference
In-Depth Information
5-1. Determining CPU Requirements
Problem
You wish to determine whether an Exadata Database Machine will provide enough CPU processing horsepower to
meet your database workload requirements.
Solution
To conduct an Exadata processor capacity-planning exercise, we recommend the following approach:
•
Query raw CPU-related data from Oracle AWR, export to Excel, and optionally plot trends
and/or identify outlier conditions.
•
Query CPU utilization averages, maximums, and other statistical information to understand
usage patterns.
•
Add relevant processor numbers for all environments targeted for Exadata deployment.
•
Determine Exadata and Exadata model specific fit based on measured CPU-related
performance data from AWR.
First, log in to SQL*Plus in your current database and connect as a user who has access to Oracle's AWR views.
Run the query in Listing 5-1, which displays CPU-related information from an Oracle 11gR2 database running on a
non-Exadata platform. Subsequent code listings in this recipe will expand upon this script, adding logic to ultimately
allow us to complete a CPU sizing analysis for Exadata.
Listing 5-1.
lst05-01-awr-cpu.sql
SYS @ PROD> select snaps.id, snaps.tm,snaps.dur,snaps.instances,
osstat.num_cpus CPUs,
osstat.num_cpus * dur * 60 cap,
((timemodel.dbt - lag(timemodel.dbt,1) over (order by snaps.id)))/1000000 dbt,
((timemodel.dbc - lag(timemodel.dbc,1) over (order by snaps.id)))/1000000 dbc,
((timemodel.bgc - lag(timemodel.bgc,1) over (order by snaps.id)))/1000000 bgc,
((timemodel.rmanc - lag(timemodel.rmanc,1) over (order by snaps.id)))/1000000 rmanc,
(((timemodel.dbt - lag(timemodel.dbt,1) over (order by snaps.id)))/1000000)/dur/60 aas ,
(((timemodel.dbc - lag(timemodel.dbc,1) over (order by snaps.id)))/1000000) +
(((timemodel.bgc - lag(timemodel.bgc,1) over (order by snaps.id)))/1000000) totora ,
osstat.load load ,
((osstat.busy_time - lag(osstat.busy_time,1) over (order by snaps.id)))/100 totos,
round(100*(((((timemodel.dbc - lag(timemodel.dbc,1) over (order by
snaps.id)))/1000000) +
(((timemodel.bgc - lag(timemodel.bgc,1) over (order by snaps.id)))/1000000)) /
(osstat.num_cpus * 60 * dur)),2) oracpupct, round(100*((((timemodel.rmanc
- lag(timemodel.rmanc,1) over (order by snaps.id)))/1000000) /
(osstat.num_cpus * 60 * dur)),2) rmancpupct,
round(100*((((osstat.busy_time - lag(osstat.busy_time,1) over (order by snaps.id)))/100) /
(osstat.num_cpus * 60 * dur)),3) oscpupct,
round(100*((((osstat.user_time - lag(osstat.user_time,1) over (order by snaps.id)))/100) /
(osstat.num_cpus * 60 * dur)),3) usrcpupct,
round(100*((((osstat.sys_time - lag(osstat.sys_time,1) over (order by snaps.id)))/100) /
(osstat.num_cpus * 60 * dur)),3) syscpupct,
Search WWH ::
Custom Search