People who have attended the UKOUG presentation this year where Mark Drake, Sr. Product Manager XML Technologies / XMLDB, Oracle HQ, and I demonstrated the first principles of the XDB Repository, might have been impressed with its (GEO/KML Spatial, Image EXIF info) capabilities combined with Google Earth. This post will zoom in on how to
Sherlock Holmes: A Game of Shadows is a worthy sequel to the previous film. I read a couple of reviews saying the plot was too complicated. This surprises me because I would have though most people who saw the first film would understand that you are not meant to be able to fathom the clues. The whole point is that he is so amazing he can string a bunch of trivial things together and come up with some outlandish conclusion. The more ridiculous the better as far as I’m concerned.
This film is even more camp than the previous outing. There is definitely some bromance going on between Holmes and Watson. What with Holmes cross dressing and his brother (Stephen Fry) calling him Shirley, you get some totally Mr Humphries moments. The onscreen chemistry between Jude Law and Robert Downey Jr. is so good I imagine they would be a riot if you went down the pub with them.
A special note must be made of Stephen Fry’s hair in one of the scenes. It is all curled up at the ends and is truly a sight to behold. I was both cringing and laughing all at the same time. Perfect casting if you ask me.
If you loved the first one you’ll love this one. Great holiday fun.
On my final day in Bulgaria I managed to leave my coat, hat and gloves in a restaurant about 2 hours away from Sofia. I realized when we were about 30 minutes from Sofia, but thought it was no point saying anything because we didn’t have time to go back and get them. When we arrived at the hotel someone else noticed I wasn’t wearing a coat and I explained my foolishness and thought no more of it.
A few days after I got home I got an email from Milena Gerova to say the coat had been located and she was going to post it back to me. A man in a van delivered it this morning, hence the title of the post, “Return of the mack”. Thanks very much Milena.
If you came here hoping to see a reference to the 1996 Mark Morrison song called “Return Of The Mack“, there it is. If you were hoping for a reference to the recent Mann: The Mack song, you have that too.
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.
We’ve now received a response to the issue described in the previous entry. In short oui updates at install time are broken (apparently fixed in 184.108.40.206, but no updates are available for that release yet to check. Hi , The OUI looks for the metadata for the patches. There is an unpublished bug 11709996 on [...]
December 18, 2011 It seems that I have been quite busy lately with computer related tasks that are not associated with Oracle Database, making it difficult to slow down and focus on items that have a foundation in logic. Today I have had some spare time to dig further into the recently released “Oracle Core” [...]
The 31st of January and 1st of February 2012 I will present a 2-day seminar about the Oracle query optimizer in Ballerup (DK). The event is organized by Miracle A/S. The content, which is based on the chapters 2, 4, 5, 6, 9 and 10 of my book, is the following:
The flyer and this page provide detailed information about the seminar.
I sometimes forget which versions of ASH have which fields, so here is a bit of a graphic and textual cheat sheet. (see https://sites.google.com/site/oraclemonitor/dba_hist_active_sess_history for some queries on ASH)
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:  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:  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.