Prompt

Once about every hour my database slows down.
Use APPM MCP to find the bad jobs/SQL, show the evidence, and suggest DBA next actions. 

Which database should I investigate for the slowness in the last hour?

❯ 1. oci_19c
     Investigate the oci_19c database
  2. oci_26ai
     Investigate the oci_26ai database
  3. Both
     Check both databases for the slow window

Response

Diagnosis: hourly slowdown on oci_19c

Three DBMS_SCHEDULER jobs owned by WMS_APP fire every hour and burn CPU. 100% of waits during these windows are CPU Foreground / ON CPU — no blocking, no I/O, no locks. Pure CPU starvation.

Hourly timeline (one sample hour, 18:00–19:00 UTC, 2026-05-15)

Same pattern repeats every hour, every day in the week-long sample.

Aggregated minute-of-hour ASH samples over the past week (2026-05-08 → 2026-05-15) show the same three peaks (:05–:06, :20–:21, plus a smaller :03 / :53 cluster), confirming the pattern is recurring rather than a one-off.

Evidence per job

1. WH_RESERVE_STOCK_JOB — hard-parse storm (worst offender)

• 731 ASH samples over 3 days, all ON CPU.

• 549 distinct sql_ids sharing the same force_matching_signature 18070540725829760305.

• Example SQL:

  UPDATE wh_bin_stock
     SET qty_reserved = qty_reserved + 1,
         last_move_ts = SYSDATE
   WHERE warehouse_id = 1
     AND sku_id       = 10001
     AND qty_on_hand - qty_reserved >= 1
     AND ROWNUM = 1
  

  warehouse_id, sku_id and the increment are literals, not binds, so every execution produces a fresh sql_id and a fresh hard parse.

• Procedure: WH_RESERVE_STOCK.

2. WH_CYCLE_COUNT_JOB — non-sargable predicates, full table scans

• fnfhqfw09q3nr (UPDATE, 184 samples) and 1c1qscvjx3yaa (SELECT, 171 samples).

• Both filter:

  TRUNC(B.LAST_COUNT_TS) <= TRUNC(SYSDATE) - 7
    AND MOD(B.BIN_ID + B.SKU_ID + :B1, N) = 0
  

  Function-on-column (TRUNC(LAST_COUNT_TS)) plus modulo of a derived expression. No index can be used → full scan of WH_BIN_STOCK every hour.

3. WH_WAVE_PLANNER_JOB — heavy ranked UPDATE

• Single SQL 1g9kkz1a0nyfd (205 samples):

  UPDATE WH_PICK_LINE P
     SET P.STATUS = 'WAVED',
         P.WAVE_ID = :B1,
         P.ASSIGNED_TS = SYSDATE
   WHERE P.PICK_ID IN (
       SELECT PICK_ID FROM (
           SELECT P2.PICK_ID,
                  ROW_NUMBER() OVER (
                      ORDER BY P2.PRIORITY ASC, P2.PROMISED_TS ASC,
                               S.VELOCITY_CLASS ASC, S.CUBE_CM3 DESC,
                               S.WEIGHT_G DESC, P2.CREATED_TS ASC,
                               P2.ORDER_ID ASC
                  ) AS RN
             FROM WH_PICK_LINE P2
             JOIN WH_SKU S ON S.SKU_ID = P2.SKU_ID
            WHERE P2.STATUS = 'OPEN'
              AND P2.PROMISED_TS <= SYSDATE + 2
              AND S.ACTIVE_FLAG = 'Y'
       )
       WHERE RN <= 30000
   )
  

  Rewrites status for up to 30 000 rows; the seven-column window sort is the cost driver.

No appm_bsh rows for WMS_APP in the window — confirms it’s CPU contention, not lock contention.

Suggested DBA next actions (in priority order)

1. Fix WH_RESERVE_STOCK first. Replace literals with bind variables in the dynamic UPDATE wh_bin_stock so 549 cursors collapse into 1. As a short-term mitigation only, ALTER SYSTEM SET CURSOR_SHARING=FORCE (or set it at session level inside the job) — but that’s a band-aid; fix the procedure.
2. Index/predicate rewrite for WH_CYCLE_COUNT. Rewrite TRUNC(LAST_COUNT_TS) <= TRUNC(SYSDATE)-7 as LAST_COUNT_TS < TRUNC(SYSDATE)-6 so an index on LAST_COUNT_TS becomes usable. The MOD(BIN_ID+SKU_ID+:B1, N)=0 partitioning trick prevents indexing — if the goal is to sample/batch a subset of rows per run, drive the batching from an ORDER BY LAST_COUNT_TS FETCH FIRST N ROWS instead.
3. Stagger and cap WH_WAVE_PLANNER. Reduce the RN <= 30000 cap, or split into smaller batches, or move it off the same HH:03 slot if it overlaps prod traffic. Check the plan for the ROW_NUMBER() sort — likely needs an index supporting (STATUS, PROMISED_TS) (or (STATUS, PRIORITY, PROMISED_TS)) to feed the window pre-sorted.
4. Stagger the schedule. All three jobs cluster in the same 18-minute hourly window. Even after tuning, spread their start_date so they don’t pile onto the CPU together.
5. Verify with one more pass after each fix: re-run the minute-of-hour distribution; the :03, :05–:07, :20 peaks should flatten.