Potential optimization of av_sec_users to decrease buffer gets and elapsed seconds

I was working on trouble shooting a separate CCP issue on our T7 and kept seeing this as our #1 and #2 CPU usage and buffer gets. I thought to reengage on it and further document it.

SELECT USER_GROUP_ID FROM TABLE (CWMS_SEC.GET_ASSIGNED_PRIV_GROUPS_TAB) WHERE DB_OFFICE_ID = :B1 

av_sec_users is doing a cartesian join; it’s only going to get slower and worse as we add security groups, users, offices, and roles.

First, do we need to keep it as a cartesian join for other tools? If we do, I think it’s a great candidate for caching.

The underlying API call is calling av_sec_users in one or two places.

A typical use case against av_sec_users resulted in this original (20 buffer gets/ 239 elapsed time):

This is the improvement (4 buffer gets / 84 elapsed time)

Basically I materialized the cartesian join here:

SELECT
      cu.userid       AS username,
      o.office_id     AS db_office_id,
      ug.user_group_id,
      ug.user_group_desc,
      ug.db_office_code,
      ug.user_group_code
  FROM at_sec_cwms_users  cu
  JOIN at_sec_user_groups ug
    ON 1 = 1
  JOIN cwms_office o
    ON o.office_code = ug.db_office_code;

And then had a second materialized view to replicate the existing view

WITH
base AS (
  SELECT
       username,
       db_office_id,
      user_group_id,
      user_group_desc,
      db_office_code,
      user_group_code
  FROM xxxx_sec_users_mv  ug
  JOIN cwms_office o
    ON o.office_code = ug.db_office_code
),
members AS (
  SELECT /*+ MATERIALIZE */ DISTINCT
      u.username, u.db_office_code, u.user_group_code
  FROM at_sec_users u
),
locked AS (
  SELECT /*+ MATERIALIZE */ 
      lu.username, lu.db_office_code, lu.is_locked
  FROM at_sec_locked_users lu
)
SELECT
    b.username,
    b.db_office_id,
    CASE WHEN b.user_group_code < 10 THEN 'Privilege User Group'
         ELSE 'TS Collection User Group' END AS user_group_type,
    CASE WHEN b.user_group_code < 20 THEN 'CWMS'
         ELSE b.db_office_id END AS user_group_owner,
    b.user_group_id,
    CASE WHEN m.username IS NOT NULL THEN 'T' ELSE 'F' END AS is_member,
    NVL(l.is_locked, 'F') AS is_locked,
    b.user_group_desc,
    b.db_office_code,
    b.user_group_code
FROM base b
LEFT JOIN members m
  ON m.username        = b.username
AND m.db_office_code  = b.db_office_code
AND m.user_group_code = b.user_group_code
LEFT JOIN locked l
  ON l.username       = b.username
AND l.db_office_code = b.db_office_code;

This index was necessary on the 2nd mview to improve on the original queries speed:

CREATE INDEX XXXX_SEC_USERS_2_MV_INDEX1 ON XXXX_SEC_USERS_2_MV (DB_OFFICE_CODE ASC, USERNAME ASC, IS_MEMBER ASC)

The ‘cost’ of indexing is it about 150second total for both refreshes to complete sequentially.

Start by reviewing av_sec_users and the underlying API call that uses it, then compare the reported buffer gets and elapsed times for the existing view and the proposed materialized views. Determine whether the Cartesian join is required by other tools, and evaluate the refresh and indexing costs. Done means an agreed optimization approach is validated against both query performance and refresh overhead.