Using an LLM in a SELECT Loop

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:

1. Question -> LLM -> SELECT1 -> Oracle -> Output1
2. Question + SELECT1 + Output1 -> LLM -> SELECT2 -> Oracle -> Output2
3. Loop N times
4. Question + SELECT1 + Output1 + … + SELECTN + OutputN -> LLM -> Report
5. 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      10075

Question + 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.