During our last A/P month end close we were pulled into an issue where the business was seeing the system take 5-10 minutes to save per invoice when they had to change site or payment information, and another 5-10 minutes to validate the invoice as well. Obviously this made the system pretty much unusable for the individuals that had to perform these tasks as part of their core job functions, so we sat down and saw that the SQL_ID being passed each time was c9zkjtwxxu17t with an explain plan cost of the SQL over 40,000 GB.
Today we sat down with several individuals with the A/P team to try and brainstorm some ways to research and replicate this issue, when we found that the Invoice Workbench was freezing even if they were not trying to save or change supplier site information but one track we need to verify is if this affects only Validated invoices. Enter my co-worker that did a search on My Oracle Support during the meeting and found the article "Performance problem in Invoice Workbench: It Takes Long Time When Changing The Supplier Site" under document ID 1572029.1 which has the patch required to fix this and details what the true cause of this issue is.
We also learned that this has existed ever since we went to R12.1.3, but over time it slowly began to take more and more time to save or validate. Why is this? Well, I am glad you asked! The root cause is in XLA tables, which was introduced with R12 architecture and these tables keep growing the more business/conversion you do so if you have a bad query hitting a small amount of data it can perform "well enough" so the business does not mind the delay but if you take the same query and greatly increase the amount of data it runs again there goes your system performance.
No comments:
Post a Comment