LightNews
jloracle.bsky.social
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
jloracle.bsky.social
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 1d
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...
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 1d
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 3
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 1d
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...
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 2d
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.
2
Reposted by Jonathan Lewis
sqldaily.bsky.social
SQLDaily @sqldaily.bsky.social · 3d
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 7
Reposted by Jonathan Lewis
pewu78.bsky.social
Piotr Wrzosek @pewu78.bsky.social · 3d
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?🤦‍♂️
pewu78.bsky.social
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 4d
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"
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 4d
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 8d
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 10d
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 10d
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 ...
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 10d
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"
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 12d
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...
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 12d
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
2
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 13d
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
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 14d
The Bay CIty Rollators
9cc
Fairport SIG
The Dave Clark Four
1 1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 15d
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 :(
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 15d
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)
3
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 15d
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.
1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 15d
Definitely an important detail; it could pre-empt the occasional surprise - particularly if you've:

set sqlblanklines on
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 16d
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.
1 1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 16d
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.
1 1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 16d
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.
1 1
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 16d
Maybe that's a precision wording thing from @tmmdv.bsky.social - perhaps the intention was "more than one statement using bind variables / that bind variable". The red box contains just one statement with a bind variable.
2
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · 16d
Maybe not a good spectator sport; but we had round-table sessions about the CBO at UKOUG that worked quite well (sometimes) with (e.g.) Chris Antognini, me, @sqlmaria.com and @vldbb.bsky.social answering questions from the audience and finding that discussion could build interesting "group answers".
1 3