Finding debug and trace profiles in EBS

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.

-- check_ebs_trace_profs.sql
--  Author: John Piwowar
-- Purpose: Identify E-Business Suite system profile option settings that may
--          be related to performance-degrading debug/trace activity
--   Notes: Prompts for a cutoff date for when profile options were set
--          May need additional tweaking for multi-language installations 
set pagesize 9999
set linesize 120
set verify off
col "Profile Option" for a25
col "Option Level" for a13
col "set for" for a20
col "Value" for a20
col "Set On" for a11
col "Blame" for a20
PROMPT Enter date value in form DD-MON-YYYY for check_since
   select tl.user_profile_option_name "Profile Option"
        , decode( val.level_id
                , 10001, 'Site'
                , 10002, 'Application'
                , 10003, 'Responsibility'
                , 10004, 'User'
                , 10005, 'Server'
                , 10006, 'Organization'
                , 10007, 'Server+Resp'
                , 'No idea, boss') "Option Level"
        , decode( val.level_id
                , 10001
                , 'EVERYWHERE!'
                , 10002
                , (select application_name
                     from fnd_application_tl
                    where application_id = val.level_value)
                , 10003
                , (select responsibility_name
                     from fnd_responsibility_tl
                    where responsibility_id = val.level_value
                      and application_id = val.level_value_application_id)
                , 10004
                , (select user_name
                     from fnd_user
                    where user_id = val.level_value)
                , 10005
                , (select host || '.' || domain
                     from fnd_nodes
                    where node_id = val.level_value)
                , 10006
                , (select name
                     from hr_all_organization_units
                    where organization_id = val.level_value)
                , 10007
                , 'Look it up' --per specification El-Ay-Zed-why
                , '''Tis a mystery') "Set for"
        , val.profile_option_value "Value"
        , val.last_update_date "Set on"
        , usr.user_name "Set By"
     from fnd_profile_options opt,
          fnd_profile_option_values val,
          fnd_profile_options_tl tl,
          fnd_user usr
    where opt.profile_option_id = val.profile_option_id
      and opt.profile_option_name = tl.profile_option_name
      and regexp_like( tl.user_profile_option_name
                     , '(trace|log|debug|audit|diag|sql)'
                     , 'i'
      and not(regexp_like( tl.user_profile_option_name
                         , '(catalog|file|login|utilities)'
                         , 'i'
      and usr.user_id = val.last_updated_by
      and usr.user_name  not in ( 'AUTOINSTALL'
                                , 'INITIAL SETUP'
                                , 'ANONYMOUS')
      and val.last_update_date > '&check_since'
    order by val.last_update_date desc

Post a Comment

Your email is never published nor shared. Required fields are marked *