Bobby Durrett's DBA Blog

I’ve moved on from my previous script which generated SELECT statements on Oracle databases using an LLM. I’m using Claude 4.6 through AWS Bedrock. In this post I will describe my latest tool which I cleverly named “readonly” for lack of better creativity. The point is that I let Claude generate commands that I then run against our databases and their hosts, but these commands are safe because they are read only. They don’t change anything.

Like my previous post this is a purely human post. I’m not using any AI to generate or check the text. I’m not even using my normal spell check and grammar checker. This is pure me which hopefully is a good thing.

I haven’t really written a business oriented script. I’ve stuck with making a tool that would help me as a DBA to do my job better. Of course the things I’m learning could be applied to business needs as well. I’m just more knowledgable about the DBA tasks that I’ve been doing than some business financial details that you might want to apply AI to.

I’ll get right into the heart of it. Here is the prompt I am using in my readonly.py script:

You are a database administration assistant that can run several commands to answer
a question about our databases. You have the following commands available:

query(database,select_statement) - You pass the name of one of our databases and a select statement and you get the results of the query back.
Example: query("ORCL","SELECT * from DUAL;")

dblist() - Returns a list of database names and the hosts that they run on.
Example: dblist()

readfile(host,file) - Returns up to 1000 lines from the given file on the given host.
Example: readfile("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace/alert_MY19DB.log")

standards() - Describes our standards for Oracle databases.
Example: standards()

directory(host,path) - Runs ls -altr on the given path and host to show list of files sorted by reverse date and time
Example: directory("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace")

tracefiledir(database) - Returns the filesystem path for the trace files and alert log
Example: tracefiledir("MY19DB")

diskfree(host) - Shows the free space on the host's filesystems
Example: diskfree("mylinuxdbhost")

awrlasthour(database) - Does a text AWR report for the most recent pair of hourly snapshots
Example: awrlasthour("MY19DB")

oracleprocesses(host) - Does a ps -ef | grep oracle on the database host to show oracle owned processes
Example: oracleprocesses("mylinuxdbhost")

networkcheck(host) - Tests network connectivity and latency from the local machine to a host
Example: networkcheck("mylinuxdbhost")

oraclehome(database) - Gets the path where the Oracle database software is installed for the database
Example: oraclehome("MY19DB")

patches(database) - Show the patches that are installed in the database's software
Example: patches("MY19DB")

sqlstat(database,sql_id) - Show the execution history of a single SQL statement
Example: sqlstat("MY19DB","f3yfg50ga0r8n")

crontab(host) - Show the scripts which oracle has scheduled to run in cron
Example: crontab("mylinuxdbhost")

uptime(host) - Show how long the host has been up since last reboot
Example: uptime("mylinuxdbhost")

parameter(database,parameter) - Show the value of a parameter on a database
Example: parameter("MY19DB","sga_target")

dbversion(database) - Show the version of the database like 11.2.0.3
Example: dbversion("MY19DB")

grep(host,file,pattern) - Search a file on a host for a pattern containing only letters,numbers,- or _
Example: grep("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace/alert_MY19DB.log","ORA-")

stop() - Break out of loop and generate final report if you have run at least 5 loops.
Example: stop()


Please only generate ASCII text.
Please keep to no more than 80 character lines.
Do not produce anything but a command in the syntax given.
Do not attempt to answer the question. Only generate commands that may help answer it.
Here is the question you are trying to answer:

Describe the active sessions on the MY19DB database.

I’ve hacked the real prompt to hide the real name of the database and its host. In the real prompt MY19DB is the name of a real non-prod database and mylinuxdbhost is its real host name (a virtual machine with Redhat Linux 7).

But the main point here is that I have given Claude 4.6 these commands to execute to answer the question which is at the end of the prompt.

• query(database,select_statement)
• dblist()
• directory(host,path)
• tracefiledir(database)
• diskfree(host)
• awrlasthour(database)
• oracleprocesses(host)
• networkcheck(host)
• oraclehome(database)
• patches(database)
• sqlstat(database,sql_id)
• crontab(host)
• uptime(host)
• parameter(database,parameter)
• dbversion(database)
• grep(host,file,pattern)
• stop()

This may be too many commands. I plan to try commenting some of these out if they seem redundant. They mimic the type of tasks I do in my DBA work. For example, oracleprocesses(host) runs ps -ef | grep oracle on the database server. It is a safe read-only command and I do it periodically when I checking things on the server side.

I don’t know about parameter(database,parameter). It already has query(database,select_statement) so it can just query v$parameter for the parameter value.

So, this list may need to be refined, but you can see that it goes beyond what a select only tool could do by allowing access to the host in, hopefully, a safe read-only fashion.

The key to this whole approach and to the selectloop script that preceded it is that each call to Claude 4.6 except for the last one is a thinking step. It has an initial question like this one:

Describe the active sessions on the MY19DB database.

Then it has the history of previous commands and their output. It also has a description of the commands and the other instructions in the prompt.

Do not produce anything but a command in the syntax given.
Do not attempt to answer the question. Only generate commands that may help answer it.

The Claude inferences in the loop are all instructed to only generate commands. Then at the end there is a final inference to process all the history of commands and their output into a report.

def generate_answer(question, history):
    """
    
    Make a final report based on the original question plus
    the history of commands and their outputs.
    
    """
    
    # Get history in text form
    
    history_text = make_history_text(history)
    
    # prompt header
    
    header = """
Please generate a short report which explains this subject based on 
the following question and the history of commands that were run and their outputs.
Please only generate ASCII text.
Please keep to no more than 80 character lines.
Please keep the output at no more than 30 lines.
Question:

"""
    
    # Combine header, question, and history
        
    combined = header + question + history_text
        
    timeout_secs = config.LLM_TIMEOUT_SECS
        
    response = getresponse(combined,timeout_secs)   
    
    # handle max tokens error
    
    if response == "ERROR: An error occurred (ValidationException) when calling the InvokeModel operation: Input is too long.":

        # remove last entry in history

        history.pop()
        
        # Get history in text form
    
        history_text = make_history_text(history)
        
        # Combine header, question, and history
        
        combined = header + question + history_text
                
        response = getresponse(combined,timeout_secs)   
             
    return response

One limitation is that with AWS Bedrock and Claude 4.6 I am limited to 1,000,000 tokens which is a few megabytes usually. The prompt + command history + question can’t exceed 1 million tokens or it throws an error and generates nothing.

I’m kind of intrigued by the 1,000,000 token limit. How do you code around it to get the LLM to generate one command after another applying all it knows without blowing out the limit? I’ve done things like limiting the number of rows returned by any query to 1000 and limiting the files downloaded to just 1000 lines. These limits are really just determined by practice. I’ve thought about using some sort of summary to compress the current history of commands and output but haven’t tried it.

In addition to the token limit, I have a time limit. I don’t want this to run for hours. I want to kick it off for a few minutes and then see what it comes up with. If I don’t write the question the right way it could be spinning for a long time going down the wrong path. I was doing 30 loops and 2 minute timeouts on selectloop. Sometimes 30 loops isn’t enough with all these commands so I’m tweaking that limit. I just put in the stop command which allows the LLM to choose to break out of the loop early. I’m playing with a minimum of 30 loops but then allow it to run up to 60 if it doesn’t generate stop().

I think I mentioned before the same sort of thing for selectloop. The history of the commands and their output is as valuable or more valuable than the final report. I can see what it queried and what files it looked at.

So what? I think that people still aren’t very excited about this kind of thing. It’s great that it runs 30-60 commands and summarizes their output, but it often gets the wrong answer. It requires a knowledgeable person (me) to review it and make their own judgement. I think people want something with an answer they can trust. I think they want an AI script that they can plug into an automation and produce a result that you can do updates based on safely. That isn’t what this is. This is a tool for maximizing an experienced user’s time by doing an independent investigation in tandem with the manual steps that user is doing to solve the same problem. Now, maybe some sort of experienced business analyst or financial analyst could use a similar tool. Instead of querying DBA_ views it could query business application tables and read application files from servers. Then the business user could review both the final output and the incremental steps of discovery that the AI tool took and use their own judgement. I think we want something that an untrained user can rely on blindly and I just haven’t come up with it yet. But it is helping me be more effective.

More to come. I hope this AI free all Bobby post is helpful.

Bobby

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

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.

P.S. I uploaded the sample code to a Github repository if you want to run it: https://github.com/bobbydurrett/SelectLoop

DBAs spend a lot of time reviewing reports about the health of their databases. I’ve used an LLM to speed up that process.

I took a daily report about our Oracle databases and used an LLM to generate a short summary that lets a DBA immediately see which databases need attention.

A typical report looks like this for each database:

The full report has over 2,000 lines that must be manually scanned by the on-call DBA each day.

The LLM-generated summary looks like this:

This summary immediately shows which databases need attention. We still manually scan the entire report but having the summary in the body of the email (with the full report attached) lets us see at a quick glance what needs attention and how urgent it is. The summary does not replace the full report; it only highlights the items that are most likely to be important. In our environment we chose 89% full as the point where we start reporting on space issues.

I’m using AWS Bedrock with the Claude Sonnet 4.6 model. Here is the Python
function that sends the combined prompt and report to Bedrock and returns the summary:

Here is the prompt that preceeds the report:

This simple use of an LLM has saved me time by putting a quick summary in the email body while preserving the full report for detailed review.

Bobby

I am working on an Oracle 19c database running on a RHEL 7 VM. I have been trying to roll back this patch:

Patch 30763851 IMPDP 11.2 TO 18C OR HIGHER HITS ORA-904 WHEN TABLES HAVE EXTENDED STATISTICS

I have it sitting on top of 19.5

Database Release Update: 19.5.0.0.191015 (30125133)

The Opatch rollback command ran fine, but Datapatch threw these errors:

[2025-10-22 18:50:15]   -> Error at line 11329: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11331: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11333: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11335: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11337: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11339: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11341: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11343: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_OPQTYPE_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11355: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_P2TPARTCOL_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11357: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_P2TPARTCOL_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11363: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_SP2TPARTCOL_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11365: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_SP2TPARTCOL_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11381: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11383: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11385: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11387: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11389: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11391: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11393: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11395: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11397: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_P2TCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11399: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_P2TCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11401: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_SP2TCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11403: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_SP2TCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11405: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11407: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_COLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11409: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"
[2025-10-22 18:50:15]   -> Error at line 11411: script rdbms/admin/dpload.sql
[2025-10-22 18:50:15]       - ORA-04063: view "SYS.KU$_PCOLUMN_VIEW" has errors"

I checked DBA_OBJECTS, and all the SYS objects are VALID. I tried querying one of the views and it worked fine. So, I went to My Oracle Support, our Oracle database support site, and searched for ORA-04063 and one of the view names and found nothing. A Google search also came up empty. I tried just ignoring it but that didn’t work. My whole goal in doing this was to apply the October 2025 patches that just came out this week. But because the SQL patch registry indicated that patch 30763851 rolled back with errors, every time I applied a new patch it would try to roll 30763851 back first and error again. Here is what DBA_REGISTRY_SQLPATCH looked like after two failed rollback attempts:

INSTALL_ID   PATCH_ID PATCH_TYPE ACTION          STATUS        
---------- ---------- ---------- --------------- --------------
         1   30125133 RU         APPLY           SUCCESS       
         2   30763851 INTERIM    APPLY           SUCCESS       
         3   30763851 INTERIM    ROLLBACK        WITH ERRORS   
         3   30763851 INTERIM    ROLLBACK        WITH ERRORS   
         4   30763851 INTERIM    ROLLBACK        WITH ERRORS   
         4   30763851 INTERIM    ROLLBACK        WITH ERRORS   

Each rollback attempt tried twice so I have four failures with two rollback attempts.

I opened a case with Oracle support just in case this was a known issue that wasn’t available for me to find on my own. Sometimes that happens. But while waiting on Oracle I kept trying to fix it myself.

The errors refer to $ORACLE_HOME/rdbms/admin/dpload.sql which I think reloads datapump after some change. It runs catmetviews.sql and catmetviews_mig.sql which have the CREATE VIEW statements for the views getting errors, like SYS.KU$_OPQTYPE_VIEW. But the code in catmetviews_mig.sql wasn’t straightforward. I imagined running some sort of trace to see why the script was throwing the ORA-04063 errors, but I never had to take it that far.

At first all this stressed me out. I thought, “I can’t back out this patch. I will never be able to patch this database to a current patch level.” Then I chilled out and realized that if it was a problem with Oracle’s code, they had to help me back out 30763851. But it might take some time to work through an SR with Oracle.

But what if it wasn’t an issue with Oracle’s code but something weird in our environment? I didn’t think it indicated a real problem, but there were some weird messages coming out that I am used to seeing. They were from triggers that come with an auditing tool called DB Protect. They were throwing messages like this:

[SYS.SENSOR_DDL_TRIGGER_A] Caught a standard exception: aliasId=100327, error=-29260, message="ORA-29260: network error: TNS:no listener"

We are used to seeing these errors when we do DDL but prior to this it didn’t cause any actual problems. We had already decommisioned the DB Protect tool but had not cleaned up the triggers. Dropping SYS.SENSOR_DDL_TRIGGER_A eliminated the ORA-04063 errors.

Probably no one will ever encounter this same issue, but I thought I would document it. If you have the same symptoms and you are not using DB Protect any more, do these commands:

DROP TRIGGER SYS.SENSOR_DDL_TRIGGER_A;
DROP TRIGGER SYS.SENSOR_DDL_TRIGGER_B;

I think the A trigger was the problem, but we don’t need either one.

Anyway, this post is just so someone who searches for ORA-04063 and one of the views will find this information and drop the triggers if they have them. It’s a long shot but might as well document it for posterity and for me.

Bobby

I recently migrated my WordPress blog from Amazon Linux 2 to Amazon Linux 2023 to take advantage of newer software versions. The process was mostly smooth, but I wanted to document a few things I ran into—especially around encryption setup.

I followed these instructions for setting WordPress up on AL 2023: https://docs.aws.amazon.com/linux/al2023/ug/hosting-wordpress-aml-2023.html

The first thing I had to do was follow these instructions to get Apache, PHP, and MariaDB setup: https://docs.aws.amazon.com/linux/al2023/ug/ec2-lamp-amazon-linux-2023.html

I also had to setup a new elastic ip: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/elastic-ip-addresses-eip.html

I used the security group that I had for my existing AL 2 EC2 so I had the right ip addresses opened up.

I copied the database over from the old to new EC2:

on the old server:

mysqldump -u blogdbuser -pMYPASSWORD --single-transaction --routines --triggers blogdb  > blogdb.sql

on the new server:

mysql -vvv -n -u blogdbuser -pMYPASSWORD blogdb <  blogdb.sql > blogdb.log

Similarly for the web pages and other files:

New server:

# clear out existing web server directory

cd /var/www/html

sudo rm -fr *

sudo rm -f .htaccess .user.ini .wpcli

Old server:

sudo tar -cvf /home/ec2-user/html.tar /var/www/html

New server:

sudo tar -xvf /home/ec2-user/html.tar -C /

Moving the DNS entries over to the new elastic ip address was easy. I just had the change the “A” records for bobbydurrettdba.com and www.bobbydurrettdba.com in Route 53. First, I changed the TTL from one day to 10 minutes so my changes would propogate quickly while I messed with things. Later I set these back. One day was 86400 seconds. Ten minutes was 600 seconds.

The biggest challenge I had was getting encryption setup properly. The documentation missed a couple of key steps. I thought about just writing this post about the encryption part because it was the only thing that wasn’t straightforward.

I was following the steps in https://docs.aws.amazon.com/linux/al2023/ug/SSL-on-amazon-linux-2023.html and using the Let’s Encrypt script for getting certificates. This was called Certbot and documented here: https://eff-certbot.readthedocs.io/en/stable/install.html

There were two key things missing from this documentation:

• How to install Snap on Amazon Linux 2023
• The need for a port 80 VirtualHost

Certbot wants you to install something called Snap to use to install Certbot. I got this from a Google search:

sudo dnf install openssl mod_ssl

sudo wget -O /etc/yum.repos.d/snapd.repo https://bboozzoo.github.io/snapd-amazon-linux/al2023/snapd.repo

sudo dnf install snapd -y

sudo systemctl enable --now snapd.socket

sudo ln -s /var/lib/snapd/snap /snap

sudo reboot

The other undocumented thing was that I had to add these lines after the “Listen 80” line in /etc/httpd/conf/httpd.conf:

sudo vi /etc/httpd/conf/httpd.conf

after Listen 80 add this:

<VirtualHost *:80>
    DocumentRoot "/var/www/html"
    ServerName bobbydurrettdba.com
    ServerAlias www.bobbydurrettdba.com
</VirtualHost>

Then I just ran the documented Certbot commands like this:

sudo snap install --classic certbot

sudo ln -s /snap/bin/certbot /usr/bin/certbot

sudo certbot --apache

It took me a few iterations to get this right. Anyway, I just wanted to post a quick note documenting this.

Bobby

1. Introduction

I set out to use Machine Learning to monitor Oracle databases but ultimately chose not to. The most valuable lesson I learned was the importance of visualizing my data to determine the best approach. By examining real production performance issues, I developed scripts that trigger alerts during potential performance problems. I used six weeks of historical performance metrics to compare current behavior against past trends. I already knew that a high value for a given metric didn’t necessarily indicate a performance issue with business impact. In the end, I built a script that sends an alert when certain metrics are the top wait event and are performing three times worse than at any point in the past six weeks. While it doesn’t guarantee a business impact, it flags anomalies that are unusual enough to warrant investigation.

2. Why I Tried Machine Learning

I think anyone reading this post would know why I tried to use Machine Learning. It’s very popular now. I took a class online about ML with Python and I bought a book about the same subject. I had already used Python in some earlier classes and in my work, so it was natural to take a class/read a book about ML based on Python. As I mentioned in an earlier post, I studied AI in school many years ago and there have been many recent advances so naturally I wanted to catch up with the current state of the art in AI and ML. Having taken the class, and later having read a book, I needed some application of what I learned to a valuable business problem. We had a couple of bad performance problems with our Oracle databases and our current monitoring didn’t catch them. So, I wanted to use ML – specifically with PyTorch in a Python script – to write a script that would have alerted on those performance problems.

3. Failed Attempt Number One – Autoencoder

In my first attempt to write a monitoring script, I worked with Copilot and ChatGPT to hack together a script using an autoencoder model. I queried a bunch of our databases and collected a list of all the wait events. My idea was to treat each wait as an input to the autoencoder. For each wait, I pulled the number of events and the total wait time over the past hour. I didn’t differentiate between foreground and background waits—I figured AI could sort that out. I also included DB time and DB CPU time. These were all deltas between two hourly snapshots, so the data represented CPU and waits for a given hour.

The concept behind the autoencoder was to compress these inputs into a smaller set of values and then reconstruct the original inputs. With six weeks of hourly snapshots, I trained the autoencoder on that historical data, assuming it represented “normal” behavior. The idea was that any new snapshot that couldn’t be accurately reconstructed by the model would be considered abnormal and trigger an alert.

I got the model fully running across more than 20 production databases. I tuned the threshold so it would have caught two recent known performance issues. But in practice, it didn’t work well. It mostly triggered alerts on weekends when backups were running and I/O times naturally spiked. It wasn’t catching real performance problems—it was just reacting to predictable noise.

Here is the PyTorch autoencoder model:

# Define the Autoencoder model
class TabularAutoencoder(nn.Module):
    def __init__(self, input_dim):
        super(TabularAutoencoder, self).__init__()
        # Encoder
        self.encoder = nn.Sequential(
            nn.Linear(input_dim, 64),  # Reduce dimensionality from input_dim to 64
            #nn.LayerNorm(64),  # Layer Normalization for stability
            nn.ReLU(True),
            nn.Dropout(0.2),   # Dropout for regularization
            nn.Linear(64, 32),  # Reduce to 32 dimensions
            nn.ReLU(True),
            nn.Linear(32, 16),  # Bottleneck layer (compressing to 16 dimensions)
        )
        # Decoder
        self.decoder = nn.Sequential(
            nn.Linear(16, 32),  # Expand back to 32 dimensions
            nn.ReLU(True),
            nn.Linear(32, 64),  # Expand to 64 dimensions
            #nn.LayerNorm(64),  # Layer Normalization for stability
            nn.ReLU(True),
            nn.Linear(64, input_dim),  # Expand back to original input dimension
         )

    def forward(self, x):
        x = self.encoder(x)
        x = self.decoder(x)
        return x

4. Failed Attempt Number Two – Binary Classification

Much more recently, I came back to using a PyTorch model to build an alerting script. I had spent some time learning about large language models and exploring how to apply GenAI to business problems, rather than working with these simpler ML models in PyTorch. But I decided to give it another try and see if I could fix the limitations I ran into with the autoencoder approach.

I didn’t really expect binary classification to work, but it’s the simplest kind of ML model, so I thought it was worth a shot. I selected a few snapshots that occurred during a severe performance problem on an important Oracle database. I labeled these as 1 (problem), and the rest as 0 (no problem). It was easy enough to train a model that correctly identified the problem snapshots during training and testing.

However, when I applied the model to later snapshots it hadn’t seen before, it started generating false alarms that didn’t make sense. It got even worse when I tried it on a completely different database. I had expected the model to pick up on the fact that the top wait event during the problem period was enq: TM – contention. A quick glance at the top foreground events in an AWR report made it obvious that this was the issue. But the trained model ended up sending an alert on snapshots that didn’t even have this wait event in the top waits.

Here is the PyTorch binary classification model:

"""

Mostly got the model from Copilot. input_dim is the number of performance
metrics which is over 100. Gets it down to 64 to 32 and then 1.
The ReLU functions add "non-linear" change to allow it to do
functions that the linear steps can't do.

"""
    
class MyModel(nn.Module):
    def __init__(self, input_dim):
        super(MyModel, self).__init__()
        self.fc1 = nn.Linear(input_dim, 64)
        self.relu1 = nn.ReLU()
        self.fc2 = nn.Linear(64, 32)
        self.relu2 = nn.ReLU()
        self.output = nn.Linear(32, 1)

    def forward(self, x):
        x = self.fc1(x)
        x = self.relu1(x)
        x = self.fc2(x)
        x = self.relu2(x)
        x = self.output(x)
        return x

Here are the foreground waits and cpu from an AWR report of the problem time:

5. Failed Attempt Number Three – Z-Score

While talking with Copilot, I decided to try a simple statistical approach to anomaly detection. One suggestion was to use a z-score. In my context, this meant looking at the top foreground wait event—enq: TM – contention—in the current snapshot and calculating its z-score relative to the mean and standard deviation of that wait’s values in previous snapshots.

Like the binary classification attempt, the z-score alerting script worked well with the original snapshots from the known problem period. But when I ran the same script against other databases, it produced a lot of false alarms.

Output of z-score script on problem database (alerts on expected snapshot):

   SNAP_ID SNAP_DTTIME         EVENT_NAME            RATIO_ZSCORE AVGWAIT_ZSCORE
---------- ------------------- --------------------- ------------ --------------
    151752 2025-06-19 00:00:41 enq: TM - contention     3.0878532     9.74301378

Output on another database (no rows expected):

   SNAP_ID SNAP_DTTIME         EVENT_NAME                    RATIO_ZSCORE AVGWAIT_ZSCORE
---------- ------------------- ----------------------------- ------------ --------------
     48916 2025-06-22 01:00:01 cursor: pin S wait on X         9.00654128     5.36254393
     48917 2025-06-22 02:00:06 cursor: pin S wait on X         7.16211659     8.09929239
     48918 2025-06-22 03:00:11 cursor: pin S wait on X         5.41578347     4.52106805
     48919 2025-06-22 04:00:13 cursor: pin S wait on X         4.69593403     4.08335551
     48920 2025-06-22 05:00:16 cursor: pin S wait on X         3.17592316     3.46979661
     49081 2025-06-28 22:00:32 cursor: pin S wait on X         4.62636243     3.15122436
     49254 2025-07-06 03:00:04 cursor: pin S wait on X         4.73771834     3.23654318
     49255 2025-07-06 04:00:10 cursor: pin S wait on X         3.96208561     3.61732606
     49289 2025-07-07 14:00:55 SQL*Net more data from dblink   18.2249941     6.61795927
     49364 2025-07-10 17:00:20 library cache lock              89.7469665     392.322777
     49365 2025-07-10 18:00:45 library cache lock              14.6228987      120.71596
     49760 2025-07-27 05:00:21 cursor: pin S wait on X         5.68469758     3.52381292

6. A Picture Is Worth a Thousand Words

Now I’m getting to the high point of this post. I graphed the waits for the snapshots where the z-score worked and the ones where it didn’t—and the truth jumped out at me. In the database with the known problem, the graph of enq: TM – contention was almost a flat line across the bottom for six weeks, followed by a huge spike during the problem hours. In another database—or even the same one with a different top wait—the graph looked completely different: a wavy pattern, almost like a sine wave, stretching back across the entire six weeks.

“Heck!” I said to myself. “Why don’t I just look for top waits that are never close to this high in the past six weeks?” Sure, it might not always be a real business problem, but if a wait is so dramatically different from six weeks of history, it’s worth at least sending an email—if not a wake-up-in-the-middle-of-the-night page.

With z-score, we were sending an alert when a metric was three standard deviations from the mean. But for wavy waits like db file sequential read, that wasn’t selective enough. So, I designed a new monitoring script: it looks at the top wait in the snapshot, checks if it’s a higher percentage of DB time than CPU, and then compares it to the past six weeks. If it’s more than three times higher than it’s ever been in that history, it triggers an alert. This is all based on the percentage of total DB time.

First the wavy graph of db file sequential read waits:

Next, this is the key graph to this entire post. Not wavy:

The light clicked on when I saw this graph. The enq: TM – contention waits were insignificant until the problem occurred.

Here is the script that checks if we should alert: neveralert.sql

7. Don’t Check Your Brain at the Machine Learning Door

When I started with the autoencoder script, I felt overwhelmed—like many people do when first approaching Artificial Intelligence and Machine Learning. I expected the model to magically work without fully understanding how or why. I relied on chat tools to help me piece together code I didn’t grasp, and when it didn’t work, I couldn’t explain why. I’m trying to be honest about my journey here so it might help others and remind myself what I learned.

Each step—from binary classification to z-score to manual logic—brought me closer to a solution rooted in my own experience. You must use your own brain. This has always been true in Oracle performance work: you can’t just follow something you read or heard about without understanding it. You need to think critically and apply your domain knowledge.

If it sounds like I didn’t get anything out of my machine learning training, that’s not the case. Both the class and the book emphasized the importance of visualizing data to guide modeling decisions. They used Matplotlib to create insightful graphs, and while I’ve used simpler visualizations in my own PythonDBAGraphs repo, this experience showed me the value of going deeper with data visualization. I think both the class and book would agree: visualizing your data is one of the best ways to decide how to use it.

Even though this journey didn’t end with a PyTorch-based ML solution, it sharpened my understanding of both ML and Oracle performance—and I’m confident it will help me build better solutions in the future.

Bobby

Sunday four batch jobs that normally run in an hour had been stuck for 3 hours and had not completed the first unit of work out of many. Earlier in the day the on call DBA had applied a SQL Profile and cancelled the jobs and rerun them but it did not help. We picked a different SQL Profile, killed the jobs, and the jobs ran normally. How did we figure out the right plan to use for a SQL Profile?

The main clue came from the output of my sqlstat.sql script:

The good plan seemed to be 2367956558. EXECUTIONS_DELTA = 1 means that the SQL finished in that hour. Elapsed Average ms of 45177.105 was 45 seconds. 19790.066 was 19 seconds.

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
--------------- ------------------------- ---------------- ------------------
     2367956558 20-APR-25 01.00.23.638 AM                1          45177.105
     2367956558 20-APR-25 03.00.29.921 AM                1          19790.066

The other plans 2166514251 and 3151484146 seemed to have executions that spanned multiple hours. For example, the first 2166514251 line had EXECUTIONS_DELTA = 0 which means it didn’t finish in that hour. Plus, the elapsed time of 8995202.39 ms = 8995 seconds = 2.5 hours suggests that it was running in parallel, probably for the entire hour.

PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms
--------------- ------------------------- ---------------- ------------------
     2166514251 20-APR-25 01.00.23.638 AM                0         8995202.39

So, it seems clear that 2367956558 is the best plan.

I could say a lot more about this incident, but I wanted to focus on the sqlstat.sql output. The values of EXECUTIONS_DELTA and Elapsed Average ms are keys to identifying plans with the best behavior.

Bobby

Introduction

Everyone who has heard the old saying “a picture is worth a thousand words” appreciates its simple wisdom. With Oracle databases you have situations where a graph of the output of a SQL query is easier to understand than the standard text output. It’s helpful to have a simple way to graph Oracle data, and Python has widely used libraries that make it easy.

This post describes a Python script that graphs data from an Oracle database using the Matplotlib graphics library. The script uses three widely used Python libraries: cx_Oracle, NumPy, and Matplotlib. This post provides a simple and easily understood example that can be reused whenever someone needs to graph Oracle data. It is written as a straight-line program without any functions or error handling to keep it as short and readable as possible. It demonstrates the pattern of cx_Oracle -> NumPy -> Matplotlib and the use of Matplotlib’s object-oriented approach.

Here is an example graph:

The script graphs database server percent CPU used versus a combination of the day of week and the hour of the day to see if there is any pattern of CPU usage throughout a typical week. This graph has about 6 weeks of hourly AWR snapshots graphed in a scatter plot with CPU percentage on the Y axis and (24 * day of week) + hour of day as the X axis. You could think of the X axis as the hour of the week. This graph might be helpful in performance tuning because it shows whether CPU usage follows a weekly pattern.

Here is the current version of the script: scatter.py.

The script has three main parts which correspond to the three non-internal Python libraries that I use:

• cx_Oracle – Query the CPU data from an Oracle database
• NumPy – Massage query data to get it ready to be graphed
• Matplotlib – Graph the data

These libraries all have lots of great documentation, but Matplotlib’s documentation is confusing at first. At least it was for me. Here are three useful links:

Quick start – This is a great overview. The picture of the “Parts of a Figure” is helpful. I don’t know if earlier versions of Matplotlib had this picture.

Axes – This is a nice list of all the methods of an Axes object. Most of the code in the example script involves calling these methods. I have trouble finding these methods using a Google search, so I bookmarked this link.

Figure – The example script does not call any Figure object methods, but I wanted to document where to find them here. I bookmarked this URL as well as the Axes one because a Matplotlib graph is composed of at least one Figure and Axes object. With the Quick start link and these two lists of methods you have all you need to write Matplotlib scripts.

cx_Oracle

The query for this graph pulls operating system CPU metrics from the DBA_HIST_OSSTAT view and uses them to calculate the percent of the time the CPU is busy. It is made of two subqueries in a with statement and the final main query.

with
myoscpu as
(select
busy_v.SNAP_ID,
busy_v.VALUE BUSY_TIME,
idle_v.VALUE IDLE_TIME
from 
DBA_HIST_OSSTAT busy_v,
DBA_HIST_OSSTAT idle_v
where
busy_v.SNAP_ID = idle_v.SNAP_ID AND
busy_v.DBID = idle_v.DBID AND
busy_v.INSTANCE_NUMBER = idle_v.INSTANCE_NUMBER AND
busy_v.STAT_NAME = 'BUSY_TIME' AND
idle_v.STAT_NAME = 'IDLE_TIME'),

The myoscpu subquery pulls the CPU busy and idle times from the view along with the snapshot id. I think these are totals since the database last came up, so you have to take the difference between their values at two different points in time to get the CPU usage for that time.

myoscpudiff as
(select
after.SNAP_ID,
(after.BUSY_TIME - before.BUSY_TIME) BUSY_TIME,
(after.IDLE_TIME - before.IDLE_TIME) IDLE_TIME 
from 
myoscpu before,
myoscpu after
where before.SNAP_ID + 1 = after.SNAP_ID
order by before.SNAP_ID)

The myoscpudiff subquery gets the change in busy and idle time between two snapshots. It is built on myoscpu. My assumption is that the snapshots are an hour apart which is the case on the databases I work with.

select 
to_number(to_char(sn.END_INTERVAL_TIME,'D')) day_of_week,
to_number(to_char(sn.END_INTERVAL_TIME,'HH24')) hour_of_day,
100*BUSY_TIME/(BUSY_TIME+IDLE_TIME) pct_busy
from 
myoscpudiff my,
DBA_HIST_SNAPSHOT sn
where 
my.SNAP_ID = sn.SNAP_ID
order by my.SNAP_ID

The final query builds on myoscpudiff to give you the day of the week which ranges from 1 to 7 which is Sunday to Saturday, the hour of the day which ranges from 0 to 23, and the cpu percent busy which ranges from 0 to 100.

import cx_Oracle
...
# run query retrieve all rows

connect_string = username+'/'+password+'@'+database
con = cx_Oracle.connect(connect_string)
cur = con.cursor()

cur.execute(query)

# returned is a list of tuples
# with int and float columns
# day of week,hour of day, and cpu percent

returned = cur.fetchall()
...
cur.close()
con.close()

The cx_Oracle calls are simple database functions. You connect to the database, get a cursor, execute the query and then fetch all the returned rows. Lastly you close the cursor and connection.

print("Data type of returned rows and one row")
print(type(returned))
print(type(returned[0]))

print("Length of list and tuple")
print(len(returned))
print(len(returned[0]))

print("Data types of day of week, hour of day, and cpu percent")
print(type(returned[0][0]))
print(type(returned[0][1]))
print(type(returned[0][2]))

I put in these print statements to show what the data that is returned from fetchall() is like. I want to compare this later to NumPy’s version of the same data. Here is the output:

Data type of returned rows and one row
<class 'list'>
<class 'tuple'>
Length of list and tuple
1024
3
Data types of day of week, hour of day, and cpu percent
<class 'int'>
<class 'int'>
<class 'float'>

The data returned by fetchall() is a regular Python list and each element of that list is a standard Python tuple. The list is 1024 elements long because I have that many snapshots. I have 6 weeks of hourly snapshots. Should be about 6*7*24 = 1008. The tuples have three elements, and they are normal Python number types – int and float. So, cx_Oracle returns database data in standard Python data types – list, tuple, int, float.

So, we are done with cx_Oracle. We pulled in the database metric that we want to graph versus day and hour and now we need to get it ready to put into Matplotlib.

NumPy

NumPy can do efficient manipulation of arrays of data. The main NumPy type, a ndarray, is a multi-dimensional array and there is a lot of things you can do with your data once it is in an ndarray. You could do the equivalent with Python lists and for loops but a NumPy ndarray is much faster with large amounts of data.

import numpy as np
...
# change into numpy array and switch columns
# and rows so there are three rows and many columns
# instead of many rows and three columns

dataarray = np.array(returned).transpose()

# dataarray[0] is day of week
# dataarray[1] is hour of day
# dataarray[2] is cpu percent

The function np.array() converts the list of tuples into a ndarray. The function transpose() switches the rows and columns so we now have 3 rows of data that are 1024 columns long whereas before we had 1024 list elements with size 3 tuples.

I added print statements to show the new types and numbers.

print("Shape of numpy array after converting returned data and transposing rows and columns")
print(dataarray.shape)

print("Data type of transposed and converted database data and of the first row of that data")
print(type(dataarray))
print(type(dataarray[0]))

print("Data type of the first element of each of the three transposed rows.")
print(type(dataarray[0][0]))
print(type(dataarray[1][0]))
print(type(dataarray[2][0]))

Here is its output:

Shape of numpy array after converting returned data and transposing rows and columns
(3, 1024)
Data type of transposed and converted database data and of the first row of that data
<class 'numpy.ndarray'>
<class 'numpy.ndarray'>
Data type of the first element of each of the three transposed rows.
<class 'numpy.float64'>
<class 'numpy.float64'>
<class 'numpy.float64'>

The shape of a ndarray shows the size of each of its dimensions. In this case it is 3 rows 1024 columns as I said. Note that the overall dataarray is a ndarray and any given row is also. So, list and tuple types are replaced by ndarray types. Also, NumPy has its own number types such as numpy.float64 instead of the built in int and float.

Now that our CPU data is in a NumPy array we can easly massage it to the form needed to plot points on our graph.

# do 24 * day of week + hour of day as x axis

xarray = (dataarray[0] * 24) + dataarray[1]

# pull cpu percentage into its own array

yarray = dataarray[2]

My idea for the graph is to combine the day of week and hour of day into the x axis by multiplying day of week by 24 and adding hour of the day to basically get the hours of the week from Sunday midnight to Saturday 11 pm or something like that. The nice thing about NumPy is that you can multiply 24 by the entire row of days of the week and add the entire row of hour of the day all in one statement. xarray is calculated in one line rather than writing a loop and it is done efficiently.

Here are some print statements and their output:

print("Shape of numpy x and y arrays")
print(xarray.shape)
print(yarray.shape)

Shape of numpy x and y arrays
(1024,)
(1024,)

Now we have two length 1024 ndarrays representing the x and y values of the points that we want to plot.

So, we have used NumPy to get the data that we pulled from our Oracle database using cx_Oracle into a form that is ready to be graphed. Matplotlib works closely with NumPy and NumPy has some nice features for manipulating arrays of numbers.

Matplotlib

Now we get to the main thing I want to talk about, which is Matplotlib. Hopefully this is a clean and straightforward example of its use.

import matplotlib.pyplot as plt
...
# get figure and axes

fig, ax = plt.subplots()

First step is to create a figure and axes. A figure is essentially the entire window, and an axes object is an x and y axis that you can graph on. You can have multiple axes on a figure, but for a simple graph like this, you have one figure, and one axes.

# point_size is size of points on the graph

point_size = 5.0
...
# graph the points setting them all to one size

ax.scatter(xarray, yarray, s=point_size)

This actually graphs the points. A scatter plot just puts a circle (or other shape) at the x and y coordinates in the two arrays. I set all points to a certain size and figured out what size circle would look best by trying different values. Note that scatter() is a method of the Axes type object ax.

# add title

ax.set_title(database+" database CPU by day of week and hour of day")

# label the x and y axes

ax.set_xlabel("24 * Day of week (1-7) + Hour of day (0-23)")
ax.set_ylabel("CPU percent used")

More methods on ax. Sets title on top center of graph. Puts labels that describe the x axis and the y axis.

# add vertical red lines for days

for day_of_week in range(8):
    ax.axvline(x=(day_of_week+1)*24, color='red', linestyle='--',linewidth=1.0)

The previous lines are all I really needed to make the graph. But then I thought about making it more readable. As I said before the X axis is basically the hour of the week ranging from 24 to 191. But I thought some red lines marking the beginning and end of each day would make it more readable. This puts 8 lines at locations 24, 48,…,192. I set the linewidth to 1.0 and used the dashes line style to try to keep it from covering up the points. I think axvline means vertical line on Axes object.

# Calculate the y-coordinate for day names
# It should be a fraction of the range between the minimum and maximum Y values
# positioned below the lower bound of the graph.
# The minimum and maximum CPU varies depending on the load on the queried database.

lower_bound = ax.get_ylim()[0]
upper_bound = ax.get_ylim()[1]
yrange = upper_bound - lower_bound
fraction = .025
y_coord = lower_bound - (fraction * yrange)

xloc = 36

for day in ['Sun','Mon','Tue','Wed','Thu','Fri','Sat']:
    ax.text(xloc, y_coord, day, fontsize=8, color='red', ha='center',fontweight='ultralight')
    xloc += 24

I kept messing with the script to try to make it better. I didn’t want to make it too complicated because I wanted to use it as an example in a blog post. But then again, this code shows some of the kinds of details that you can get into. The text() method of ax just puts some text on the graph. I made it red like the dashed lines and tried to make the letters light so they wouldn’t obscure the main parts of the graph. The x coordinates were just the center of the word and essentially the middle of the day. The first day starts at x=24 so 12 hours later or x=36 would be halfway through the day, approximately. I just had a list of the three-character day names and looped through them bumping the x location up by 24 hours for each day.

But the y coordinate was more complicated. I started out just choosing a fixed location for y like -5. For one database this worked fine. Then I tried another database, and it was way off. The reason is that Matplotlib scales the y coordinates based on the graphed data. If your database’s cpu is always around 30% then the range of visible y coordinates will be close to that. If your database’s cpu varies widely from 0% to 100% then Matplotlib will set the scale wide enough so the entire range 0 to 100 is visible. So, to put the text where I wanted it, just below the y axis line, I needed to make it a percentage of the visible y range below the lowest visible value. The get_ylim() method shows the calculated lower and upper bounds of the y axis which were calculated based on the y values of the graphed points. I manually messed with the value for the variable fraction until it looked right on the screen. Then I ran the script with a variety of databases to make sure it looked right on all of them.

# show graph

plt.show()

Lastly you just show the graph. Note that like the subplots() call this is not a method of an axes or figure object but just a matplotlib.pyplot call. Everything else in this example is a call to a method of the ax Axes type object.

Conclusion

This post shows how to graph Oracle database data using Python libraries cx_Oracle, NumPy, and Matplotlib. It first shows how to pull Oracle data into Python’s native data structures like lists and tuples. Then it shows how to convert the data into NumPy’s ndarrays and manipulate the data so that it can be graphed. Lastly it shows how to use Matplotlib Axes object methods to graph the data and add useful elements to the graph such as labels, vertical lines, and text.

This is a simple example, and all the software involved is free, open-source, widely used, easy to install, and well-documented. Give it a try!

I am not a very good blogger. Only four posts in 2024. I will need to pick it up because I just increased my spend on AWS for this blog. This site kept going down and I finally spent a few minutes looking at it and found that it was running out of memory. I was running on a minimal t2.micro EC2 which has 1 virtual CPU and 1 gigabyte of memory with no swap. So, rather than just add swap I bumped it up to a t3.small and paid for a 3-year reserved instance. It was about $220, nothing outrageous. Worth it to me. I still have two years left on the t2.micro reserved instance that I was using for the blog so I need to find a use for it, but I don’t regret upgrading. I spent a little money to make the site more capable so now I must write more posts!

I have three Machine Learning books to work through. After finishing my edX ML class with Python I picked up a book that covered the same topics and used the same Python libraries and I have been steadily working through it: Machine Learning with PyTorch and Scikit-Learn. I am on chapter 9 and want to get up to chapter 16 which covers Transformers. My edX class got up to the material in chapter 15 so the book would add to what was taught in the class. Earlier chapters also expand on what was taught in the class. The cool thing about the book is the included code. Lots of nice code examples to refer to later. Even the Matplotlib code for the graphs could be very helpful to me. I’ve gotten away from using Matplotlib after using it in my PythonDBAGraphs scripts.

My birthday is the day after Christmas, so I get all my presents for the year at the end of December. I got two ML books for Christmas/Birthday. Probably the first I will dive into after I finish the PyTorch/Scikit-Learn book is Natural Language Processing with Python. This is available for free on the NLTK web site. I have the original printed book. I think once I get through the Transformers chapter of the PyTorch book it makes sense to look at natural language processing since that is what ChatGPT and such is all about. I have played with some of this already, but I like the idea of working through these books.

The second book that I got in December as a present seems more technical and math related, although the author claims to have kept the math to a minimum. It is Pattern Recognition and Machine-Learning. This might be a slower read. It seems to be available for free as a PDF. ChatGPT recommended this and the NLTK book when I was chatting with it about my desire to learn more about Artificial Intelligence and Machine Learning.

I have all these conversations with ChatGPT about whether it makes sense for me as an Oracle database specialist to learn about machine learning. It assures me that I should, but it might be biased in favor of ML. Oracle 23ai does have AI in its name and does have some machine learning features. But it could just be a fad that blows over after the AI bubble bursts as many have before it. I can’t predict that. My current job title is “Technical Architect”. I work on a DBA team and I’m in the on-call rotation like everyone else, but my role includes learning about new technology. Plus, I think that I personally add value because of some of the computer science background I had in school and that I have been refreshing in recent years. Plus, I need to get some level of understanding of machine learning for my own understanding regardless of how much we do or don’t use it for my job. Just being a citizen of the world with a computer science orientation I feel is enough motivation to get up to speed on recent AI advancements. Am I misguided to think I should study machine learning?

Despite all this talk about AI, I am still interested in databases. I have this folder on my laptop called “Limits of SQL Optimization”. I had these grandiose ideas about writing interesting blog posts about what SQL could or couldn’t do without human intervention. Maybe SQL is a little like AI because the optimizer does what a human programmer would have to do without it. I’m interested in it all really. I like learning about how computer things work. I’ve spent my career so far working with SQL statements and trying to understand how the database system processes them. I thought about playing with MySQL’s source code. I downloaded it and compiled it but that’s about it. Plus, some day we will have Oracle 23ai and I’ll have to figure out how to support it in our environment, even if we do not use its AI features. Anyway, I’m sure I will have some non-AI database things to post about here.

To wrap up I think I may have some things to post on this blog in 2025, so it’s worth the extra expense to keep it running. Could be some more machine learning/artificial intelligence coming as I work through my books. I still have databases on the brain. Wish you all a great new year.

Bobby