Below query can be useful for finding the Debug Enabled on Profiles at Site,Application,Responsibilty and User Level.

select fpot.user_profile_option_name profile_name
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10001 /* Site Level */
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_APPLICATION_TL fa
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Resp' lo
, frt.responsibility_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_RESPONSIBILITY_TL frt
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and frt.language = Userenv('Lang')
and fpot.language = Userenv('Lang')
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = frt.responsibility_id
and fpov.level_value_application_id = frt.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_USER fu2
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
and fpot.language = Userenv('Lang')
and fpo.end_date_active is null
order by profile_name, lo, lov;