Estimate the Amazon RDS engine size for an Oracle database by using AWR reports - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsRelated resources

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

Created by Abhishek Verma (AWS) and Eduardo Valentim (AWS)

Environment: Production

Source: Oracle Database

Target: Amazon RDS or Amazon Aurora

R Type: Re-architect

Workload: Oracle

Technologies: Databases; Migration

AWS services: Amazon RDS; Amazon Aurora

Summary

When you migrate an Oracle database to Amazon Relational Database Service (Amazon RDS) or Amazon Aurora, computing the CPU, memory, and disk I/O for the target database is a key requirement. You can estimate the required capacity of the target database by analyzing the Oracle Automatic Workload Repository (AWR) reports. This pattern explains how to use AWR reports to estimate these values.

The source Oracle database could be on premises or hosted on an Amazon Elastic Compute Cloud (Amazon EC2) instance, or it could be an Amazon RDS for Oracle DB instance. The target database could be any Amazon RDS or Aurora database.

Note: Capacity estimates will be more precise if your target database engine is Oracle. For other Amazon RDS databases, the engine size can vary due to differences in database architecture.

We recommend that you run the performance test before you migrate your Oracle database.

Prerequisites and limitations

Prerequisites

  • An Oracle Database Enterprise Edition license and Oracle Diagnostics Pack license in order to download AWR reports.

Product versions

  • All Oracle Database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c,19c.

  • This pattern doesn’t cover Oracle Engineered Systems or Oracle Cloud Infrastructure (OCI).

Architecture

Source technology stack  

One of the following:

  • An on-premises Oracle database

  • An Oracle database on an EC2 instance

  • An Amazon RDS for Oracle DB instance

Target technology stack

  • Any Amazon RDS or Amazon Aurora database

Target architecture

For information about the full migration process, see the pattern Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT.

Automation and scale

If you have multiple Oracle databases to migrate and you want to use additional performance metrics, you can automate the process by following the steps described in the blog post Right-size Amazon RDS instances at scale based on Oracle performance metrics.

Tools

  • Oracle Automatic Workload Repository (AWR) is a repository that’s built into Oracle databases. It periodically gathers and stores system activity and workload data, which is then analyzed by Automatic Database Diagnostic Monitor (ADDM). AWR takes snapshots of system performance data periodically (by default, every 60 minutes) and stores the information (by default, up to 8 days).  You can use AWR views and reports to analyze this data.

Best practices

  • To calculate resource requirements for your target database, you can use a single AWR report, multiple AWR reports, or dynamic AWR views. We recommend that you use multiple AWR reports during the peak load period to estimate the resources required to handle those peak loads. In addition, dynamic views provide more data points that help you calculate resource requirements more precisely. 

  • You should estimate IOPS only for the database that you plan to migrate, not for other databases and processes that use the disk.

  • To calculate how much I/O is being used by the  database, don’t use the information in the Load Profile section of the AWR report. Use the I/O Profile section instead, if it’s available, or skip to the Instance Activity Stats section and look at the total values for physical read and write operations.

  • When you estimate CPU utilization, we recommend that you use the database metrics method instead of operating system (OS) statistics, because it’s based on the CPU used only by databases. (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.

  • Consider I/O throughput limits―Amazon Elastic Block Store (Amazon EBS) throughput and network throughput―for the specific instance size when you’re determining the right instance type.

  • Run the performance test before migration to validate the engine size.

Epics

TaskDescriptionSkills required

Enable the AWR report.

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

DBA

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;
DBA

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;
DBA

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;
DBA
TaskDescriptionSkills required

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:

  • Load Profile section of the AWR report

  • Instance Activity Stats section of the AWR report (use this section for Oracle Database 12c or later)

  • I/O Profile section of the AWR report (use this section for Oracle Database versions before 12c)

  • AWR views

The following steps describe these four methods.

DBA

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.

 

Per Second

Per Transaction

Per Exec

Per Call

DB Time(s):

26.6

0.2

0.00

0.02

DB CPU(s):

18.0

0.1

0.00

0.01

Background CPU(s):

0.2

0.0

0.00

0.00

Redo size (bytes):

2,458,539.9

17,097.5

 

 

Logical read (blocks):

3,371,931.5

23,449.6

 

 

Block changes:

21,643.5

150.5

 

 

Physical read (blocks):

13,575.1

94.4

 

 

Physical write (blocks):

3,467.3

24.1

 

 

Read IO requests:

3,586.8

24.9

 

 

Write IO requests:

574.7

4.0

 

 

Read IO (MB):

106.1

0.7

 

 

Write IO (MB):

27.1

0.2

 

 

IM scan rows:

0.0

0.0

 

 

Session Logical Read IM:

 

 

 

 

User calls:

1,245.7

8.7

 

 

Parses (SQL):

4,626.2

32.2

 

 

Hard parses (SQL):

8.9

0.1

 

 

SQL Work Area (MB):

824.9

5.7

 

 

Logons:

1.7

0.0

 

 

Executes (SQL):

136,656.5

950.4

 

 

Rollbacks:

22.9

0.2

 

 

Transactions:

143.8

 

 

 

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.

DBA

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.

Statistic

Total

per Second

per Trans

physical read total IO requests

2,547,333,217

3,610.28

25.11

physical read total bytes

80,776,296,124,928

114,482,426.26

796,149.98

physical write total IO requests

534,198,208

757.11

5.27

physical write total bytes

25,517,678,849,024

36,165,631.84

251,508.18

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

DBA

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.

 

Read+Write Per Second

Read per Second

Write Per Second

Total Requests:

4,367.4

3,610.3

757.1

Database Requests:

4,161.5

3,586.8

574.7

Optimized Requests:

0.0

0.0

0.0

Redo Requests:

179.3

2.8

176.6

Total (MB):

143.7

109.2

34.5

Database (MB):

133.1

106.1

27.1

Optimized Total (MB):

0.0

0.0

0.0

Redo (MB):

7.6

2.7

4.9

Database (blocks):

17,042.4

13,575.1

3,467.3

Via Buffer Cache (blocks):

5,898.5

5,360.9

537.6

Direct (blocks):

11,143.9

8,214.2

2,929.7

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)

DBA

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;
DBA
TaskDescriptionSkills required

Choose a method.

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

  • By using the actual available cores of the processor

  • By using the utilized cores based on OS statistics

  • By using the utilized cores based on database statistics

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.

DBA

Option 1: Estimate requirements based on available cores.

In AWR reports:

  • CPUs refer to logical and virtual CPUs. 

  • Cores are the number of processors  in a physical CPU chipset. 

  • A socket is a physical device that connects a chip to a board. Multi-core processors have sockets with several CPU cores.

You can estimate available cores in two ways:

  • By using OS commands

  • By using  the AWR report

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.

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

XXXX

<DB_ID>

XXXX

1

05-Sep-20 23:09

12.1.0.2.0

NO

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

<host_name>

Linux x86 64-bit

80

80

2

441.78

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. 

DBA

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.

Statistic

Value

End Value

FREE_MEMORY_BYTES

6,810,677,248

12,280,799,232

INACTIVE_MEMORY_BYTES

175,627,333,632

160,380,653,568

SWAP_FREE_BYTES

17,145,614,336

17,145,872,384

BUSY_TIME

1,305,569,937

 

IDLE_TIME

4,312,718,839

 

IOWAIT_TIME

53,417,174

 

NICE_TIME

29,815

 

SYS_TIME

148,567,570

 

USER_TIME

1,146,918,783

 

LOAD

25

29

VM_IN_BYTES

593,920

 

VM_OUT_BYTES

327,680

 

PHYSICAL_MEMORY_BYTES

474,362,417,152

 

NUM_CPUS

80

 

NUM_CPU_CORES

80

 

NUM_CPU_SOCKETS

2

 

GLOBAL_RECEIVE_SIZE_MAX

4,194,304

 

GLOBAL_SEND_SIZE_MAX

2,097,152

 

TCP_RECEIVE_SIZE_DEFAULT

87,380

 

TCP_RECEIVE_SIZE_MAX

6,291,456

 

TCP_RECEIVE_SIZE_MIN

4,096

 

TCP_SEND_SIZE_DEFAULT

16,384

 

TCP_SEND_SIZE_MAX

4,194,304

 

TCP_SEND_SIZE_MIN

4,096

 

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

DBA

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.

 

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

184662

28-Sep-20 09:00:42

1226

35.8

End Snap:

185446

06-Oct-20 13:00:20

1876

41.1

Elapsed:

 

11,759.64 (mins)

 

 

DB Time:

 

312,625.40 (mins)

 

 

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.

DBA
TaskDescriptionSkills required

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.

  1. Check for the metrics in the Instance Efficiency Percentage table. The table uses the following terms:

    • Buffer Hit % is the percentage of times a particular block was found in the buffer cache instead of performing a physical I/O. For better performance,  target 100 percent . 

    • Buffer Nowait % should be close to 100 percent.

    • Latch Hit % should be close to 100 percent. 

    • % Non-Parse CPU is the percentage of CPU time spent in non-parsing activities. This value should be close to 100 percent..

    Instance Efficiency Percentages (target 100%)

    Buffer Nowait %:

    99.99

    Redo NoWait %:

    100.00

    Buffer Hit %:

    99.84

    In-memory Sort %:

    100.00

    Library Hit %:

    748.77

    Soft Parse %:

    99.81

    Execute to Parse %:

    96.61

    Latch Hit %:

    100.00

    Parse CPU to Parse Elapsd %:

    72.73

    % Non-Parse CPU:

    99.21

    Flash Cache Hit %:

    0.00

     

     

    In this example, all the metrics look fine, so you can use the SGA and PGA for the existing database as the capacity planning requirement.

  2. Check the memory statistics section and calculate the SGA/PGA.

     

    Begin

    End

    Host Mem (MB):

    452,387.3

    452,387.3

    SGA use (MB):

    220,544.0

    220,544.0

    PGA use (MB):

    36,874.9

    45,270.0

   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.

DBA
TaskDescriptionSkills required

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:

  • 68 cores of CPU

  • 143 MBPS of throughput  

  • 4367 IOPS for disk I/O

  • 464 GB of memory

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.

DBA

Related resources