Introduction
This post describes a way to use a Large Language Model (LLM) to investigate Oracle database problems. In this approach, the LLM repeatedly generates SELECT statements based on previous queries and their results. Each query provides additional context, allowing the LLM to gather information step by step before producing a final report for a DBA to review.
My previous attempts at using an LLM only performed a single inference. I had to gather the input data myself and decide what output I wanted the LLM to produce. In the SELECT Loop approach the LLM can gather its own information through each query it generates. Every iteration expands the context available to the model, allowing it to investigate a problem in several steps instead of trying to answer immediately with incomplete information.
Here is the outline of how this approach works:
- Question -> LLM -> SELECT1 -> Oracle -> Output1
- Question + SELECT1 + Output1 -> LLM -> SELECT2 -> Oracle -> Output2
- Loop N times
- Question + SELECT1 + Output1 + … + SELECTN + OutputN -> LLM -> Report
- Report -> DBA
Question -> LLM -> SELECT1 -> Oracle -> Output1
The first step is to have the LLM generate a SQL query to try to answer some question about the database and then run it. The LLM’s prompt might look like this:
On an Oracle 11.2.0.4 database I want to investigate the performance of a sql statement with SQL_ID b4rj0h8hh0sxf.
Please generate a select statement that will give more information about b4rj0h8hh0sxf's performance.
Please only generate ASCII text.
Please keep to no more than 80 character lines.
Please output only the select statement that you want to run next.The LLM generates some useful query against the database’s DBA or V$ views like:
SELECT
...
s.iowait_delta AS iowait_usecs,
s.clwait_delta AS clwait_usecs,
s.apwait_delta AS apwait_usecs,
s.ccwait_delta AS ccwait_usecs
FROM
dba_hist_sqlstat s
JOIN dba_hist_snapshot sn
ON sn.snap_id = s.snap_id
AND sn.dbid = s.dbid
AND sn.instance_number = s.instance_number
WHERE
s.sql_id = 'b4rj0h8hh0sxf'
AND s.dbid = (SELECT dbid FROM v$database)
ORDER BY
sn.begin_interval_time,
s.plan_hash_value;The next step is just to run the generated query against the database and get its output like:
SNAP_ID BEGIN_TIME END_TIME PLAN_HASH_VALUE EXECS
---------- --------------- --------------- --------------- ----------
88771 15-MAY-26 01:00 15-MAY-26 02:00 3752201481 11225
88772 15-MAY-26 02:00 15-MAY-26 03:00 3752201481 22707
88773 15-MAY-26 03:00 15-MAY-26 04:00 3752201481 11381
88795 16-MAY-26 01:00 16-MAY-26 02:00 3752201481 11316
88796 16-MAY-26 02:00 16-MAY-26 03:00 3752201481 23920
88797 16-MAY-26 03:00 16-MAY-26 04:00 3752201481 10076
88819 17-MAY-26 01:00 17-MAY-26 02:00 3752201481 11315
88820 17-MAY-26 02:00 17-MAY-26 03:00 3752201481 24907
88821 17-MAY-26 03:00 17-MAY-26 04:00 3752201481 9086
88843 18-MAY-26 01:00 18-MAY-26 02:00 3752201481 11209
88844 18-MAY-26 02:00 18-MAY-26 03:00 3752201481 24009
88845 18-MAY-26 03:00 18-MAY-26 04:00 3752201481 10075Question + SELECT1 + Output1 -> LLM -> SELECT2 -> Oracle -> Output2
Next take the original question and append the first SELECT statement and its output and request a second SELECT statement. The LLM suggested SELECT1. Now we are giving it both its SELECT1 query and its output. Then the LLM gets to take the next step in the problem investigation by generating a second SELECT statement. By showing it what it previously chose and the query output we are giving the LLM context that it didn’t have the first time we asked it the question. This prompt looks something like this:
On an Oracle 11.2.0.4 database I want to investigate the performance of a sql statement with SQL_ID b4rj0h8hh0sxf.
Following this prompt are the outputs of select statements that you recommended running. After evaluating them
please generate an additional select statement that will give more information about b4rj0h8hh0sxf's performance.
Please only generate ASCII text.
Please keep to no more than 80 character lines.
Please output only the select statement that you want to run next.
Output of your previous select statements:This prompt would be followed by the first select statement and its output. I’m assuming that the output contains the SELECT statement and the query results. The query the LLM returns will probably be different from the first one because it already knows the result of the first query.
Once the LLM generates it, run the second query.
Loop N times
Now pass the original question plus all the queries and their outputs into the LLM requesting another SQL. Repeat this process N times. This pattern is the main point of this post. The LLM starts with the original question and generates a select statement. We run the query, append the query and its output to the prompt, and ask for another query. This process repeats until we have gathered enough information. The LLM is taking us down its own path investigating the question.
Keep generating new SELECT statements based on all the previous SELECT statements and their outputs.
Question + SELECT1 + Output1 + … + SELECTN + OutputN -> LLM -> Report
Finally stop generating new queries and take all the ones you have generated and all their outputs and ask the LLM to generate a final report. Here is part of a report:
## 4. The Plan Change Event on 26-JUN-26
On snap 89780 (26-JUN-26 02:00-03:00) plan 361274665 appeared for
35 executions alongside the normal plan. By snap 89781 (03:00-04:00)
the bad plan had taken over entirely.
Performance comparison for 26-JUN-26:
| Metric | Normal plan | Bad plan snap89781 | Bad plan snap89782 |
|------------------|----------- |---------------------|---------------------|
| Executions | 16,775 (03h) | 11 | 13 |
| Avg elapsed (s) | 0.08 | 54.79 | 59.56 |
| Avg buffer gets | 17,673 | 14,982,688 | 18,208,586 |
| Avg disk reads | 37 | 6,832 | 6,463 |
| Avg I/O wait (s) | ~0 | 3.31 | 3.78 |
| Rows returned | ~155 | ~73,765/exec | ~72,639/exec |
The bad plan is approximately **500x slower** per execution and
performs **800-1,000x more buffer gets**. Disk reads per execution
jump from ~37 to ~6,800.What makes this interesting is that the LLM chose the sequence of queries that gathered the information used in this report.
Report -> DBA
The last step is for the DBA to review the report. No one should look at an AI generated report like this and act on it without an experienced DBA reviewing it. I don’t see this kind of report replacing DBAs. I see it as empowering them. Instead of me manually writing and running various queries to solve a problem I can give the problem to an LLM to take a first crack at it.
Conclusion
I struggled to find a practical use for LLMs in my DBA work until I started experimenting with this SELECT loop approach. By repeatedly generating queries, evaluating their results, and using that information to drive the next query, the LLM can investigate a problem before producing a final report. I don’t see this replacing DBAs, but I do see it helping experienced DBAs investigate problems more quickly.
