Tuning very large databases, more commonly known as VLDB, has always been a challenging part of DBAs jobs. Delivering optimal performance for applications that access a VLDB is essential for the business.
When Oracle database best practices are applied, many tuning nightmares have disappeared. The following are the most common performance issues that can affect VLDBs:
1.) Suboptimal application coding
2.) Inefficient application and database design
3.) Inaccurate or missing optimizer statistics
4.) Lack of indexes or too many indexes
5.) Limited hardware resources
6.) Bad network design
In this blog I will review common problems that impact VLDBs performance and will also cover a real world scenario demonstrating the challenge and our team’s solution.
Image Source: https://docs.adobe.com/docs/en/aem/6-0/deploy/configuring/performance.html
To demonstrate the issue of suboptimal application coding, lets look at a classic example of a poor performance incident—an issue that was ultimately resolved with a simple code modification.
A reconciliation batch job scheduled to run at the end of each business day was performing inconsistently. The job sometimes took only 30 minutes to complete and at other times up to four hours. During the database’s worst performance, it took nearly 24 hours to complete.
This inconsistent performance significantly impacted the subsequent dependent jobs, and as a result, several key business users were unable to get their daily reports on time.
The job itself was straightforward, there were no complex queries or complex logic.
The job had to perform the following simple tasks:
- Delete data from the target tables.
- Load data from text files into the target tables.
- Run queries against the target tables to generate reports.
During the course of investigation, the following behavior was observed:
The database was growing in size and at a uniform rate each day.
Important queries were favoring the nested loops in the query execution plan.
As a temporary workaround the following action plan was used:
- Reorganization of the target tables using the alter table ... move command.
- All indexes in the schema were rebuilt.
- Application schema statistics were gathered.
- In case of the failure of the preceding steps to resolve the inconsistent performance, Schema was exported into another database residing on a different server having more CPU and memory capacity.
Despite these workarounds, the problem reappeared and gradually returned to the worst state (taking nearly 24 hours to complete).
Interestingly, when the person responsible for the application at the client was contacted, he mentioned that data in all tables was deleted and subsequently loaded from different sources, and the observed growth was unlikely with the amount of data he was loading daily.
Our team focus shifted to the data load script.
Upon reviewing the code of the data load script, a classic mistake was observed – the data was removed from the tables with the DELETE command followed by direct path loads. If you go by the basics and best practices, you can easily spot the reason for the consistent growth in the database. When DELETE is used to remove complete data from a table, the high-watermark is not reset; therefore, subsequent direct-path load inserts start loading data above the high-watermark, ignoring the free data blocks below the high-watermark.This approach causes continuing data growth and consumes significant amounts of free space in the datafiles. Hence, queries on these tables were performing poorly.
The simple solution to this issue was to replace the DELETE command with TRUNCATE TABLE. Once this change was applied, the job was consistently completed in just 30 minutes.
By focusing on the basics like “DELETE” vs. the “Truncate” command as in this case and adopting the strategy of using the “Truncate” the entire job was completed in just 30 minutes of time. Also, there have been no issues with performances since the problem was fixed.
It is important to get the basics right, and as you can see tuning the code just a little made the DBA job easy and trouble free. For any questions on the topic click below. You can also leave a comment in the fields below.