Welcome

Welcome to the Dark Side, Mark!

Whoops, security breach.

Well it turns out that thanks to "Homeland Security" your complete driver's license information may be on the Internet ... although the good news is that you can get it taken off. It seemed like such a good idea that I've taken mine off.

Go here, search for it, and go through the "Hide my Records" procedure if it's there. What is wrong with these people?

I already checked for George Bush of Texas, but I suppose there's a few of them and the "real" one isn't listed. If you're quick though (ie. quicker than him) you can get yourself a copy of the license of Mr Thomas Kyte of Leesburg, Virginia before he gets the records removed ... that's quite the photo!

A Dose of Stupidity

Natural selection is a fine thing, but when a person's stupidity and arrogance takes someone else's life we can only hope that there will be some serious repurcussions.

http://www.kcsg.com/story.aspx?id=682

No helmet, no protective gear, and 85mph in a 35mph zone on a new machine? At the very least I hope this guy got skinned well enough that he'll need a helmet to get women to look at him in future.

More Security For You Sir.

http://www.computerworld.com/securitytopics/security/story/0,10801,110880,00.html

Looks like some kind of security administrator role being introduced. $20,000 per CPU or $400 per user seems a littlesteep to me though.

Soon Time to Move Again

It's that time again, when the wife and I have to fill in forms and wishlists to try and influence our next posting. Last time we got pretty lucky - Colorado Springs wasn't on our list but it turned out pretty well.

The eternal problem is that wherever there are Air Force bases, all commerce other than the defence industry tends to flee. It's a trend that is continuing, accelerating even, with the consolidation of Air Force units into the larger bases with plenty of room for growth -- ie. in the middle of a God-forsaken wilderness with a 60 mile commute to the nearest habitation.

That isn't to say that they aren't good places to live -- Eglin AFB in Florida is next to one of the best beaches in the world, and Vandenburg AFB is nicely positioned on the California coast. Still they are not exactly major centres of commercial high-tech activity. In Colorado Springs I landed on my feet with a work-at-home contract with a longterm client, but that sort of thing wears thin after a while. Pretty quickly actually, especially in conjunction with a move to a new town.

The current hot prospect looks like Hanscom AFB, just outside Boston, or maybe the Pentagon. I think I like the idea of Boston more. Actually I like the idea of Europe even more than that, with some interesting vacancies at Ramstein to the south-west of Frankfurt -- I'll have to brush up on the non-existant German that I didn't learn from either the Turks I worked with in Munich or the girlfriend from Manchester I was living with at the time.

Early days yet though, and the whole process just epitomises the story of the best-laid plans of mice and men often going astray. If the worst comes to the worst and we do end up in the middle of nowhere then I'll be hoping for at least some nearby reasonable-sized airport, and I'll do the road-warrior thing for a few years. Or we'll just find the wife a new employer who doesn't unreasonably insist on moving it's employees every three years or so, regardless of the diverse other opportunities in the area. Tut! Maybe we'll move to New Zealand instead.

Space Requirement for Compressed Row Updates

It seems that when you update rows within compressed blocks the data is temporarily decompressed then recompressed, possibly causing the table to grow.

Here I create an uncompressed copy of DBA_OBJECTS, which takes 788 blocks to store. The compressed copy is then shown to take 256 blocks.

On updating all of the rows the table has grown to 1038 blocks, approximately 256+788, yet as the DBMS_ROWID-based queries show the rows have not moved. It seems that an additional approximately-788 blocks of space have been added to move the table's high water mark.

Curioser and curioser.

SQL> drop table t1
 2 

Table dropped.

SQL>
SQL> create table t1
 2  nocompress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL>
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60120 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> drop table t1
 2  /

Table dropped.

SQL>
SQL> create table t1
 2  compress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      256            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL> update t1 set owner = lower(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

Introspection time

I wandered over to Similar Minds to try their Jung personality test.

ISTP - "Engineer". Values freedom of action and following interests and impulses. Independent, concise in speech, master of tools. 5.4% of total population.

Free Jung Personality Test (similar to Myers-Briggs/MBTI)

Apparantly it means this. some of those observations seem to be pretty spot-on -- ie. the one's that reflect well on me. Just ignore all the others, they're not true. Also the "athletic" thing, pay no attention to that.

Careerwise I seem to be doing OK. Here are some suggestions from the website:

1. Police and Detective Work
2. Forensic Pathologists
3. Computer Programmers, System Analysts and Computer Specialists
4. Engineers
5. Carpenters
6. Mechanics
7. Pilots, Drivers, Motorcyclists
8. Athletes
9. Entrepreneurs

Well, #3 there is obviously on target ... I also built a very nice fence and gate, and next I'm putting up some trellises so that's #5 ... I have a Masters degree in Aeronautical Engineering and an HNC in Marine Engineering, so that's #4 ... I ride a 2005 Triumph Tiger, #7. I really ought to work harder on #8 though.

OK, back to work people.

Bad Form

I just finished filling in the thirteen-pages of forms required to transfer Number Three Son to a new day care. Here are some statistics on the number of times the forms required particular information

• His name: Seven
• His date of birth: Four
• His current age: Two
• His doctor's name: Three
  
• My address: Seven
• My phone number: Ten
  
• My signature: Eighteen

Things I have now lost as a result:

• Use of my writing hand
• Memory of my social security number
  
• Last trace of sympathy for de-normalisation for performance

And my will to live is fading fast ... there must be a beer around here somewhere ...

In Praise of ISO 8601

As a UK ex-patriate and US resident I'm very sensitive to date formats. Moving from the DD/MM/YYY format to the MM/DD/YYYY format was a traumatic experience, and after eight years I still get a nervous twitch when I need to place the month before the day. What kind of a twisted mind thought that up?

Probably the same one who decided that in my first job in the US I'd be dealing with a system for which the preferred date display formats were to be YYDDD and YYYYDDD. There can't be many people who are instinctively aware that the first of October is day 274 of a non-leap year, but if you find someone who is then I can tell you who they work for. Furthermore, they may belive that this data format is known as the Julian Date, and they're wrong about that of course. Further-furthermore it's surprising how many database designers believe that a requirement to display a date in YYYYDDD format means that it is a great idea to store it as a NUMBER(7), leading to it being displayed by default in many tools as "2,006,274.00". Actually it's not surprising, it's just irritating for the hopefully-obvious reasons.

The other exception to the adherence to the MM/DD/YYYY format was the Immigration Service, who seemed to prefer DD/MM/YYYY on their forms. Thus I was lulled into a false sense of security through the whole visa process, and only ambushed with the MM/DD/YYYY abomination when I was fully commited to the process.

This is why I have such a liking for ISO 8601, which specifies the standard date format to be YYYY-MM-DD, with a reduced precision option of YYYY-MM and an optional time component of what we Oracleers would represent as hh24:mi:ss. I could lay out a complete set of reasons why this makes more sense but it is done very well here, and the display of week numbers is also nicely handled.

Now then, do you suppose that if the massed hordes of database developers and administrators rose up against the MM/DD/YYYY and the less-objectionable DD/MM/YYYY and DD-Mon-YYYY formats in favour of YYYY-MM-DD then we might make a meaningful impact on this scourge? I'd hope so. If we all started a "Movement Against Date Display Stupidity" (MADDS) by claiming that there was a fatal bug in the system when displaying anything other than YYYY-MM-DD then we might deceive our way to virtue, and all mankind would benefit.

It'd do me a power of good, anyway.

A Quick Materialized View Performance Note

I was asked in an email this morning about how to improve materialized view refresh performance, and it's something that appears in Google hits for the blog quite frequently. So I thought I'd write a quick-and-dirty posting to summarise some of the research (and the resulting practical applications) that I've been fiddling with recently.

Here are some bullet points:

1. The materialized view fast refresh mechanism is a one-size-fits-all solution, and is probably not efficient for 99% of summary table maintenance operations.
2. The join of the aggregated change data to the MV is function-based, as the columns of both relations are wrapped in the Sys_Op_Map_NonNull() function that allows "null = null" joins. I think that it is extremely unlikely that anyone has nullable attribute columns in their fact or summary tables, so this (and the composite function-based index required to support it) are a waste of resources.
3. Because of the nature of the join it seems to be extremely unlikely that partition pruning of the summary table could take place.
4. The join mechanism promotes nested loop joins, where a hash join is probably more efficient (that's technically an outer join in the merge, of course).
   
5. The refresh mechanism assumes that a merge will be required, when sometimes an insert is not only possible but is very much more efficient.

If performance (and robustness, IMHO) are an issue for you then I would advise that you do the following:

1. Use materialized views only for enabling query rewrite (which means creating them on a prebuilt summary table, and unless you are loading to the summary by partition exchange then you have to drop the MV, refresh the summary table, and recreate the MV). In 10g it is much more easy to use the DBMS_Advanced_Rewrite package instead of MV's.
2. Write your own refresh code, based on the usual principles of writing good SQL. If you don't need a merge then don't use it. If you don't need to join to dimension tables to get higher attributes then don't do it.
   
3. Leverage different levels of aggregation to help produce higher levels. For a series of hierarchical summaries, multi-level aggregations can be extremely beneficial.
   
4. Consider storing the refresh and MV definition SQL in CLOB columns of a summary management table, so they can be tuned and edited without needing to open up package code to do so.
5. Consider using a complete refresh, either through MV's or manually, for higher aggregation levels, particularly when you can reference another summary table to do so.
   

My practical experience of taking this path delivered a reduction in the refresh time of a set of seven materialized views based on a single fact table, from a couple of hours down to six minutes. The publication of this is an example of what my client's technical lead calls "polishing the crown" :D

Anyway, this is all a sort-of abstract from a whitepaper that I'm working on right now, which will include all sorts of proofs and demonstrations of the above, plus some process and risk management advice, but it doesn't look like it'll be ready very soon. Feel free to comment or ask questions on the points raised above though -- I don't want anyone to be kept in suspenders 'till the paper is finished.

My Dishwasher is List Partitioned

My dishwasher has two levels in it. The bottom level is for plates and Large Things, the top level is for cups and Small Things, and there is a removable cutlery partition attached to the door.

If I had a spare cutlery partition then I could keep one of them in use by the dishwashing system while the other one is treated as a regular cutlery holder, and it could be filled with dirty cutlery as the items become available. When it was full I could perform a cutlery partition exchange between the one with clean items and the one now full of dirty items.

If I had spare levels then they could be treated in a similar manner. I believe that commercial dishwashers have exactly that configuration, thus they operate with lower downtime because of this exchange mechanism, although the overall configuration requires more space.

Within the cutlery partition there are six subpartitions. I like to fill each one with a single type of cutlery -- one for knives, two for spoons (they don't stack as well), a couple for forks, and one for other items. Although it is more work to separate the items into these subpartitions it has the advantage of physically clustering all the spoons together and I can access all items of one type without having to scan the complete cutlery partition.

For the upper and lower levels similar principles apply, although they are not really subpartitioned in the same way. Instead the large plates are clustered in a single contiguous range -- the small plates, the glasses and the mugs each have their own place. Again it is more work to insert the items like this, but the advantage of faster retrieval is similar because I don't have to scan the complete level to pick similar items out from dissimilar ones.

That is all.

The State of The Sponge

I notice that the blog just passed it's 80,000 page load since I started using Statcounter to monitor it, and that's just one day short of its first anniversary so it's time for stats.

Looking at the statistics for the last 1000 visits, one-third of them come from Google searches -- most seem to be to do with materialized views, oddly enough.

186 www.google.com   
77  tkyte.blogspot.com   
23  www.google.co.uk   
23  www.bloglines.com   
20  oracledoug.blogspot.com   
19  www.google.co.in   
14  www.google.com.au   
10  www.google.ca   
9   www.google.fr   
8   thinkoracle.blogspot.com   
7   blogs.oracle.com   
7   orawin.info   
6   www.google.de   
6   oradot.com   
6   www.rittman.net   
5   www.google.es   
5   www.blogger.com   
5   www.petefinnigan.com   
4   my5.statcounter.com   
4   www.google.be
4   www.google.it   
4   search.blogger.com   
4   www.newsgator.co.uk   
4   www.netvibes.com   
3   www.google.com.tr   
3   www.google.fi   
3   uk.my.yahoo.com   
3   www.edpadgett.com   
3   ora-dev.blogspot.com   
3   search.msn.com   
3   www.google.se   
2   www.google.com.sg   
2   www.google.co.il   
2   www.oracle-base.com   
2   www.google.ru   
2   www.google.ro   
2   www.google.ch   
2   search.yahoo.com   
2   pjs-random.blogspot.com   
2   www.google.ie
2   bloglines.com   
2   www.google.com.ph   
2   www.google.nl   
2   oracle-base.com   
2   discuss.joelonsoftware.com   
2   www.google.com.sg   
1   blogsearch.google.com   
1   www.dbasupport.com   
1   my2.statcounter.com   
1   oramossoracle.blogspot.com   
1   www.nomad8.com   
1   www.google.co.za   
1   my.yahoo.com   
1   www.google.com.tw   
1   www.google.com.hk   
1   www.google.pl   
1   www.google.co.kr   
1   tkyte-test.blogspot.com   
1   www.google.is   
1   www.google.cl
1   idyller.blogspot.com   
1   www.google.com.ar   
1   www.google.co.jp   
1   peec2006.blogspot.com   
1   www.google.com.br   
1   www.google.gr   
1   portal.umbrialistens.com   
1   www.google.co.ve   
1   kdsatplay.blogspot.com   
1   www.google.dk   
1   www.google.com.eg   
1   oraqa.com   
1   www.google.com.co   
1   ambigramania.blogspot.com   
1   www.google.pt   
1   www.surfwax.com   
1   www.google.com.mx   
1   www.alltheweb.com

Fifty-two countries are represented, if we ignore the unknowns ...

402 41.66%   United States
132 13.68%   United Kingdom
80   8.29%   Canada
60   6.22%   Lithuania
39   4.04%   Netherlands
37   3.83%   India
26   2.69%   Australia
20   2.07%   Germany
17   1.76%   France
15   1.55%   Switzerland
13   1.35%   Spain
11   1.14%   Unknown   -
9    0.93%   Russian Federation
8    0.83%   Belgium
7    0.73%   Turkey
7    0.73%   Japan
6    0.62%   Norway
6    0.62%   Denmark
5    0.52%   Portugal
4    0.41%   United Arab Emirates
4    0.41%   Italy
4    0.41%   Sweden
4    0.41%   Israel
3    0.31%   Colombia
3    0.31%   Greece
3    0.31%   Singapore
2    0.21%   Romania
2    0.21%   Philippines
2    0.21%   Poland
2    0.21%   Austria
2    0.21%   Ireland
2    0.21%   Serbia And Montenegro
2    0.21%   Finland
2    0.21%   Latvia
2    0.21%   Bolivia
2    0.21%   Uruguay
2    0.21%   Argentina
2    0.21%   South Africa
2    0.21%   Puerto Rico
1    0.10%   Pakistan
1    0.10%   Bulgaria
1    0.10%   Estonia
1    0.10%   Kenya
1    0.10%   Chile
1    0.10%   Andorra
1    0.10%   Ukraine
1    0.10%   Iceland
1    0.10%   New Zealand
1    0.10%   Egypt
1    0.10%   Brazil
1    0.10%   Slovenia
1    0.10%   Korea, Republic Of
1    0.10%   Hong Kong

Good to see the Netherlands up there this time! Readership seems to be up to 13 Dutch people, unless some of them are cheating and using multiple computers.

The most frequently occuring city for Sponge readers is London, UK, then Calgary, Ontario, Arlington, Nieuwegein ...

The most popular article is "Writing Good SQL". It's a perennial favourite with the Google crowd.

That's all -- back to work people!

A Story of Database FUD

The sorry tale here ... http://discuss.joelonsoftware.com/default.asp?joel.3.328948.11

Bad Advice On Materialized View Deferrable Constraints

An article by Jonathan Lewis on constraint problems with materialized views set off a little bell in my head about a metalink document on the subject, Note 284101.1.

The note protests the use of unique, foreign key and primary key constraints on materialized views, and includes the following interesting phrases ...

"It is not a good idea to create unique (unique indexes ) constraints , primary key or Foreign key constraints on the snapshot base table ... There should not be any Unique/PK constraints at Mview base table ... Drop the Unique/PK constriants on Mview Base Tables."

Then one of the workarounds is "You can have Deferred Constraints". The document doesn't identify why having deferred constraints might be a bad idea, or why not having integrity constraints on the table at all is preferable to having them deferred .. I can't think of a reason why that might be so, and I can think of several reasons why it might not be so (query optimization being the prime one).

All this misdirection and ambiguity, combined with the abbreviation of an error code from "ORA-00001" to "ORA-1" in the title of a help document (does that make sense to anyone?) make this a pretty poor piece of work. It's not the Most Useless Metalink Article Ever, but it's close.

By the way, if you read the title and the first few words of this article, and thought "Dave's going to say that Jonathan Lewis gave bad advice!" before realising I wasn't ... the effect was intentional :D If you didn't think that then my plan failed.

What's Wrong With Opera?

Not the musical form, obviously there's nothing wrong with that at all.

The browser is very nice, but I can't read the Oracle forums with it. If I go here then clicking on any link takes me here. Opera also doesn't display the little images that are scattered all around the page, and I get a bunch of rectangles with text in them. Internet Explorer doesn't have this problem, nor the issues that I wrote about in Firefox the other day (heresy, I know).

Why is reading HTML and following links so difficult? You'd imagine that they'd have it close to working by now. Well, maybe there's some configuration thing that needs to be done to the browser, but I'm not sure why I should have to spend my time messing about with this kind of crap.

OK, I promise to write something later this week about a technology that I do like. Um ....

Development and Tuning

I had a pithy thought earlier today.

1. Development is the business of making the database do work
2. Tuning is the business of reducing the amount of work the database does.

Therefore, tuning is the opposite of development. It is "anti-development"!

What's Wrong With Firefox?

A number of things.

1. On one of my machines it consistently refuses to open particular web sites. http://www.dbdebunk.com/index.html is one of them -- IE has no problem, Opera has no problem. No error message, nothing. It just seems to pretend that I haven't asked it do do anything.
2. It's a resource hog. Memory usage often tops 150Mb, and can only be reduced by stopping and restarting it. If my machine startys running slowly then I know where to look -- Firefox is sitting and chewing up CPU cycles to no apparant effect.
3. It's unstable. I generally get four or five crashes a week, and they're not associated with having a lot of windows or tabs open either.

It didn't used to be this bad, and it seems to be getting worse. AdBlock has been the only reason to keep using it, but that's not enough anymore. I'd actually rather have adverts on the page than put up with the all of Firefox's crap .