I’ve often found in my travels that I’ve come up with a (potential) solution to a problem and wanted to test it “right now” – only to run onto the horns of a dilemma. A typical client offers me one of two options:
- Option 1: test it on the production system – which is generally frowned on, and sometimes I can’t even get access to the production system anyway.
- Option 2: test it on something that looks (more...)
Prompted by a question on OTN I came up with a strategy for producing an ORA-00060 deadlock that DIDN’T produce a deadlock graph (because there isn’t one) and didn’t get reported in the alert log (at least, not when tested on 220.127.116.11). It’s a situation that shouldn’t arise in a production system because it’s doing the sorts of things that you shouldn’t do in a production system: but possibly if you’re trying (more...)
I have a table with several indexes on it, and I have two versions of a query that I might run against that table. Examine them carefully, then come up with some plausible reason why it’s possible (with no intervening DDL, DML, stats collection, parameter fiddling etc., etc., etc.) for the second form of the query to be inherently more efficient than the first.
bit_1, id, small_vc
Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM). It’s the ASSM that causes the problem, but it requires a mixture of circumstances to create a little surprise.
datafile -- OMF
This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
trunc (ts,'DD') ts1, sum(fieldb) fieldb
ts, max(fieldb) fieldb
select trunc(sysdate) - 1/24 ts, 1 fieldb from dual
select trunc(sysdate) - 2/24 ts, 2 fieldb from dual
select trunc(sysdate) - 3/24 ts, 3 fieldb from dual
One response to my series on reading execution plans was an email request asking me to clarify what I meant by the “order of operation” of the lines of an execution plan. Looking through the set of articles I’d written I realised that I hadn’t made any sort of formal declaration of what I meant, all I had was a passing reference in the introduction to part 4; so here’s the explanation.
By “order of operation” (more...)
A comment on one of my early blogs about the 12c in-memory database option asked how Oracle would deal with read-consistency. I came up with a couple of comments outlining the sort of thing I would look for in a solution, and this note is an outline on how I started to tackle the question – with a couple of the subsequent observations. The data is (nearly) the same as the data I generated for my previous article on the (more...)
The title of this piece is the name given to a new feature in 18.104.22.168, and since I’ve recently blogged about a limitation of the in-memory option I thought I’d pick this feature as the next obvious thing to blog about. This is a bit of a non sequitur, though, as the feature seems to have nothing whatsoever to do with the in-memory option; instead it’s a cunning mechanism combining aspects of the star-transformation (but without the bitmap indexes), (more...)
Here’s a script to create a table, with index, and collect stats on it. Once I’ve collected stats I’ve checked the execution plan to discover that a hint has been ignored (for a well-known reason):
create table t2
rownum <= 3000
create index t2_i1 on t2(n1);
method_opt => 'for all columns size 1'
explain plan for
This is the index to a series of articles I’ve been writing for redgate, published on their AllThingsOracle site, about generating and reading execution plans. I’ve completed a few articles that haven’t yet been published, but I’ll add their URLs when they’re available.
I don’t really know how many parts it’s going to end up as – there’s an awful lot that that you could say about reading execution plans, even when you’re trying to cover just (more...)