Unmasking Performance Bottlenecks: A DBA's Guide to Finding Slow Concurrent Programs

As an Oracle Apps DBA, one of the most critical tasks is ensuring the smooth and efficient operation of concurrent programs. A single long-running or poorly performing program can bring the entire system to a halt.

For proactive performance analysis, it's essential to regularly identify which concurrent programs are consuming the most time. This allows you to focus your tuning efforts where they will have the greatest impact.

Here is a powerful and straightforward SQL query that I use regularly to find the top 10 longest-running completed concurrent programs in Oracle E-Business Suite.

The Query

-- This query identifies the top 10 longest-running completed concurrent programs.
-- It joins two key tables to get the request details and program names.
select
    fcpt.user_concurrent_program_name as "Program Name",
    fcr.request_id as "Request ID",
    fcr.actual_start_date as "Start Time",
    fcr.actual_completion_date as "End Time",
    -- Calculate the run time in minutes
    round((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 2) as "Run Time (Minutes)"
from
    apps.fnd_concurrent_requests fcr,
    apps.fnd_concurrent_programs_tl fcpt
where
    -- Join condition to link requests to program names
    fcr.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.program_application_id = fcpt.application_id
    and fcpt.language = userenv('LANG')
    -- Filter for completed requests with a start and end time
    and fcr.phase_code = 'C' -- 'C' for Complete
    and fcr.actual_start_date is not null
    and fcr.actual_completion_date is not null
order by
    "Run Time (Minutes)" desc
fetch first 10 rows only; -- Fetch only the top 10 longest-running programs

How It Works: An Expert's Breakdown

Let's dissect this query to understand its power:

• Data Source: The query leverages two essential tables:

  • fnd_concurrent_requests: This is the master table for all concurrent requests. It contains the raw data, including the request_id, program_id, and, most importantly, the actual_start_date and actual_completion_date timestamps.

  • fnd_concurrent_programs_tl: This table provides the user-facing name of the program (user_concurrent_program_name), making the output immediately understandable without needing to cross-reference program IDs.

• The Crucial Calculation: The heart of this query is the round((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 2) calculation. In Oracle, subtracting two DATE data types results in a value representing the number of days between them. By multiplying this by 24 (hours per day) and then 60 (minutes per hour), we get the total runtime in minutes.

• Targeted Filtering: The WHERE clause is key to getting meaningful results.

  • fcr.phase_code = 'C': We only look at requests that have successfully completed.

  • fcr.actual_start_date is not null and fcr.actual_completion_date is not null: This ensures we're only calculating the runtime for programs that had a complete lifecycle recorded.

• Ranking the Results: order by "Run Time (Minutes)" desc sorts the output to show the most time-consuming programs first. fetch first 10 rows only is an efficient, modern Oracle feature that limits the result set to the top 10, preventing an unwieldy output in a busy system.

Final Thoughts

This simple, yet effective, query is an invaluable tool for any Oracle Apps DBA. It helps to quickly pinpoint performance hogs, allowing you to prioritize your performance tuning efforts and keep your Oracle EBS environment running smoothly. Regularly running this query can be a game-changer for maintaining a healthy system.