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

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.

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)

Normally yes. If it asks for a bind value when running this then it uses varchar2 and causes the issue. It looks as if this happens only if you try to Run Script with a single SQL. Once there is more than 1 statement, it doesn't ask for bind and all is good.

TIL the SQL*Plus commands "document" and "set document on/off" became obsolete in 10.2 (But they still work, though go a bit funny in sqlcl)

If this treatment for Huntington's disease works out, this is going to be huge for those affected and their families. It’s another example of the legacy of the Human Genome project and the growing field of gene therapy. www.bbc.co.uk/news/article...

Blog post for September 2025 SQL Developer isn't entirely CBO-friendly. jonathanlewis.wordpress.com/2025/09/24/s...

Here's a fairly recent little note on parallel execution that came to mind while looking at a short list of parameter that had been supplied with a question on the oracle-l listserver about the limits of parallelism: jonathanlewis.wordpress.com/2022/09/09/p...

I'll be presenting at the UKOUG conference in Birmingham this year: jonathanlewis.wordpress.com/public-appea...

Just in from the MOSC forums: From 18th Sept Oracle will allow only corporate email addresses to be linked forum accounts - so it looks like I won’t be getting any more ‘most valued contributor’ awards.

Busy stewing up a large volume of apple and blackberry sauce. Apples from the orchard (i.e. back garden) and blackberries from the allotment.

There's a current thread on the MOS Forums (needs an account) suggesting that the 19.27 RU has introduced a bug that means the delete of expired auto_stats_advisor_tasks isn't doing a complete job. (Two separate sites discussing symptoms and workarounds). community.oracle.com/mosc/discuss...

Took a number of cuttings off a very healthy sage bush a couple of weeks ago and stuck them in a jam-jar of water. I've just potted up 7 which had grown a good set of roots, discarded 2 which had got nowhere, and left 2 in the jar to grow more roots - cutting back some of their leaves to help.

Went to @monikalewandowska.bsky.social presentation about nvl() at the #POUG2025 conference and had a conversation about an unexpected cardinality estimate - which didn't reproduce in my version of Oracle - but prompted me to revisit an ancient blog note: jonathanlewis.wordpress.com/2007/01/09/c...

How to open a Champagne bottle with an axe: leave it to someone else and make sure you’re standing far behind them

Reading this article reminded me that it was about time I checked a few examples of (old) concatenation / (new) "or expansion". One odd detail of the new feature is that it can't find a suitable predicate for expansion when a hint to use_concat() can jonathanlewis.wordpress.com/2023/01/30/l...

Sometimes it's just too hard to say no. I've just submitted an abstract for the UKOUG 2025 conference in Birmingham. (Nov 30 - Dec 2) The abstract is the same that I supplied for the Polish OUG 2025 (4th - 6th Sept) jonathanlewis.wordpress.com/public-appea...

There can be few things more satisfying to the gardener/allotmenteer than stepping out into the orchard (min 5 trees with no more than 20m between crown edges) to pick your own apples for the pot. Maybe picking oranges in your own Orangery - but mine isn't one of the stately homes of England.

Finished the draft of my presentation for #poug2025. Now have a week to cut it down from about 6 hours to something a lot nearer 40 minutes and polish (no pun intended) what's left.

Automatic error mitigation has arrived! BASIC idea: if the optimizer hits an ORA-00600 it looks for ways to bypass the code path that allowed the error to appear, generating a set of hints (like a SQL directive) to block the problem And it happens in real-time - so you don't know it was needed!

A long time ago I eliminated a lot of work from a batch update by pointing out that setting a flag to zero for 5M rows was a huge waste when all but a couple of hundred were already zero. Adding "where flag != 0" was a simple and (after careful checking) appropriate improvement to the code. But ...

Here's a question I posed a few years ago, and got a lot of interesting answers in the comments. Entertaining reading: jonathanlewis.wordpress.com/2009/11/12/n...

That's one of the little details I regularly fall back to re-read. I highly recommend it to everyone who ever think about object statistics further than #of-rows and ndv....

A question that's just appeared on the Oracle forums made me wonder how well known the table_cached_blocks preference for collecting object statistics was. If you don't know what it is then you need to read the 2nd half of this tip I wrote for IOUG 2015: jonathanlewis.wordpress.com/2021/09/20/o...

I've just (re-)discovered the 5 minute interview with Tanel Poder for the Virtual Conference he set up in 2020. I found it quite interesting to listen to - it makes a couple of interesting point in response to his leading question: www.youtube.com/watch?v=QQP7...

If you're using, or thinking of using, interval partitioning then you need to check whether this performance problem with bulk inserts it likely to be a problem: jonathanlewis.wordpress.com/2018/02/21/i... Last tested 19.11 and 23.6

Are you thinking about using "partition by reference", you might want to read this note about the hidden costs before you make your final decision: jonathanlewis.wordpress.com/2018/03/19/r...

I've just been reading the Gresham college calendar for the next year and listed 20 lectures that I plan to attend (and a couple more that I'll have to watch online). www.gresham.ac.uk/whats-on?see...

The fallacy of not gathering stats to keep execution plans from changing came up again, so I blogged about it blogs.oracle.com/optimizer/po...

I've just been prompted to glance through this "case study" that I wrote a few years ago explaining how to read through a fairly lengthy execution plan for a slightly complex query: jonathanlewis.wordpress.com/2020/05/05/e...

Interesting (AI?) choices: "Based on your purchase of a battery charger you may also be interested in ...." A dustbin A bottle opener Compostable food waste bags Various things that use batteries ... Batteries Got there in the end; but no thanks the charger comes with its own batteries.

I've just been prompted to glance through this "case study" that I wrote a few years ago explaining how to read through a fairly lengthy execution plan for a slightly complex query: jonathanlewis.wordpress.com/2020/05/05/e...

Are other users in the UK seeing poor response times from forums.oracle.com and community.oracle.com ? Both have seemed to respond very slowly for the last couple of days.

Here's an old note of mine addressing the question of procedural code that (probably redundantly) checks if relevant rows exist before starting to process those rows: jonathanlewis.wordpress.com/2015/07/29/e...

Here's an old note of mine addressing the question of procedural code that (probably redundantly) checks if relevant rows exist before starting to process those rows: jonathanlewis.wordpress.com/2015/07/29/e...

A new post about a defect / limitation in the optimizer's handling of bind variables that turns out to have an important impact on the usefulness of the "offset / fetch next" syntax for pagination: jonathanlewis.wordpress.com/2025/08/01/b...

Golden oldie. This is an absolute gem from an old client site explaining why you shouldn't limit yourself to optimising a query to death without considering what else might be going on at the same time: jonathanlewis.wordpress.com/2010/07/30/s...

The "epoch" function got better by turning it into an SQL-Macro. simpleoracledba.blogspot.com/2025/07/orac... With Thanks to Erik, Jonathan and Priit for their remarks. /cc @evrocs.bsky.social @jloracle.bsky.social @ppiipuu.bsky.social @oracleace.bsky.social

If you're still using basicfile LOBs in 19c or later you shouldn't be (though it may be hard to find the time to convert a large LOB holding table to use securefile) If you're seeing wasted time or space with b.f. LOBs then you need to read this series: jonathanlewis.wordpress.com/2017/01/26/b...

Is oracle.forums.com slow at present, or is it just me (and 2 different networks)?

Reading a thread about SQL and probability in poker (Texas hold'em specifically) on the Oracle database discussion forum I was reminded of a couple of posts I made about "swapping join inputs" in hash joins: jonathanlewis.wordpress.com/2010/12/10/q... jonathanlewis.wordpress.com/2020/12/08/h...

Bugfix 22567457: 11 years after I wrote about the problem, and 9 years after a related bug appeared on MOS, the change to make SMON_SCN_TIME a simple heap table rather than a clustered table has appeared in an update dated April 2014. jonathanlewis.wordpress.com/2014/03/03/f...

Golden Oldie: an example of a common error when testing a production SQL statement to optimize its execution plan: jonathanlewis.wordpress.com/2010/10/31/c...

Here's a golden oldie for those who use Java in the database and still expect to get useful timing information from ASH. "You can't always get what you want." (h/t Mick Jagger) jonathanlewis.wordpress.com/2019/12/18/w...

If a letter from an investment company says: "under the new calculation, the ongoing charges for most customers will be lower" what percentage of customers would you set as a reasonable boundary for "most"? I'd say the emotional overtone was trying to build an image of 98% or greater. Reality ... ?

Transaction priority (23ai): here's a detail that's easy to miss: priority_txns_high_wait_target has been set to 10 session 1 sets txn_priority = high session 1 executes "update t1 set n2 = n2 + 1 where id = 1" but the row is locked by session 2 Why would session 1 end up waiting 40 seconds?

Just prompted to post this threat warning: trying to find the best option is fraught with side effects you might miss (the "problem" is still present in 23.6: jonathanlewis.wordpress.com/2020/11/03/f...

If you're keen on cricket or real-world mathematical insights then you might enjoy this short video from the Oxford Mathemetical Institute: www.youtube.com/shorts/b3nvo...

The neighours are calling: www.esa.int/Space_Safety...