Jonathan Lewis

QUALIFY: The WHERE Condition for Analytic Functions Filtering analytic functions was not straight-forward in Oracle and most other databases. Oracle 26ai now supports the QUALIFY clause in SQL. It works like a WHERE condition, but can be used for an…

Since last week, I can finally use the QUALIFY clause in Oracle. If you never heard of this SQL extension, read my newest blog post:

danischnider.wordpress.com/2025/10/24/q...

Umbrellas - to help you avoid the side effects of clouds?
And it looks like you've got the multi-cloud option there.

SQLcl autotrace is way more than “just” (SQL*Plus) autotrace As part of the research I did for the 2nd edition of our Exadata book I used session statistics quite heavily. Session statistics can provide additional insights in situations where the wait interf…

Today I revisited a 4yo post on one of SQLcl’s underrated features: autotrace — shows full session stats for a SQL statement (way beyond SQL*Plus). Still works in latest release. Updated prereqs too. More details here
🔗 martincarstenbach.com/2021/06/10/s...
#Oracle #SQLcl #performance #IKYK

Coincidentally I've been looking at hybrid histograms in the last couple of days and started sketching some notes about the factors involved in calculating join cardinalities for two tables with four join predicates and a couple of non-join predicates.

Then I realised how much work that would be!

But will it be a deep dive session?

Unfortunately the OVM version is only 23.9 - with all the Developer/ORDS/etc. stuff - not a "23.26"

YouTube video by Nancy DeHaven Flanders & Swann: The Gas Man Cometh

youtu.be/v1dvAxA9ib0

And now with an update (already!) describing a minor change when switching to system managed (autoallocate) extent sizes:

jonathanlewis.wordpress.com/2025/10/10/m...

A new post about parallel DML and how to waste huge amounts of space in your database in no time at all.

jonathanlewis.wordpress.com/2025/10/10/m...

If you're in the mood to read a long and detailed article about shrinking indexes, here's one from 2022 that should meet your requirements:

jonathanlewis.wordpress.com/2022/09/02/s...

Here's a note I wrote for redgate / simpletalk a few years ago about basic index compression - costs and benefits: www.red-gate.com/simple-talk/...

It's part 5 of a series; part 4 was the technical bit about the mechanics of (basic) index compression. There's a link to it at the start of part 5.

10 Common Mistakes Java Developers Make when Writing SQL This article is part of a series. You might also like: 10 More Common Mistakes Java Developers Make when Writing SQLYet Another 10 Common Mistakes Java Developers Make When Writing SQL Java develop…

Are you making any of these common #SQL mistakes?

Forgetting about NULL
Processing data in Java memory
Using JDBC Pagination to paginate large results
Using aggregate instead of window functions

@lukaseder.bsky.social explains what to do instead & lists 7 more

Looks like it's not available anymore, my SR regarding this was closed with something like "Internal bug was submitted for investigation, but hey, we got that alternate features "Look-up By Error or Error Code's First Argument" or "Search by Stack Trace" added which replace it.
For real?🤦‍♂️

This forum thread might be useful if you're playing around with 23ai and ONNX models (whatever they are) that don't come from Oracle Corp.
forums.oracle.com/ords/apexds/... - particularly the comment from "Peter"

I'm on the agenda for the #ukoug conference 2025.
9:00 a.m. on Monday morning (I tell myself that that's to encourage people to be on-site early enough to attend the opening keynote afterwards)

Abstract at:
jonathanlewis.wordpress.com/public-appea...

How did you find a cat that looks so much like you?

Or is that an AI: "Create a video of a cat that looks like Dr. Tim Hall at a keyboard"

2/2
Image 1
The "TP" activity is running on the opposite node to "Low" and "TPUrgent"
Image 2
Low is on the same node as TP, TPUrgent is on the opposite node so Low and TP are competing locally for the same buffered blocks instead of cross instance.

(Guesswork, of course, but reasonably likely)

1/2
I thought it was a quiz to which you knew the answer rather than a request for suggestions. The problem I have with pictures like this is that there may, or may not, be information in the positioning of the blocks. Assuming that position is significant the most obvious suggestion is that ...

Cheat's guess: based on the categories of the 4 sections in each image you changed the transaction priority of some jobs.

Alternatively, you associated some jobs with specific nodes to reduce cross-instance buffer busy waits, giving you far more LOCAL BBW for "Medium"

A variant of this topic just appeared on one of the Oracle forums - what do you get for the rowid in a join view where you haven't explicitly selected any rowids? (In my case - which table gets the delete when you delete from a join view)

jonathanlewis.wordpress.com/2022/05/31/j...

rowlen surprise Here’s a little detail about Oracle’s calculation of user_tables.avg_row_len that I hadn’t noticed before – and I really should have noticed it years ago, so maybe I’m…

There are two ways I suprise myself when thinking about Oracle puzzles:

1) Struggling to unravel some internal detail then finding that I wrote about it 20 years ago

2) Discovering a "new" detail I should have noticed 20 years

Here's an example of (2):
jonathanlewis.wordpress.com/2025/09/29/r...

There's a problem on the oracle-l listserver at present about an insert taking far too much time (and CPU). It's a known issue and there are 47 statistics in v$sysstat (19.11) with names like 'ASSM%' to help diagnose it.

How many do you think are described in the database reference manual?

None.

The Bay CIty Rollators
9cc
Fairport SIG
The Dave Clark Four

Downloaded a recent SQL Developer earlier on today - Windows 64bit version - is it supposed to have a Cylon eye scanner going on all the time in the bottom right corner?!

Couldn't find the "run as sqlcl" option, then checked your tweets and found that I now need to download the VS stuff as well :(

A question on the Oracle db discusion forum asked for advice on using "alter index .... monitoring usage" in 19c. The correct advice is "don't use it, Oracle replaced it with 'index usage tracking' in 12.2

Pt 1 of 3: jonathanlewis.wordpress.com/2024/03/15/i...

(links to pts 2 & 3 in the pingbacks)

I have heard that capsules of omega-3 plus omega-6 can help - though catching, cooking and eating your own "oily" fish might be more fun.

But my problem is stopping after 60 minutes - and I'll bet I'm not the only one.

Definitely an important detail; it could pre-empt the occasional surprise - particularly if you've:

set sqlblanklines on

Note (from SQL*Plus):
select user from dual
-- leave this line empty
/

select user from dual
-- leave this line empty
;

The first reports the user, the second echoes the query
That's not a proof of the previous comment, just an observation that ";" and "/" are supposed to have different effects.

Could that be anything to do with the difference between using VS Code vs. not?

Technically (for SQL*Plus) ";" is a separator and "/" is an execute, it is (was, a long time ago) just an SQL*Plus convention that ";" appearing at the end of a line was treated as if it indicated an execute.

Ah yes, I remember it well. (h/t Maurice Chevalier)
Hall 5, Bouncer Chandler, and Stirrer Widlake pretending someone had handed in a question about rebuilding indexes and setting optimizer_index_cost_adj.