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 220.127.116.11 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 and hint have something to do with the costs of accessing compressed data by rowid on an Exadata system (see, for example, this posting) and it’s very easy to check what the hint does:
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 18.104.22.168 (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 would start at the original table block and have to follow an ever growing chain of pointers to reach the data.
This is not correct, and it’s worth making a little fuss about the error since it’s the sort of thing that can easily become an urban legend that results in people rebuilding tables “for performance” when they don’t need to.
Oracle behaves quite intelligently with migrated rows. First, the migrated row has a pointer back to the original location and if the row has to migrate a second time the first place that Oracle checks for space is the original block, so the row might “de-migrate” itself; however, even if it can’t migrate back to the original block, it will still revisit the original block to change the pointer in that block to refer to the block it has moved on to – so the row is never more than one step away from its original location. As a quick demonstration, here’s some code to generate and manipulate some data:
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 have amazingly different performance characteristics; and in this particular case I can see an obvious reason why the two nodes could have different plans.
Here’s the query reported in the bug:
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.
Here’s a little script to demonstrate an observation about a missed opportunity for avoiding work that appeared in my email this morning (that’s morning Denver time):
I’ll be buying the tickets for my flight to Seattle and Kaleidoscope 14 some time tomorrow. The cut-off date on my credit card bill is today, so if I get the tickets tomorrow I won’t have to pay for them until the end of March.
When you know you have to pay it’s worth thinking about when you have to pay. It’s a principle that works in Oracle databases, too.
On the flip-side – sometimes you don’t realise that the clever thing you’ve done now is going to make someone else pay later.
To create an index on a table (with no existing indexes) Oracle has to start by doing a tablescan.
What’s the difference between the tablescan it uses for a B-tree index and the tablescan it uses for a bitmap index ? Why ?
I was going to give a hint that if you answered the “why” first that might lead you to the right idea and a test for the “what”, but we already have an answer, with a sample of proof.
It doesn’t matter which bit of Oracle technology you want to use, eventually someone, somewhere, runs into the special case where something nasty happens. Here’s an edge case for people using (index) clusters – Oracle Bug 17866999 ora-1499 for cluster following rman convert
It comes from a conversation on Oracle-L where Jack van Zanen reported a problem of inconsistent results after migrating data between platforms using rman to converts some tablespaces containing index clusters. This is the starting post where he shows a query that is clearly getting the wrong answer (select where channel_number = 503 obviously shouldn’t return data with channel_number 501).
Sitting in the lounge waiting to be called for my flight I was musing on the 12c feature of having multiple indexes defined on the same ordered column set when a thought crossed my mind and I decided to run a little test that looked like this:
create table t1 as select * from all_objects where rownum <= 10000;
create unique index t1_pk on t1(object_id);
alter table t1 add constraint t1_pk primary key(object_id);
create index t1_i1 on t1(object_id, object_name);
drop index t1_pk;
expect ORA-02429: cannot drop index used for enforcement of unique/primary key
alter table t1 modify primary key using index t1_i1;
drop index t1_pk;
For years I’ve been assuming that you really have to mess around with the PK (and any related FKs) if you want to change the index supporting the primary key – but this code demonstrates that you can add a new index to a table and “move” the primary key to it before dropping the original index.
The worrying thing about this (for me, at any rate) is that it isn’t a new feature – after testing it on 22.214.171.124 I started working backwards, and it works down to 126.96.36.199 (the earlist 9i I have access to). It doesn’t work on 188.8.131.52, and the 184.108.40.206 version behaves slightly differently from later versions because the original PK index disappears as the constraint is moved.
As I’ve often said about trust – keep an eye on the date and version of any article you read, it may no longer be true.
A recent “Hot topics” email from Oracle support listed the following bug as one which had recently been updated:
17727676 OPTIMIZER HINT IGNORED WHEN USING INVISIBLE INDEXES
Since the optimizer is one of my pet topics I thought I’d take a quick look at what it said – and found this heart-warming introduction;
Hdr: 17727676 220.127.116.11.0 RDBMS 18.104.22.168.0 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: OPTIMIZER HINT IGNORED WHEN USING INVISIBLE INDEXES
*** 11/03/13 03:46 am ***
Based on a blog article from the international recognized Oracle Expert Jonathan Lewis ...
If it’s on MoS surely it’s just got to be true! (Yes, I know I’ve said the opposite in the past – but it’s definitely right some of the time)
The bug/blog in question was this one, and the problem is fixed in 12.2
I was involved in a thread on Oracle-L recently started with the question: “How many LIOs is too many LIOs”. Rather than rewrite the whole story, I’ve supplied a list of links to the contributions I made, in order – the final “answer” is actually the answer to a different question – but travels an interesting path to get there.#
I’ve got a script to emulate the requirement so that people can see for themselves the bug that I mention in post 15; I’ll try to add a couple of notes to it and publish it some time, but for the moment I’ll just remind myself that it’s called (slightly counter-intuitively: no_sort_problem.sql)
Here’s a recent request from the OTN database forum – how do you make this query go faster (tkprof output supplied):
from a, b
where A.MARK IS NULL
and a.cntry_code = b.cntry_code and b.dir_code='XX' and b.numb_type='XXX'
and upper(Trim(replace(replace(replace(replace(replace(replace(replace(a.co_name,'*'),'&'),'-'),'/'),')'),'('),' '))) like
Here’s a little detail I could do without in my database:
owner, object_type, object_name
object_name like '_'
OWNER OBJECT_TYPE OBJECT_NAME
--------------- ------------------- --------------------
APEX_030200 PROCEDURE F
PUBLIC SYNONYM F
APEX_030200 PROCEDURE G
APEX_030200 PROCEDURE P
PUBLIC SYNONYM P
APEX_030200 FUNCTION V
PUBLIC SYNONYM V
APEX_030200 PROCEDURE Z
PUBLIC SYNONYM Z
9 rows selected.
Public names like P and F for procedures or functions are just not on (unless I create them myself).
If you know anything about bitmap indexes you probably know that a single entry in a bitmap index takes the form (key_value, starting rowid, ending rowid, BBC compressed bit string). So an entry covers a single value for a column over a range of rowids in the table, and the string of bits for that (notional) range is reduce to a minimum by a compression mechanism that eliminate repeated zeros in multiples of 8.
So here’s a question – to which I don’t know the answer, although you may be surprised when you try to find it:
If you have a very large table and in one of its columns the first row and the last row (and no others) hold the value 0 (say) and you create a bitmap index on this column, what’s the largest number of rows you could have in the table before Oracle would HAVE to create two index entries in order to cover both rows ?
Follow-up question – once you start getting close to working out the answer, can you think of a way to provide an example without actually creating a table with that many rows in it ?