Selectloop – AI Queries an Oracle Database to Answer a Question

This is a follow up to my July post. I have had almost two months to work with my new Selectloop Python script and I want to tell people what I learned so it will be helpful to them. Also it will help me to write this out and think about what I want to say.

First, I want to say that even though this post is about AI it is not generated by AI. This is all my words. I’m not even going to use spell or grammar checkers. I tend to write without commas so unless I get motivated to go back later and figure out where they go this may be comma free. I wrote my previous post but I did go to Copilot for advice and I feel like it makes what I write more generic. With all the “AI slop” out there you don’t need me adding to it.

Second, I was very suprised at how little interest my previous post generated. I documented Selectloop both on this blog and internally. My own DBA team had zero interest and no one commented on the blog post and it did not get many hits. That’s fine but what is funny is how excited I am about Selectloop and what I learned through it. Somehow I have not connected with people about how significant this is. Maybe it is because I have been on this AI journey and many of my readers and coworkers have not. Maybe people have been burned out on too much AI hype. To finally find a valuable use for AI has been such a joy. Maybe this post will help. I’ve learned some practical things:

• Run Selectloop for only a few minutes and look for quick results
• The generated queries are often more useful than the final report
• If you ask the same question twice it might give a good answer once and a bad the other time

So, I’ve simplified the command line to look like this:

python selectloop.py MYDATABASE question.txt

It runs with these settings:

• 30 select statements
• 120 second timeout for SQL query and AI inference
• 5000 line max rows fetched from query

This typically runs in a few minutes. I like to set it running while doing something else. You can also run several runs at the same time. 30 queries is enough to see if it is going down the right path. This is just from my experience. Sometimes the queries have syntax errors so you want to run enough so you get a fair number that actually run.

Here is an example of a simple question.txt file:

Find the top query for the past week and
describe its plan and purpose if you can.

Here is the console output from the run:

$ python selectloop.py MYDATABASE question.txt

Database: MYDATABASE
Question file: question.txt
Report file: selectloop_report_07482b1e.txt
Number of loops: 30
Query and Bedrock timeout in seconds: 120
Maximum number of rows fetched from query: 5000

Processing select statement 1 prompt length = 355
Processing select statement 2 prompt length = 1321
Processing select statement 3 prompt length = 1789
Processing select statement 4 prompt length = 2958
Processing select statement 5 prompt length = 5237
Processing select statement 6 prompt length = 6721
Processing select statement 7 prompt length = 8025
Processing select statement 8 prompt length = 9642
Processing select statement 9 prompt length = 10419
Processing select statement 10 prompt length = 10853
Processing select statement 11 prompt length = 11463
Processing select statement 12 prompt length = 14170
Processing select statement 13 prompt length = 14706
Processing select statement 14 prompt length = 15017
Processing select statement 15 prompt length = 15485
Processing select statement 16 prompt length = 15935
Processing select statement 17 prompt length = 17948
Processing select statement 18 prompt length = 21775
Processing select statement 19 prompt length = 23310
Processing select statement 20 prompt length = 24142
Processing select statement 21 prompt length = 26078
Processing select statement 22 prompt length = 34193
Processing select statement 23 prompt length = 36095
Processing select statement 24 prompt length = 36933
Processing select statement 25 prompt length = 37713
Processing select statement 26 prompt length = 43045
Processing select statement 27 prompt length = 49334
Processing select statement 28 prompt length = 51252
Processing select statement 29 prompt length = 54094
Processing select statement 30 prompt length = 55196

Generating final report

Run time in seconds: 179

Here is the final report.

*** Report generated by AI. It may contain errors. ***

TOP QUERY REPORT: PAST WEEK
===========================================================================

MOST EXECUTED QUERY (SELECT 3)
-------------------------------
SQL ID : d7bgf84kwj3s7
Executions : 2,816,949
Last Active : 2026-09-29 22:02:38

SQL Text:
  SELECT /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
  P.VALCHAR FROM SYS.OPTSTAT_USER_PREFS$ P
  WHERE P.OBJ#=:B2 AND P.PNAME=:B1

PURPOSE:
This is an internal Oracle optimizer statistics query. It reads
per-object optimizer preferences from the SYS.OPTSTAT_USER_PREFS$
table. Oracle calls it during statistics gathering to check whether
any object-level overrides (e.g. degree, method_opt) have been set
via DBMS_STATS.SET_TABLE_PREFS.

EXECUTION PLAN (SELECT 4):
  Step 0: SELECT STATEMENT (cost=1)
  Step 1: TABLE ACCESS BY INDEX ROWID on SYS.OPTSTAT_USER_PREFS$
  Step 2: INDEX UNIQUE SCAN on SYS.I_USER_PREFS$
          Access: P.OBJ#=:B2 AND P.PNAME=:B1

The plan is optimal: a unique index lookup with no disk reads
(SELECT 5: disk_reads=0) and exactly 1 buffer get per execution.
Avg elapsed and CPU times are near zero microseconds per call.

CONTEXT (SELECT 6, 7, 11):
The single ASH sample ties this SQL to SYS running DBMS_SCHEDULER
job ORA$AT_OS_OPT_SY_13592, the Oracle auto optimizer stats task.
SELECT 11 confirms "auto optimizer stats collection" is ENABLED and
completing successfully across daily maintenance windows (SELECT 16).

No action is required. This is expected Oracle background activity.
===========================================================================

This was not an active database so the top query was a system generated one.

Notice how it refers to the select numbers like this:

EXECUTION PLAN (SELECT 4):
  Step 0: SELECT STATEMENT (cost=1)
  Step 1: TABLE ACCESS BY INDEX ROWID on SYS.OPTSTAT_USER_PREFS$
  Step 2: INDEX UNIQUE SCAN on SYS.I_USER_PREFS$
          Access: P.OBJ#=:B2 AND P.PNAME=:B1

Here is select number 4:

-- SELECT number 4

-- Retrieves execution plan details for SQL ID 'd7bgf84kwj3s7' from v$sql_plan.

SELECT
    p.plan_hash_value,
    p.operation,
    p.options,
    p.object_owner,
    p.object_name,
    p.object_type,
    p.cost,
    p.cardinality,
    p.bytes,
    p.cpu_cost,
    p.io_cost,
    p.access_predicates,
    p.filter_predicates,
    p.id,
    p.parent_id,
    p.depth,
    p.position
FROM v$sql_plan p
WHERE p.sql_id = 'd7bgf84kwj3s7'
ORDER BY p.plan_hash_value, p.id;

PLAN_HASH_VALUE        OPERATION        OPTIONS OBJECT_OWNER         OBJECT_NAME    OBJECT_TYPE COST CARDINALITY BYTES CPU_COST IO_COST                  ACCESS_PREDICATES FILTER_PREDICATES ID PARENT_ID DEPTH POSITION 
--------------- ---------------- -------------- ------------ ------------------- -------------- ---- ----------- ----- -------- ------- ---------------------------------- ----------------- -- --------- ----- -------- 
      324713838 SELECT STATEMENT           None         None                None           None    1        None  None     None    None                               None              None  0      None     0        1 
      324713838 SELECT STATEMENT           None         None                None           None    1        None  None     None    None                               None              None  0      None     0        1 
      324713838     TABLE ACCESS BY INDEX ROWID          SYS OPTSTAT_USER_PREFS$          TABLE    1           1    42     8381       1                               None              None  1         0     1        1 
      324713838     TABLE ACCESS BY INDEX ROWID          SYS OPTSTAT_USER_PREFS$          TABLE    1           1    42     8381       1                               None              None  1         0     1        1 
      324713838            INDEX    UNIQUE SCAN          SYS       I_USER_PREFS$ INDEX (UNIQUE)    0           1  None     1050       0 "P"."OBJ#"=:B2 AND "P"."PNAME"=:B1              None  2         1     2        1 
      324713838            INDEX    UNIQUE SCAN          SYS       I_USER_PREFS$ INDEX (UNIQUE)    0           1  None     1050       0 "P"."OBJ#"=:B2 AND "P"."PNAME"=:B1              None  2         1     2        1 

6 rows selected.

Elapsed time: 0.00 seconds

It’s really easy to refer back to the select statements from the final report using the number.

I’ve updated the repository to contain this updated version: https://github.com/bobbydurrett/SelectLoop

The only hassle with the repository is that you have to do your own setup of your AWS account and database credentials. But the main code is all there in selectloop.py.

So, the point of this is just that Selectloop will run 30 queries for you against an Oracle database. You can ask it whatever you want. It runs for a few minutes. It may or may not be useful. But there have been times when it has been very useful in my work. You really have nothing to lose cutting one or more executions of Selectloop loose while you continue to work in other ways. Chances are if it is useful you will find not only good information in the final report, but also in the list of select statements and their output. I don’t know. I think it is extremely cool. If you are up for it, give it a try.

Bobby