Delivering timely and accurate finance and sales reports is the most critical responsibility that any Oracle E-Business Suite Applications Manager has. For just about every business, these reports must be available every morning before business users log in.
To use an easy to understand example, this blog entry will reference a cash flow report that needs to arrive in the CFO’s inbox first thing in the morning, every morning.
For this to happen, completion times of ETL jobs or reporting programs cannot vary and must be consistent. However, there are any number of potential database performance issues that disrupt this schedule.
This blog entry looks at a few possible causes of performance issues within an Oracle E-Business Suite database, how to identify them and, most importantly, how to resolve them.
First Root Cause of Poor Database Performance: Changes in Data Volume
If you’re trying to diagnose the root cause of a database performance issue, it is important to closely examine the response times of the underlying SQLs in an ETL job or a program query. One of the factors that defines the response time of a piece of SQL code is the data volume. Just because a query executed quickly under certain testing conditions, it does not mean it will be fast enough in Production because the data is vastly different in a Development or Test environment compared to Production.
The way to guard against this is to conduct thorough load testing in the non-production environment; but unfortunately comprehensive load testing too often gets left out of project plans because it is viewed as a “nice to have.”
To be truthful, there are a lot of times that you can get by without doing exhaustive load testing. However, when issues do occur, they typically strike during the very moments when you can least afford a business disruption, such as when transactions spike during the holiday season.
Oracle E-Business Suite doesn’t provide a lot of Out-of-the-Box tools for diagnosing and correcting these types of issues. So the first thing that an Oracle Apps DBA who is troubleshooting a database performance issue should do is to manually measure the data volume being processed relative to the time it takes to process, and adjust SQL as needed.
Second Root Cause of Database Performance Issues: Read-Only Production Access Leading to Bad SQL
A second common root cause of poor database performance is tied to an unintentional, yet all too common error. This often occurs when a developer who has read-only access to the Production environment runs a badly written SQL query to retrieve data. Because the developer lacks the visibility needed to formulate an efficient SQL query, the poorly written query causes a large amount of disk reads, which in turn cause IO waits, which degrade database performance.
The upshot of all of this is that those critical cash flow reports fail to reach the CFO on time.
Fine Tune SQL to Streamline Your Database and Deliver Critical Data On Time
Tuning Oracle E-Business Suite’s SQL to use the proper query plan is the fastest, least risky and most cost-effective way to resolve these types of poor Oracle database performance issues.
However, conducting a manual search for poorly formed SQL statements is a tedious, time-consuming and labor intensive endeavor – even when you don’t need to generate a trace file. That’s where Syntax Enterprise Care® custom Oracle EBS monitoring directly boosts your overall operating efficiency.
Here’s one example of Syntax Enterprise Care® custom monitors in action:
Syntax EnterpriseCare® detects a PL/SQL query selecting two different “issue transaction” type id’s based on passed values.
Because both id’s are queried simultaneously on either side of an OR function, Oracle performs a full table scan of ALL rows when executing the query, even though indexes are available. These full table scans create a high demand for IO from storage and degrades system performance for all users and concurrent requests.
When this occurs, Syntax EnterpriseCare® creates an alert in ServiceNow and the Syntax DBA team responds by splitting the PL/SQL statement into 2 parts that run serially but remain linked using the UNION ALL command. This removes the need for the OR function and eliminates system slowing full table scans.
The results of such fixes can be dramatic. One actual use case saw concurrent request times drop from 60 plus minutes to just over 3 seconds!
Automate Your Way to Improved Oracle EBS Efficiency with Syntax EnterpriseCare®
Do you want to know the best part about the above scenario?
An automated process detected the Issue Transaction error and generated an alert before business users were ever aware. Business never suffered a disruption, no one needed to open a ticket for a Service Request – the Syntax DBA team didn’t even need to create trace files — all thanks to Syntax EnterpriseCare®.
In practical terms, it means that not only did the cash flow reports arrive in the inbox on time, the CFO never knew there was even an issue.
Syntax EnterpriseCare® is the industry’s leading ERP monitoring tool for Oracle E-Business Suite and JD Edwards because it proactively polls your systems to identify and resolve issues before they grow to become problems.
Click on the links below to see how Syntax EnterpriseCare® will help you:
- Resolve EBS 500 Internal Server Errors
- Stop slow SQL code in its tracks with Top I/O Monitoring
- Troubleshoot FTP/SFTP integration issues
For a general overview of this state-of-the-art monitoring solution, visit our Syntax EnterpriseCare® web page or request a 1-1 conversation with one of our experts.