It happens all too often in E-Business Suite systems, especially in test and development. Someone sets a profile option to gather trace information for an SR, or to debug a pesky series of forms. They follow the instructions in the relevant My Oracle Support note to the letter, but in the ensuing rush to ship the trace info to the support analyst or to dive into the debug files, they forget to disable the profile option that produces all of that lovely verbose output.
Time passes. Then the email starts to come in.
From your systems administrators:
"Hey, any idea why /opt is at 95% on the dev database server?"
"Dude, why are there several thousand .dbg files in /var/tmp?"
From that one extra-special user who remembers that you like detail in your problem statements, and whose only noticeable flaw is a reluctance to be cloned:
"I've noticed that when I try to do anything at all in Order Management on the test system, it's really dragging. Other parts of the system seem fine, though. Could you check if anything weird is happening with the three forms shown in the attached screenshots?"
From the Service Desk:
"We have lots of people complaining that E-Business Suite is slow. Have you guys changed anything?"
After a long afternoon of looking around, you learn that:
- /var/tmp is full of debug files from a concurrent request that runs every 10 minutes
- /opt is filling up because someone (hey, don't snicker, maybe it was you. We've all been there) decided to set the "Initialization SQL Statement - Custom" profile for your most active user to perform a 10046 database event trace. At level 12. Without restricting by application or responsibility.
- Thankfully, Order Management debug settings mostly affect only the Order Management module. Unfortunately, when they're not also restricted by user, everyone suffers.
- Most of the other users complaining about slow E-Business Suite performance aren't even using the instance where the above problems arose. They're just struggling with old desktop machines, trying to keep 20 Forms windows open at once, or failing to resist adding one more really cute widget to their browser toolbar. Hey, not every E-Business Suite performance problem starts on the server side.
All is well again, but it took forever to track down all of those different settings. Wouldn't it be nice if there was a quick way to find debug-related system profile options that had been set recently (or not-so-recently)? You could use a script like this, but that might be a bit too noisy if there are frequent profile option changes.
Here's a script that I use to try to capture as many of the debug and trace profile settings as possible. It's based on the query that I just linked, but filters on some common strings so you're more likely to get results that are relevant to identifying errant debug settings. It also attempts to ignore options that have been set as defaults at install or patch time. I'm not too proud of the multiple scalar subqueries inside the decode() statement. Clearly I stopped somewhere between "okay, it works" and "let me polish this 'til it gleams."
Oh, and I'm toying with the idea of dropping my various scripts and snippets into Teh Cloud™ at GitHub. I don't claim to be organized enough to rate a repository, but I think the idea of gists is pretty nifty.