🚀 Post-Migration Insights: Boosting Performance on AWS RDS for Oracle
I encountered an interesting performance challenge after migrating an Oracle database to AWS RDS. One of our critical views, which ran seamlessly on-premises, started taking significantly more time on RDS.
🛠 Analyzing the Issue:
After digging into the view, I found that it involved an external table sourcing data from a CSV file stored outside the database. While examining the wait events through trace files, the predominant wait was external table read waits.
This raised the question: why was this happening?
💡 The Discovery:
• On-premises, the CSV file used by the external table resided on a local hard drive (DB server) enabling quick data access.
• In AWS RDS, however, the external table was mounted on EBS (Elastic Block Store), which operates like a Storage server (SAN)
I.E., If you create directory in rds ( create or replace directory example) the mount location comes from ebs volume
Even after upgrading the storage type for better throughput, the performance still lagged behind the on-premises environment. The reason? AWS RDS is a PaaS solution, meaning we don’t control the underlying infrastructure as we would do with an on-prem setup.
🏗️ The Solution:
To overcome this, I decided to convert the external table into an internal table by loading its data into the database.
🚀 The Results:
So, I created the internal table from external table (CTAS) and running the query again:
1. The performance improved dramatically on the second execution.
2. Question arises however both internal and external table comes from EBS how internal table performing better?
Internal tables leverage Oracle’s buffer cache to store frequently accessed data blocks in memory, significantly reducing I/O.
• On the other hand, external tables always perform direct reads from the file on disk, resulting in repeated I/O operations and higher wait times (hence the external table read waits).
🎯 Key Takeaways:
• External Tables: Great for staging data but involve direct I/O, making them slower for repeated queries.
• Internal Tables: Store data inside the database, taking advantage of Oracle’s caching mechanisms, thus improving performance.
• AWS RDS Challenges: Since RDS is PaaS, the storage setup might not match on-prem setups like local hard drives. Performance tuning often requires understanding how Oracle interacts with the underlying AWS infrastructure.
All the Best!!
#AWSRDS #Performancetunning #extenaltable #oracle
AWS Certified Solution Architect Associate | Workmates Core2Cloud Solution Pvt Ltd
3moLooks great