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 126.96.36.199 I started working backwards, and it works down to 188.8.131.52 (the earlist 9i I have access to). It doesn’t work on 184.108.40.206, and the 220.127.116.11 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 18.104.22.168.0 RDBMS 22.214.171.124.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 ?
When a “cache read” tablescan (or index fast full scan) takes place we generally expect to see waits on “db file scattered read” as Oracle performs multi-block reads to do the scan. But we all know that Oracle will skip over blocks that are already in the cache and can therefore end up doing multi-block reads of many different sizes, even down to the point where it does single block reads (waiting for “db file sequential read”).
A quirky little question came up on OTN a little while ago: “for a large table we expect multiblock reads to be positioned at the end of the LRU for prompt re-use; but when Oracle does a single block read as part of a tablescan does it go to the end of the LRU (because it’s part of a large tablescan) or does it go to the mid-point of the LRU (because it’s a single block read)?”
The description of how blocks are treated in a tablescan has been simplified, of course, but the question is still valid – so what’s the answer, and how (without going into an extreme level of detail) would you demonstrate it ?
I see Tanel has just started a short series of articles on where the X$ data come from so, for those with time on their hands (?anyone?) here’s a little script I wrote a few years ago when I wanted to check which v$ objects corresponded to the same internal structure in the SGA: (as v$session and v$session_wait used to)
It starts with a function that has to be created in the SYS schema – so no doing this on production systems. The it’s a pipelined function so that we can treat its output like a table, which means I need to create an object type and a table type before creating the function. In the function I select each x$ name from the list of x$ names (x$kqfta) and for each table I construct a dynamic SQL statement selecting the first row from the table.
Over the versions of Oracle, various bugs have resulted in deadlocks and crashes when selecting a row from particular X$ objects, so there a short list of exceptions that has grown gradually over time. The code has some calls to dbms_output(), which you can choose to hide by setting serveroutput off. Depending on your system you may see a very large number of X$ with no data in them.
Everybody “knows” that when you do a tablescan of a table that it starts with two buffer gets on the segment header, though older versions (ca. 8i and earlier) of Oracle used to do 4 buffer gets on the segment header. The upshot of this is that many people still say that if you create a table and insert a single row then you’re going to get 3 buffer gets when you tablescan a table: two for the segment header and one for the data block:
So here’s a test, with the second set of autotrace stats which, for reasons I’ll describe shortly, may not be immediately reproducible on your system:
It’s always useful to collect baseline information – especially when it helps you notice that the baseline has moved in a way that might explain the next performance problem you see. Here’s an example demonstrating the benefit.
I have a table with a LOB column that is stored out of line. Many years ago I decided that I wanted to compare how the redo generation varied as I change the LOB from cached to nocache (with nologging). So here was one of my simplest test scripts (stripped to a minimum):
I’m afraid this is one of my bad puns again – an example of the optimizer making a real hash of the index hash join. I’m going to create a table with several indexes (some of them rather similar to each other) and execute a query that should do an index join between the obvious two indexes. To show how obvious the join should be I’m going to start with a couple of queries that show the cost of simple index fast full scans.
Here’s the data generating code:
This is one of those posts where the investigation is left as an exercise – it’s not difficult, just something that will take a little time that I don’t have, and just might end up with me chasing half a dozen variations (so I’d rather not get sucked into looking too closely). It comes from an OTN question which ends up reporting this predicate:
WHERE ( LENGTH ( :b7) IS NULL OR
UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
AND STATE = 0;
The three bind variables all hold the same value; there is a function-based index on upper(trim(codfsc)), and another on upper(trim(coduic)). The execution plan for this query is a full tablescan, but if you eliminate the first predicate Oracle can do a concatenation of two index range scans. This variation doesn’t surprise me, the optimizer’s ability to introduce concatenation is limited; however, I did wonder whether some small variation in the SQL would allow the optimizer to get just a little more clever.
Would you get concatenation if you changed the first predicate to (:b7 is null); if not, would a similar query that didn’t depend on function-based indexes do concatenation; if not is there any rewrite of this query that could do a tablescan ONLY for the case where :b7 was null ?
Demonstrations of any levels of success can be left in the comments if anyone’s interested. To get a fixed font that preserves space start the code with “sourcecode” and end with “/sourcecode” (removing the quotation marks and replacing them with square brackets).