Oracle Graph DBA Advisor

Oracle Graph DBA Advisor

A system prompt, SQL template set, and knowledge base that turns an MCP-compatible LLM into a read-only Oracle Property Graph performance advisor for Oracle Database 23ai and 26ai.

The primary path is Diagnostic Mode: analyze an existing graph workload, collect database evidence, explain the root cause, and produce DBA-ready recommendations. For ADB Serverless, the preferred runtime is ADB Native MCP with one controlled read-only SQL tool.

What this does

Diagnostic Mode answers questions like:

Analyze my graph workload and tell me what is slow, why it is slow, and what the DBA team should change.

The advisor:

1. Confirms the safety posture and stays read-only by default.
2. Builds a database health baseline from performance views.
3. Discovers property graph objects, owners, backing tables, indexes, and stats.
4. Finds expensive SQL/PGQ workload from V$SQL, AWR, and ASH.
5. Reads execution plans and maps relational operators back to graph hops.
6. Explains root cause with SQL_ID, plan, wait, and object evidence.
7. Produces recommendations with DDL, validation SQL, and rollback text.

The skill is designed for DBAs and platform teams that already have an Oracle property graph workload and need a repeatable diagnostic workflow without giving the LLM write access to the database.

Current focus

Area	Status	Notes
Diagnostic Mode	Primary	Customer-facing path for workload diagnosis and tuning.
ADB Native MCP	Preferred for ADB Serverless	No SQLcl runtime dependency; expose only approved database-side tools.
SQLcl MCP	Secondary fallback	Useful for local, on-prem, Base DB, ADB Dedicated, or non-ADB Native MCP cases.
Consultive Mode	Secondary	Design-from-description flow; kept below the diagnostic path.
Agent Factory governance	Roadmap	Evaluate only for governance, guardrails, auditing, and tool allowlisting.

Architecture

flowchart TB
    user["DBA / Admin<br/>diagnostic request"]
    llm["MCP-compatible LLM<br/>advisor runtime"]

    subgraph skill["Graph DBA Advisor skill"]
        prompt["SYSTEM_PROMPT.md<br/>methodology and safety rules"]
        templates["sql-templates/<br/>diagnostic SQL"]
        knowledge["knowledge/<br/>graph and optimizer rules"]
    end

    subgraph runtime["Primary runtime: ADB Native MCP"]
        endpoint["ADB Native MCP endpoint<br/>one endpoint per target database"]
        auth["OAuth or bearer token<br/>dedicated technical user"]
        tool["RUN_SQL<br/>read-only SELECT/WITH only"]
        guardrails["Database-side guardrails<br/>literal-aware DDL/DML/comment blocking"]
    end

    subgraph database["Oracle Database 23ai / 26ai"]
        perf["Performance evidence<br/>V$SQL, plans, waits, AWR, ASH"]
        catalog["Graph catalog<br/>DBA_PROPERTY_GRAPHS, DBA_PG_*"]
        output["Advisor output<br/>root cause, recommendations, rollback SQL"]
    end

    fallback["SQLcl MCP fallback<br/>local or non-ADB Native cases"]

    user --> llm
    llm --> skill
    skill --> endpoint
    endpoint --> auth
    auth --> tool
    tool --> guardrails
    guardrails --> perf
    guardrails --> catalog
    perf --> output
    catalog --> output
    output --> llm

    skill -. secondary compatibility path .-> fallback
    fallback -.-> database

    classDef actor fill:#f8fafc,stroke:#64748b,color:#0f172a;
    classDef skillNode fill:#eef2ff,stroke:#4f46e5,color:#111827;
    classDef runtimeNode fill:#ecfdf5,stroke:#059669,color:#064e3b;
    classDef dbNode fill:#fff7ed,stroke:#ea580c,color:#7c2d12;
    classDef fallbackNode fill:#f3f4f6,stroke:#9ca3af,color:#374151,stroke-dasharray: 5 5;

    class user,llm actor;
    class prompt,templates,knowledge skillNode;
    class endpoint,auth,tool,guardrails runtimeNode;
    class perf,catalog,output dbNode;
    class fallback fallbackNode;

For ADB Serverless Diagnostic Mode, the runtime should expose a minimal MCP tool surface. The recommended tool contract is RUN_SQL with one input only: QUERY. It must accept only read-only diagnostic SQL and reject DDL, DML, PL/SQL, comments, statement terminators, client commands, side-effect packages, and SELECT FOR UPDATE when those tokens appear outside string literals. Returned recommendation text may safely contain words such as CREATE INDEX, DROP INDEX, or FOR UPDATE inside string literals. The recommended implementation applies server-side pagination internally; MCP clients should not send OFFSET or LIMIT arguments to RUN_SQL.

SQLcl MCP remains a compatibility path when ADB Native MCP is not the target. It should not be required for the production ADB Serverless diagnostic skill.

Diagnostic Mode requirements

Use this checklist to implement the Diagnostic Mode skill.

Environment