Author | Lightnews

Jonathan Lewis

@jloracle.bsky.social

240 followers 46 following 360 posts

Nearly-retired Oracle performance specialist. 50 years using computers, 40 years self-employed, 35 years as a freelance Oracle specialist.

Posts Media Videos Starter Packs

Pinned

Jonathan Lewis @jloracle.bsky.social · Nov 21

Copying from ex-Twitter, and pinning:

The demon subdomain, including my old website disappeared a while ago, but there is a copy from December 2018 on the Wayback machine starting at: t.co/D9qBXqwJTu

https://web.archive.org/web/20181213051606/http://www.jlcomp.demon.co.uk/

t.co

2 5

Reposted by Jonathan Lewis

Dani Schnider @danischnider.bsky.social · 1d

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

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…

danischnider.wordpress.com

1 3 8

Jonathan Lewis @jloracle.bsky.social · 2d

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

1 2

Reposted by Jonathan Lewis

Martin Bach @martindba.bsky.social · 4d

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

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…

martincarstenbach.com

1 3

Jonathan Lewis @jloracle.bsky.social · 4d

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!

1 1 1

Jonathan Lewis @jloracle.bsky.social · 9d

But will it be a deep dive session?

2 3

Jonathan Lewis @jloracle.bsky.social · 10d

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

1 1

Jonathan Lewis @jloracle.bsky.social · 11d

youtu.be/v1dvAxA9ib0

Flanders & Swann: The Gas Man Cometh

YouTube video by Nancy DeHaven

youtu.be

1 1

Jonathan Lewis @jloracle.bsky.social · 15d

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

Jonathan Lewis @jloracle.bsky.social · 15d

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

1 4

Jonathan Lewis @jloracle.bsky.social · 15d

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

Jonathan Lewis @jloracle.bsky.social · 16d

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.

Reposted by Jonathan Lewis

SQLDaily @sqldaily.bsky.social · 17d

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

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…

buff.ly

4 8

Reposted by Jonathan Lewis

Piotr Wrzosek @pewu78.bsky.social · 17d

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?🤦‍♂️

Piotr Wrzosek @pewu78.bsky.social · Jul 1

ORA-600/ORA-7445 Troubleshooting tools on MOS are not working for me - at least since yesterday.

1 2

Jonathan Lewis @jloracle.bsky.social · 18d

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"

Jonathan Lewis @jloracle.bsky.social · 18d

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

1 4

Jonathan Lewis @jloracle.bsky.social · 22d

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"

1 3

Jonathan Lewis @jloracle.bsky.social · 23d

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)

Jonathan Lewis @jloracle.bsky.social · 23d

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

Jonathan Lewis @jloracle.bsky.social · 24d

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"

Jonathan Lewis @jloracle.bsky.social · 26d

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

Jonathan Lewis @jloracle.bsky.social · 26d

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

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…

jonathanlewis.wordpress.com

Jonathan Lewis @jloracle.bsky.social · 26d

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.

1 4

Jonathan Lewis @jloracle.bsky.social · 27d

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

1 1

Jonathan Lewis @jloracle.bsky.social · 28d

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 :(

Jonathan Lewis @jloracle.bsky.social · 29d

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)