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:
- 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 (<=>).
- Augmentation: It augments the prompt by inserting those retrieved database logs directly into the CONTEXT block of the system prompt.
- 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:
"""
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.
