URL Masking at DBASupport.com forums

http://www.dbasupport.com/forums/showthread.php?p=226665

Rather sinister, I think. I wonder what's behind it?


(Busy day for posts today, huh?)

Why Space Travel Is A Stupid Idea

It seems to me that all other arguments aside, when a lamp falling onto the outside of a space ship and causing "... five small indentations, with the largest about the size of a stick of gum, and one 6-inch (15-centimeter) to 7-inch (17-centimeter) long scratch" makes it onto the news, then it's time to be rethinking whether this is a goal worth pursuing. Doubtless at this very moment a ten-man committee is being formed to write a review of the "Procedures for Luminescent Output Device Restraint In Shuttle Proximity".

Bonus opinion: I don't believe that the spokesman for the space shuttle's manufacturer, Lockheed Martin, is called Marion LaNasa. I think they made that up.

Unfortunate Timing

My plans to attend UKOUG in November, and maybe even submit a paper, have been thoroughly sunk. Following the collapse of our plans to get posted to Eastern Europe, the wife is scheduled for a deployment to Sicily from October to December and I'll be playing the part of Mr. Mom to our herd of offspring for three months.

Speaking of which, what's the collective noun for small boys? A wrestle of boys? A destruction? A puddle? Any of those would seem to fit.

Things I Need: Part I

Now that my Poweredge is up and running, I'm casting about for a New Shiny Thing that I absolutely must have.

I'm unimpressed with mere items of technological clothing.

What I need is a JL421 Badonkadonk. Am I right fellas?

A List Of Weekend Successes

A list of things we did this weekend

1. Recommissioned sprinkler system, watered plant, soaked lawn, decommissioned sprinkler system
2. Cut kids' hair -- no fatal wounds inflicted.
3. Cleared basement
4. Got rid of four old tires
5. Made chicken and rice soup
6. Mended kite -- flew kite -- broke kite again
7. Boxed 10 cu.ft. of kids clothes, to go to Afghanistan via USAF apparantly.
   
8. Got Poweredge 6400 running on Centos 4, moved it to the basement & started Oracle 10.1 install.

OK, about item 8.

The tricky part of the business was the boot. I had grub stage2read errors coming out the wazoo, and eventually gave up on trying "conventional" fixes. Booting with a floppy drive turned out to be the answer, as long as it referenced an initrd. Quite the learning experience.

Note to self: make backup of floppy disk! Maybe two backups.

Poweredge 6400 -- the Great o/s Debate

First, some boring details.

The service tag for this machine is H6MGN01, and the recorded configuration seems to be pretty accurate. The critical part is this: "ASSEMBLY, CARD (CIRCUIT), POWEREDGE EXPANDABLE RAID CONTROLLER NUMBER, 3-DC, 128"

Maybe it's a truism that the tricky part of working with these systems is getting the drivers for the array loaded before the install, but I seem to have worked that out. There was a nervous moment of array errors before I realized that the controller card was slightly unseated during shipping, but that seems to be a non-issue now.

Dell appear to have pretty good support for Linux, on the whole. I've been through the information at http://linux.dell.com/storage.shtml and deduced the following.

1. The PERC 3/DC requires a MegaRAID driver "megaraid_mbox"
2. The required driver is present in RHEL4 kernel 2.6.9-5.EL and higher -- sounds good for a Whitbox or Centos install!
3. SLES9 ought to work, but has not been tested on the PERC3 series by Dell
4. The PERC3/DC firmware update must be extracted to floppy on a Windows machine
5. There are Solaris drivers for the Adaptec card, but not for the PERC3. Boooh.
   

Point 1: no problem. Actually SuSE 8 appears to have the right drivers also. I tried a quick install yesterday evening but it choked on reboot with a "GRUB loading stage2Read error" -- maybe something needs tweaking there.

Point 3: Rats. I'm going to have to get creative there, with some major surgery to move a floppy over to my Windows machine.

Anyway, I'm downloading Centos ISO's right now, so we'll see how they perform. Do you think that driver has to be specifically loaded before the install? I guess we'll find out ...

Poweredge 6400 -- the Great o/s Debate

First, some boring details.

The service tag for this machine is H6MGN01, and the recorded configuration seems to be pretty accurate. The critical part is this: "ASSEMBLY, CARD (CIRCUIT), POWEREDGE EXPANDABLE RAID CONTROLLER NUMBER, 3-DC, 128"

Maybe it's a truism that the tricky part of working with these systems is getting the drivers for the array loaded before the install, but I seem to have worked that out. There was a nervous moment of array errors before I realized that the controller card was slightly unseated during shipping, but that seems to be a non-issue now.

Dell appear to have pretty good support for Linux, on the whole. I've been through the information at http://linux.dell.com/storage.shtml and deduced the following.

1. The PERC 3/DC requires a MegaRAID driver "megaraid_mbox"
2. The required driver is present in RHEL4 kernel 2.6.9-5.EL and higher -- sounds good for a Whitbox or Centos install!
3. SLES9 ought to work, but has not been tested on the PERC3 series by Dell
4. The PERC3/DC firmware update must be extracted to floppy on a Windows machine
5. There are Solaris drivers for the Adaptec card, but not for the PERC3. Boooh.
   

Point 1: no problem. Actually SuSE 8 appears to have the right drivers also. I tried a quick install yesterday evening but it choked on reboot with a "GRUB loading stage2Read error" -- maybe something needs tweaking there.

Point 3: Rats. I'm going to have to get creative there, with some major surgery to move a floppy over to my Windows machine.

Anyway, I'm downloading Centos ISO's right now, so we'll see how they perform. Do you think that driver has to be specifically loaded before the install? I guess we'll find out ...

A New Arrival

My new (used) Poweredge 6400 arrived today -- a new dawn of technical complexity, electrical power consumption and noise pollution has arrived in the Sponge household.

The first technical challenge is what name to give to the machine? In accordance with my own conventions it has to be that of a London train station, and because it is short, wide, and dressed in black I immediately thought it looks like a Victoria. Unfortunately I already have one of those, so I think it'll have to be "Waterloo".

It'll be a fine chance to give a brief history lesson on the Empire's Days of Glory to my mob of ungrateful foreign colonial children.

Predicate Pushing And Analytic Functions

I'm sure that there must be a fair number of Oracle professionals who carry around in their heads a little score card of some of their best tuning results ever ... hopefully, at least, 'cos otherwise I'm a weirdo. Anyway, today I improved the performance of a set of decision support queries and achieved my best result ever - improving query speed by a factor of 180,000, from an original run time of one hour down to a new time of 0.02 seconds.

The wise monkeys in the audience will immediately be thinking "partition pruning!" or "materialized view!", and in fact if you thought the former then you'd be right. Here's how it worked.

I had defined a view against a large fact table so that it included several analytic functions. The details really don't matter, but the intention was to allow a column to contribute to a metric value only once per transaction_id, so the metric definition was something like:

Decode(Row_Number() Over (Partition By Transaction_ID Order By 1),1,Qty,0)

Queries that access this view had predicates on a number of columns, including some very selective indexed columns (Item_ID) and a partition key column (Fiscal_Month). Unfortunately there is nothing in the database to tell the optimizer that each unique value of Transaction_ID had but a single value Item_ID and Fiscal_Month, so logically the predicates could not be applied until after the analytic function had been calculated. Hence there was no predicate pushing on the fiscal_month and item_id, and neither partition pruning nor index access was considered. The query was actually scanning about one quarter of the table (it looks like a combination of subpartition pruning and partition pruning was taking place, but this table is multicolumn range + list composite partitioned, and pruning at the partition level was only taking place on the leading column of the partition key).

However, we included the two predicate columns in the analytic functions' partition clause like so:

Decode(Row_Number() Over (Partition By Transaction_ID, Item_Id, Fiscal_Month Order By 1),1,Qty,0)

Now this doesn't change the result because the Item_Id and Fiscal_Month entries are actually logically redundant, but it did allow both 9i and 10g to push the predicates and give full partition and subpartition pruning and index-based access.

Quite a nice result.

Here's a script that I used to demonstrate that it would work.

drop table t1;
create table t1
   (
   txid   ,
   month  ,
   item_cd,
   qty  
   )
as
select
   floor(rownum/5),
   floor(rownum/20),
   floor(rownum/10),
   floor(rownum/5)
from
   dual
connect by
   level < 100
/

explain plan for
select txid,
       qty
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number() 
              Over (Partition By txid Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

explain plan for
select txid,
       qty_fix
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number()
              Over (Partition By txid Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

explain plan for
select txid,
       qty_fix
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number()
              Over (Partition By txid,item_cd Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

In 10g the explain plan with predicate pushing was very straightforward:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2273146475

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     9 |   252 |     3  (34)| 00:00:01 |
|   1 |  VIEW               |      |     9 |   252 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |     9 |   351 |     3  (34)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     9 |   351 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ITEM_CD"=0)

Note the filter being applied to line 3. Where the predicate was not pushed it was applied to line 1.

The 9i explain plan was rather ... funky. But it worked.