A
As a continuation of the first article, today I will focus mainly on APEX-related checks. To avoid one huge email, this part covers APEX-related checks, which you can find in the SEND_APPS procedure. Let's get started.
I usually ignore all working copies, but even with this approach you might still have some leftover apps in your environment. So, in the CORE_CUSTOM package (under g_apps), you can limit the scope of your apps.
This might be interesting: how would you select the same specific apps in multiple queries (without hardcoding the values in more than one place)? You declare the list in the package specification and create a function so you can use it in any statement:
g_apps apex_t_varchar2 := apex_t_varchar2(
100,
110,
120,
...
);
FUNCTION get_apps
RETURN apex_t_varchar2
AS
BEGIN
RETURN core_custom.g_apps;
END;
Then you can use it like this:
SELECT t.*
FROM apex_applications t
JOIN TABLE (core_reports.get_apps()) f
ON TO_NUMBER(f.column_value) = t.application_id;
Spoiler alert: there is no more technical magic in this article. I will just cover related views.
What to check?
- Overview of your APEX apps
- Traffic Today
- Workspace Files
- Developers Timeline
- For each tracked app:
- REST Services overview
APEX apps overview – to see the latest updates, version numbers, detect changes (especially on non-dev environments), and check if app scans have stalled:
SELECT
t.application_group AS app_group,
t.application_id AS app_id,
t.application_name AS app_name,
t.version,
--t.build_status,
t.pages,
t.last_updated_by AS updated_by,
t.last_updated_on AS updated_at,
--
COALESCE (
TO_CHAR(t.last_dependency_analyzed_at, 'YYYY-MM-DD HH24:MI'),
CASE WHEN f.column_value IS NOT NULL THEN 'MISSING' END
) AS analyzed_at
--
FROM apex_applications t
LEFT JOIN TABLE (core_reports.get_apps()) f
ON TO_NUMBER(f.column_value) = t.application_id
WHERE t.is_working_copy = 'No'
AND t.application_group NOT LIKE '\_\_%' ESCAPE '\'
ORDER BY
1, 2;
Traffic Today – to get a high-level picture of app usage (requests, users, sessions, errors, etc.):
SELECT
t.application_id AS app_id,
t.application_name AS app_name,
--
NULLIF(a.page_views, 0) AS page_views,
NULLIF(a.page_events, 0) AS page_events,
NULLIF(a.distinct_pages, 0) AS pages_,
NULLIF(a.distinct_users, 0) AS users_,
NULLIF(a.distinct_sessions, 0) AS sessions_,
NULLIF(a.error_count, 0) AS errors_,
ROUND(a.maximum_render_time, 2) AS render_time_max
--
FROM apex_applications t
LEFT JOIN TABLE (core_reports.get_apps()) f
ON TO_NUMBER(f.column_value) = t.application_id
JOIN apex_workspace_log_archive a
ON a.application_id = t.application_id
AND a.log_day = core_reports.get_start_date()
WHERE t.is_working_copy = 'No'
AND t.application_group NOT LIKE '\_\_%' ESCAPE '\'
ORDER BY
1, 2;
Workspace Files – to detect changes in workspace files:
SELECT
f.file_name,
DBMS_LOB.GETLENGTH(f.file_content) AS file_size,
f.last_updated_by AS updated_by,
f.last_updated_on AS updated_at
--
FROM apex_workspace_static_files f
WHERE (
f.file_name LIKE '%.css'
OR f.file_name LIKE '%.js'
)
AND f.file_name NOT LIKE '%.min.%'
ORDER BY 1;
Developers Timeline – for an overview of developer activity in APEX Builder (not inside the apps). Note that this does not necessarily reflect whether developers are actively working:
SELECT
l.apex_user AS user_id,
--
COUNT(*) AS total,
--
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '00' THEN 1 END), 0) AS "00",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '01' THEN 1 END), 0) AS "01",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '02' THEN 1 END), 0) AS "02",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '03' THEN 1 END), 0) AS "03",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '04' THEN 1 END), 0) AS "04",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '05' THEN 1 END), 0) AS "05",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '06' THEN 1 END), 0) AS "06",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '07' THEN 1 END), 0) AS "07",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '08' THEN 1 END), 0) AS "08",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '09' THEN 1 END), 0) AS "09",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '10' THEN 1 END), 0) AS "10",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '11' THEN 1 END), 0) AS "11",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '12' THEN 1 END), 0) AS "12",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '13' THEN 1 END), 0) AS "13",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '14' THEN 1 END), 0) AS "14",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '15' THEN 1 END), 0) AS "15",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '16' THEN 1 END), 0) AS "16",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '17' THEN 1 END), 0) AS "17",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '18' THEN 1 END), 0) AS "18",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '19' THEN 1 END), 0) AS "19",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '20' THEN 1 END), 0) AS "20",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '21' THEN 1 END), 0) AS "21",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '22' THEN 1 END), 0) AS "22",
NULLIF(COUNT(CASE WHEN TO_CHAR(l.view_timestamp, 'HH24') = '23' THEN 1 END), 0) AS "23"
--
FROM apex_workspace_activity_log l
LEFT JOIN apex_applications a
ON a.application_id = l.application_id
WHERE 1 = 1
AND a.application_id IS NULL
AND l.apex_user NOT LIKE '%MONITOR%'
--
AND TO_CHAR(l.view_timestamp, 'YYYY-MM-DD') = core_reports.get_start_date()
GROUP BY ALL
ORDER BY 1;
Component Changes – to see what changed that day in the app (by developer and page):
SELECT
g.developer,
g.page_id,
g.page_name,
--
COUNT(DISTINCT g.component_id) AS components,
--
NULLIF(COUNT(CASE WHEN g.audit_action = 'Insert' THEN 1 END), 0) AS inserted_,
NULLIF(COUNT(CASE WHEN g.audit_action = 'Update' THEN 1 END), 0) AS updated_,
NULLIF(COUNT(CASE WHEN g.audit_action = 'Delete' THEN 1 END), 0) AS deleted_
--
FROM apex_developer_activity_log g
WHERE 1 = 1
AND g.application_id = core.get_app_id()
AND g.audit_date >= core_reports.get_start_date()
AND g.audit_date < core_reports.get_end_date()
AND g.developer != USER
GROUP BY ALL
ORDER BY
1, 2, 3;
Performance – to check how individual pages performed that day:
WITH t AS (
SELECT
a.id,
--
CASE WHEN GROUPING_ID(a.id) = 0 THEN a.application_id END AS application_id,
CASE WHEN GROUPING_ID(a.id) = 0 THEN a.page_id END AS page_id,
CASE WHEN GROUPING_ID(a.id) = 0 THEN a.page_name END AS page_name,
CASE WHEN GROUPING_ID(a.id) = 0 THEN a.view_date END AS view_date,
CASE WHEN GROUPING_ID(a.id) = 0 THEN MAX(a.page_view_type) END AS page_view_type,
--
SUM(a.elapsed_time) AS elapsed_time,
--
COUNT(DISTINCT a.apex_user) AS count_users
--
FROM (
SELECT
CASE WHEN page_view_type = 'Rendering' THEN a.id
ELSE LAG(CASE WHEN page_view_type = 'Rendering' THEN a.id END IGNORE NULLS) OVER (ORDER BY a.id)
END AS request_id,
a.id,
a.application_id,
a.page_id,
a.page_name,
a.apex_user,
a.view_date,
a.elapsed_time,
a.page_view_type,
a.view_timestamp,
a.apex_session_id
FROM apex_workspace_activity_log a
WHERE 1 = 1
AND a.application_id = core.get_app_id()
AND a.view_date >= core_reports.get_start_date()
AND a.view_date < core_reports.get_end_date()
) a
GROUP BY
a.id,
a.request_id,
a.application_id,
a.page_id,
a.page_name,
a.view_date
HAVING a.request_id IS NOT NULL
)
SELECT
t.application_id AS app_id,
t.page_id,
t.page_name,
MAX(t.count_users) AS users_,
--
NULLIF(COUNT(CASE WHEN t.page_view_type = 'Rendering' THEN t.id END), 0) AS rendering_count,
ROUND(AVG( CASE WHEN t.page_view_type = 'Rendering' THEN t.elapsed_time END), 2) AS rendering_avg,
ROUND(MAX( CASE WHEN t.page_view_type = 'Rendering' THEN t.elapsed_time END), 2) AS rendering_max,
--
NULLIF(COUNT(CASE WHEN t.page_view_type = 'Processing' THEN t.id END), 0) AS processing_count,
ROUND(AVG( CASE WHEN t.page_view_type = 'Processing' THEN t.elapsed_time END), 2) AS processing_avg,
ROUND(MAX( CASE WHEN t.page_view_type = 'Processing' THEN t.elapsed_time END), 2) AS processing_max,
--
NULLIF(COUNT(CASE WHEN t.page_view_type = 'Ajax' THEN t.id END), 0) AS ajax_count,
ROUND(AVG( CASE WHEN t.page_view_type = 'Ajax' THEN t.elapsed_time END), 2) AS ajax_avg,
ROUND(MAX( CASE WHEN t.page_view_type = 'Ajax' THEN t.elapsed_time END), 2) AS ajax_max
--
FROM t
GROUP BY ALL;
Requests History – to track user activity over the past two weeks (this is just a snippet for brevity; the real view pivots the data):
SELECT
a.application_id,
a.page_id,
a.page_name,
a.view_date,
--
NULLIF(SUM(CASE WHEN a.page_view_type = 'Rendering' THEN 1 ELSE 0 END), 0) AS rendering,
NULLIF(SUM(CASE WHEN a.page_view_type = 'Processing' THEN 1 ELSE 0 END), 0) AS processing,
NULLIF(SUM(CASE WHEN a.page_view_type = 'Ajax' THEN 1 ELSE 0 END), 0) AS ajax,
--
COUNT(*) AS activity,
COUNT(DISTINCT a.apex_user) AS users,
COUNT(DISTINCT a.apex_session_id) AS sessions,
--
ROUND(AVG(a.elapsed_time),2) AS avg_time,
ROUND(MAX(a.elapsed_time), 2) AS max_time
FROM (
SELECT
a.application_id,
a.page_id,
a.page_name,
TRUNC(a.view_date) AS view_date,
a.page_view_type,
a.elapsed_time,
a.apex_user,
a.apex_session_id
FROM apex_workspace_activity_log a
WHERE a.application_id = core.get_app_id()
) a
GROUP BY
a.application_id,
a.page_id,
a.page_name,
a.view_date;
And finally, not per app, but as a good wrap-up: the REST services...
REST Services Overview – with source code and arguments check (so if you have the source code in packages, as you should, it verifies this, ensures all procedure arguments are covered by bind variables, and checks that all bind variables in the service are used). This query is a bit longer, so check the source code – it is full of magic.
Comments
Post a Comment