Oracle’s database instrumentation generates a huge amount of performance telemetry and AWR reports can still feel like a wall of text when you just want clear, targeted answers. In this post, I show how I turned a 24‑hour AWR report into an interactive performance tuning assistant using a local LLM (via Ollama on Windows 11) and Oracle AI Database 26ai running inside a VirtualBox VM.

This experiment was inspired by Lorenzo De Marchis’ “From theory to practice: building a RAG pipeline with LangChain and Oracle AI Database 26ai in Python,” where Oracle AI Database 26ai acts as the vector store behind a RAG pipeline. I kept the same core idea—Oracle as the vector database, Python as the glue—and code but adapted it to a hybrid lab setup with Ollama, VirtualBox, and an AWR‑specific use case. But also inspired by several customers that during last year’s Oracle AI World mentioned they are uploading AWR reports to online AI LLM’s to ask questions about them.

Rather than manually scrolling through an AWR HTML report, I can now ask questions like “Summarize the top performance issues in this 24‑hour period” and let the assistant answer using only the information embedded from that report.

Architecture overview

At a high level, the architecture combines a Windows host, a VirtualBox VM, and Oracle 26ai’s native vector search.

• Windows 11 gaming PC
  Runs Ollama as the local inference server. I chose this PC because I have an NVIDIA RTX3060 GPU and may retrieve results faster.
• VirtualBox VM running Oracle Linux 9
  Hosts Oracle AI Database 26ai, which stores the AWR report, embedding vectors, and metadata in regular tables plus a vector index and hosts the Python application that orchestrates retrieval and generation.
• Python RAG layer
  Connects to Oracle AI Database 26ai via the Python LangChain driver, performs similarity search using SQL over the vector column, and calls Ollama’s HTTP API to generate grounded answers.

Conceptually, the design mirrors Lorenzo’s rag.py module: the database is responsible for similarity search, and the language model is responsible for natural‑language generation. This keeps data, embeddings, and retrieval inside Oracle, while the LLM remains fully local on your own hardware.

This is very important if you want to prevent your data from leaving your premises or avoid token fees.

Why AWR plus a local RAG assistant?

AWR reports are extremely rich: they summarize instance statistics, wait events, time model data, and “Top SQL” sections between two snapshot IDs. They are necessary for serious performance diagnostics, but they are not always friendly to read under time pressure.

A local RAG assistant addresses three pain points:

• Navigating complexity: Instead of remembering exactly which section covers which metric, you can ask natural questions such as “Which wait events dominate DB time?” or “Do we have CPU or I/O bottlenecks?” and let retrieval pick the right section.
• Keeping data on‑premises: AWR content is operationally sensitive. By running Oracle AI Database 26ai in a VirtualBox VM and Ollama on a local PC, you keep all data and prompts within your controlled environment.
• Experimenting with models: Ollama makes it easy to try different LLMs (Llama 3, Mistral, etc.) against the same AWR corpus by changing a model name, without rebuilding your pipeline.

The result is not meant to replace expert tuning but to act as a fast‑feedback assistant that helps you spot patterns and narrow down questions more quickly.​

Preparing the AWR data

Generating a 24‑hour AWR report

First, I collected the baseline AWR report from the test database. Oracle documents the standard scripts for generating AWR reports, such as awrgrpt.sql and awrrpt.sql, which can output HTML or text. However, I’m using the DBMS_WORKLOAD_REPOSITORY package to generate the report in text format.

From SQL*Plus on the test database:

SQL> SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_text(db_id, inst_id, start_snap, end_snap));

This produces a text output that I can easily spool into a file like awr_123_145.txt that contains all the usual sections (Instance Efficiency, Load Profile, Top Timed Events, SQL ordered by Elapsed Time, and so on).

I then copy this TXT file into a working directory accessible from the machine where I run the Python ingestion script (in my case, the Linux host where I also run Oracle and the Python RAG code).

Ingesting and chunking the AWR report with Python and LangChain

Next, we use Lorenzo’s Python ingest.py to:

1. Parse the AWR text file.
2. Split it into meaningful chunks.
3. Generate embeddings for each chunk.
4. Insert the chunks and embeddings into the Oracle AI Database using the Oracle Python driver.

I also needed to modify the ingest.py file because Ollama LLM is hosted in a deifferent machine. So, I installed Ollama locally in the Linux host and then modified the configuration setting the OLLAMA_HOST variable in order to connect to my AIServer hosts (Windows 11).

OLLAMA_HOST=http://AIServer:11434

In a production‑quality version, you would parse the report header to extract snapshot timestamps and normalize them into TIMESTAMP columns. You might also refine the chunking strategy to align with specific AWR sections such as “SQL ordered by Elapsed Time” or “Top Timed Events” for more precise retrieval.

Building the retrieval query in Oracle AI Database 26ai

At query time, I use Lorenzo’s rag.py script. I just pass the prompt and receive the output from the LLM using RAG augmentation from the Oracle database.

$ python3.11 -m app.rag "Summarize the main performance issues highlighted in this AWR"

Example questions for the AWR assistant

Once the pipeline is in place, you can treat the AWR report as a conversational knowledge base. Here are some practical queries that work well:

• “Summarize the main performance issues highlighted in this AWR.”
• “Which wait events contributed most to DB time, and during which hours were they highest?”
• “List the top 5 SQL statements by elapsed time and explain in plain language what they are doing.”
• “Does the report suggest CPU bottlenecks, I/O bottlenecks, or something else?”
• “Based on this report, what are three concrete tuning actions I should consider first?”

A typical interaction might look like this (paraphrased):

• Question: “What are the top three tuning actions suggested by this AWR?”
• Assistant: “The top three tuning actions suggested by this AWR are:
• Investigate the cause for high “Disk file I/O Calibration” waits.
• Investigate the cause for high “Disk file I/O Calibration” waits in Module “DBMS_SCHEDULER”.
• Investigate the cause for high “Disk file I/O Calibration” waits in Service “SYS$USERS”.”​

Because the assistant is restricted to the retrieved chunks, its answers remain grounded in actual AWR sections rather than hallucinated generic advice.

Here’s the final prompt after tuning it several times.

You are an expert Oracle Database Administrator with deep expertise in performance analysis and AWR report interpretation. Your task is to thoroughly analyze the provided AWR report and deliver a comprehensive performance analysis.

ANALYSIS REQUIREMENTS:

1. TOP EVENTS IDENTIFICATION:

• Extract and rank the top 10 wait events from the AWR report
• Identify the top 5 SQL statements by elapsed time and CPU time
• Highlight any significant changes in system metrics compared to previous snapshots

1. ROOT CAUSE ANALYSIS:

• For each top event, explain:
  • What type of wait event it is (CPU, I/O, Latch, Lock, etc.)
  • Why it’s occurring (resource contention, inefficient queries, configuration issues)
• The impact on database performance
• Analyze the correlation between events and SQL statements

1. DETAILED RECOMMENDATIONS:

• Provide specific, actionable steps to resolve each issue
• Include SQL optimization suggestions for top SQL statements
• Recommend database configuration changes if needed
• Suggest hardware or infrastructure improvements if applicable
• Include monitoring recommendations to prevent recurrence

1. REPORT STRUCTURE:

• Executive summary of key findings
• Detailed event analysis with root cause explanation
• Prioritized remediation steps
• Performance improvement estimates
• Follow-up actions and monitoring requirements

OUTPUT FORMAT:

• Clear, technical language appropriate for DBA audience
• Include specific Oracle commands where applicable
• Provide justification for each recommendation
• Highlight critical issues requiring immediate attention

INPUT: AWR data is already ingested. Use RAG.

Provide a comprehensive analysis that enables the DBA to quickly identify, understand, and resolve performance bottlenecks.

**Performance Analysis Report**

**Top Events Identification**

1. Top 10 wait events:
        * db file sequential read (14.34%)
        * db file scattered read (12.55%)
        * latch: cache buffers chains (8.42%)
        * latch: enqueue (5.67%)
        * CPU time (4.22%)
        * latch: shared pool (3.91%)
        * wait on library cache (3.56%)
        * enq: TX - row lock contention (2.95%)
        * latch: redo allocation (2.73%)
        * log file sync (2.46%)
2. Top 5 SQL statements by elapsed time:
        * SQL_ID "r6swge3rhwsa3" (96% of database time)
        * SQL_ID "ws4dr3ag24rfg" (100% of database time)
        * SQL_ID "5tm1ruvkx3wsx" (4% of database time)
        * SQL_ID "tf4dus7dsfgh" (2% of database time)
        * SQL_ID "gv3dsh4jklmn" (1% of database time)
3. Significant changes in system metrics:
        * Total database time was 7 seconds.
        * Average number of active sessions was 0.

**Root Cause Analysis**

1. db file sequential read (14.34%):
        * This wait event indicates that the database is spending a significant amount of time waiting for sequential reads from disk.
        * Possible causes:
                + Inefficient queries or indexes.
                + Insufficient disk space or slow disk performance.
                + Configuration issues (e.g., inadequate block size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
2. db file scattered read (12.55%):
        * This wait event indicates that the database is spending a significant amount of time waiting for scattered reads from disk.
        * Possible causes:
                + Inefficient queries or indexes.
                + Insufficient disk space or slow disk performance.
                + Configuration issues (e.g., inadequate block size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
3. latch: cache buffers chains (8.42%):
        * This wait event indicates that the database is spending a significant amount of time waiting for latches on the cache buffers chains.
        * Possible causes:
                + Insufficient cache size or inadequate memory allocation.
                + Inefficient queries or indexes.
                + Configuration issues (e.g., inadequate latch size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.
4. latch: enqueue (5.67%):
        * This wait event indicates that the database is spending a significant amount of time waiting for latches on the enqueue structure.
        * Possible causes:
                + Insufficient enqueue size or inadequate memory allocation.
                + Inefficient queries or indexes.
                + Configuration issues (e.g., inadequate latch size).
        * Impact on performance: High wait times can lead to increased response times and decreased throughput.

**Detailed Recommendations**

1. Investigate the PL/SQL statement with SQL_ID "r6swge3rhwsa3" for possible performance improvements.
        * Supplement the information given here with an ASH report for this SQL_ID.
2. Optimize the queries and indexes related to the SQL statements with SQL_ID "r6swge3rhwsa3" and "ws4dr3ag24rfg".
        * Use the SQL Tuning Advisor to identify and optimize these queries.
3. Review and adjust the database configuration to ensure adequate cache size, memory allocation, and latch size.
4. Consider adding more disk space or upgrading the disk performance to reduce wait times for sequential and scattered reads.
5. Review and adjust the database configuration to ensure adequate enqueue size and memory allocation.

**Monitoring Recommendations**

1. Monitor the wait times and latch waits for the identified SQL statements and queries.
2. Monitor the cache size, memory allocation, and latch size to ensure they are adequate.
3. Monitor the disk space and performance to ensure they are sufficient.

**Performance Improvement Estimates**

1. Estimated benefit of optimizing the PL/SQL statement with SQL_ID "r6swge3rhwsa3" is 0.02 active sessions and 15.56% of total activity.

**Follow-up Actions and Monitoring Requirements**

1. Schedule regular monitoring and analysis of the identified SQL statements and queries.
2. Review and adjust the database configuration as needed to ensure adequate resources.
3. Consider adding more disk space or upgrading the disk performance to reduce wait times for sequential and scattered reads.

Note: This report is a summary of the top events and recommendations. For a detailed analysis, please refer to the original AWR report and ADDM report.

Thanks,
Alfredo