Re: Data Warehouse. Massive Join. HELP!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 24 Feb 1999 21:38:01 -0000
Message-ID: <919892306.12575.1.nnrp-02.9e984b29@news.demon.co.uk>

5 Hours seems about the right ballpark for doing your 7 table join using indexes. I would estimate ca. 48M logical I/Os minimum to do this, and a minimum time of 1 hr 20min if running serially.

Are you CPU bound or I/O bound during the 5 hours ?

Have you checked the session_events to see what waits are occurring, and how they can be reduced.

Are you creating the table unrecoverably ? Eliminate the I/O due to redo log writes.

What is the database block size - a block size mismatch between database and device drivers can slow I/O significantly. Is the database on UNIX file system, Veritas file system, or (logical) raw devices ? It makes a difference.

Have you tried enabling hash joins ?

How big is the sort_area_size/hash_area_size ?

Are you running parallel ? Could you scan the 2m row table in parallel and drive through the indexes from there ? this might give you an almost linear improvement.

There are loads of options to consider.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Wed Feb 24 1999 - 15:38:01 CST