APEX Performance

APEX  Performance

COLUMN workspace           FORMAT A20          HEADING 'Workspace'
COLUMN application_id      FORMAT 9999999      HEADING 'App ID'
COLUMN page_id             FORMAT 9999         HEADING 'Page'
COLUMN total_page_events   FORMAT 999,999,999  HEADING 'Events'
COLUMN avg_elapsed_time    FORMAT 999,999.99   HEADING 'Avg (ms)'
COLUMN total_elapsed_time  FORMAT 999,999,999.99 HEADING 'Total (ms)'
COLUMN rnk_avg             FORMAT 999          HEADING 'Rank‑Avg'
COLUMN rnk_tot             FORMAT 999          HEADING 'Rank‑Tot'

SET LINESIZE 200
SET PAGESIZE 100

WITH day_stats AS (
    SELECT
        workspace,
        application_id,
        page_id,
        COUNT(*)                                 AS total_page_events,
        ROUND(AVG(elapsed_time), 2)               AS avg_elapsed_time,
        ROUND(SUM(elapsed_time), 2)               AS total_elapsed_time
    FROM
        APEX_240200.apex_workspace_activity_log
    WHERE
        view_date >= TRUNC(SYSDATE) - 1          
        AND view_date <  TRUNC(SYSDATE)  + 1         
    GROUP BY
        workspace,
        application_id,
        page_id
),
ranked AS (
    SELECT
        d.*,
        DENSE_RANK() OVER (ORDER BY avg_elapsed_time DESC)  AS rnk_avg,
        DENSE_RANK() OVER (ORDER BY total_elapsed_time DESC) AS rnk_tot
    FROM
        day_stats d
)
SELECT
    workspace,
    application_id,
    page_id,
    total_page_events,
    avg_elapsed_time,
    total_elapsed_time,
    rnk_avg,
    rnk_tot
FROM
    ranked
WHERE
    rnk_avg <= 10
    OR rnk_tot <= 10
ORDER BY
    rnk_avg ASC,
    rnk_tot ASC;

Sample Output

WORKSPACE            APP ID PAGE  EVENTS    AVG (ms)        TOTAL (ms)  Rank-Avg Rank-Tot
-------------------- ------ ----- --------- ------------- ----------------- -------- --------
MY_WORKSPACE           102   15   1,234      452.37          557,832         1        2
ANOTHER_WS              57    8     987      698.45          690,123         2        1

Additional Checks

COLUMN application_id   FORMAT 9999      HEADING 'App ID'
COLUMN application_name FORMAT A30       HEADING 'Application Name'
COLUMN page_id          FORMAT 9999      HEADING 'Page ID'
COLUMN created_by       FORMAT A20       HEADING 'Created By'
COLUMN last_updated_by  FORMAT A20       HEADING 'Last Updated By'
COLUMN last_updated_on  FORMAT A12       HEADING 'Last Updated On'

SELECT
    application_id,
    application_name
FROM
    APEX_240200.apex_applications
WHERE
   workspace = '__NAME__'
ORDER BY
    1;
--
--
--
SELECT
    application_id,
    page_id,
    last_updated_by,
    TO_CHAR(last_updated_on, 'DD-MON-YYYY') AS last_updated_on
FROM
    APEX_240200.apex_application_pages
WHERE
        workspace = '__NAME__'
    AND last_updated_by IS NOT NULL
ORDER BY
    1,
    2;