FBI Bug

Here’s a funny little optimizer bug – though one that seems to have been fixed by at least 10.2.0.3. It showed up earlier on today in a thread on the OTN database forum. We’ll start (in 9.2.0.8) with a little table and two indexes – one normal, the other descending.

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 10000
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

-- collect stats

create index t1_id_asc  on t1(id) compute statistics;
create index t1_id_desc on t1(id desc) compute statistics;

You’ll notice that the two indexes are on the same column, even though one of them has (redundantly) declared the column to be treated in descending order. If I now execute the following query, noting the hint to use index t1_id_asc, would you expect the presence of index t1_id_desc to make any difference to the plan ?

select 
	/*+ index(t1 t1_id_asc) */
	* 
from 
	t1
where
	id between 1001 and 2000
;

Here are the plans, first with t1_id_desc in place:

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     3 |   342 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     3 |   342 |     5 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_ASC   |     3 |       |     4 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID">=1001 AND "T1"."ID"<=2000)
       filter(SYS_OP_DESCEND("T1"."ID")<=HEXTORAW('3DF4FDFF')  AND
              SYS_OP_DESCEND("T1"."ID")>=HEXTORAW('3DEAFF') )

Now after dropping index t1_id_desc

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1001 |   111K|    22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  1001 |   111K|    22 |
|*  2 |   INDEX RANGE SCAN          | T1_ID_ASC   |  1001 |       |     4 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID">=1001 AND "T1"."ID"<=2000)

The shape of the two plans hasn’t changed, of course, they’ve both obeyed my hint. Notice, however, the changes in the Predicate Information and the estimated cardinalities. With index t1_id_desc in place, the optimizer has found the definition of the hidden column representing the “id desc” and used it to generate some new predicates, introducing some extra factors into the calculation of selectivity.

In the case of the OTN query, the initial inputs were bind variables – which meant the new predicates looked like:

       filter(SYS_OP_DESCEND("D"."CREATEDAT")<=SYS_OP_DESCEND(:Z) AND
              SYS_OP_DESCEND("D"."CREATEDAT")>=SYS_OP_DESCEND(:Z))

In this version of Oracle this resulted in the predicates being treated as “guesses on an index range scan” – which resulted in the optimizer ignoring the appropriate index (until hinted) even though the cost calculation gave a lower cost.

Update 31 Dec 2011: It’s just occurred to me that this is another example of a case where you can drop an index that isn’t being used and find that execution plans can change as a side effect. (Conversely, you create and index that should change any execution plans – and some plans change, even though they don’t use the index.) Luckily, as I pointed out above, this demo came from 9.2.0.8, and the behaviour has been fixed by 10.2.0.3 (and possible earlier).

I wish

Here’s a simple update statement that identifies a few rows in a table then updates a column where a matching value can be derived from another table – it’s an example of an update by correlated subquery:

update	t1
set	small_vc = (
		select
			max(small_vc)
		from
			t2
		where	t2.id = t1.id
	)
where
	mod(id,100) = 0
and	exists (
		select null
		from t2
		where 	t2.id = t1.id
	)
;

There are a number of different strategies of varying efficiency we could use to modify the data in the same way. We could use the merge command to compare the results of the aggregate subquery on t2 with rows in t1, taking advantage of the 10g enhancement of the merge command to apply results only on matched rows. We could do something with pl/sql bulk selects from the aggregate then apply a forall bulk update taking advantage of array exceptions. In fact, depending on the volume and pattern of data in the two tables, either of the three mechanisms might turn out to be the most efficient in some circumstances.

There is a fourth mechanism which we can’t (or shouldn’t) employ – even though there may be cases where it is more efficient than the three I’ve mentioned so far. It’s the mechanism of the updateable join view, where we join t1 to an aggregate query on t2 on the column that was the correlation column, then update across the join:

update
	(
	select
		t1.small_vc	t1_vc,
		v1.max_vc	v1_vc
	from
		t1,
		(
		select
			t2.id,
			max(small_vc)	max_vc
		from
			t2
		group by
			id
	)	v1
	where
		mod(t1.id,100) = 0
	and	v1.id = t1.id
	)
set	t1_vc = v1_vc
;

There is a problem with this statement, though – it fails with Oracle error “ORA-01779: cannot modify a column which maps to a non key-preserved table” unless you add the hint /*+ bypass_ujvc */ (bypass update join view check ?) and even that hint fails to work in my copy of 11.2.0.2.

A critical feature of Oracle’s implementation of updatable join views is that only one table will be updated by the statement, and that table must be “key-preserved” – meaning that any row from the table to be updated can (logically) appear at most once in the view. A simple restriction to ensure that this requirement is met is to insist that the join(s) to the other tables(s) should always be with equality on the primary (or unique) key of the table(s).

In this case, though, because we aggregate on t2.id, and then join on t2.id, it is clearly the case that any row that appears in t1 can only appear once in the join between t1 and the aggregate of t2. In this update, t1 is clearly key-preserved – but, absent the hint, the optimizer will not allow the mechanism to come into play. (SQL Server will handle this particular example correctly, by the way.)

So, yet another item on my wishlist for the optimizer – let’s see a few more cases of updateable join views being recognised.

In passing, the path that the optimizer produces for join view update, when hinted, isn’t ideal because it fails to spot an opportunity for transitive closure that would make it more efficient. Here’s the execution plan (with Predicate Section):

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |      |       |       |       |   100 |
|   1 |  UPDATE               | T1   |       |       |       |       |
|*  2 |   HASH JOIN           |      |   100 |  3500 |       |   100 |
|*  3 |    TABLE ACCESS FULL  | T1   |   100 |  1500 |       |    27 |
|   4 |    VIEW               |      | 10000 |   195K|       |    72 |
|   5 |     SORT GROUP BY     |      | 10000 |   146K|   488K|    72 |
|   6 |      TABLE ACCESS FULL| T2   | 10000 |   146K|       |    27 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."ID"="T1"."ID")
   3 - filter(MOD("T1"."ID",100)=0)

As you can see, Oracle aggregates the whole of t2, then uses the result to probe a hash table built from a subset of t1. But the basic join is on t2.id = t1.id, which means that the optimizer could, in principle, generate the predicate mod(v1.id,100) = 0 and then push it into the inline view before aggregating a subset of t2. Here’s the plan if you add the predicate manually:

-------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |       |   100 |  3500 |    51 |
|   1 |  UPDATE                         | T1    |       |       |       |
|*  2 |   HASH JOIN                     |       |   100 |  3500 |    51 |
|   3 |    VIEW                         |       |   100 |  2000 |    23 |
|   4 |     SORT GROUP BY               |       |   100 |  1500 |    23 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1500 |    23 |
|*  6 |       INDEX FULL SCAN           | T2_I1 |   100 |       |    21 |
|*  7 |    TABLE ACCESS FULL            | T1    |   100 |  1500 |    27 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."ID"="T1"."ID")
   6 - filter(MOD("T2"."ID",100)=0)
   7 - filter(MOD("T1"."ID",100)=0)

It’s an interesting little side effect of the change (for my particular data set) that the probe table is now the t1 table (whereas the probe was a sorted aggregate of t2.id in the earlier version). This means that Oracle will be updating t1 as it scans it; in the previous plan Oracle would be jumping around t1 at random to update it in an order dictated by the arrival order of the aggregated rows coming from t2. In principle, this type of change in the order of updates could result in a reduction in the number of random physical I/Os that take place on t1.

Footnote:

If you want to experiment with variations on this problem then you can start with code like the following to create the two tables – bear in mind that there’s no great point in examining the variation in performance that different mechanisms give when playing with such a small (and regular) data set:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level )
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum ;

create table t2 as select * from t1;

-- collect stats

create unique index t1_i1 on t1(id);
create unique index t2_i1 on t2(id);
alter table t1 modify id not null;
alter table t2 modify id not null;

Events

l have just added a quick addendum about checking for trace events (and related information) to the Appendix page of Oracle Core.

This item is a temporary note to alert subscribers to the update, and it will be deleted in a few days.

Hacky Xmas

Christmas time for sysadmins who’ve been naughty.

Correlation oddity

This one’s so odd I nearly posted it as a “Quiz Night” – but decided that it would be friendlier simply to demonstrate it. Here’s a simple script to create a couple of identical tables. It’s using my standard environment but, apart from fiddling with optimizer settings, I doubt if there’s any reason why you need to worry too much about getting the environment exactly right.

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1000
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

create table t2 as select * from t1;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 => 'for all columns size 1'
	);

end;
/

create index t2_i1 on t2(id);

All I’ve done is create a small amount of data, collected stats, and built one index. Now run the following (fairly similar) queries.

update	t1
set	small_vc = (
		select
			max(small_vc)
		from
			t2
		where	t2.id = t1.id
	)
where
	mod(id,100) = 0
;

commit;

update	t1
set	small_vc = (
		select
			small_vc
		from
			t2
		where	t2.id = t1.id
	)
where
	mod(id,100) = 0
;

commit;

All I’m doing it an update of 100 rows through a correlated subquery. For consistency you might want to recreate the tables before running each of the updates, or perhaps run the test twice with the two updates in the opposite order the second time around. I’m not trying to do anything subtle or difficult to produce an anomaly, I’m just trying to demonstrate a strange difference between these two updates.

As you might expect, both updates (in my environment) operated through a simple subquery mechansim; Oracle didn’t attempt to transform them into a some sort of flattened update join view, so the two execution plans looked like this:

Aggregate subquery plan:
-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |       |       |       |    27 |
|   1 |  UPDATE                       | T1    |       |       |       |
|*  2 |   TABLE ACCESS FULL           | T1    |   100 |  1500 |    27 |
|   3 |   SORT AGGREGATE              |       |     1 |    15 |       |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |     1 |    15 |     2 |
|*  5 |     INDEX RANGE SCAN          | T2_I1 |     1 |       |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MOD("ID",100)=0)
   5 - access("T2"."ID"=:B1)


Simple query plan:
----------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |       |       |       |    27 |
|   1 |  UPDATE                      | T1    |       |       |       |
|*  2 |   TABLE ACCESS FULL          | T1    |   100 |  1500 |    27 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    15 |     2 |
|*  4 |    INDEX RANGE SCAN          | T2_I1 |     1 |       |     1 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MOD("ID",100)=0)
   4 - access("T2"."ID"=:B1)

There really doesn’t seem to be much difference in the mechanics. For each of the 100 rows selected from table t1, we run a subquery doing a range scan against table t2, using the index t2_i1 to find corresponding rows. In every case there is only one row match: in one update I copy back the maximum value from the list that consists of of just one row, in the other update I copy back the value from every one of the matching rows I find (all one of them); luckily the second update only ever finds one matching row for each row in the driving table, so the update completes rather than crashing with error ORA-01427: single-row subquery returns more than one row.

So – would you expect the two updates to perform differently ? I wouldn’t; but they do; here are some of the session stats:

Aggregate subquery
------------------
Name                                      Value
----                                      -----
db block gets                               208
db block changes                            407
redo entries                                108
redo size                                43,996
undo change vector size                  14,944

Simple subquery
---------------
Name                                      Value
----                                      -----
db block gets                               105
db block changes                            203
redo entries                                 63
redo size                                34,624
undo change vector size                  11,248

Obviously there were many other statistics I could have reported – consistent gets, table scan rows gotten, and so on – but all the other statistics matched very closely. What you see here is just the small number of statistics that tell us that the amount of data change was different depending on whether on not you used a (redundant) aggregation to get the value you needed to do the update. In passing, if you’re running 9i or lower, or if you’re running 10g and above with RAC, or supplemental logging enabled, or if you’re running this test in the SYS schema, you’ll see the number of redo entries at (or very close to) 202 and 101 respectively – the actual number of entries in my test is the effect of in-memory undo and private redo.

To find out what’s going on, the easiest thing to do is look at a symbolic dump of the redo log for this update which, stripped to a minimum, shows the following as the redo generated for one of the later rows updated in the batch of 100.

Aggregate subquery:
-------------------
REDO RECORD - Thread:1 RBA: 0x0005c6.00000057.0030 LEN: 0x00c8 VLD: 0x01
CHANGE #1 TYP:0 CLS:30 AFN:2 DBA:0x00807017 OBJ:4294967295 SCN:0x0000.04f86220 SEQ:  2 OP:5.1
	ktubu redo: slt: 30 rci: 99 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800130 OBJ:98261 SCN:0x0000.04f86192 SEQ:  2 OP:11.4
	KDO Op code: LKR row dependencies Disabled
 
REDO RECORD - Thread:1 RBA: 0x0005c6.00000057.00f8 LEN: 0x0100 VLD: 0x01
CHANGE #1 TYP:0 CLS:30 AFN:2 DBA:0x00807017 OBJ:4294967295 SCN:0x0000.04f86221 SEQ:  1 OP:5.1
	ktubu redo: slt: 30 rci: 100 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800130 OBJ:98261 SCN:0x0000.04f86221 SEQ:  1 OP:11.5
	KDO Op code: URP row dependencies Disabled


Simple subquery:
----------------
REDO RECORD - Thread:1 RBA: 0x0005c7.00000044.0090 LEN: 0x0160 VLD: 0x01
CHANGE #1 TYP:0 CLS:26 AFN:2 DBA:0x00801610 OBJ:4294967295 SCN:0x0000.04f86288 SEQ:  1 OP:5.1
	ktubu redo: slt: 36 rci: 42 opc: 11.1 objn: 98261 objd: 98261 tsn: 5
CHANGE #2 TYP:2 CLS: 1 AFN:5 DBA:0x0180012c OBJ:98261 SCN:0x0000.04f86224 SEQ:  1 OP:11.19
	Array Update of 1 rows: 
	KDO Op code:  21 row dependencies Disabled

When we update using the aggregate subquery Oracle generates two redo records per row: the first locks the row (op code 11.4), the second modifies it (op code 11.5). When we do the update through a simple subquery Oracle generates a single redo record per row, which is an “array update” (op code 11.19).

This anomaly may look familiar – but this time it’s nothing to do with the audit_trail, it just seems to be the way things work. At present I can’t think of any good reason for the difference, but maybe there’s something subtle I’m overlooking.

I Wish

A couple of days ago I wrote about some things I’d like to see in index statistics, which means changing dbms_stats.gather_index_stats(). Here’s an idea for dbms_stats.gather_table_stats(). I’d like to see the number of chained and migrated rows counted separately in dba_tables when we run the packaged procedure. The optimizer will use information about chained or migrated rows, but the information is only collected if you use the analyze command (and even then the two figures are summed into the single value chain_cnt) .

I have to say that it does look as if the information that Oracle would need to count (and distinguish between) chained rows and migrated rows is already available in the blocks. If we check a bit of a block dump we can see that various flags seem to tell us everything that the code would need to know. Here’s an extract from a table block dump, showing the start of three rows (technically row pieces) in the block.

tab 0, row 0, @0x1c79
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x0180000b.0

tab 0, row 1, @0x1952
tl: 807 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [200]
 78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 ...

tab 0, row 2, @0x162b
tl: 807 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [200]
 78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 ...

Notice particularly the “flag byte (fb)”, and the presence of one row reporting a “next rowid (nrid)”.
Amongst other things, the flag byte tells us what the row pieces represent.

H – this is a row header
F – this is the first row piece of a row
L – this is the last row piece of a row

These three flag values start us in the right direction. If we have a simple migrated row then we will have a head piece (in the original row position), pointing to the complete row. The head piece will have an H flag byte, and an nrid pointing to the actual row; when we get to the actual row its flag byte will show only the FL bits, and there will be an hrid (head rowid) associated with it, pointing back to the original location.

If we have a simple chained row, the first section will have the H and F bits set, and an nrid entry; any “middle” rowpieces will have an nrid, but no flag bits set, and the last row piece will have the L bit set, and no nrid or hrid.

If a row has migrated, and then chained after migrating, the first piece will have the H bit set, and an nrid entry; the next piece will have the F bit set and both an hrid and nrid; any middle pieces will have an nrid but no flag bits set; and the last piece will have the L bit set, but no nrid or hrid.

There may be other alternatives, of course, e.g. a row that first chains on an update, leaving a few columns behind, and then migrates those columns out of the initial location; then there may be other variations when rows shrink and start to “unchain” or “migrate back (partially)”; and I haven’t even started to think about rows with more than 254 columns which have “intra-block” chaining – i.e. several row pieces in the same block. But looking at the flags and information about head/next row pieces, it looks as if a full scan of a table could identify the number of chained rows and the number of migrated rows separately. If it has an H only it has migrated (but may also have chained), if it’s got an HF it has chained (only). Any rows with no flag setting or L don’t add much value unless we want to work out an average number of pieces per chained row. Any rows with FL are the migrated rows.  Any rows with F only are rows which have migrated and then chained.

Inevitably there could be accounting errors on sampling, though. We might find 10 rows with H only, but only 7 rows with FL; what assumption should we make about the three outstanding rows ? Might they be just migrated, or could they be migrated and chained ? In view of the room for error, it might be sensible to implement the counts only as part of the approximate NDV mechanism that only runs if you choose the auto sample size option, and always seems to do a full segment scan.

Related articles.

Another difference between analyze and dbms_stats
Anomalies with counting continued row activity

All Things Oracle

Last year I wrote a few articles for Simpletalk, a web service created by Redgate for users of SQL Server. This year, Redgate is setting up a similar service called “All things Oracle” (I’ve added a link in my blogroll) for Oracle users, and I’ve volunteered to write articles for them occasionally.

Some of the stuff they publish will be complete articles on their website, some will be short introductions with links to the authors’ own websites. My first article for them has just been posted – it’s an article that captures a couple of key points from the optimizer presentation I did at the UKOUG conference a couple of weeks ago.

I wish

Here are a few thoughts on dbms_stats – in particular the procedure gather_index_stats.

The procedure counts the number of used leaf blocks and the number of distinct keys using a count distinct operation, which means you get an expensive aggregation operation when you gather stats on a large index. It would be nice efficiency feature if Oracle changed the code to use the new Approximate NDV mechanism for these counts.

IOT Trap

In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.

The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.

Test Data

The UKOUG conference is over for another year – but it has left me with plenty to do and lots of things to investigate. Here’s just one little point that I picked up during one of the 10 minute “Oak Talks” that members of the Oak Table Network were doing in the lunch breaks.

There is a fairly well-known strategy for generating a list of numbers by using a “select from dual … connect by …” query, but I hadn’t realised that there were two ways of using it. The code I’ve usually used is this:

select
        rownum id 
from
        dual 
connect by 
        rownum <= 4000
;

to_char()

Here’s an odd little detail about the to_char() function that happened to show up in a little demonstration code I used to create some data for last Friday’s quiz night.

When generating test data I often rely on doing things with rownum, and one of the thngs I do quite frequently is turn it into a character string. Nowadays I usually use the lpad() function to do this conversion because that lets me specify the defined length of the resulting column. But last Friday, rather than starting from scratch with my standard template script, I created my test case by cloning a script that I’d written in 2003 and the script had used the to_char() function.