In this post, I demonstrate how a basic LangGraph ReAct agent can work with Oracle MCP Server for Oracle Database. This is just a one of the proof-of-concept steps toward developing an Oracle database diagnostic AI agent application.
The Model Context Protocol (MCP) lets you build servers that expose data and functionality to LLM applications in a secure, standardized way. Think of it like a web API, but specifically designed for LLM interactions. The Oracle MCP Server for Oracle Database is Oracle's implementation of the Model Context Protocol (MCP). It comes with SQLcl 25.2 version, which stands for the SQL Developer Command Line tool, a Java-based command-line interface for Oracle Database. The Key functions and features of the Oracle MCP Server for Oracle Database are summarized as follows:
Direct AI-Database Integration:
It allows AI assistants to directly connect to, query, and analyze data within Oracle databases, eliminating the need for manual SQL generation and execution by users. This enables "agentic workflows" where AI can autonomously implement its recommendations.
Leverages Oracle SQLcl:
The MCP Server integrates through Oracle SQLcl, extending its capabilities to support MCP-based communication. This allows AI applications to access database operations through a defined set of tools and utilize preconfigured SQLcl connections.
Natural Language Interaction:
AI clients can use natural language to perform various database operations, including executing SQL queries, invoking PL/SQL procedures, exploring the data dictionary, and running SQLcl-specific commands.
Enhanced Security:
It leverages existing Oracle Database security frameworks, ensuring that AI access adheres to established organizational security policies and provides transparency and traceability of AI-driven actions within the database.
Broad Compatibility:
The MCP Server works with various Oracle Database versions (e.g., 19c to 23ai) and can be deployed in diverse environments, including on-premises, hyperscalers (Azure, AWS, Google, OCI), and even on local machines.
Facilitates Conversational Databases:
It represents a significant step towards "conversational databases," where AI can interact with structured data in a more intuitive and efficient manner, streamlining tasks like data retrieval, analysis, and report generation.
In my dev env, I've first set up the MCP server and created a saved connection as follows:
- Software needed:
(a) I unzip'ed the latest sqlcl (25.2.2) in the directory ~/denis/python_proj/sqlcl/bin
(b) I placed Oracle instant client at ~/denis/python_proj/instantclient_23_9
(c) JDK 21 at ~/denis/python_proj/jdk-21.0.8
- Set up environment variables
(denis_venv) [dsun001@linuxhost006 python_proj]$ cat setup_mcp_env_dsun001.sh
export JAVA_HOME=/path/to/oracle/denis/python_proj/jdk-21.0.8
export PATH=$JAVA_HOME/bin:$PATH
export ORACLE_HOME=/path/to/oracle/denis/python_proj/instantclient_23_9
cd /path/to/oracle/denis/python_proj/sqlcl/bin
Create a saved connection
cd /path/to/oracle/denis/python_proj/sqlcl/bin
(denis_venv) [dsun001@linuxhost006 bin]$ ./sql test/xxxxx@somehost.mycompany.com:1521/test1db
SQLcl: Release 25.2 Production on Fri Oct 03 18:29:05 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Fri Oct 03 2025 18:29:06 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> conn -save test1db_mcp -savepwd
Name: test1db_mcp
Connect String: somebody.mycompany.com:1521/test1db
User: test
Password: ******
Test the connection
cd /path/to/oracle/denis/python_proj/sqlcl/bin
(denis_venv) [dsun001@linuxhost006 bin]$ ./sql -name test1db_mcp
SQLcl: Release 25.2 Production on Fri Oct 03 18:33:17 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL>
Start MCP server and misc commands
cd /path/to/oracle/denis/python_proj/sqlcl/bin
(denis_venv) [dsun001@linuxhost006 bin]$ ./sql -mcp
---------- MCP SERVER STARTUP ----------
MCP Server started successfully on Fri Oct 03 18:34:11 EDT 2025
$ ./sql /nolog
SQL> connect -name test1db_mcp
Connected.
SQL> connect -name test1db_mcp
Connected.
SQL> connmgr list
.
+-- test1db_mcp
SQL> connmgr show test1db_mcp
Name: test1db_mcp
Connect String: linuxhost001scan.mycompany.com:1521/test1db
User: test
Password: ******
A LangGraph ReAct agent is an AI agent built using the LangGraph framework that implements the ReAct (Reasoning + Acting) paradigm. Here's a breakdown of its key components and how it operates:
ReAct Framework:
This paradigm, inspired by human problem-solving, combines reasoning and action-taking. The agent iteratively performs the following steps:
Thought: The Large Language Model (LLM) reasons about the user query or current state, determining the next logical step.
Action: The LLM decides which tool to use (if any) and how to use it based on its reasoning.
Observation: The agent executes the chosen tool and observes the result, which then informs the next "Thought" step.This loop continues until the agent reaches a final answer or a predefined stop condition.
With the help of Copilot and some sample code googled from web, I was able to quickly stand up a Python program to interact with the MCP, do some basic testing. This gives me greater confidence to explore further in the AI agent world, the program called diagagent, the code repo of the project can be found here: https://github.com/denissun/DBRE-YDS/tree/main/diagagent
One interesting conversation history can be seen in the following section:
You can see that the agent can correctly identify the problem area of a test db running some testing workload, which intentionally did not use correct index .
(denis_venv) [dsun001@linuxhost006 diagagent]$ python app_cli.py
WARNING: All log messages before absl::InitializeLog() is called are written to STDERR
E0000 00:00:1759598621.279845 1613965 alts_credentials.cc:93] ALTS creds ignored. Not running on GCP and untrusted ALTS is not enabled.
============================================================
?? Diagnostic AI Agent through SQLcl MCP - Command Line Interface
============================================================
Ask natural language questions about your Oracle database.
Type 'quit', 'exit', or 'q' to exit the program.
============================================================
?? Available Commands:
help, h - Show this help message
quit, exit, q - Exit the program
clear, cls - Clear the screen
reset, restart - Reset session and conversation history
test - Test SQLcl MCP connection
?? Example queries:
• Connect to MYDB database
• How many employees earn more than 10000?
• Show me the top 5 customers by revenue
• What tables are available in the database?
• Create a summary report of sales by region
?? Database Connection:
The agent maintains your database connection and conversation context.
Once connected, you don't need to reconnect for subsequent queries.
Use 'reset' if you need to start fresh or change connections.
------------------------------------------------------------
?? Enter your question: list connections
?? Processing: list connections
------------------------------------------------------------
?? Initializing persistent MCP session (single-run)...
?? Initializing MCP protocol handshake...
---------- MCP SERVER STARTUP ----------
MCP Server started successfully on Sat Oct 04 13:23:45 EDT 2025
Press Ctrl+C to stop the server
----------------------------------------
Oct 04, 2025 1:23:46 PM io.modelcontextprotocol.server.McpAsyncServer$AsyncServerImpl lambda$asyncInitializeRequestHandler$5
INFO: Client initialize request - Protocol: 2025-06-18, Capabilities: ClientCapabilities[experimental=null, roots=null, sampling=null], Info: Implementation[name=mcp, version=0.1.0]
Oct 04, 2025 1:23:46 PM io.modelcontextprotocol.server.McpAsyncServer$AsyncServerImpl lambda$asyncInitializeRequestHandler$5
WARNING: Client requested unsupported protocol version: 2025-06-18, so the server will sugggest the 2024-11-05 version instead
?? Loading MCP tools (first time)...
? Loaded 5 tools
? Persistent session & agent ready
?? Processing query with persistent session...
E0000 00:00:1759598626.798840 1613965 alts_credentials.cc:93] ALTS creds ignored. Not running on GCP and untrusted ALTS is not enabled.
? Agent Response:
------------------------------------------------------------
connect to test1db_mcp
------------------------------------------------------------
?? Enter your question: connect to test1db_mcp
?? Processing: connect to test1db_mcp
------------------------------------------------------------
?? Processing query with persistent session...
? Agent Response:
------------------------------------------------------------
show tables
------------------------------------------------------------
?? Enter your question: any performance issues?
?? Processing: any performance issues?
------------------------------------------------------------
?? Processing query with persistent session...
? Agent Response:
------------------------------------------------------------
I cannot directly detect performance issues. However, I can run SQL queries to gather information that can help identify potential problems. What metrics or aspects of database performance are you most interested in? For example, I can check:
1. **Long-running queries:** Identify queries that take a long time to execute.
2. **High CPU usage:** Determine if any particular queries are consuming excessive CPU resources.
3. **Index usage:** Analyze whether indexes are being used effectively.
4. **Wait events:** Investigate what events are causing sessions to wait.
Please specify what you would like me to check, and I will generate the appropriate SQL queries.
------------------------------------------------------------
?? Enter your question: Wait events
?? Processing: Wait events
------------------------------------------------------------
?? Processing query with persistent session...
? Agent Response:
------------------------------------------------------------
The top wait events are:
* rdbms ipc message
* PX Idle Wait
* Space Manager: slave idle wait
* pmon timer
* class slave wait
* SQL\*Net message from client
* wait for unread message on broadcast channel
* watchdog main loop
* ges remote message
* gcs yield cpu
These wait events can indicate different types of performance bottlenecks. For example, "SQL\*Net message from client" might suggest network issues or slow client processing, while "PX Idle Wait" is often related to parallel execution. "rdbms ipc message" is related to inter-process communication.
Do you want to investigate any of these further? For example, I can provide more details on "SQL\*Net message from client".
------------------------------------------------------------
?? Enter your question: any long running sql?
?? Processing: any long running sql?
------------------------------------------------------------
?? Processing query with persistent session...
? Agent Response:
------------------------------------------------------------
Here are the top 10 long-running SQL statements, based on elapsed time:
1. **SQL_ID: anagjqfhrc7v3**
* Elapsed Time: 99817854477 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: A PL/SQL block that includes a loop with `DBMS_LOCK.SLEEP(1)`, selects from `big_table`, and conditionally updates `big_table`.
2. **SQL_ID: anagjqfhrc7v3**
* Elapsed Time: 83189131874 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: Same PL/SQL block as above.
3. **SQL_ID: fa1tzyz2rg3h0**
* Elapsed Time: 70702684556 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: `UPDATE BIG_TABLE SET OBJECT_NAME =LOWER(OBJECT_NAME) WHERE TRUNC(LAST_DDL_TIME) BETWEEN SYSDATE-30 AND SYSDATE -25`
4. **SQL_ID: anagjqfhrc7v3**
* Elapsed Time: 38188454383 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: Same PL/SQL block as above.
5. **SQL_ID: fa1tzyz2rg3h0**
* Elapsed Time: 37070296233 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: `UPDATE BIG_TABLE SET OBJECT_NAME =LOWER(OBJECT_NAME) WHERE TRUNC(LAST_DDL_TIME) BETWEEN SYSDATE-30 AND SYSDATE -25`
6. **SQL_ID: 05jnwzh1jfb9y**
* Elapsed Time: 12756757422 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: `SELECT MAX(OBJECT_NAME) FROM BIG_TABLE WHERE TRUNC(LAST_DDL_TIME) = TRUNC(SYSDATE-:B1 )`
7. **SQL_ID: 05jnwzh1jfb9y**
* Elapsed Time: 6706007056 (likely microseconds)
* Module: SQL\*Plus
* SQL Text: `SELECT MAX(OBJECT_NAME) FROM BIG_TABLE WHERE TRUNC(LAST_DDL_TIME) = TRUNC(SYSDATE-:B1 )`
8. **SQL_ID: 6hnhqahphpk8n**
* Elapsed Time: 5102794792 (likely microseconds)
* Module:
* SQL Text: `select free_mb from v$asm_diskgroup_stat where name=:1`
9. **SQL_ID: 6hnhqahphpk8n**
* Elapsed Time: 3787833663 (likely microseconds)
* Module:
* SQL Text: `select free_mb from v$asm_diskgroup_stat where name=:1`
10. **SQL_ID: fhf8upax5cxsz**
* Elapsed Time: 3021389633 (likely microseconds)
* Module:
* SQL Text: `BEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class,:rep_id, :suc); END;`
It appears that the PL/SQL block (SQL\_ID: anagjqfhrc7v3) and the update statement (SQL\_ID: fa1tzyz2rg3h0) against `BIG_TABLE` are the most time-consuming. The select statement (SQL\_ID: 05jnwzh1jfb9y) against `BIG_TABLE` also appears in the list. You should investigate these further. It seems like the PL/SQL code is deliberately slowing down via `DBMS_LOCK.SLEEP(1)`.
Do you want to investigate any of these SQL IDs further? For example, I can explain the execution plan for the update statement.
------------------------------------------------------------
?? Enter your question: can you suggest how to tune fa1tzyz2rg3h0?
?? Processing: can you suggest how to tune fa1tzyz2rg3h0?
------------------------------------------------------------
?? Processing query with persistent session...
? Agent Response:
------------------------------------------------------------
The SQL ID `fa1tzyz2rg3h0` corresponds to the following SQL statement:
`UPDATE BIG_TABLE SET OBJECT_NAME =LOWER(OBJECT_NAME) WHERE TRUNC(LAST_DDL_TIME) BETWEEN SYSDATE-30 AND SYSDATE -25`
Here's a breakdown of potential tuning strategies:
**1. Indexing:**
* **LAST_DDL_TIME:** The `TRUNC(LAST_DDL_TIME)` in the `WHERE` clause is likely preventing efficient index usage. If `LAST_DDL_TIME` is a `DATE` or `TIMESTAMP` column, create an index on `LAST_DDL_TIME`. The database might then be able to use an index range scan.
```sql
CREATE INDEX idx_big_table_last_ddl_time ON BIG_TABLE (LAST_DDL_TIME);
```
**2. Partitioning:**
* If `BIG_TABLE` is large and `LAST_DDL_TIME` represents a natural partitioning key (e.g., daily or weekly), consider partitioning the table by range on `LAST_DDL_TIME`. This can significantly improve the performance of queries that filter on this column.
**3. Statistics:**
* Ensure that the table statistics are up-to-date. This allows the optimizer to make informed decisions about the execution plan.
```sql
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCHEMA_NAME', tabname => 'BIG_TABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
```
Replace `SCHEMA_NAME` with the actual schema name.
**4. Eliminate `TRUNC`:**
* The `TRUNC(LAST_DDL_TIME)` is problematic for index usage. Rewrite the query to avoid truncating the date. For example:
```sql
UPDATE BIG_TABLE
SET OBJECT_NAME = LOWER(OBJECT_NAME)
WHERE LAST_DDL_TIME >= TRUNC(SYSDATE - 30)
AND LAST_DDL_TIME < TRUNC(SYSDATE - 24); -- Corrected the upper bound
```
**5. Case-Insensitive Collation:**
* If you are using a case-insensitive collation, the `LOWER` function might be redundant. Check the collation of the `OBJECT_NAME` column. If it's already case-insensitive, removing `LOWER` might save some CPU cycles.
**6. Batch Processing (If Applicable):**
* If this update is performed frequently, consider batching the updates into larger transactions. This reduces the overhead associated with transaction management.
**7. Check for Blocking:**
* Ensure that other sessions are not blocking this update statement. Use `v$lock` and `v$session` to identify any blocking issues.
Before implementing any of these changes, it's crucial to:
1. **Test:** Test the changes in a non-production environment first.
2. **Explain Plan:** Examine the execution plan before and after the changes to verify that the optimizer is using the index and that the plan is more efficient. You can get the execution plan using `EXPLAIN PLAN FOR` followed by the SQL statement, then query the `TABLE(DBMS_XPLAN.DISPLAY)` to view the plan.
Which of these areas would you like to explore further? For example, I can show you how to get the execution plan.
In summary, integrating a LangGraph ReAct agent with Oracle's SQLcl MCP server bridges the gap between natural language interaction and complex database diagnostics. By combining conversational AI with direct, secure access to database performance views, the agent can autonomously navigate metadata, surface performance bottlenecks like unindexed range queries or artificial latencies, and deliver actionable tuning recommendations. This proof-of-concept highlights the strong potential of autonomous AI agents in transforming routine database administration and diagnostic workflows.
From Blogger iPhone client
