Friday, July 20, 2018

Identify EBS profile option settings that may be related to performance-degrading debug/trace activity

-- 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
 ;

No comments: