诊断 oracle ERP/EBS 低效的程序

--查找 低效的程序 SQL (转自ITPUB - http://www.itpub.net/forum.php?mod=viewthread&tid=1804814)

SELECT wk.*,
       decode(wk.description,
              NULL,
              pt.user_concurrent_program_name,
              wk.description || ' (' || pt.user_concurrent_program_name || ')') program,
       pt.user_concurrent_program_name user_concurrent_program_name,
       fe.execution_method_code,
       decode(fe.execution_method_code,
              'H',
              '主机',
              'S',
              '立即',
              'J',
              'Java 存储过程',
              'K',
              'Java 并发程序',
              'M',
              '多语言功能',
              'P',
              'Oracle Reports',
              'I',
              'PL/SQL 存储过程',
              'B',
              '请求集阶段函数',
              'A',
              '派生',
              'L',
              'SQL*Loader 程序',
              'Q',
              'SQL*Plus',
              'E',
              'Perl 并发程序',
              'Others') execution_method_name,
       fe.execution_file_name,
       fi.last_run_date,
       fi.avg_run_time,
       fi.max_run_time,
       fi.min_run_time
  FROM (SELECT r.ROWID row_id,
               r.request_id,
               r.request_date,
               r.requested_by,
               r.requested_start_date,
               r.responsibility_application_id,
               r.responsibility_id,
               r.parent_request_id,
               r.controlling_manager,
               r.description,
               r.actual_start_date,
               r.actual_completion_date,
               r.completion_text,
               r.argument_text,
               r.program_application_id,
               r.concurrent_program_id,
               (r.actual_completion_date - r.actual_start_date) * (86400 / 60) minute1
          FROM fnd_concurrent_requests r
         WHERE 1 = 1 --r.request_id IN (1122991, 1109743, 1109563)
           AND (r.actual_completion_date - r.actual_start_date) * 86400 > 60 * 30) wk,
       fnd_concurrent_programs_tl pt,
       fnd_concurrent_programs pb,
       fnd_executables_vl fe,
       fnd_conc_prog_onsite_info fi
WHERE 2 = 2 --(SQL*Plus,PL/SQL 存储过程,Oracle Reports,Java 存储过程,Java 并发程序)
   AND pb.application_id = wk.program_application_id
   AND pb.concurrent_program_id = wk.concurrent_program_id
   AND pb.application_id = pt.application_id
   AND pb.concurrent_program_id = pt.concurrent_program_id
   AND pt.LANGUAGE = userenv('LANG')
   AND fe.executable_id = pb.executable_id
   AND fi.program_application_id = wk.program_application_id
   AND fi.concurrent_program_id = wk.concurrent_program_id
ORDER BY pt.user_concurrent_program_name,
          wk.minute1 DESC;