Estimate the Amazon RDS engine size for an Oracle database by using AWR reports

Enable the AWR report.

To enable the report, follow the instructions in the Oracle documentation.

Check the retention period.

To check the retention period of the AWR report, use the following query.

SQL> SELECT snap_interval,retention FROM dba_hist_wr_control;

Generate the snapshot.

If the AWR  snapshot interval isn’t granular enough to capture the spike of the peak workload, you can generate the AWR report manually. To generate the manual AWR snapshot, use the following query.

SQL> EXEC dbms_workload_repository.create_snapshot;

Check recent snapshots.

To check recent AWR snapshots, use the following query.

SQL> SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,
 to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval
 FROM dba_hist_snapshot
 ORDER BY 1;

Choose a method.

IOPS is the standard measure of input and output operations per second on a storage device, and includes both read and write operations. 

If you are migrating an on-premises database to AWS, you need to determine the peak disk I/O used by the database. You can use the following methods to estimate disk I/O for your target database:

The following steps describe these four methods.

Option 1: Use the load profile.

The following table shows an example of the Load Profile section of the AWR report.

Important: For more accurate information, we recommend that you use option 2 (I/O profiles) or option 3 (instance activity statistics) instead of the load profile.

Based on this information, you can calculate IOPs and throughput as follows:

   IOPS = Read I/O requests: + Write I/O requests = 3,586.8 + 574.7 = 4134.5 

   Throughput = Physical read (blocks) + Physical write (blocks) = 13,575.1 + 3,467.3 = 17,042.4

Because the block size in Oracle is 8 KB, you can calculate total throughput as follows:

   Total throughput in MB is 17042.4 * 8 * 1024 / 1024 / 1024 = 133.2 MB

Warning: Don’t use the load profile to estimate the instance size. It isn’t as precise as instance activity statistics or I/O profiles.

Option 2: Use instance activity statistics.

If you’re using an Oracle Database version before 12c, you can use the Instance Activity Stats section of the AWR report to estimate IOPS and throughput. The following table shows an example of this section.

Based on this information, you can calculate total IOPS and throughput as follows:

   Total IOPS = 3,610.28 + 757.11 = 4367 

   Total Mbps = 114,482,426.26 + 36,165,631.84 = 150648058.1 / 1024 / 1024 = 143 Mbps

Option 3: Use I/O profiles.

In  Oracle Database 12c, the AWR report includes an I/O Profiles section that presents all the information in a single table and provides more accurate data about database performance. The following table shows an example of this section.

This table provides the following values for throughput and total IOPS:

   Throughput = 143 MBPS (from the fifth row, labeled Total, second column)

   IOPS = 4,367.4 (from the first row, labeled Total Requests, second column)

Option 4: Use AWR views.

You can see the same IOPS and throughput information by using AWR views. To get this information, use the following query: 

break on report
 compute sum of Value on report
 select METRIC_NAME,avg(AVERAGE) as "Value"
 from dba_hist_sysmetric_summary
 where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
 group by metric_name;

Choose a method.

You can estimate the CPU required for the target database in three ways:

If you’re looking at utilized cores, we recommend that you use the database metrics method instead of OS statistics, because it’s based on the CPU used only by the databases that you’re planning to migrate. (OS statistics also include CPU usage by other processes.) You should also check CPU-related recommendations in the ADDM report to improve performance after migration.

You can also estimate requirements based on CPU generation. If you are using different CPU generations, you can estimate the required CPU of the target database by following the instructions in the whitepaper Demystifying the Number of vCPUs for Optimal Workload Performance.

Option 1: Estimate requirements based on available cores.

In AWR reports:

You can estimate available cores in two ways:

To estimate available cores by using OS commands

Use the following command to count the cores in the processor.

$ cat /proc/cpuinfo |grep "cpu cores"|uniq
cpu cores    : 4
cat /proc/cpuinfo | egrep "core id|physical id" | tr -d "\n" | sed s/physical/\\nphysical/g | grep -v ^$ | sort | uniq | wc -l 

Use the following command to count the sockets in the processor.

grep "physical id" /proc/cpuinfo | sort -u
  physical id     : 0
  physical id     : 1

Note:  We don’t recommend using OS commands such as nmon and sar to extract CPU utilization. This is because those calculations include CPU utilization by other processes and might not reflect the actual CPU that is used by the database.

To estimate available cores by using the AWR report

You can also derive CPU utilization from the first section of the AWR report. Here’s an excerpt from the report.

In this example, the CPUs count is 80, which indicates that these are logical (virtual) CPUs. You can also see that this configuration has two sockets, one physical processor on each socket (for a total of two physical processors), and 40 cores for each physical processor or socket. 

Option 2: Estimate CPU utilization by using OS statistics.

You can check the OS CPU usage statistics either directly in the OS (using sar or another host OS utility) or by reviewing the IDLE/(IDLE+BUSY) values from the Operating System Statistics section of the AWR report. You can see the seconds of CPU consumed directly from v$osstat. The AWR and Statspack reports also show this data in the Operating System Statistics section.

If there are multiple databases on the same box, they all have the same v$osstat values for BUSY_TIME.

If there are no other major CPU consumers in the system, use the following formula to calculate the percentage of CPU utilization:

   Utilization = Busy time / Total time

   Busy time = requirements = v$osstat.BUSY_TIME

   C = Total time (Busy + Idle)

   C = capacity = v$ostat.BUSY_TIME + v$ostat.IDLE_TIME

   Utilization = BUSY_TIME / (BUSY_TIME + IDLE_TIME)

      = -1,305,569,937 / (1,305,569,937 + 4,312,718,839 )

      = 23% utilized

Option 3: Estimate CPU utilization by using database metrics.

If multiple databases are running in the system, you can use the database metrics that appears at the beginning of the report.

To get CPU utilization metrics, use this formula:

   Database CPU usage (% of CPU power available) = CPU time / NUM_CPUS / elapsed time

where CPU usage is described by CPU time and represents the time spent on CPU, not the time waiting for CPU. This calculation results in:

   = 312,625.40 / 11,759.64/80 = 33% of CPU is being used

   Number of cores (33%) * 80 = 26.4 cores

   Total cores = 26.4 * (120%) = 31.68 cores

You can use the greater of these two values to calculate the CPU utilization of the Amazon RDS or Aurora DB instance.

Note: On IBM AIX, the calculated utilization doesn’t match the values from the operating system or the database. These values do match on other operating systems.

Estimate memory requirements by using memory statistics.

You can use the AWR report to calculate the memory of the source database and match it in the target database. You should also check the performance of the existing database and reduce your memory requirements to save costs, or increase your requirements to improve performance. That requires a detailed analysis of the AWR response time and the service-level agreement (SLA) of the application. Use the sum of Oracle system global area (SGA) and program global area (PGA) usage as the estimated memory utilization for Oracle. Add an extra 20 percent for the OS to determine a target memory size requirement. For Oracle RAC, use the sum of the estimated memory utilization on all RAC nodes and reduce the total memory, because it’s stored on common blocks.

   Total instance memory in use = SGA + PGA = 220 GB + 45 GB  = 265 GB

Add 20 percent of buffer:

   Total instance memory = 1.2 * 265 GB = 318 GB 

Because SGA and PGA account for 70 percent of host memory, the total memory requirement is: 

   Total host memory = 318/0.7 = 464 GB

Note: When you migrate to Amazon RDS for Oracle, the PGA and SGA are pre-calculated based on a predefined formula. Make sure that the pre-calculated values are close to your estimates.

Determine the DB instance type based on disk I/O, CPU, and memory estimates.

Based on the estimates  in the previous steps, the capacity of the target Amazon RDS or Aurora database should be:

In the target Amazon RDS or Aurora database, you can map these values to the db.r5.16xlarge instance type, which has a capacity of 32 cores, 512 GB of RAM, and 13,600 Mbps of throughput. For more information, see the AWS blog post Right-size Amazon RDS instances at scale based on Oracle performance metrics.