David Aldridge

Following on from the previous post, a little glitch in the plans: the instance appeared to crash late last night during the load. Hopefully not some exotic bug associated with dynamic sampling. I modified the delete-and-lock strategy yesterday after remembering that we have a number of indexes that are created with the “compute statistics” option. Since DBMS_STATS.LOCK_TABLE_STATS is [...]

I am absolutely sick and tired of dealing with issues where misleading partition statistics (for example) cause ETL queries to go crazy and try to sort-merge tables with tens of millions of rows each. Since we’re about to run a test load on production data I’m going to drop all table and index statistics and lock [...]

“See-qwel” or “S-Q-L”? Have your say on this highly important matter here, and help shape the future of our industry.

I’m told that it’s very important to keep one’s brain in shape as one grows older. Or “drifts gently towards the twilight” as I prefer to think of it. Trying to look on the optimistic side, obviously. Fortunately I end each day with my brain wrung out like a well-used sponge courtesy of some ETL development work [...]

Greg Rahn has an excellent entry on statistics gathering and the use and abuse of dbms_stats and initialization parameters at the Structured Data blog, one of my favourites. I added a comment on dynamic sampling, of which I am a big proponent, and Greg has another recent post on a case where dynamic sampling saved [...]

I just posted this question to the Oracle list, but thought I might post it here in case that reaches a wider audience. It also may be a situation that others in the DW arena have faced before.   I have a large and busy OLTP table, 100GB or so, against which there is a need to capture [...]

The same old “stored procedures are wonderful/evil” debate is again being practiced at the Joel on Software forums. Enjoy.

When you’re designing ETL processes the mantra of “do it in SQL” realy gets engrained in your way of thinking. PL/SQL-based processing as a method of handling business logic is anathema, as the principle of “row-by-row equals slow-by-slow” is very well highlighted by the processing of millions of rows. Very recently though I found a case where [...]

I’ve wondered before about a convenient way to tackle a particular problem — you want to run a select for a big bunch of rows, maybe for measuring wait events for a tricky query, but you want to discard the output so you don’t incur the network traffic and waits to the client. You could [...]

I puzzled a little on the way to work about efficiently populating a hierarchy table based on a flat dimension. (”Why?” … seriously, you just don’t want to know that). So let us suppose you have a table like this: create table dim   (city varchar2(30),   state varchar2(30),   region varchar2(30),   country varchar2(30)) / … and data like this …  insert into dim values [...]

First, raise an enhancement request with your database vendor … Yes, don’t get excited — I don’t know of a way to do it. I wish there was though, and the ability to rebuild multiple indexes (or partitions of different indexes) at the same time would be useful because … Well the problem is in the domain of [...]

So it turns out that the proposed merger between XM Satellite Radio (my employer) and Sirius Satellite Radio was just approved by the Department of Justice. Just the FCC to go now. Interesting times ahead … and a bunch of stuff I was working on is probably going to get cancelled, with a bit of luck. [...]

I interviewed a couple of senior ETL developer candidates last week, so I shook the dust off of some of my tried-and-true probes for technical knowledge. I’ve always had a liking for asking for the differences between “truncate” and “delete” ever since I was asked it myself some years ago. I think it allows candidates to demonstrate [...]

I scanned the structure of a data warehouse staging area yesterday, and developed a mild nervous tic. The cause: finding TIMESTAMP(6) data types scattered around willy-nilly. Firstly, my natural caution and fear of the unknown was aroused by seeing that microsecond timings were being stored, even if it was to log times of inserts and updates of rows and not [...]

The specific cause of the bogglation is the “Interactive Reporting” feature, which you can take for a test drive here. Click on the “Interactive Reports” tab and then poke the “View Customized Interactive Reports” button.  Integrating Flashback Query (click on the little cog wheel) is a little stroke of genius, and a testament to the [...]

Here’s a curiosity. Or a banal observation, depending on your perspective. There are, broadly speaking, two different causes of buffer busy waits. They are nicely described in the documentation here. Waiting for a block to be read into the buffer cache by a different session. Waiting for a block in memory to become available in some way (maybe [...]