Building a Snowflake Cortex DBA Alert Analyst Agent

This document outlines the creation of an AI-assisted Database Operations Alert Analyst Agent designed to reduce operational "alert noise". It utilizes a standardized schema across on-premise Oracle and AWS RDS databases, with data synchronized into Snowflake in near real-time via Fivetran replication. A dedicated semantic view maps the rigid database schema into natural language synonyms, allowing users to interact with the data without writing manual SQL. Built natively within Snowflake using standard compute resources, the agent is configured with a specific persona to identify recurring patterns, prioritize critical severities, and explain database error codes. Ultimately, this MVP deployment establishes a foundational architecture for real-time operational awareness and future automated workflows.

Link to the doc

 

Prompt-Based AI Agents: Designing Systems Where Plain Text Is the Architecture

Software architecture usually treats code as the source of truth and natural language as documentation. Prompt-based AI agents flip this model entirely: the workflow, domain rules, and decision-making logic live in plain English Markdown files, while code acts solely as an execution engine.

By paring an agent down to its essentials, you can build a flexible system around two core primitives: * The Playbook (The Instructions): A natural language document defining goals, step-by-step reasoning, constraints, and output formats. * The Runtime (The Engine): A lightweight script that feeds the playbook to a Large Language Model (LLM) and executes any actions the model requests.

---

Architectural Breakdown: Separating Logic from Code

In traditional software, adding a feature requires writing explicit if/else logic, custom functions, and output parsers. In a prompt-based architecture, control flow is emergent—the LLM decides the steps dynamically based on the playbook.

• Code as a Dumb Pipe: The underlying code knows nothing about business domains, databases, or specific APIs. It simply passes user input and playbook instructions to the model, executes generic requests (like making an HTTP call), and returns raw responses back to the model.
• The Autonomous ReAct Loop: The agent operates on a continuous Reason -> Act -> Observe cycle. The model evaluates user intent against the playbook, decides whether to trigger an action, observes the result, and repeats until it can construct a final answer.
• Instant Domain Swapping: Because domain knowledge is completely decoupled from the codebase, changing the agent's entire function requires only pointing the runtime at a different Markdown file. The code stays identical whether the agent is querying a database, auditing system health, or managing internal tickets.

---

Conceptual Parallels: Playbooks vs. Claude Code Skills

This design pattern closely mirrors how Claude Code Skills operate. Both treat structured natural language as executable code:

Concept	Prompt-Based Agent	Claude Code Skills
Logic Layer	Playbook (.md file)	Custom Skill (SKILL.md)
Action Layer	Generic HTTP Tool	OS Primitives (Bash, Read, Write)
Runtime	Custom LLM Script	Claude Code CLI Engine

• Declarative Capabilities: Instead of writing Python or TypeScript modules to expand what the agent can do, you write clear instructions describing how to perform a task.
• General-Purpose Primitives: Both systems avoid bespoke, single-use tools. Instead, they give the LLM broad execution primitives (like raw API requests or shell commands) and rely on the instruction file to guide how those tools are used safely and effectively.
• Adaptive Control Flow: If an action fails—such as an API returning an error—the LLM uses the playbook's guidance to interpret the failure and dynamically adjust its strategy without crashing the application.

---

High-Level Trade-offs

Pros

• Zero-Code Expansion: Adding new capability requires writing Markdown, not code.
• Human-Readable Logic: Non-engineers can review and audit agent behavior easily.
• Minimal Maintenance: Very small codebase footprint with minimal wrapper boilerplate.

Cons

• Non-Deterministic Output: Models may occasionally deviate from instructional paths.
• Prompt Brittleness: Unclear prompt wording can trigger unexpected tool calls or formats.
• Higher Cost & Latency: Multi-step tool loops send prompts back and forth across every iteration.

---

When to Use This Pattern

• Ideal Use Cases: Internal automation tools, rapid prototyping, and flexible domains where requirements change rapidly and non-engineers need to tune behavior without redeploying code.
• Poor Use Cases: Safety-critical or financial operations requiring strict determinism, or high-throughput microservices where LLM round-trip latency is unacceptable.

---

Explore the Codebase

To see how a minimal runtime script and Markdown playbooks work together in practice, check out the repository on GitHub:

• GitHub Repository: Prompt-Based AI Agent Codebase

Using Oracle SQLcl MCP with Claude Code CLI tool

While SQL Developer for VS Code provides a built-in "one-click" MCP for Copilot, Claude Code requires you to explicitly add the database MCP server (like SQLcl for Oracle or generic SQL servers) using its CLI.

Here are the steps I used to configure it with CC: 

 (1) Download SQLcl 

You may download the latest Oracle SQLcl tool which supports MCP at https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/ , current version 26.1.

 

In my case, it was downloaded last year with version 25.2 it is at my local path: C:\users\dsun\sqlcl-latest\sqlcl\bin 

(2) Add the SQL MCP Server In my project directory C:\Users\dsun\ClaudeCode_DBA_Assistant:

PS C:\Users\dsun\ClaudeCode_DBA_Assistant> claude mcp add --transport stdio sqlcl -- C:\users\dsun\sqlcl-latest\sqlcl\bin\sql -mcp
Added stdio MCP server sqlcl with command: C:\users\dsun\sqlcl-latest\sqlcl\bin\sql -mcp to local config
File modified: C:\Users\dsun\.claude.json [project: C:\Users\dsun\ClaudeCode_DBA_Assistant]

(3) Verify the Connection

 
PS C:\Users\dsun\ClaudeCode_DBA_Assistant> claude mcp list
sqlcl: C:\users\dsun\sqlcl-latest\sqlcl\bin\sql -mcp - v Connected
PS C:\Users\dsun\ClaudeCode_DBA_Assistant>

(4) Create a saved connection Note: using -thin is necessary in my environment to avoid error from " thick client "

PS C:\users\dsun\sqlcl-latest\sqlcl\bin> .\sql -thin 'test/"xxzzzzzz"@dbhost1.mycompany.com:1521/testdb'


SQLcl: Release 25.2 Production on Wed Jul 01 07:07:37 2026

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 01 2026 07:07:42 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

SQL> conn -save testdb_cc -savepwd
Name: testdb_cc
Connect String: dbhost1.mycompany.com:1521/testdb
User: test
Password: ******
 

(5) Interact with database in CC CLI Using natural language e.g. sqlcl show 10 largest tables in testdb_cc

PS C:\Users\dsun\ClaudeCode_DBA_Assistant> claude
 ¦?¦¦¦?¦   Claude Code v2.1.181
??¦¦¦¦¦??  Haiku 4.5 · API Usage Billing
  ?? ??    ~\ClaudeCode_DBA_Assistant


> sqlcl show 10 largest tables in testdb_cc

  Thought for 3s, called MCP (ctrl+o to expand)

? I'll connect to testdb_cc and query the 10 largest tables using sqlcl.

  Called sqlcl 2 times (ctrl+o to expand)

? Now I'll query the 10 largest tables by size.

  Called sqlcl (ctrl+o to expand)

? Let me check the available columns in DBA_TABLES and use the correct approach.

  Called sqlcl 2 times (ctrl+o to expand)

? Let me use DBA_SEGMENTS instead, which contains the actual allocated space:

  Called sqlcl (ctrl+o to expand)

? Here are the 10 largest tables in testdb_cc:

  +-------------------------------------------------------------------+
  ¦ Owner ¦    Table Name     ¦ Size (GB) ¦ Segment Type ¦ Tablespace ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ TEST  ¦ LOGFILE_SYNC_TEST ¦ 57.30     ¦ TABLE        ¦ USERS      ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER1 ¦ CF1               ¦ 2.06      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER3 ¦ CF1               ¦ 2.01      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER5 ¦ CF1               ¦ 2.00      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER4 ¦ CF1               ¦ 2.00      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER7 ¦ CF1               ¦ 2.00      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER6 ¦ CF1               ¦ 2.00      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER2 ¦ CF1               ¦ 1.99      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ USER8 ¦ CF1               ¦ 1.98      ¦ TABLE        ¦ IOPS       ¦
  +-------+-------------------+-----------+--------------+------------¦
  ¦ TPCC  ¦ STOCK             ¦ 0.18      ¦ TABLE        ¦ TPCC_DATA  ¦
  +-------------------------------------------------------------------+

  Key observations:
  - The TEST.LOGFILE_SYNC_TEST table dominates storage at 57.3 GB (likely a test/load table)
  - Multiple user schemas (USER1-USER8) have similarly-sized CF1 tables (~2 GB each) in the IOPS tablespace
  - The TPCC.STOCK table is relatively small at 0.18 GB in the TPCC_DATA tablespace

  Would you like to analyze space usage patterns, identify candidates for cleanup, or investigate the purposes of these large tables?

? Worked for 25s

? recap: Identified 10 largest tables in testdb_cc: TEST.LOGFILE_SYNC_TEST dominates at 57.3GB, followed by USER schemas' CF1 tables at ~2GB each. Awaiting next diagnostic step. (disable recaps in /config)

In summary, this post demonstrates how to connect Oracle SQLcl to the Claude Code CLI tool using the Model Context Protocol (MCP). By registering SQLcl as an MCP server and saving database credentials locally, users can query Oracle databases using natural language commands directly within the Claude Code interface. In the provided walkthrough, Claude successfully identifies the 10 largest tables in an Oracle 19c database by dynamically inspecting data dictionary views like DBA_SEGMENTS.

AI Engineer - DB Diagnostic AI Agent through SQLcl MCP

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

AI coding agent : a toy project helps me understand the agentic AI concept a lot

Following the following course :

 

I have been able to complete the AI coding agent project , which really helped me understand agentic AI from a practical point of view. To put the agent to the test, I introduced an intentional bug in a simple Calculator class by setting the operator precedence incorrectly:

class Calculator:
    def __init__(self):
        self.operators = {
            "+": lambda a, b: a + b,
            "-": lambda a, b: a - b,
            "*": lambda a, b: a * b,
            "/": lambda a, b: a / b,
        }
        self.precedence = {
            "+": 3,           # should be 1, set to 3 so 3 + 5 * 2 = 16
            "-": 1,
            "*": 2,
            "/": 2,
        }

As expected, this flaw caused the calculator to fail basic math. For instance, the expression 3 + 5 * 2 returned 16 instead of the correct value of 13:

(venv313) C:\Users\dsun\Denis_files\python_proj\ai_apps\aicodeagent>python calculator\main.py " 3 + 5 * 2 "
{
  "expression": " 3 + 5 * 2 ",
  "result": 16
}

What defines an "agent" is its ability to autonomously use tools and iterate based on its findings. In this setup, the agent was equipped with four primary functions:

available_functions = types.Tool(
    function_declarations=[
        schema_get_files_info,
        schema_get_file_content,
        schema_run_python_file,
        schema_write_file,
    ]
)

The following "conversation" log illustrates the agent's decision-making process as it navigated the codebase and applied the fix automatically: (venv313) C:\Users\dsun\Denis_files\python_proj\ai_apps\aicodeagent>python main.py " 3 + 5 * 2 should not be 16 please fix the code problem under calculator folder"

User: " 3 + 5 * 2 should not be 16 please fix the code problem under calculator folder"
Model: "I want to call get_files_info..."
 - Calling function: get_files_info
Tool: "Here's the result of get_files_info..."
Model: "I want to call get_files_info..."
 - Calling function: get_files_info
Tool: "Here's the result of get_files_info..."
Model: "I want to call get_file_content..."
 - Calling function: get_file_content
Tool: "Here's the result of get_file_content..."
Model: "I want to call get_files_info..."
 - Calling function: get_files_info
Tool: "Here's the result of get_files_info..."
Model: "I want to call get_file_content..."
 - Calling function: get_file_content
Tool: "Here's the result of get_file_content..."
Model: "I want to call write_file..."
 - Calling function: write_file
Tool: "Here's the result of write_file..."
Model: "I want to call run_python_file..."
 - Calling function: run_python_file
Tool: "Here's the result of run_python_file..."
Model: "Great! The output is 13, which means the fix was successful. The calculator now correctly evaluates the expression "3 + 5 * 2".
"

With the agent's intervention complete, the bug was resolved without any manual code editing:

(venv313) C:\Users\dsun\Denis_files\python_proj\ai_apps\aicodeagent>python calculator\main.py " 3 + 5 * 2 "
{
  "expression": " 3 + 5 * 2 ",
  "result": 13
}

In summary,this experiment highlighted the most critical aspect of agentic AI: the feedback loop. By allowing the agent to run code, analyze errors, and rewrite files, we move beyond simple text generation into the realm of autonomous problem-solving. To me, this project was a vital stepping stone in understanding how AI can act as a true collaborator in the development process.

PostgreSQL - Sampling pg_stat_activity with pgcheck

I have added a new option (-psas or pg_stat_activity_sampling) to the pgcheck tool, which samples pg_stat_activity at 1-second intervals over a 1-minute duration. This feature generates reports on Average Active Sessions (AAS), top wait events, and top queries, offering a useful alternative to AWS Performance Insight. While it doesn't provide a long-term history of active sessions, it offers a valuable snapshot for immediate troubleshooting. Here is an example of the output under a pgbench workload:

(venv) someip.myco.com:/u01/app/postgres/pgcheck [etsdb] $ pgcheck.py ~/ini/dbaets.ini -psas
Trying to obtain connection info from the configuation file  /opt/oracle/ini/dbaets.ini ...
wait for 1 min ...
#### pg_stat_activity Sampling start from 2019-08-05 12:25:05.535325 to 2019-08-05 12:26:05.657529
# Average Active Session Report #
Average Active Sessions : 4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# wait event report #
wait_event_type    wait_event     #sessions
-----------------  --------------  ----------
Client             ClientRead     107
Lock               transactionid  78
Lock               tuple          23
None               None           17
IO                 XactSync       15
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Top SQL  report #
query                                                         wait_event     #sessions
---------------------------------------------------           --------------  ----------
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  ClientRead     32
END;                                                          ClientRead     26
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  ClientRead     25
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  ClientRead     24
END;                                                          transactionid  21
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  transactionid  20
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  transactionid  19
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  transactionid  18
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  None           7
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  tuple          7
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  XactSync       7
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  tuple          6
END;                                                          tuple          5
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  tuple          5
UPDATE pgbench_branches SET bbalance = bbalance + 1458 WHERE  None           4
END;                                                          None           4
END;                                                          XactSync       4
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  None           2
UPDATE pgbench_branches SET bbalance = bbalance + 27 WHERE b  XactSync       2
UPDATE pgbench_branches SET bbalance = bbalance + 3758 WHERE  XactSync       2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You can schedule this job to run every minute and output the results to a file if you need to preserve a history of AAS, top wait events, and top SQL queries. 

Implementation Notes 

Previously, this feature required the user to have write privileges because it used temporary tables. I have updated the implementation to eliminate the dependency on temporary tables, instead using a Pandas DataFrame to process the data in memory. This allows the tool to run successfully even on read-only databases. 

Important Considerations 

Please note that because sampling occurs every second, short-lived queries (executing in the millisecond range) may be missed. An Average Active Session count of zero does not necessarily mean no queries were executed during the minute. For a more comprehensive view of executed queries, consider sampling pg_stat_statements using the pgcheck -psss option.

pg_stat_activity sampling is best used to understand the general workload and overall database health.

Summary 

The new -psas option in pgcheck provides a lightweight, real-time snapshot of PostgreSQL activity by sampling pg_stat_activity every second for one minute. This tool helps identify Average Active Sessions and top wait events, serving as a helpful diagnostic resource. By moving to in-memory processing with Pandas, this feature is now compatible with read-only database connections, making it more flexible for troubleshooting performance issues.

SnowSQL config password needs to be enclosed in double quotes

Today I tested how to use snowsql to connect to SnowFlake. I put the connection credential in the config file. But I encountered the following error message in the intial attempt:

(venv) c:\Users\Yu\.snowsql>snowsql

250001 (08001): Failed to connect to DB: abcdefg-hij01234.snowflakecomputing.com:443. Incorrect username or password was specified.

If the error message is unclear, enable logging using -o log_level=DEBUG and see the log to find out the cause. Contact support for further help.

Goodbye!

It turned out I need to enclose the password with double quotes in the config file.

for example, in the c:\Users\Yu\.snowsql\.config file:

accountname =abcdefg-hij01234

username =denissun

# password =Ab0cdE725##FG0 -- not working

password = "Ab0cdE725##FG0"  -- works!

After put double quotes, I tested again and it succeeded:

(venv) c:\Users\Yu\.snowsql>snowsql

* SnowSQL * v1.4.1

Type SQL statements or !help

denissun9883#COMPUTE_WH@(no database).(no schema)>!help

+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+

| Command  | Use                    | Aliases   | Description                                        |

|------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------|

| !abort   | !abort <query id>             |       | Abort a query                                       |

| !connect  | !connect <connection_name>        |       | Create a new connection                                  |

| !define  | !define <variable>=<value>        |       | Define a variable as the given value                            |

| !edit   | !edit <query>               |       | Opens up a text editor. Useful for writing longer queries. Defaults to last query     |

| !exit   | !exit                   | !disconnect | Drop the current connection                                |

| !help   | !help                   | !helps, !h | Show the client help.                                   |

| !options  | !options                 | !opts    | Show all options and their values                             |

| !pause   | !pause                  |       | Pauses running queries.                                  |

| !print   | !print <message>             |       | Print given text                                      |

| !queries  | !queries help, <filter>=<value>, <filter> |       | Lists queries matching the specified filters. Write <!queries> help for a list of filters. |

| !quit   | !quit                   | !q     | Drop all connections and quit SnowSQL                           |

| !rehash  | !rehash                  |       | Refresh autocompletion                                   |

| !result  | !result <query id>            |       | See the result of a query                                 |

| !set    | !set <option>=<value>           |       | Set an option to the given value                              |

| !source  | !source <filename>, <url>         | !load    | Execute given sql file                                   |

| !spool   | !spool <filename>, off          |       | Turn on or off writing results to file                           |

| !system  | !system <system command>         |       | Run a system command in the shell                             |

| !variables | !variables                | !vars    | Show all variables and their values                            |

+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+

It could be due to my password has special characters. 

From Blogger iPhone client

AI Engineer - My Proof-of-Concept RAG Application

A Retrieval-Augmented Generation (RAG) application is an AI framework that enhances Large Language Models (LLMs) by integrating a targeted information retrieval mechanism. This allows LLMs to access and query domain-specific, external data beyond their pre-trained knowledge base, leading to more accurate, up-to-date, and contextually grounded responses. 

To demonstrate this concept, I developed a command-line Python utility named llm_chat.py. The application acts as a specialized AI Assistant for Database Administrators (DBAs). It takes a user’s plain-text query, converts it into a vector embedding, and queries a PostgreSQL database powered by the pgvector extension to pull the top 10 most relevant historical DBA kowledge base event  posts. It then passes both the question and the retrieved contextual data to Gemini to generate two distinct responses: a step-by-step instructional answer and an executive narrative summary.

How the RAG Architecture Works 

Standard Large Language Models generate answers using only the general knowledge present in their training data. This program implements true RAG by breaking the response pipeline into three distinct phases:

1. Retrieval: Instead of querying the model directly, the application first retrieves exact, domain-specific knowledge (matching historical event titles and IDs) from a PostgreSQL vector database using cosine distance searching (<=>).
2. Augmentation: It augments the prompt by inserting those retrieved database logs directly into the CONTEXT block of the system prompt. 
3. Generation: Finally, the LLM generates an answer strictly grounded in your private, real-world DBA data - mitigating generic answers and preventing hallucinations. 

Example Outputs 

Example 1: Software Installation Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to install Oracle 19c software?
Your query: how to install Oracle 19c software?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   3241  score:0.140  title: Install Oracle Database 19c software on Linux 9
event_id:   2757  score:0.266  title: Solaris 11.4 install oracle-database-preinstall-19c 
event_id:   2774  score:0.288  title: New Oracle 19c - Grid Install Error / Remediation  on Redhat Linux 8.4
event_id:   2762  score:0.294  title: Upgrade a 12.2 RAC database to 19.3
event_id:   2765  score:0.315  title: Oracle GI 19.3 on Linux 8 installation issue and resolution
event_id:   2771  score:0.333  title: Cloning An Oracle 19c Oracle Home through gold image
event_id:   2763  score:0.347  title: Oracle Database 19.0.0.0.0 is certified on Linux x86-64 Red Hat Enterprise Linux 8 Update 0+
event_id:   2596  score:0.351  title: oracle 19c multitenant license notes
event_id:   2634  score:0.368  title: check required linux os packages for Oracle software
event_id:   2866  score:0.374  title: clone a 19c database using rman duplicate from active database 


======= How to do things as a DBA =========================================================
Question: how to install Oracle 19c software?
Answer:
1. **OS Prep:** Use `oracle-database-preinstall-19c` package (if available for your OS) to install required packages. Manually install if needed.
2. **Download:** Download Oracle 19c software from Oracle.
3. **Extract:** Unzip the downloaded software.
4. **Run Installer:** Execute `runInstaller` from the extracted directory.
5. **Follow Prompts:** Choose installation options. For RAC, install Grid Infrastructure first.
6. **Root Scripts:** Run scripts as root when prompted.
7. **Verify:** Check logs for errors after installation.

   Consider Gold Image cloning for faster deployments.


======= Your DBA AI is giving summarization ==================================================
Question: how to install Oracle 19c software?
Answer:
DBA tasks involve installing Oracle 19c on Linux/Solaris, addressing installation errors, upgrading RAC databases, cloning Oracle homes/databases, verifying OS package requirements, and working with multitenant licenses.

Example 2: Performance Tuning Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to add hint to tune sql?
Your query: how to add hint to tune sql?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   1775  score:0.286  title: postgresql pg_hint_plan -- controls execution plan with hinting phrases in comment of special form
event_id:   3183  score:0.287  title: General advice for developer to tune sql
event_id:    216  score:0.315  title: Suggest tuning a query by adding hint to skip a particular index - NO_INDEX(smc SMC_IDX_RELEASE_DATE)
event_id:   3290  score:0.367  title: mtasCronOptPurge_request.sh - tuning a delete statement adding hints
event_id:   1908  score:0.369  title: STRAIGHT_JOIN sql tuning
event_id:   3028  score:0.371  title: Run SQL tuning advisor for 1r8xa9fumhq2d
event_id:   2093  score:0.377  title: My SQL Tuning Primer
event_id:    248  score:0.392  title: about query 4xx9vuzyj1m71 - RE Dev SQL tuning opportunity- RE Introscope Alert Message
event_id:    245  score:0.394  title: SQL -2rg6vs7y2kr64 - takes long time - fixed by restoring old stats to generate a better [execution plan] [sql tuning]
event_id:   3303  score:0.396  title: spfprdsc - 11trc93bakbv0 sql tuning


======= How to do things as a DBA =========================================================
Question: how to add hint to tune sql?
Answer:
To tune SQL using hints (like `NO_INDEX` or `STRAIGHT_JOIN`), embed them within a special comment block in your SQL:

/*+ NO_INDEX(table_alias index_name) */

For example:

SELECT /*+ NO_INDEX(smc SMC_IDX_RELEASE_DATE) */ * FROM smc WHERE ...

This tells the optimizer to skip `SMC_IDX_RELEASE_DATE` on table `smc`. Remember to test thoroughly! Consider also using SQL Tuning Advisor for systematic optimization.


======= Your DBA AI is giving summarization ==================================================
Question: how to add hint to tune sql?
Answer:
The DBA tuned SQL queries using hints within comments. Examples include skipping indexes (NO_INDEX) and forcing join order (STRAIGHT_JOIN). The DBA also used SQL Tuning Advisor and restored old statistics to improve execution plans. Several specific queries were tuned.

Prompt Engineering & Future Enhancements 

Notice how applying two different prompt templates to the exact same retrieved context yields dramatically different outputs tailored to specific roles: 

Instructional Prompt (Prompt 1):

Designed to act as an expert senior mentor explaining practical, step-by-step procedures to junior DBAs.

prompt = f"""
INSTRUCTIONS:
You are an expert Oracle database administrator, based on the context provided, you can explain how to do things to any junior to mid-level DBAs.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Summarization Prompt (Prompt 2): 

Designed to synthesize historical enterprise activities into a high-level operational narrative.

prompt = f"""
INSTRUCTIONS:
Based on the context provided, you can summarize and give a narrative about what are the tasks or activities DBA performed.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Currently, this proof of concept extracts embeddings exclusively from Event Titles in the DBAETS knowledge base. In future iterations, I plan to chunk and generate vector embeddings from both the Event Titles and full Event Descriptions/Body Content. Combining title metadata with rich document content will significantly increase context granularity, enabling Gemini to produce even deeper, more accurate diagnostic guidance. 

Summary 

This proof of concept demonstrates how easily a local enterprise dataset - such as DBAETS event posts - can be transformed into an intelligent operational assistant using RAG architecture. By pairing vector similarity search in PostgreSQL (pgvector) with the reasoning capabilities of Gemini, llm_chat.py bridges the gap between static enterprise documentation and interactive, multi-purpose guidance. Whether generating actionable technical steps for junior staff or summarizing complex administrative histories for management, RAG transforms raw relational data into an indispensable knowledge retrieval tool.

AI Engineer - Embedding Generation from Gemini API and Storing in PostgreSQL

In this post, I first demonstrate that sample Python programs have been developed and tested to do the following two tasks: 

1. Create embeddings from sample source data using the Germini API 

2. Use PostgreSQL as a vector database and store embeddings data in it using pgvector. 

Then I demonstrate similarity search in PostgreSQL with pgvector. 

1. Source data preparation: 

 From the DBAETS application `events` table in the PostgreSQL, dumping ` event_id` and `title` data into a csv file. Total 2021 records.

 \copy ( select event_id, title from events order by 1 ) to 'events_title.csv' with csv 

 The `events` table stores data about database administration events, each entry represents a certain kind of DBA task or activity. The `title` of the `event` table is trivial, but the purpose of using it as the source of content is to make me familiar with the embedding creation and storing process. The end goal is to use the `description` column of the `events` table, which contains a detailed description of the event, therefore providing the useful domain knowledge for DBAs.

 2. Embedding generation 

The python program emb_to_csv.py is developed to generate embedding from source csv file and generate a csv file including embeddings of `title`. Gemini API is used with model: ‘text-embedding-004’, which generates vectors with 768 dimensions .

==============  screenshot run the program ==============
(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python emb_to_csv_2.py
   event_id                                              title
0         3  PREP CALL for the 2pm actual call to discuss r...
1         4                   Query Certification / 04-11-2012
2         5        [DemoApp] [Migration] -  RD175935 - Tonight
3         6  RE: myhostpd3:testprod3 ALERT_LOG Error: [SR 3-...
4         7                    space add in mrosple/mxdsacdd02
total batches : 22 batch size: 100 total records: 2120
process batch no: 0
loop: 0 event_id: 3
loop: 10 event_id: 13
loop: 20 event_id: 24
loop: 30 event_id: 34
loop: 40 event_id: 44
loop: 50 event_id: 54
loop: 60 event_id: 64
loop: 70 event_id: 74
loop: 80 event_id: 84
loop: 90 event_id: 94
process batch no: 1
loop: 100 event_id: 104
loop: 110 event_id: 114
loop: 120 event_id: 124
loop: 130 event_id: 134
loop: 140 event_id: 144
loop: 150 event_id: 155
loop: 160 event_id: 165
loop: 170 event_id: 175
loop: 180 event_id: 185
loop: 190 event_id: 195
process batch no: 2
loop: 200 event_id: 205
…
(ommitting ..)

I intentionally used some small batch size and sleep 200 seconds between batches to avoid Gemini API free tier rate limit, if I don't do that, I could end up with errors "RATE_LIMIT_EXCEEDED". 

3. Embedding loacding 

The python program emb_csv_to_pgvector.py is developed to load the embeddings from csv to a PostgreSQL table. 

4. Similarity search using SQL 

 The following query calculates vector distances using the <#> negative inner product operator to compare event titles against a target event (event_id = 2690). It retrieves the target event's self-similarity score alongside the top 10 most similar distinct events, returning their event IDs, titles, and similarity scores in a single combined result.

etsdb=> -- top event titles that are mostly similar to first title
etsdb=> (
etsdb(> select event_id, title,  embedding <#> embedding as similarity
etsdb(> from event_title_embeddings
etsdb(> where event_id=2690
etsdb(> )
etsdb-> union all
etsdb-> (
etsdb(> select t.event_id , t.title , (t.embedding <#> i.embedding ) as similarity
etsdb(> from event_title_embeddings t join event_title_embeddings i
etsdb(> on t.event_id != i.event_id
etsdb(> and i.event_id=2690
etsdb(> order by similarity
etsdb(> limit 10
etsdb(> )
etsdb-> ;
 event_id |                              title                               |     similarity
----------+------------------------------------------------------------------+---------------------
     2690 | Master note for operation support - mydb2                        | -0.9999982118606567
     2431 | Master note for operation support - mydb1 BVC                    | -0.8375328183174133
     2629 | Master note for operation support - omrtppdb OEM repostory       | -0.8193813562393188
     2444 | Master note for operation support - iexdb                        | -0.7917341589927673
     2450 | mydb4 database support master notes                              | -0.7660208344459534
     2459 | master note for operation support - mydb12sc APP1                | -0.766010582447052
     2876 | Operations Master notes for EV6V- mmypos on dbhost41/42/43       | -0.6405361294746399
     2953 | Master Notes -APP2                                               | -0.6208126544952393
     2937 | SCM master notes                                                 | -0.6061583161354065
     2275 | mydb1 database related doc                                       | -0.6051017045974731
     2879 | Master Notes - APP1 - mydb3                                      | -0.6019692420959473
(11 rows)

In summary, I successfully established an end-to-end embedding pipeline using the Gemini text-embedding-004 model and PostgreSQL with pgvector. By generating 768-dimensional vector embeddings from DBA event titles and querying them using inner product distance (<#>), the setup demonstrated accurate semantic similarity retrieval across database task records. This foundational work sets the stage for scaling up to full event descriptions and implementing a robust domain-specific vector search system.

AI Engineer - Starting My Journey

Recently, I have come across a youtube video that is quite inspiring. It explains what AI Engineers do and how to develop AI applications in PostgreSQL. It made me believe I can develop something useful as if I am an AI Engineer! At least I know Python and PostgreSQL reasonably well.

One of the functionalities of the DBAETS application I developed is it is a knowledge base for DBAs. From time to time, I post notes there about database related activities,  such as troubleshooting, performance tuning and so on and so forth. My ambition is to develop a Q&A RAG application for DBAs to ask questions and get answers utilizing the knowledge-base data in the DBAETS.

What is RAG?

"In the field of AI, RAG stands for Retrieval-Augmented Generation. It's a framework that combines the strengths of traditional information retrieval systems with generative large language models (LLMs). RAG enhances AI responses by retrieving relevant information from external sources, such as databases or web pages, and using that information to augment the LLM's generation process. This approach helps create more accurate, up-to-date, and relevant text outputs. "

Let the journey begin! And I will post any meaningful progress along the way.

As of today, I have achieved three things as described in the following:

•  I turned my PostgreSQL db as a vector database by installing the pgvector extension

– installation of pgvector

pgdbhost003.mycompany.com, as root

cd /u01/stage

git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git

cd pgvector

make

make install 

– enable the extension

postgres=# \c etsdb

You are now connected to database "etsdb" as user "postgres".

etsdb=# CREATE EXTENSION vector;

CREATE EXTENSION

etsdb=#

– create a table with `vector` data type

etsdb=# create table items(id bigserial primary key, embedding vector(3));

CREATE TABLE

– insert 

etsdb=# INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

INSERT 0 2

– query

etsdb=# SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

 id | embedding

----+-----------

  1 | [1,2,3]

  2 | [4,5,6]

As you can see I have successfully turned my PostgreSQL database into a vector store.

• I am able to call Gemini API

In the following example script, I ask a LLM model what an AI Engineer do through an API

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>type test_googleapi.py
# ref : https://github.com/google-gemini/cookbook
# ref : https://github.com/googleapis/python-genai
#
# RAG:
#
# https://medium.com/@saurabhgssingh/understanding-rag-building-a-rag-system-from-scratch-with-gemini-api-b11ad9fc1bf7
#
#
# type of services
#   ** Gemini Developer API
#   ** Vertex AI
# pip install google-genai
#
import os
from google import genai
from google.genai import types
client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))
response = client.models.generate_content(
    model="gemini-2.0-flash-001",
    contents="Explain what an AI Enigeer do?",
)
print(response.text)

Run the program:

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>python test_googleapi.py

An AI Engineer is a professional who focuses on **applying artificial intelligence (AI) and machine learning (ML) models to solve real-world problems.** They bridge the gap between theoretical AI research and practical implementation. Think of them as the builders and architects of AI systems.

Here's a breakdown of what an AI Engineer typically does:

**1. Data Handling and Preparation:**

*   **Data Collection:** Gathering data from various sources, including databases, APIs, and other data stores.

*   **Data Cleaning and Preprocessing:**  Cleaning messy data (handling missing values, outliers, and inconsistencies) to make it suitable for training ML models. This is a crucial step as the quality of the data directly impacts the model's performance.

*   **Data Transformation:** Converting data into a format that can be easily processed by ML algorithms. This might involve feature scaling, encoding categorical variables, and creating new features.

*   **Data Augmentation:** Generating synthetic data to increase the size and diversity of the training dataset, which can improve model robustness.

**2. Model Development and Training:**

*   **Model Selection:** Choosing the appropriate AI/ML model based on the problem, data characteristics, and desired outcome (e.g., regression, classification, clustering, or a specific deep learning architecture).

*   **Model Training:**  Using the prepared data to train the selected model. This involves tuning the model's parameters to optimize its performance.

*   **Model Evaluation:**  Assessing the trained model's performance using various metrics and techniques (e.g., accuracy, precision, recall, F1-score, ROC curves).

*   **Hyperparameter Tuning:** Optimizing the model's hyperparameters (parameters that are not learned during training) to improve its performance. This often involves using techniques like grid search or Bayesian optimization.

**3. Model Deployment and Maintenance:**

*   **Model Deployment:**  Putting the trained model into a production environment where it can be used to make predictions on new data. This could involve deploying the model to a cloud platform, a server, or an embedded device.

*   **Model Monitoring:** Tracking the model's performance in production to ensure it continues to perform as expected.

*   **Model Retraining:**  Retraining the model with new data to keep it up-to-date and prevent performance degradation (model drift).

*   **Infrastructure Management:** Managing the infrastructure required to run the AI system, including servers, databases, and cloud resources.

**4. Collaboration and Communication:**

*   **Collaboration:** Working closely with data scientists, software engineers, and other stakeholders to build and deploy AI solutions.

*   **Communication:**  Effectively communicating technical concepts and findings to both technical and non-technical audiences.

*   **Documentation:**  Creating and maintaining documentation for the AI system, including the model architecture, training process, and deployment instructions.

**Key Skills for AI Engineers:**

*   **Programming:** Strong proficiency in programming languages like Python (most common), Java, or C++.

*   **Machine Learning:**  A solid understanding of machine learning algorithms and techniques.

*   **Deep Learning:**  Experience with deep learning frameworks like TensorFlow, PyTorch, or Keras.

*   **Data Science Fundamentals:** Knowledge of data analysis, data visualization, and statistical modeling.

*   **Data Engineering:**  Skills in data wrangling, data cleaning, and data pipelining.

*   **Cloud Computing:**  Experience with cloud platforms like AWS, Azure, or Google Cloud.

*   **DevOps:** Familiarity with DevOps practices like continuous integration and continuous delivery (CI/CD).

*   **Software Engineering:**  Understanding of software development principles, design patterns, and testing methodologies.

*   **Problem-Solving:**  Strong analytical and problem-solving skills.

*   **Communication:**  Excellent communication and collaboration skills.

**In summary, an AI Engineer is a hands-on role that requires a blend of programming, machine learning, and software engineering skills. They are responsible for building, deploying, and maintaining AI systems that solve real-world problems.** They are not just theorists; they are builders who bring AI models to life.

•  I can generate embeddings:

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>type gen_emb.py

from google import genai

import os

client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))

result = client.models.embed_content(

        model="gemini-embedding-exp-03-07",

        contents="What is the meaning of life?")

print(result.embeddings)

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>python gen_emb.py

[ContentEmbedding(values=[-0.022372285, -0.004451784, 0.013473644, -0.053762246, -0.020569915, 0.011864573, 0.015185799, 0.006950965, 0.03180835, 0.007074574, 0.027503368, -0.00600613, -0.014889315, 0.03269886, 0.12054204, 0.019322146, 0.000517173, 0.0045754807, -0.00856155, -0.01532448, 0.015616342, -0.008661197, -0.017454486, 0.0099245, -0.015551475, 0.012284064, 0.020809751, -0.0037114064, 0.025106275, 0.008105811, 0.020252233, 0.0019548477, -0.010780675, 0.027334962, -0.017213175, -0.011735542, 0.009507163, -0.015499457, -0.013591795, 0.0138707, -0.022853972, -0.009638755, -0.0034423112, -0.018855078, 0.018475225, -0.010515843, 0.015031793, -0.042978574, -0.013993226, 0.007916359, -0.012274015, 0.011758872, -0.010251529, -0.15881006, 0.016281825, 0.0103672175, -0.006364179, -0.009997806, -0.025991082, -0.027687864, -0.008586312, -0.014933809, -0.007584574, -0.021427568, 0.008805106, -0.009369353, -0.02012641, 0.011695516, 0.0020037016, 0.012606018, -0.01629937, 0.015133599, -0.005364407, -0.013935832, 0.0065723164, 0.014414399, 0.016648233, -0.009122886, 0.0006896179, -0.0042646583, 0.0001541545, -0.01034019, -0.025387881, -0.01852225, -0.005335359, 0.013288918, -0.008168338, -0.005782173, 0.017077502, -0.0015941358, 0.027414756, 0.00015515718, 0.019269329, 0.026197737, -0.015433854, -0.016303977, -0.0048079626, -0.013850489, 0.018751703, 0.0037166988, -0.029702129, -0.018824589, 0.0037240938, -0.00461113, 0.0010804693, 0.0029576228, 0.020787966, 0.014922842, 0.010893234, -0.00839888, 0.016467841, -0.011249123, -0.014664661, -9.935649e-06, -0.016037786, -0.17255014, 0.011858983, -0.0148698045, -0.019271476, 0.02731455, 0.029812986, 0.0046130605, -0.012905329, 0.010795695, -0.0144493915, 0.0009820901, 0.02241333 .....  ]

In summary, I have successfully enabled vector capabilities in a PostgreSQL database and demonstrated the ability to call the Gemini API. This blog post serves as a starting point of my exploration toward developing practical AI solutions.

High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

Building a Lightweight, In-Database Benchmarking Framework with DBMS_SQL and DBMS_SCHEDULER

Simulating high-concurrency database workloads often relies on external testing tools such as JMeter, HammerDB, or custom application drivers. While effective, external tools introduce network latency variations, connection pool overhead, and complex infrastructure setup.

When your objective is to evaluate pure database execution performance—such as engine efficiency, latch contention, or indexing strategies under heavy stress—executing the load directly inside the database engine provides the cleanest, most repeatable results.

This post details a pure PL/SQL methodology for conducting high-concurrency database load testing in Oracle using built-in packages.

Architectural Overview of the Methodology

The native load-testing framework consists of three core components:

1. The Workload Engine (DBMS_SQL): Executes targeted SQL statements inside a high-throughput loop, leveraging dynamic cursor parsing, variable binding, and randomized test data to prevent soft/hard parsing bottlenecks.
2. The Concurrency Orchestrator (DBMS_SCHEDULER): Spawns and manages asynchronous background worker threads, allowing effortless scaling across multiple CPU cores.
3. The Observability Engine (job_logs): Captures real-time metrics including execution start times, completion times, and total iterations per job thread.

Step 1: High-Efficiency Query Execution with DBMS_SQL

To measure true execution efficiency without incurring the overhead of PL/SQL static SQL context switching or hard parsing, we utilize the DBMS_SQL package.

By opening a dynamic cursor once, parsing the statement skeleton, and binding randomized search criteria during loop iterations, we isolate execution performance while mimicking realistic application behavior.

create or replace procedure run_sql_proc(p_iteration number, p_desc varchar2 default '') 

is 

  curid number; 

  sqltext varchar2(4000); 

  ret number; 

  l_counter number := 0; 

  l_mtn test.leadlist.mtn%type; 

  l_jobid number; 

begin

   -- Log execution start and obtain tracking ID

   insert into job_logs(job_id, executions, stime) 

   values (job_logs_seq.nextval, p_iteration, sysdate) 

   returning job_id into l_jobid;

   

   -- Define parameterized SQL statement

   sqltext := 'select /*test1*/ * from test.leadlist where mtn = :mtn';

   curid   := DBMS_SQL.OPEN_CURSOR; 

   

   loop 

       if l_counter > p_iteration then 

          exit; 

       end if;

       

       -- Generate random bind values to simulate realistic access patterns

       l_mtn := 'mtn' || to_char(round(10000 * dbms_random.value())); 

|---|

       DBMS_SQL.PARSE(curid, sqltext, DBMS_SQL.NATIVE); 

       DBMS_SQL.BIND_VARIABLE(curid, 'mtn', l_mtn); 

       ret := DBMS_SQL.EXECUTE_and_fetch(curid); 

       

       l_counter := l_counter + 1; 

  end loop; 

 

  dbms_sql.close_cursor(curid); 

 

  -- Log execution completion

  update job_logs 

  set etime = sysdate, description = 'Done - ' || p_desc 

  where job_id = l_jobid; 

end; 

/

Key Technical Considerations in run_sql_proc:

• Bind Variable Injection (:mtn): Prevents library cache lock contention by avoiding unique unparameterized SQL strings.
• Random Data Generation (DBMS_RANDOM): Simulates dynamic multi-user key lookups across the target table dataset.
• Single-Fetch Optimization (EXECUTE_and_fetch): Executes and fetches index lookup results in a single call to minimize overhead.

Step 2: Programmatic Concurrency Control via DBMS_SCHEDULER

To simulate multi-user workload patterns without maintaining open client terminal sessions, we leverage DBMS_SCHEDULER. The following submission script accepts a substitution variable (&&p_thread) to instantiate an exact number of parallel background jobs.

declare 

  l_counter number := 0; 

  l_msg varchar2(200); 

  l_job_action varchar2(400); 

begin 

  loop 

     l_counter := l_counter + 1; 

     if l_counter > &&p_thread then 

        exit; 

     end if;

     l_msg := 'local2-thread-' || &&p_thread || '_' || to_char(l_counter); 

     l_job_action := 'begin run_sql_proc(1000000,''' || l_msg || ''' ); commit; end;';

     dbms_scheduler.create_job(

         job_name   => 'RUN_SQL_JOB_' || to_char(l_counter), 

         job_type   => 'PLSQL_BLOCK', 

         job_action => l_job_action, 

         enabled    => true, 

         auto_drop  => true, 

         comments   => 'run_sql_job'

     );

     

     -- Optionally pin job threads to specific RAC instances

     dbms_scheduler.set_attribute('RUN_SQL_JOB_' || to_char(l_counter), 'INSTANCE_ID', '1');

  end loop; 

end; 

/

undefine p_thread

Step 3: Parametric Scalability Testing Execution Model

To evaluate database scalability under stress, tests should be executed in geometric thread increments: 1, 2, 4, 8, 16, 32, 64, and 128 threads.

Each thread runs a designated workload loop (e.g., 1,000,000 iterations per job). By adjusting &&p_thread, performance engineers can pinpoint exact saturation thresholds, latch bottlenecks, and throughput limits.

Test Environment Workflow:

1. Truncate or initialize the telemetry logging table (job_logs).
2. Execute the scheduler submission script with p_thread = 1.
3. Wait for job completion and collect timing metrics.
4. Repeat for higher thread counts (2, 4, 8, 16, 32, 64, 128).

Step 4: Tracking Metrics with Custom Instrumentation

A key requirement for database benchmarking is accurate timing. By capturing execution timestamps directly inside the procedure surrounding the workload loop, network latency and client-side logging delays are completely eliminated.

Performance Metrics Captured

• Average Job Completion Time (Minutes): Indicates how scaling concurrency impacts single-thread latency.
• Queries Per Second (QPS): Total aggregate database throughput across all active concurrent worker threads.

$$QPS = \frac{\text{Total Iterations Across All Threads}}{\text{Max Elapsed Duration (Seconds)}}$$

---

Secondary Case Study: Evaluating Partitioned Index Performance

To demonstrate this methodology in practice, the framework was deployed on an Oracle test database   to benchmark local versus global partitioned index access paths.

Test Dataset Setup

• Table: TEST.LEADLIST (Daily interval-partitioned table)
• Volume: 140,000 rows across 1,682 partitions
• Access Pattern: Index lookup by MTN key (select * from test.leadlist where mtn = :mtn)

 CREATE TABLE "TEST"."LEADLIST"

   (    "LEADLISTID" VARCHAR2(20),

        "MTN" VARCHAR2(10),

        "CREATEDATE" DATE DEFAULT sysdate,

        "LASTMODFIEDUSER" VARCHAR2(20)

   ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

  PARTITION BY RANGE ("CREATEDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))

 (PARTITION "PART_MIN"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ) ;

insert into test.leadlist

(leadlistid, mtn, createdate,lastmodfieduser)

select

'aa' || to_char(level) ,

'mtn' ||  to_char(round(10000 * dbms_random.value())),

sysdate - 1682 * dbms_random.value(),

'user123'

from dual

connect by level <=140000

;

create index test.leadlist_ix1 on test.leadlist(mtn) local parallel 8;

alter index test.leadlist_ix1 noparallel;

create index test.leadlist_ix1 on test.leadlist(mtn) parallel 4;

alter index test.leadlist_ix1 noparallel;

Test Results Data

 Fig. 1 Average Job completion time vs Number  of Threads

Fig. 2 QPS vs Number  of Threads

Concurrent Threads	Global Index Avg Time (min)	Local Index Avg Time (min)	Global Index Total QPS	Local Index Total QPS
1 Thread	1.3	6.9	~15,000	~3,000
8 Threads	1.3	6.7	~100,000	~20,000
32 Threads	1.8	7.2	~290,000	~75,000
64 Threads	2.7	7.7	~402,000 (Peak)	~138,000
128 Threads	5.4	10.0	~396,000	~212,000

Benchmark Results Analysis & Observations

1. Completion Time Scaling: At 64 concurrent threads, jobs running against the Global Index completed in an average of 2.7 minutes, compared to 7.7 minutes for jobs using the Local Index.
2. Throughput Saturation: The Global Index configuration reached peak performance at 64 concurrent threads (~402,000 QPS). Beyond 64 threads, throughput leveled off due to CPU resource saturation.
3. Index Access Efficiency: Because the query predicate did not include the partition key (CREATEDATE), the Local Index required checking 1,682 individual index partitions per lookup, causing higher logical read overhead compared to a single B-tree lookup in the Global Index.

Summary

By combining DBMS_SQL for efficient dynamic SQL execution and DBMS_SCHEDULER for concurrent background process orchestration, database engineers can build a powerful, zero-dependency load testing framework directly inside Oracle. This native methodology provides precise, isolated performance insights free from network noise or third-party tool overhead.