This is possibly my longest title to date – I try to keep them short enough to fit the right hand column of the blog without wrapping – but I couldn’t think of a good way to shorten it (Personally I prefer to use the expression CTE – common table expression – over “factored subquery” […]
Author Archives Jonathan Lewis
Link to previous example of expensive due to virtual columns and auto_sample_size : http://jonathanlewis.wordpress.com/2013/09/27/virtual-stats/ OTN example: https://community.oracle.com/thread/2620088 CREATE BITMAP INDEX “QQRCSBI0″.”I_S_RMP_EVAL_CSC_MSG_ACTIONS” ON “QQRCSBI0″.”S_RMP_EVALUATION_CSC_MESSAGE” (DECODE(INSTR(“XML_MESSAGE_TEXT”,”),0,0,1)) IOTs – the tablescan is a fast full scan of the index with a jump to the overflow – so one lio per row first, possibly lots of reads. Won’t get […]
From time to time I’ve posted a reminder that subquery factoring (“with subquery”) can give you changes in execution plans even if the subquery that you’ve taken out of line is written back inline by Oracle rather than being materialized. This can still happen in 12c – here’s a sample query in the two forms […]
Here’s a little example of why you should be very cautious about implementing undocumented discoveries. If you take a look at the view v$sql_hints in 18.104.22.168 you’ll discover a hint (no_)cluster_by_rowid; and if you look in v$parameter you’ll discover two new parameters _optimizer_cluster_by_rowid and _optimizer_cluster_by_rowid_control. It doesn’t take much imagination to guess that the parameters […]
A warning on Oracle-L from Chris Dunscombe: If you’ve got a large stats history – with lots of histogram data – then the upgrade could take an unexpectedly long time. Presumably the same is true if you upgrade from 22.214.171.124 (or earlier) to 12c.
At one of the presentations I attended at RMOUG this year the presenter claimed that if a row kept increasing in size and had to migrate from block to block as a consequence then each migration of that row would leave a pointer in the previous block so that an indexed access to the row […]
Recently appeared on Mos – “Bug 18219084 : DIFFERENT EXECUTION PLAN ACROSS RAC INSTANCES” Now, I’m not going to claim that the following applies to this particular case – but it’s perfectly reasonable to expect to see different plans for the same query on RAC, and it’s perfectly possible for the two different plans to […]
I’ve just added a catalogue of Richard Foote’s articles on IOTs to the list I made a couple of years ago of Martin Widlake’s articles, so this is just a temporary note to point people to the updated list.
Here’s a simple little demonstration of an enhancement to the optimizer in 12c that may result in some interesting changes in execution plans as cardinality estimates change from “guesses” to accurate estimates. Since there are no indexes in sight the execution plan has to be a tablescan. The interesting thing, though, is the optimizer’s prediction […]