Category: PIPER-Rx Reports (Free) Name: FCR006-10 Concurrent Program Runtimes by Total Runtime ReportDefFileName: .\FCR006-10 Concurrent Program Runtimes by Total Runtime.fr3 ===Begin Comments=== Copyright (C) 2009 G Piper This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. a copy of the GNU Affero General Public Licenses provided is part of the down load Terms and Conditions available on our site www.piper-rx.com or you can refere to www.gnu.org/licenses/gpl.html ===End Comments=== ===Begin SQL=== -- **************************************************** -- ** PIPER-RX - Free Reports -- ** -- ** Copyright (C) 2009 G.Piper -- ** -- ** Program runtimes by total runtime -- ** -- ** ------------------------------------------------- -- ** Version 1.0 Jul-09 G.Piper Initial Creation -- **************************************************** SELECT upper(:START_DATE) start_date, upper(:END_DATE) end_date, database_name.db_sid, decode(fa.application_short_name, 'SQLGL','GL', 'SQLAP','AP', fa.application_short_name) app_name, fcpt.user_concurrent_program_name program_name, fcqt.concurrent_queue_name manager, count(*) executions, round(min(fcr.actual_completion_date - fcr.actual_start_date)*24*60,1) min_time, round(avg(fcr.actual_completion_date - fcr.actual_start_date)*24*60,1) avg_time, round(max(fcr.actual_completion_date - fcr.actual_start_date)*24*60,1) max_time, round(sum(fcr.actual_completion_date - fcr.actual_start_date)*24,1) tot_time FROM applsys.fnd_concurrent_requests fcr, applsys.fnd_concurrent_programs_tl fcpt, applsys.fnd_concurrent_processes fcpr, applsys.fnd_concurrent_queues_tl fcqt, applsys.fnd_application fa, ( SELECT vd.name db_sid FROM gv$database vd WHERE vd.inst_id = 1 ) database_name WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id and fcr.program_application_id = fcpt.application_id and fcr.controlling_manager = fcpr.concurrent_process_id and fcpr.queue_application_id = fcqt.application_id and fcpr.concurrent_queue_id = fcqt.concurrent_queue_id and fcqt.language = userenv('LANG') and fcpt.language = userenv('LANG') and fcr.program_application_id = fa.application_id and fcr.actual_completion_date is not null and (fcr.actual_completion_date - fcr.actual_start_date)*24*60 > 0 and fcr.actual_start_date >= to_date(upper(:START_DATE), 'DD-MON-YY HH24:MI') and fcr.actual_start_date <= to_date(upper(:END_DATE), 'DD-MON-YY HH24:MI') GROUP by database_name.db_sid, fa.application_short_name, fcpt.user_concurrent_program_name, fcqt.concurrent_queue_name ORDER by 11 desc ===End SQL=== ParamName: START_DATE ParamType: String ParamValue: 01-Jul-09 00:00 ParamExp: ParamName: END_DATE ParamType: String ParamValue: 31-Jul-09 23:59 ParamExp: ===Begin Report Definition===