Tune Amazon RDS for Oracle CDBs with Amazon Performance Insights

Amazon Relational Database Service (Amazon RDS) for Oracle supports the Oracle Multitenant configuration on Oracle Database versions 19c and 21c running Oracle Enterprise Edition or Standard Edition 2. With this release, the Amazon RDS for Oracle DB instance can operate as a multitenant container database (CDB) hosting one or more pluggable databases (PDBs). A PDB is a set of schemas, schema objects, and non-schema objects that logically appears to a client as a non-CDB.

With Oracle Multitenant, you can consolidate standalone databases by either creating them as PDBs or migrating them to PDBs. Database consolidation can deliver improved resource utilization for DB instances, reduced administrative load, and potential reduction in total license requirements.

Amazon RDS Performance Insights is a database performance tuning and monitoring feature that helps you quickly assess the load on your database, and determine when and where to take action. Performance Insights uses a dashboard that visualizes database load, along with which SQL statements are causing the load and why, making detecting performance problems straightforward for both experts and non-experts.

When analyzing the load on an Oracle CDB, you might want to identify which PDBs contribute the most to the database load. You might also want to compare the performance of individual PDBs that are running similar queries to fine-tune performance. For more information about Oracle CDBs, see Working with CDBs in RDS for Oracle.

In this post, we will discuss how to identify resource-intensive SQL queries at a PDB level on a visual dashboard in Performance Insights.

Performance Insights has introduced a new PDB dimension to help you visualize and analyze the distribution of the load on individual PDBs within the CDB on a RDS for Oracle instance. Now, you can slice the database load metric by the PDB and SQL dimensions to identify the top queries running on each of the PDBs.

Analyzing RDS Oracle Workload with Multitenant architecture

Initial performance can be monitored by Amazon CloudWatch, which gives an overall view of the RDS instance (for more information, see Monitoring Amazon RDS metrics with Amazon CloudWatch). However, this doesn’t highlight the database load and queries running on an individual PDB. For example, the following screenshot highlights CloudWatch metrics for an Oracle Multitenant database with two PDBs deployed on Amazon RDS. This is a general view to check how a CDB is performing, irrespective of the load running on an individual PDB.

Similarly, Enhanced Monitoring, which is used to monitor OS-level metrics of an RDS instance in real time, also gives you the performance at the instance level but not at the PDB level. Enhanced Monitoring is used to identify different processes or threads running at the server level. This is useful when you’re troubleshooting a performance problem that isn’t visible from CloudWatch because it’s based on a 1-minute average. Enhanced Monitoring can be configured to capture metrics with a granularity as fine as 1 second. For more details comparing Enhanced Monitoring and CloudWatch metrics, see Differences between CloudWatch and Enhanced Monitoring metrics. The following screenshot shows an example of Enhanced Monitoring for an RDS for Oracle instance multitenant database with two PDBs.

So, how do you monitor queries or database load running at the PDB level, given that it can’t be identified from CloudWatch metrics or Enhanced Monitoring?

Amazon RDS Performance Insights dashboard helps you identify resource-intensive SQL queries at the database level. This helps you identify the actual wait events, top SQL queries, top hosts, and top users running at the RDS instance. It uses average active session (AAS) as a load aggregation method over time. Amazon RDS Performance Insights helps you analyze database performance at the CDB level as well as the PDB level.

Considerations

By default, Performance Insights is enabled at database creation. You can also enable it by modifying an existing instance. Performance Insights is fully automated, and in most cases the overhead is around 1 percent of a vCPU. All the data storage, processing, and aggregation are automatically managed and done outside of the database host to minimize impact on the database. It stores seven days of performance history in a rolling window at no additional cost. If you need longer-term retention, you can choose to pay for up to two years of performance history retention. And Performance Insights samples every second, which makes it the right tool to identify even a tiny problematic query.

You can use the AWS Management Console for Amazon RDS to start looking at Performance Insights for CDBs by choosing the CDB you want to evaluate from the dropdown menu of Performance Insights, as shown in the following screenshot.

The following screenshot is an example of Performance Insights for a multitenant database with two PDBs deployed on an RDS for Oracle instance. It highlights the AAS running on a container database. Initially, it’s sliced by Pluggable Database (PDB), which you can change in the Sliced by menu. In our example, we have database load running on a PDB (ORAPDB2). The second PDB (ORAPDB1) is sitting idle and doesn’t have any queries or database load running on it. To look at the overall load on the CDB and all PDBs, choose CDB$ROOT. Data used in analysis is just a sample workload, your actual analysis will be based on your database workload.

By choosing the respective PDB, you can view top SQL queries or wait events for that PDB. In our example, ORAPDB1 had little or no load running on it. And because the database load is only on ORAPDB2, choosing it will highlight the workloads that need further investigation.

If you choose a PDB that doesn’t have a query or database running on it, you’ll see the AAS graph showing nothing running on it.

You can also look at top queries or top waits running at the instance level to identify which query could be a potential candidate for performance tuning. Or if there’s a query that isn’t using the optimal execution plan and so can be further tuned. For example, the Executions/sec metrics highlights the number of queries run per second. If a query is running frequently and is taking longer to complete, start looking at it and identify which part is more costly to decide how to further fine-tune the query. You can also identify the completed query by hovering over the incomplete query.

You can also identify the top PDB in terms of customer workload, which can simplify the troubleshooting process. In our lab, only queries are running on ORAPDB2. Hence it appears as the top PDB.

Conclusion

In this post, we discussed the new features recently released in Amazon RDS Performance Insights that are useful to identify individual PDB performance. Also, how it can help you to quickly identify changes in the performance of a SQL query. And if necessary, make improvements to your application to optimize the query, add or remove an index, or scale up your database. Most importantly it’s the only native AWS tool that you can use to look at database level information.

Try out the new features for yourself, and let us know your questions and feedback in the comments section.

About the Authors

Viqash Adwani is a Sr. Database Specialty Architect with Amazon Web Services. He works with internal and external Amazon customers to build secure, scalable, and resilient architectures in the AWS Cloud and help customers perform migrations from on-premises databases to Amazon RDS and Amazon Aurora databases.

Manash Kalita is a Senior Database Specialist Solutions Architect with Amazon Web Services. He works with AWS customers designing customer solutions on database projects, helping them migrate and modernize their existing databases to the AWS Cloud as well as orchestrate large-scale migrations in AWS.