Oracle Automatic Workload Repository (AWR) Analysis with AI

Introduction

For many, reading an Oracle Automatic Workload Repository (AWR) report is a daunting task, a seeming combination of alchemy and magic.

But what if AI could help us analyse database and storage performance, identify bottlenecks and obtain actionable recommendations ?

Create AWR Report

The first step is to create or obtain an Oracle AWR report, for example:

At the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify HTML format

Enter value for report_type: html

Specify number of days

Enter value for num_days: 1

Specify a beginning and ending snapshot ID for the AWR report:

Enter value for begin_snap: 5863
Enter value for end_snap: 5864

Enter a report name, or accept the default report name:

Enter value for report_name:
Using the report name awrrpt_1_5863_5864.html

AI Prompt

We now need an AI prompt, the below is an example I’ve been using to help with overall database analyses and storage performance.

#Role
You are an expert Oracle DBA and performance tuning specialist.
#Task
Your task is to analyze the provided Oracle AWR report and give actionable recommendations for improving database performance.
Based on the attached AWR report data above, please provide the following:
1. **Executive Summary:** A brief overview of the main performance issues.
2. **Storage Performance:** a brief overview of the storage performance including read and write IOPS, bandwidth and average block sizes. 
3. **Key Bottlenecks:** Identify the top 3 most critical performance bottlenecks (e.g., top wait events, high-impact SQL).
4. **Actionable Recommendations:** Provide specific, technical steps to resolve each bottleneck.
5. **Areas for Further Investigation:** Suggest sections of the report that require a deeper dive if the initial recommendations are not sufficient.

The output below is from Google Gemini but I have also tried ChatGPT which provided similar results but in slightly different format, which you may prefer.

Google Gemini

Create a new Google Gemini chat, click + and select Upload files, navigate and select the required Oracle AWR report.

Copy and paste the AI prompt into a new chat, and click > to submit.

After a short amount of time Gemini will return its results.

Before we start using the Gemini output, let’s validate some of the AI findings against the provided Oracle AWR, for example.

Executive Summary

Reviewing the AWR report we can confirm the DB Time and Elapsed Time from the header section.

Storage Performance

Again, the Storage Performance aligns to the values in the AWR Load Profile.

Key Bottlenecks

And the Key Bottlenecks findings are inline with results within the provided AWR.

Summary

In this blog post I have shared how we can use an AI to assist in analyses of an Oracle AWR report.

In my limited testing, the observations and recommendations are impressive, and the Google Gemini generated output format very usable.

It maybe too early to share finding without any sanity checks and validation, but nethertheless a great time saver.

Next steps include further prompt development and testing with Claude.