oci-dbm-workload

name

oci-dbm-workload

description

Generate a controlled, time-bounded database workload on an Oracle Autonomous Database via OCI DB Management Jobs. Produces real enq:TX row lock contention and parse pressure (cursor:pin S / hard parses) so the oci-dbm-diagnose skill can analyze the resulting wait profile against live AWR data. Use this skill when a user asks to "generate workload", "create test load", "simulate lock contention", "produce parse pressure", or "set up a diagnostic scenario" on an ADB. Always runs via DBM SQL Jobs — no direct database credentials required. Requires the target ADB to be onboarded via oci-dbm-commission.

OCI DBM Workload Generator — Row Lock Contention + Parse Pressure

What it does

Submits one DBM SQL Job (PLSQL, IMMEDIATE) that registers DBMS_SCHEDULER jobs inside the database. The scheduler — not the DBM Job — runs the workload over time. The DBM Job returns immediately after registration. Scheduler jobs self-terminate at end_time with no external polling.

Two scenarios run in parallel:

Scenario	Pattern	Wait event produced
Row lock contention	N sessions loop-updating the same row in WL_HOTROW	enq: TX - row lock contention
Parse pressure	M sessions execute literal-SQL with random literals to force hard parses	cursor: pin S wait on X, high hard-parse rate

AWR snapshots are taken at workload start and end (via WL_CLEANUP scheduler job) so the oci-dbm-diagnose skill has a clean delta to report on.

---

Pre-flight: verify MCP tools

Before collecting inputs, confirm these tools are available on the MCP server:

• create_sql_job — submit PLSQL job (IMMEDIATE)
• get_job_run — check submission succeeded
• get_managed_database — confirm target ADB

Call get_managed_database(managed_database_id=<ocid>). If it returns management_option=ADVANCED and lifecycle_state=ACTIVE, the target is valid.

If create_sql_job is not available, stop and report — do not attempt any other submission path.

---

Inputs — grill the user if not provided

Input	Default	Notes
managed_database_id	—	OCID of the target ADB (from oci-dbm-commission output)
compartment_id	—	Compartment OCID (same compartment as the managed database)
named_credential_id	—	OCID of the DBM named credential (USER_PRINCIPAL scope, set up post-commission). Find it via OCI Console → Database Management → Named Credentials, or ask the user.
duration_seconds	300	How long the workload runs (5 min default)
rowlock_parallelism	4	Concurrent sessions fighting for the same row
parse_parallelism	2	Sessions driving hard parses
Non-production confirmation	—	Must be explicit. Do not proceed without it.

Do not substitute defaults silently. Present the full parameter summary and ask for confirmation before submitting the job.

---

Workload PL/SQL block

Substitute the four parameters before submitting. Replace exactly:

• <duration_seconds> — e.g. 300
• <rowlock_parallelism> — e.g. 4
• <parse_parallelism> — e.g. 2

DECLARE
    v_start_snap NUMBER;
    v_end_time   TIMESTAMP := SYSTIMESTAMP + NUMTODSINTERVAL(<duration_seconds>, 'SECOND');
BEGIN
    -- 1. Idempotent cleanup of prior runs
    FOR rec IN (SELECT job_name FROM user_scheduler_jobs
                WHERE job_name LIKE 'WL\_ROWLOCK\_%' ESCAPE '\'
                   OR job_name LIKE 'WL\_PARSE\_%' ESCAPE '\'
                   OR job_name = 'WL_CLEANUP') LOOP
        BEGIN
            DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    END LOOP;

    -- 2. Workload target table
    BEGIN EXECUTE IMMEDIATE 'DROP TABLE WL_HOTROW PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
    EXECUTE IMMEDIATE 'CREATE TABLE WL_HOTROW (id NUMBER PRIMARY KEY, val NUMBER)';
    EXECUTE IMMEDIATE 'INSERT INTO WL_HOTROW VALUES (1, 0)';
    COMMIT;

    -- 3. Run-log table (idempotent)
    BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE WL_RUN_LOG (run_ts TIMESTAMP, label VARCHAR2(50), snap_id NUMBER)';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;

    -- 4. Opening AWR snapshot
    v_start_snap := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
    INSERT INTO WL_RUN_LOG VALUES (SYSTIMESTAMP, 'workload_start', v_start_snap);
    COMMIT;

    -- 5. Row lock contention sessions
    FOR i IN 1..<rowlock_parallelism> LOOP
        DBMS_SCHEDULER.CREATE_JOB(
            job_name   => 'WL_ROWLOCK_' || i,
            job_type   => 'PLSQL_BLOCK',
            job_action => 'BEGIN
                             WHILE SYSTIMESTAMP < TIMESTAMP ''' ||
                               TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS') || ''' LOOP
                               BEGIN
                                 UPDATE WL_HOTROW SET val = val + 1 WHERE id = 1;
                                 DBMS_LOCK.SLEEP(0.05);
                                 COMMIT;
                               EXCEPTION WHEN OTHERS THEN ROLLBACK;
                               END;
                             END LOOP;
                           END;',
            start_date => SYSTIMESTAMP,
            enabled    => TRUE,
            auto_drop  => TRUE);
    END LOOP;

    -- 6. Parse pressure sessions (literal SQL forces hard parse each iteration)
    FOR i IN 1..<parse_parallelism> LOOP
        DBMS_SCHEDULER.CREATE_JOB(
            job_name   => 'WL_PARSE_' || i,
            job_type   => 'PLSQL_BLOCK',
            job_action => 'DECLARE n NUMBER; BEGIN
                             WHILE SYSTIMESTAMP < TIMESTAMP ''' ||
                               TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS') || ''' LOOP
                               EXECUTE IMMEDIATE
                                 ''SELECT COUNT(*) FROM dual WHERE 1 = '' ||
                                 TRUNC(DBMS_RANDOM.VALUE(1, 100000))
                                 INTO n;
                               DBMS_LOCK.SLEEP(0.05);
                             END LOOP;
                           END;',
            start_date => SYSTIMESTAMP,
            enabled    => TRUE,
            auto_drop  => TRUE);
    END LOOP;

    -- 7. Cleanup job: closing AWR snapshot + table drop, fires at end_time + 30s
    DBMS_SCHEDULER.CREATE_JOB(
        job_name   => 'WL_CLEANUP',
        job_type   => 'PLSQL_BLOCK',
        job_action => 'DECLARE v_end_snap NUMBER; BEGIN
                         v_end_snap := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
                         INSERT INTO WL_RUN_LOG VALUES (SYSTIMESTAMP, ''workload_end'', v_end_snap);
                         COMMIT;
                         BEGIN EXECUTE IMMEDIATE ''DROP TABLE WL_HOTROW PURGE'';
                         EXCEPTION WHEN OTHERS THEN NULL; END;
                       END;',
        start_date => v_end_time + INTERVAL '30' SECOND,
        enabled    => TRUE,
        auto_drop  => TRUE);
END;

---

Submission

Call create_sql_job with:

name:                  wl_<dbname>_<YYYYMMDD_HHMM>   (e.g. wl_dbmdemo_20260501_1430)
compartment_id:        <compartment_ocid>
managed_database_id:   <managed_database_ocid>
named_credential_id:   <named_credential_ocid>
sql_text:              <substituted PL/SQL block above>
sql_type:              PLSQL
schedule_type:         IMMEDIATE
description:           Workload generator — row lock contention + parse pressure

After submission, retrieve the job run via get_job_run(job_run_id=<id from response>). A run_status of SUCCEEDED or COMPLETED means the scheduler jobs were registered successfully inside the database. The workload is now running.

---

Post-submission handoff

Surface to the user:

Workload registered successfully.

Start time:      <now>
End time:        <now + duration_seconds>
AWR close snap:  <end_time + 30 seconds>  (taken automatically by WL_CLEANUP)

To retrieve snap IDs after workload ends:
  SELECT label, snap_id, run_ts FROM WL_RUN_LOG ORDER BY run_ts;

Diagnostic window:
  - Live diagnostics (oci-dbm-diagnose): start NOW — workload is mid-flight
  - AWR-based diagnostics: run AFTER <end_time + 30s> when closing snapshot is taken

Do not poll the workload. Do not start oci-dbm-diagnose yourself. Hand off the timing and snap-ID query and stop.

---

Failure handling

Failure	Action
Job run run_status=FAILED	Report the error_message from get_job_run. Common cause: named credential lacks CREATE JOB privilege on the DB user.
PL/SQL compile error in job result	Surface the full error text. Check for syntax issues in the substituted block (especially the timestamp literal format).
get_managed_database returns non-ADVANCED	Stop. The ADB is not fully DBM-enabled. Run oci-dbm-enable first.
Named credential not known	Ask: "What is the OCID of the DBM named credential for this database? You can find it in OCI Console → Database Management → Named Credentials."

---

Demo environment values (DBMDEMO)

• Managed DB OCID: ocid1.autonomousdatabase.oc1.phx.anyhqljrqgp2kriabddvtbcg5ocsemiytiddszbpeskraixzbydmddestzha
• Compartment OCID: ocid1.compartment.oc1..aaaaaaaac5jtulhrssnemmnzgr5tweter4hxslxnu4w5ophju6pi5hu3x54q
• Named credential: retrieve from OCI Console or list via DBM API for this database