MCP Query Optimizer

An MCP (Model Context Protocol) server that gives AI assistants (Claude, Cursor, etc.) the ability to analyze and optimize SQL queries against live Oracle and MySQL databases — without executing the query.

The AI connects to your database, collects execution plans and metadata, detects performance anti-patterns, and produces a structured optimization report with concrete CREATE INDEX / DROP INDEX recommendations and an optimized query variant.

---

How It Works

You ask: "Why is <query> slow on <db_name>?" -- need this db accessiable 
        ↓
AI calls analyze_oracle_query / analyze_mysql_query
        ↓
MCP validates SQL (security check — no DML/DDL allowed)
        ↓
Connects to your database (read-only)
        ↓
Runs EXPLAIN PLAN — never executes the query
        ↓
Collects metadata: table stats, index stats, column stats, optimizer params
        ↓
Detects anti-patterns: full table scans, Cartesian products, unused indexes
        ↓
Returns structured facts{} + analysis prompt to the AI
        ↓
AI writes optimization report with specific SQL recommendations with option to compare orgin query to optimize one

---

Supported Databases

Database	Versions	Tool
Oracle	11g, 12c, 18c, 19c, 21c	analyze_oracle_query
MySQL	5.7, 8.0+	analyze_mysql_query

---

Key Design Principles

• Read-only — never executes the query being analyzed, only EXPLAIN PLAN / EXPLAIN FORMAT=JSON
• Safe — two-phase SQL validation blocks all DML, DDL, DCL, and PL/SQL before any DB interaction
• Token-efficient — three output presets control how much data is returned to the AI (minimal ~5K, compact ~20K, standard ~40K tokens)
• No external dependencies — no data warehouse, no telemetry, no cloud services required

---

Available Tools

Tool	Database	Purpose
analyze_oracle_query	Oracle	Full query analysis: plan + table/index/column stats + anti-pattern detection
analyze_mysql_query	MySQL	Full query analysis: plan + table/index stats + index usage from performance_schema
compare_oracle_query_plans	Oracle	Side-by-side cost comparison of original vs optimized query
compare_mysql_query_plans	MySQL	Side-by-side cost comparison of original vs optimized query
get_slow_queries	Oracle	List slowest queries from V$SQL ranked by elapsed time, CPU, or I/O
analyze_query_by_sql_id	Oracle	Fetch a query from V$SQL by SQL_ID and run full analysis
get_slow_mysql_queries	MySQL	List slowest queries from performance_schema
analyze_mysql_query_by_digest	MySQL	Fetch a query from performance_schema by digest and analyze it
list_available_databases	Both	List configured database presets and test connectivity
get_knowledge_base_content	—	Fetch embedded documentation for any topic

---

What the AI Can Do With This MCP

Slow Query Analysis

Ask: "Why is this query slow?" or "Optimize this SQL" or "Compare 2 queries"

The AI will:

1. Run list_available_databases() to confirm the DB type
2. Call analyze_oracle_query() or analyze_mysql_query() with your SQL
3. Review the execution plan for full table scans, bad join methods, missing indexes
4. Produce a report with specific CREATE INDEX statements and a rewritten query
5. Offer to verify the improvement with compare_*_query_plans()

Example conversation:

You:  "This query takes 45 seconds on our transformer_master DB. Can you optimize it?
       SELECT o.id, c.name, SUM(p.amount)
       FROM orders o
       JOIN customers c ON o.customer_id = c.id
       JOIN payments p ON p.order_id = o.id
       WHERE o.status = 'OPEN' AND o.created_at > '2024-01-01'
       GROUP BY o.id, c.name"

AI:   [calls analyze_oracle_query("prod_oracle", sql)]

      Query Performance Analysis
      ──────────────────────────
      Issues Found:
      • Full table scan on ORDERS (12.4M rows) — no index on (status, created_at)
      • Full table scan on PAYMENTS (38M rows) — no index on order_id
      • Hash join on CUSTOMERS is fine (indexed PK lookup)

      Recommendations:
        CREATE INDEX idx_orders_status_date ON orders(status, created_at);
        CREATE INDEX idx_payments_order_id  ON payments(order_id);

      Estimated improvement: ~95% cost reduction (cost 84,200 → ~4,100)

      Optimized query: [same query — indexes do the work, no rewrite needed]
      Confidence: High

      Growth Impact (2x/5x/10x rows):
      | Growth | Est. Cost | Risk                        |
      |--------|-----------|-----------------------------| 
      | 1x     | 84,200    | baseline (current)          |
      | 2x     | ~168,000  | critical — add indexes now  |
      | 5x     | ~420,000  | system impact               |
      | 10x    | ~840,000  | query will time out         |

      Shall I run compare queries plans to verify the improvement?

---

Finding Slow Queries in Production (Oracle) -note, this require specific privileges

Ask: "What are the slowest queries running on prod right now?"

You:  "Show me the top 5 slowest queries from the last 2 hours"

AI:   [calls get_slow_queries("prod_oracle", limit=5, order_by="elapsed", last_hours=2)]

      Rank 1 — sql_id: 6pvvuu6m6z9x4
        Avg elapsed: 47.3s | Executions: 142 | Avg buffer gets: 2,847,000
        Preview: SELECT o.id, c.name FROM orders o JOIN customers...

      Rank 2 — sql_id: 9abcd1234xyz
        Avg elapsed: 12.1s | Executions: 891 | Avg buffer gets: 445,000
        ...

You:  "Analyze rank 1"

AI:   [calls analyze_query_by_sql_id("prod_oracle", "6pvvuu6m6z9x4")]
      → Full optimization report

---

Plan Comparison (Before / After)

Ask: "Did adding that index actually help?"

You:  "Compare the original query vs the version with the new index hint"

AI:   [calls compare_oracle_query_plans("prod_oracle", original_sql, optimized_sql)]

      Comparison Result:
      ┌─────────────┬──────────┬──────────┐
      │             │ Original │ Optimized│
      ├─────────────┼──────────┼──────────┤
      │ Cost        │ 84,200   │ 4,100    │
      │ Plan steps  │ 12       │ 7        │
      │ FTS count   │ 2        │ 0        │
      └─────────────┴──────────┴──────────┘
      Improvement: 95.1% cost reduction ✅
      Optimizer switched: TABLE ACCESS FULL → INDEX RANGE SCAN

---

MySQL Index Usage Audit

Ask: "Do we have any unused indexes on the orders table?"

You:  "Check for unused or duplicate indexes on our MySQL prod DB"

AI:   [calls analyze_mysql_query("mysql_prod", "SELECT * FROM orders WHERE status='OPEN'")]

      Index Audit:
      • idx_orders_legacy — UNUSED (0 reads in performance_schema)
        → Safe to DROP — saves ~2.3GB disk + reduces write overhead
      • idx_orders_status and idx_orders_status_date — DUPLICATE
        → idx_orders_status is redundant (covered by idx_orders_status_date)
        → DROP INDEX idx_orders_status ON orders;

---

Analysis Depth & Output Presets

These are two independent controls:

depth — controls what gets collected from the database

Value	Speed	What's Collected
plan_only	~0.3s	Execution plan only — no metadata
standard	1–3s	Plan + all table/index/column stats (default)

output_preset — controls how much is returned to the AI

Value	Tokens	Best For
minimal	~5K	Large/complex queries, tight token budget
compact	~20K	Routine analysis — recommended default
standard	~40K	Deep analysis, storage investigation

Combining them:

depth="standard", output_preset="compact"   → full collection, compact output (recommended)
depth="plan_only", output_preset="minimal"  → fastest possible, plan only
depth="standard", output_preset="standard"  → everything, for deep investigations

---

Security Model

All SQL is validated in two phases before any database interaction:

1. Security check — blocks INSERT, UPDATE, DELETE, MERGE, CREATE, DROP, ALTER, GRANT, REVOKE, BEGIN, EXECUTE, CALL, SELECT INTO, and all other non-SELECT operations
2. Syntax check — validates the SELECT query parses correctly and references valid objects

Dangerous queries are rejected immediately. The MCP never executes the query being analyzed.

---

Quick Start

1. Configure your databases

Copy the example config and add your database connections:

cp server/config/settings.yaml server/config/settings.yaml

database_presets:

  prod_oracle:
    type: oracle
    user: myuser
    password: mypassword
    dsn: hostname:1521/service_name

  prod_mysql:
    type: mysql
    host: hostname
    port: 3306
    user: myuser
    password: mypassword
    database: myschema

2. Configure environment

cp .env.example .env
# Edit .env and set MCP_PORT and any other values

3. Run with Docker

docker compose up

Or directly:

pip install -r server/requirements.txt
python server/server.py

4. Point your MCP client at the server

The MCP endpoint depends on the FastMCP version. Check the server startup logs for the exact path. Typically one of:

http://localhost:8101/mcp    # FastMCP 2.x streamable-http
http://localhost:8101        # if mounted directly at root

---

Authentication

Authentication is optional and controlled by a single flag in settings.yaml.

Disabled (default for local/dev use)

server:
  authentication:
    enabled: false

All requests are accepted with no credentials required. Useful for local development or trusted internal networks.

Enabled (recommended for shared/production use)

server:
  authentication:
    enabled: true
    api_keys:
      - name: "dba-team"
        key: "your-secure-key-here"
        description: "DBA team access"
      - name: "claude-desktop"
        key: "another-key-here"
        description: "Claude Desktop client"

When enabled, every request must include a Bearer token:

Authorization: Bearer your-secure-key-here

The following endpoints are always public regardless of auth setting:

• GET /health
• GET /healthz
• GET /health/deep
• GET /version

Generating secure keys

python -c "import secrets; print(secrets.token_urlsafe(32))"

Claude Desktop config (with auth enabled)

{
  "mcpServers": {
    "query-optimizer": {
      "url": "http://localhost:8101/mcp",
      "headers": {  // adjust path if needed — check server startup logs
        "Authorization": "Bearer your-secure-key-here"
      }
    }
  }
}

Claude Desktop config (auth disabled)

{
  "mcpServers": {
    "query-optimizer": {
      "url": "http://localhost:8101/mcp"  // adjust path if needed — check server startup logs
    }
  }
}

---

Required Database Privileges

Oracle

GRANT SELECT ON all_tables            TO <user>;
GRANT SELECT ON all_indexes           TO <user>;
GRANT SELECT ON all_tab_col_statistics TO <user>;
GRANT SELECT ON all_ind_columns       TO <user>;
GRANT SELECT ON all_constraints       TO <user>;
GRANT SELECT ON v$sql                 TO <user>;  -- for get_slow_queries
GRANT SELECT ON v$parameter           TO <user>;  -- for optimizer params
-- plan_table must exist (created automatically or via GRANT)

MySQL

GRANT SELECT ON information_schema.*   TO '<user>'@'%';
GRANT SELECT ON performance_schema.*   TO '<user>'@'%';  -- for index usage stats
GRANT SELECT ON <your_schema>.*        TO '<user>'@'%';

---

Project Structure

mcp_query_optimizer/
├── server/
│   ├── tools/
│   │   ├── oracle_analysis.py        # Oracle MCP tools (analyze, compare, slow queries)
│   │   ├── mysql_analysis.py         # MySQL MCP tools (analyze, compare, slow queries)
│   │   ├── oracle_collector_impl.py  # Oracle metadata collection logic
│   │   ├── mysql_collector_impl.py   # MySQL metadata collection logic
│   │   ├── plan_visualizer.py        # Execution plan ASCII tree builder
│   │   ├── database_tools.py         # list_available_databases tool
│   │   └── help_tools.py             # get_knowledge_base_content tool
│   ├── monitoring/
│   │   └── oracle_monitor.py         # Real-time Oracle V$ view monitoring
│   ├── knowledge_base/               # Embedded docs served to the AI
│   │   ├── overview.md
│   │   ├── architecture.md
│   │   ├── workflows.md
│   │   ├── depth_modes.md
│   │   ├── troubleshooting.md
│   │   └── tools/
│   │       ├── analyze_oracle_query.md
│   │       └── analyze_mysql_query.md
│   ├── config/
│   │   └── settings.yaml             # Database presets + server config
│   ├── server.py                     # FastAPI/MCP server entrypoint
│   ├── mcp_app.py                    # MCP app instance
│   ├── db_connector.py               # Oracle connection manager
│   ├── mysql_connector.py            # MySQL connection manager
│   ├── auth_middleware.py            # API key authentication
│   ├── config.py                     # Config loader
│   ├── Dockerfile
│   └── requirements.txt
├── docker-compose.yaml
├── .env.example
└── README.md