R12.1.3: Payables Open Interface Import performance issues

With all of the issues that have been rattling out of our R12.1.3 instance, it is good to learn about one in advance of having it happen to us when we can least afford it.  In that light, today's post is about the patches located within the My Oracle Support page "Performance Issue In Payables Open Interface Import" in note ID 1900462.1 where an import of several million lines did not complete in a timely fashion.  Do you want to wait until your Import had this many lines, or do you want to prevent disaster and plan for these patches?

Weekend Learning: Total Session History utility

I cannot wait to get my hands on Chris Craft's new utility Total Session History which is supposed to combine AWR (Automatic Workload Repository) and ASH (Active Session History).  This utility is targeted at Exadata platforms to give us a complete look at everything that is going on in our systems so I want to dig into this to see what I am missing right now in my mental picture of what our system is doing!  If you have a chance to use this before I do next week, let me know what you think in the comments!

OAUG Connection Point - AppsTech 2014: Back Presenting!

Just a few days ago I told you that the OAUG Connection Point - AppsTech 2014 was now going to be presented via E-Learning, and that the schedule was full so I would not be able to present.  Well it looks like there have been some changes, as I have heard back from the organizers and I am back on the schedule!  That means that I will get to share my Workflow presentation again on July 21st!

R12.1.3: Invoice Workbench freezes on saving

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.

Using shell scripts to monitor DB files

Since I am sure that you got enough Twitter information from KScope this week, I am going to skip the regular Thursday Twitter to focus on on a tweet from Xavier Picamal about monitoring log files!  Why is this such a good article to read?  Well I am spending some time planning out additional monitoring for our platforms using shell scripts like this, and seeing different examples of what can be done or different ways to do the same thing can only expand our horizons and make our solutions better.

While Xavier is looking through .log files, this makes me think about being able to search through .trc files that get automatically generated.  What does this make you think of?  Have you seen other examples of how to do this, or other things to monitor in this way?

AWR taking too much SYSAUX space

While we might take our AWR data for granted, but the My Oracle Support article "How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace" from note 287679.1 shows us several ways that we can run out of space in SYSAUX.  These points should be yet another monitoring hook for us, so we keep our system up and running 100% especially if we decide to make modifications to how we capture or retain AWR data!

OAUG Connection Point - AppsTech 2014: Now Online!

I let you know last month that I was selected for the OAUG Connection Point - AppsTech conference in Pittsburgh for the second year in a row, but unfortunately the level of registrations were not near last year's level so the conference has been modified so it is now online only.  As a consequence, the list of presenters has been trimmed down to fit an E-Learning model so I will not be presenting at the conference this year.  I have been told that there will be a fee for the conference, but it has not yet been announced what it is, and when I get more information I will be sure to pass it on to you.

If you were hoping to see me in Pittsburgh, I am sorry about this turn of events but I am sure we will have many more opportunities in the future to meet!

Weeklong Learning: KScope

Last week I mentioned on the Thursday Twitter that you could view some of the sessions from the KScope, but if you aren't paying attention to KScope on Twitter this week then you are just plain missing out!  I have learned that many times presenters will link to their presentations or even answer extra questions so you do not have to be at the conference to get the benefit of the information shared there.

Weekend Learning: EAS sign on for multiple Shared Services

I may have mentioned recently that we help administer Hyperion products in addition to our main EBS application, and for those of you out there in similar situations I want to share this post by John Goodwin explaining how he was able to utilize the sign in information for a standalone EAS over to multiple Shared Services instances.  Reading through this it may seem complicated, but taking a step back it is fairly straight-forward and elegant in the solution!  I learned a bit more from this one, that I did not expect to get out of the article, so I am glad that I found the article!

Happy Birthday to the blog!

It is hard to believe that just a year ago I was marveling about having over a thousand hits, and today I am on the verge of TWENTY thousand hits in just TWO years!  Not bad for a little site I started as a personal outlet for some thoughts/opinions I had on some subjects huh?

I have branched out to new social media outlets (Twitter and Google+), attended multiple conferences (OAUG Connection Point - AppsTech and Hotsos Symposium) and continue to expand my horizons by learning from others!  Over the next month, I will be expanding and changing the site a bit to make it more personalized and easier to find and keep track of some things.

Here's to another year of fun and learning!

Unmounting ASM disk by querying V$ASM_FILE on 11gR2

The title of My Oracle Support note 1145365.1 "Alert: Querying V$ASM_FILE Gives ORA-15196 After ASM Was Upgraded From 10gR2 to 11gR2 with an AU size > 1M" does not quite tell you the full story because it neglects to tell you what the ORA-15196 message means or what it results in!  The error is for "invalid ASM block header" and it results in the disk going offline if it has less than a certain number of files in the disk group.

I think it would be good to know if a disk will go offline all of a sudden if the GV$ASM_FILE or V$ASM_FILE objects are queried from, so take this as an opportunity to be proactive and run the query in this note to investigate your ASM disk groups before you find out the wrong way that this is an issue!

Thursday Twitter - 6/19/14

Yes, there was something besides World Cup tweets on Twitter this week.  :}

Announcements:

https://twitter.com/Exadata/status/479662416889126912 - Announcing the Exadata X4 platform!

https://twitter.com/ddfdba/status/478990788060135424 - David Fitzjarrell shares that Apress/IOUG press is now online.

https://twitter.com/lakshram/status/478948781639995392 - Laskhmi Sampath lets us know that MS Office 2013 is now certified with EBS R12.1.3!

https://twitter.com/RaghuSripada/status/478903134795087873 - I don't know how, but Raghuram found my tweet from a few days ago and added it to his daily EM paper!

https://twitter.com/BW_Contract/status/478894158883454976 - OAUG creates a new SIG and GEO!

Training/Free Stuff:

https://twitter.com/OracleBlogs/status/479632864330674177 - Did you know that the Developer Relations YouTube channel is a year old now?  It even has more than 100 videos up too!

https://twitter.com/OraclePress/status/478957839785132033 - Here are 23 FREE Q&As from the OCP Upgrade to 12c exam guide!

https://twitter.com/OracleDatabase/status/478937644869898240 - A whitepaper on Oracle Partitioning!

General News/Information:

https://twitter.com/OracleSecurity/status/479674988006752256 - Oracle Security posits that organized crime is as powerful as some countries.  Agree or disagree?

https://twitter.com/JLOracle/status/479674719705133057 - Jonathan Lewis on the costs for DELETE operations.

https://twitter.com/DBAKevlar/status/479651586869059584 - Kellyn Pot'Vin shares a post from a WebLogic blog on analyzing a stuck piece of code.

https://twitter.com/dba_hba/status/479623095917899776 - Allan Robertson shares the first part of Service Reliability and Pythian shares part two here https://twitter.com/Pythian/status/479626536023121920.

https://twitter.com/CondeBond/status/479608920101896193 - Xavier Picamal shares a LinkedIn debate about log file sync wait.

https://twitter.com/OracleSecurity/status/478986709028589569 - What are 5 things you can learn from security threats?

https://twitter.com/OracleBlogs/status/478831701406535680 - Do you support Oracle Fusion Middleware?  Then maybe this pretty new Oracle blog on practical experience with the application might be your cup of tea.

https://twitter.com/kylehhailey/status/478821132628262912 - Kyle Hailey shares more insights from Andrew Holdsworth: if you are updating 1 row per ms, do you know how long it takes for 1 trillion rows?

https://twitter.com/DBAKevlar/status/478810546649968640 - Do you need to present data?  Here are 50 ways to do it!

https://twitter.com/PrasannaPeshkar/status/478777801684688896 - Prasanna Peshkar found this post from Tanel Poder to be pretty awesome.

https://twitter.com/oraclemagazine/status/478766616420040704 - Oracle shares trends in data center consolidation.

https://twitter.com/OraTweets/status/478614088105201664 - Iggy Fernandez has part 2 of a series about the EXPLAIN PLAN.

https://twitter.com/leight0nn/status/478338159596421120 - Leighton Nelson tells us how MTTR setting changes the DBWR work profile.

https://twitter.com/fuadar/status/478129331060101120 - Fuad Arshad shares a take on the Oracle 12c In-Memory Option.

Conference News:

https://twitter.com/debralilley/status/479615019702046720 - Why does Debra Lilley present at user groups?

https://twitter.com/JLOracle/status/478990049632325633 - Don't miss an opportunity to learn from Jonathan Lewis!

https://twitter.com/odtug/status/478962428865425409 - Not going to KScope?  You can view it remotely!

https://twitter.com/pythianpartners/status/478952837154877441 - Pythian was at the ECRUG conference this year and so was Subhajit https://twitter.com/DCSubhajit/status/478909515287764992!

https://twitter.com/debralilley/status/478934279217745921 - Debra was at the BGOUG conference!  It's nice to be an ACE Director!

https://twitter.com/kylehhailey/status/478880636888154112 - In a few days, I'll tell you why this tweet from Andrew Holdsworth at the AOUG Vienna is SO funny!

https://twitter.com/martinberx/status/478840663489146880 - Martin Berger got to see Christian Antognini at the AOUG conference speak about adaptive query optimization!

https://twitter.com/martinberx/status/478810733481041920 - Martin also shares this slide from Andrew Holdsworth about performance that people get.

Social Media/DevOps:

https://twitter.com/thatjeffsmith/status/478878378821304320 - THAT Jeff Smith shares a post about why you should start presenting.  Sure it's not about Social Media or DevOps, but it is about getting out of your comfort zone and interacting with people; both of which are tenants of these two headings!

OTN Virtual Technology Summit

The last few years, OTN presents Virtual Days which include hands-on labs and this year they are changing up the script a bit!  It looks like instead of more "hard-core" and lab intensive work OTN is opting for sharing a lot of information about Middleware, System Administration, Databases, and Java with a lot of different experts during a Virtual Technology Summit on July 9th for the audiences in the Americas (10th and 16th for regions outside the Americas).  Did I mention that this is free?

This looks like a really great event, and I signed up minutes after I read it in my Architect Community Newsletter which you should also sign up for here.

Firewalls, Damn Firewalls, and Projects

Okay, maybe it is not grammatically what the original phrase was about but I thought it was a good title.  :}

We recently had a project where we were replacing a set of servers, and one of the things that caught us out during the PROD cutover was the firewalls!  Firewalls between databases, firewalls between middle tier applications; some we had caught in QA, others we did not, but it all added up to extra time in our deployment that could have been avoided.

Take our mistake, and learn from it to make your next project that much more successful.  I know the next time we have something like this going in, I am going to grill everybody available about the firewalls!

R12: Incomplete Payables conversion from 11i

Were you aware that the data that existed in 11i within the AP_ENCUMBRANCE_LINES_ALL table was not upgraded into the new XLA tables?  I didn't know that either!  The MOS note "Enhancement Request: R12 Doe Not Show Upgraded Encumbrance Accounting Lines From Payables" from note ID 1673271.1 does reference R12.2 but since all versions of R12 do go through the XLA conversions I believe it would apply to all versions!  Food for thought if you are going through the upgrade process, or something to keep in mind if you have already upgraded just in case the business did not catch this in testing originally and suddenly realizes it after implementation.