Built-in Tools

The Oracle Database MCP Toolkit includes several built-in tools for JDBC log analysis, RDBMS trace parsing, vector similarity search, and SQL execution plan analysis.

Oracle JDBC Log Analysis

These tools analyze Oracle JDBC thin client logs to extract performance statistics, queries, errors, and connection events.

No database connection required for log analysis tools. You can run the server with only these tools enabled without setting db.url.

get-jdbc-stats

Extract performance statistics from JDBC log files. Parameters:

logFilePath (string, required): Path to the JDBC log file

Returns:

Error counts (server and client)
Packets sent/received
Bytes sent/received
Connection information
Timestamps and duration

Example:

"Get JDBC statistics from /var/log/app/jdbc.log"

Sample Output:

{
  "errors": {
    "server": 3,
    "client": 1
  },
  "packets": {
    "sent": 1542,
    "received": 1538
  },
  "bytes": {
    "sent": 245678,
    "received": 1023456
  },
  "duration_ms": 45230
}

get-jdbc-queries

Retrieve all executed SQL queries with timestamps and execution times. Parameters:

logFilePath (string, required): Path to the JDBC log file

Returns:

List of SQL statements
Execution timestamps
Query execution times
Statement types (SELECT, INSERT, UPDATE, etc.)

Example:

"Get all queries from JDBC log /logs/application.log"

Sample Output:

[
  {
    "timestamp": "2024-03-15T10:23:45.123Z",
    "query": "SELECT * FROM customers WHERE id = ?",
    "execution_time_ms": 23,
    "type": "SELECT"
  },
  {
    "timestamp": "2024-03-15T10:23:45.456Z",
    "query": "UPDATE orders SET status = ? WHERE id = ?",
    "execution_time_ms": 67,
    "type": "UPDATE"
  }
]

get-jdbc-errors

Extract all errors reported by both server and client. Parameters:

logFilePath (string, required): Path to the JDBC log file

Returns:

Error messages
Error codes (ORA-xxxxx)
Timestamps
Error source (server or client)
Stack traces (if available)

Example:

"Extract errors from JDBC log at /logs/errors.log"

Sample Output:

[
  {
    "timestamp": "2024-03-15T10:30:12.456Z",
    "source": "server",
    "code": "ORA-00001",
    "message": "unique constraint violated",
    "details": "Duplicate key error on CUSTOMERS_PK"
  },
  {
    "timestamp": "2024-03-15T10:31:05.789Z",
    "source": "client",
    "code": "JDBC-001",
    "message": "Connection timeout",
    "stack_trace": "..."
  }
]

get-jdbc-connection-events

Show connection lifecycle events (open, close, errors). Parameters:

logFilePath (string, required): Path to the JDBC log file

Returns:

Connection open events
Connection close events
Connection errors
Connection IDs
Timestamps and durations

Example:

"Show connection events from /logs/jdbc.log"

Sample Output:

[
  {
    "event": "OPEN",
    "connection_id": "conn-12345",
    "timestamp": "2024-03-15T10:00:00.000Z",
    "url": "jdbc:oracle:thin:@host:1521/service"
  },
  {
    "event": "CLOSE",
    "connection_id": "conn-12345",
    "timestamp": "2024-03-15T10:45:30.123Z",
    "duration_ms": 2730123
  }
]

list-log-files-from-directory

List all visible files in a specified directory for analysis. Parameters:

directoryPath (string, required): Path to the directory

Returns:

List of file names
File sizes
Last modified timestamps

Example:

"List all log files in /var/log/app/"

Use Case: Analyze multiple log files with one prompt:

"List log files in /logs/, then analyze the most recent one"

jdbc-log-comparison

Compare two JDBC log files for performance metrics, errors, and network information. Parameters:

logFilePath1 (string, required): Path to the first log file
logFilePath2 (string, required): Path to the second log file

Returns:

Side-by-side comparison of statistics
Differences in error counts
Performance deltas
Network traffic comparison

Example:

"Compare JDBC logs /logs/before.log and /logs/after.log"

Sample Output:

{
  "file1": "/logs/before.log",
  "file2": "/logs/after.log",
  "comparison": {
    "errors": {
      "file1": 15,
      "file2": 3,
      "delta": -12,
      "improvement": "80%"
    },
    "avg_query_time_ms": {
      "file1": 234,
      "file2": 45,
      "delta": -189,
      "improvement": "80.8%"
    },
    "packets_sent": {
      "file1": 1000,
      "file2": 950,
      "delta": -50
    }
  }
}

Use Case: Before/after optimization analysis:

"Compare JDBC logs before and after adding connection pooling"

RDBMS/SQLNet Trace Analysis

These tools parse Oracle RDBMS and SQLNet trace files.

get-rdbms-errors

Extract errors from RDBMS/SQLNet trace files. Parameters:

traceFilePath (string, required): Path to the trace file

Returns:

Error messages from RDBMS
Error codes
Timestamps
Context information

Example:

"Extract errors from trace file /u01/app/oracle/diag/rdbms/trace/orcl_ora_12345.trc"

get-rdbms-packet-dumps

Extract packet dumps for a specific connection ID. Parameters:

traceFilePath (string, required): Path to the trace file
connectionId (string, required): Connection ID to filter

Returns:

Packet dump data
Timestamps
Packet direction (send/receive)
Packet contents (hex and ASCII)

Example:

"Get packet dumps for connection ID 54321 from /oracle/trace/sqlnet.trc"

Use Case: Network-level debugging:

"Show me packet dumps for connection 12345 to debug the protocol error"

Vector Similarity Search

Perform semantic similarity search using Oracle’s vector features.

Requires database connection and Oracle Database 23ai or later with vector features enabled.

similarity_search

Find semantically similar text using vector embeddings. Parameters:

question (string, required): Natural language query
topK (integer, optional, default: 5): Number of results to return
table (string, optional, default: profile_oracle): Table name
dataColumn (string, optional, default: text): Text/CLOB column
embeddingColumn (string, optional, default: embedding): VECTOR column
modelName (string, optional, default: doc_model): Vector model name
textFetchLimit (integer, optional, default: 4000): Max text length to return

Returns:

JSON array of similar rows
Similarity scores (distance metrics)
Text snippets (truncated to textFetchLimit)

How It Works:

Converts your question to a vector using VECTOR_EMBEDDING
Calculates similarity using VECTOR_DISTANCE
Returns top K most similar results

Example:

"Find documents similar to 'database performance tuning best practices'"

Sample Output:

[
  {
    "score": 0.023,
    "text": "Oracle Database performance tuning involves analyzing execution plans, optimizing SQL queries, and proper indexing strategies...",
    "metadata": {
      "id": 42,
      "title": "Performance Tuning Guide"
    }
  },
  {
    "score": 0.047,
    "text": "Database optimization requires understanding query patterns, using appropriate indexes, and monitoring system resources...",
    "metadata": {
      "id": 89,
      "title": "SQL Optimization"
    }
  }
]

SQL Generated (simplified):

SELECT 
  SUBSTR(text, 1, 4000) as text_snippet,
  VECTOR_DISTANCE(embedding, 
    VECTOR_EMBEDDING(doc_model USING 'database performance tuning' as data)
  ) as distance
FROM profile_oracle
ORDER BY distance
FETCH FIRST 5 ROWS ONLY

Custom Table Example:

"Search the knowledge_base table using 'article_text' and 'text_vector' columns for articles about 'connection pooling'"

Advanced Usage:

{
  "question": "Oracle backup strategies",
  "topK": 10,
  "table": "documentation",
  "dataColumn": "content",
  "embeddingColumn": "content_embedding",
  "modelName": "custom_doc_model",
  "textFetchLimit": 2000
}

SQL Execution Plan Analysis

Generate and analyze Oracle execution plans with LLM-ready prompts for tuning.

Requires database connection and appropriate privileges (EXPLAIN PLAN or execute permissions).

explain_plan

Generate execution plans and receive pre-formatted prompts for LLM-based tuning suggestions. Parameters:

sql (string, required): SQL query to analyze
mode (string, optional, default: static): Plan mode (static or dynamic)
execute (boolean, optional): Execute SQL to obtain cursor (dynamic mode)
maxRows (integer, optional, default: 1): Limit rows fetched during execution
xplanOptions (string, optional): DBMS_XPLAN formatting options

Returns:

planText: Formatted execution plan from DBMS_XPLAN
llmPrompt: Structured prompt for LLM analysis and tuning suggestions

Mode: Static (EXPLAIN PLAN)

What it does:

Uses EXPLAIN PLAN FOR ...
Generates estimated execution plan
Does not execute the SQL
Safe for DML statements

Default xplanOptions: BASIC +OUTLINE +PROJECTION +ALIAS Example:

"Explain the execution plan for: SELECT * FROM orders WHERE customer_id = 12345"

Sample Output:

Plan Text:
---------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |   100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ORDERS  |     1 |   100 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

LLM Prompt:
-----------
Analyze this Oracle execution plan and provide tuning recommendations:

SQL Query:
SELECT * FROM orders WHERE customer_id = 12345

Execution Plan:
[plan text above]

Please provide:
1. Identify performance bottlenecks (full table scans, high costs)
2. Suggest index improvements
3. Recommend query rewrites if applicable
4. Estimate performance impact of suggestions

Mode: Dynamic (DBMS_XPLAN.DISPLAY_CURSOR)

What it does:

Optionally executes the SQL to obtain a cursor
Uses DBMS_XPLAN.DISPLAY_CURSOR
Shows actual runtime statistics
Includes actual rows, buffers, reads

Default xplanOptions: ALLSTATS LAST +PEEKED_BINDS +OUTLINE +PROJECTION Example:

"Explain the execution plan for SELECT * FROM large_table WHERE status = 'PENDING' using dynamic mode and execute it"

Parameters:

{
  "sql": "SELECT * FROM large_table WHERE status = 'PENDING'",
  "mode": "dynamic",
  "execute": true,
  "maxRows": 10
}

Sample Output:

Plan Text:
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows | Buffers | Reads  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |     10 |   15234 |  12000 |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |      1 |  10000 |     10 |   15234 |  12000 |
-------------------------------------------------------------------------------------------

LLM Prompt:
-----------
Analyze this Oracle execution plan with ACTUAL runtime statistics:

SQL Query:
SELECT * FROM large_table WHERE status = 'PENDING'

Execution Mode: DYNAMIC (actual statistics)
Rows Fetched: 10 (limited by maxRows parameter)

Execution Plan with Runtime Stats:
[plan text above]

Key Observations:
- E-Rows: Estimated rows by optimizer
- A-Rows: Actual rows processed
- Buffers: Logical reads (buffer cache)
- Reads: Physical reads (disk I/O)

Please analyze:
1. Compare estimated vs actual rows (cardinality issues?)
2. Identify high buffer/read operations
3. Suggest indexes or partitioning
4. Recommend statistics refresh if cardinality is off
5. Evaluate if full table scan is appropriate

Custom XPLAN Options

Static mode custom options:

{
  "sql": "SELECT ...",
  "mode": "static",
  "xplanOptions": "ADVANCED +OUTLINE +PROJECTION"
}

Dynamic mode custom options:

{
  "sql": "SELECT ...",
  "mode": "dynamic",
  "execute": true,
  "xplanOptions": "ALLSTATS LAST +PEEKED_BINDS +COST +BYTES"
}

Use Cases

Development: Safe Analysis

"Explain this UPDATE statement without running it:
UPDATE employees SET salary = salary * 1.1 WHERE department = 'SALES'"

Production: Actual Statistics

"Analyze the execution plan for this slow query using dynamic mode:
SELECT * FROM transactions WHERE date > SYSDATE - 7"

Tuning Workflow

# Step 1: Get plan
"Explain execution plan for: SELECT * FROM orders WHERE status = 'PENDING'"

# Step 2: LLM analyzes the llmPrompt
# (LLM suggests adding an index on status column)

# Step 3: Implement and compare
"Create index orders_status_idx on orders(status);"
"Explain execution plan for the same query again"

Tool Selection

Use the -Dtools system property to enable specific tools:

# Only JDBC log analysis
java -Dtools=get-jdbc-stats,get-jdbc-queries,get-jdbc-errors \
     -jar oracle-db-mcp-toolkit-1.0.0.jar

# Only database tools (vector search + explain plan)
java -Dtools=similarity_search,explain_plan \
     -Ddb.url=jdbc:oracle:thin:@host:1521/service \
     -Ddb.user=user \
     -Ddb.password=pass \
     -jar oracle-db-mcp-toolkit-1.0.0.jar

# All tools (default)
java -Dtools=all \
     -Ddb.url=jdbc:oracle:thin:@host:1521/service \
     -Ddb.user=user \
     -Ddb.password=pass \
     -jar oracle-db-mcp-toolkit-1.0.0.jar

Complete Workflow Examples

Performance Troubleshooting

# Step 1: Analyze application logs
"Get JDBC statistics from /logs/app.log"

# Step 2: Identify slow queries
"Get all queries from the JDBC log"

# Step 3: Analyze execution plan for slowest query
"Explain the execution plan for: [slow query SQL]"

# Step 4: Use LLM prompt to get tuning suggestions
# (The explain_plan tool provides an llmPrompt field)

# Step 5: Compare before/after
"Compare JDBC logs /logs/before.log and /logs/after_optimization.log"

Semantic Search Application

# Step 1: Create table with embeddings (run SQL directly)
CREATE TABLE knowledge_base (
  id NUMBER PRIMARY KEY,
  title VARCHAR2(255),
  text CLOB,
  embedding VECTOR
);

# Step 2: Insert documents with embeddings
# (Use VECTOR_EMBEDDING function)

# Step 3: Search using similarity_search
"Find articles similar to 'Oracle Database backup and recovery'"

# Step 4: Refine search
"Search knowledge_base table for the top 10 articles about 'performance tuning'"

Production Issue Investigation

# Step 1: List recent logs
"List all log files in /var/log/oracle/"

# Step 2: Check for errors
"Extract errors from /var/log/oracle/jdbc_2024-03-15.log"

# Step 3: Analyze connection issues
"Show connection events from the same log file"

# Step 4: Check RDBMS traces
"Extract errors from trace file /u01/app/oracle/diag/rdbms/trace/alert_ORCL.log"

# Step 5: Deep dive on specific connection
"Get packet dumps for connection ID 54321 from /oracle/trace/sqlnet.trc"

Overview

MySQL

Oracle Database

Oracle JDBC Log Analysis

get-jdbc-stats

get-jdbc-queries

get-jdbc-errors

get-jdbc-connection-events

list-log-files-from-directory

jdbc-log-comparison

RDBMS/SQLNet Trace Analysis

get-rdbms-errors

get-rdbms-packet-dumps

Vector Similarity Search

similarity_search

SQL Execution Plan Analysis

explain_plan

Mode: Static (EXPLAIN PLAN)

Mode: Dynamic (DBMS_XPLAN.DISPLAY_CURSOR)

Custom XPLAN Options

Use Cases

Development: Safe Analysis

Production: Actual Statistics

Tuning Workflow

Tool Selection

Complete Workflow Examples

Performance Troubleshooting

Semantic Search Application

Production Issue Investigation

Next Steps

Custom Tools

Deployment

Build docs developers (and LLMs) love

Overview

MySQL

Oracle Database

​Oracle JDBC Log Analysis

​get-jdbc-stats

​get-jdbc-queries

​get-jdbc-errors

​get-jdbc-connection-events

​list-log-files-from-directory

​jdbc-log-comparison

​RDBMS/SQLNet Trace Analysis

​get-rdbms-errors

​get-rdbms-packet-dumps

​Vector Similarity Search

​similarity_search

​SQL Execution Plan Analysis

​explain_plan

​Mode: Static (EXPLAIN PLAN)

​Mode: Dynamic (DBMS_XPLAN.DISPLAY_CURSOR)

​Custom XPLAN Options

​Use Cases

​Development: Safe Analysis

​Production: Actual Statistics

​Tuning Workflow

​Tool Selection

​Complete Workflow Examples

​Performance Troubleshooting

​Semantic Search Application

​Production Issue Investigation

​Next Steps

Custom Tools

Deployment

Build docs developers (and LLMs) love

Oracle JDBC Log Analysis

get-jdbc-stats

get-jdbc-queries

get-jdbc-errors

get-jdbc-connection-events

list-log-files-from-directory

jdbc-log-comparison

RDBMS/SQLNet Trace Analysis

get-rdbms-errors

get-rdbms-packet-dumps

Vector Similarity Search

similarity_search

SQL Execution Plan Analysis

explain_plan

Mode: Static (EXPLAIN PLAN)

Mode: Dynamic (DBMS_XPLAN.DISPLAY_CURSOR)

Custom XPLAN Options

Use Cases

Development: Safe Analysis

Production: Actual Statistics

Tuning Workflow

Tool Selection

Complete Workflow Examples

Performance Troubleshooting

Semantic Search Application

Production Issue Investigation

Next Steps