Only Four Left…

While troubleshooting a performance problem on a test E-Business Suite system this week, I ran across a very large smoking gun: 5GB log file in the APPLPTMP directory. Reasoning that it was unlikely that someone purposely selected APPLPTMP as the location for a non-Apps-related log file, I wanted to know a few things:

1. What was generating the log file
2. Who had initiated the logging (so I could confirm that, *ahem* 5GB later, they had collected all the necessary data)
3. How long this had been logging (if it was a long time, I could be justified in disabling the log without consulting the user)

I could probably have found most of what I needed to know by checking the log file's contents and OAM, but I took a chance that the name of the log file was probably set via a profile option (not exactly a long-odds guess, given its name and location), and wrote a query against the FND_PROFILE tables to find my answers:

applmgr@testsrv:/var/tmp> sqlplus apps @whatlog dec19_02.out
--SQLPLUS banner and password prompt snipped for your benefit, dear reader!
--Never let it be said that I am inconsiderate.
Profile Option  Option Level  Value           Set on    Scoldee
--------------- ------------- --------------- --------- ---------------
CSI: Debug Log  Site          dec19_02.out    19-DEC-08 NAMEWITHHELD
File Name

Based on this output, I checked and disabled the corresponding Site-level Install Base debug that had been set (and apparently forgotten) the week before Christmas. Easy mistake to make, particularly on a test system, where such things might not get noticed for a while, but...ow. In related news, if you think starting a concurrent program with 32 parallel workers on a 4-core single-node EBS server is bad news, you should try it with that application's debug level set to 10. Even with 4 workers, the individual database sessions spent over 75% of their time calling SYS.UTL_FILE.FOPEN.

The script

Here's the final version of the SQL script I used to track down this information. Now I don't have to worry about where I saved it! The original query I used was not nearly as pretty as this, of course. "Quick and dirty" does not usually include decode() and column formatting. Please note that if your system uses more than one language, the lookup in the profile option translation table might need a tweak. Also, it is very unlikely that this script will help you track down the origin of every debug file in APPLPTMP, but if the file name looks user-generated, and not system-generated, it's worth a try.

set pagesize 9999
set linesize 80
set verify off
col "Profile Option" for a15
col "Option Level" for a13
col "Value" for a15
col "Set On" for a9
col "Scoldee" for a15
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"
     , val.profile_option_value "Value"
     , val.last_update_date "Set on"
     , usr.user_name "Scoldee"
  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 val.profile_option_value like '%&1%'
   and usr.user_id = val.last_updated_by
/
exit;