Re: Bulk DML question ...

The only other things I can suggest are fairly obvious and you've probably already thought of them but here goes.

You want to put the smaller of the two tables inside your use_hash hint.

If possible, make sure the table you are creating is hitting different spindles than the two input tables.

If its possible, get your temporary tablespace on isolated devices that are not raid 5. It could even be on a raid 0 array since nothing in the tablespace is permanant.

You may try increasing your hash_area_size to 20M and make the extents in your temp tablespace 2 or 4 X hash_area_size.

You might consider trying a sort merge join. Some of the web sites I've looked at indicate that the advantages of using a hash join diminish if both of the tables are orders of magnitude larger than the hash_area_size
(here one of the them www.fors.com/eoug97/papers/0244.htm).

I know you said that there isn't time or money to change the business process, but would it be possible to denormalize group_code into table1?

Regardless of whether a sort merge or hash join is best, I think you'll get the most bang for the buck tuning wise by getting temp off of a raid 5 volume.

Hope this helps,

John

In article <8tv6in$ta4$1_at_nnrp1.deja.com>,   sergey_s_at_my-deja.com wrote:
> Yes, the explain plan shows hash join and every step is
> parallel-to-parallel (except for the collection at the end, of
 course -
> that one's parallel-to-serial). The query creates a "processing table"
> (approx. 1.2 billion rows less than the base table). This processing
> table is later queried to create more temporary tables which in turn
 are
> used to create the final aggregate tables. Basically, the aggregation
> process was broken down into smaller steps for performance reasons (we
> achieved 30% reduction in time).
>
> Thank you!
> Sergey
>
> In article <8tuua4$lf8$1_at_nnrp1.deja.com>,
> jdarrah_co_at_my-deja.com wrote:
> > Does the explain plan show that its using hash and that the
 statement
> > is running in parrallel? Also, what's the next step after you
 create
> > the table?
> >
> > In article <8tqg61$15b$1_at_nnrp1.deja.com>,
> > sergey_s_at_my-deja.com wrote:
> > > Let's say I have two tables:
> > >
> > > CREATE TABLE1
> > > (
> > > TIME_KEY NUMBER,
> > > ID NUMBER,
> > > NAME VARCHAR2(20),
> > > AMOUNT NUMBER(8,2),
> > > COUNT NUMBER,
> > > ...
> > > );
> > >
> > > CREATE TABLE2
> > > (
> > > ID NUMBER,
> > > GROUP_CODE NUMBER,
> > > ...
> > > );
> > >
> > > TABLE1 has approx. 3 billion rows (almost 200 Gig.). TABLE2 has
 approx.
> > > 3 million rows. TABLE1 is partitioned by TIME_KEY (partitions have
> > > anywhere from 50 to 100 million rows each). I looked into making
> > > partitions smaller based on additional criteria, but the next
 candidate
> > > column has only about 500 unique values.
> > >
> > > Every row in TABLE1 has a match in TABLE2. In other words, I have
 no
> > > choice but to do full table scans. I already looked into business
> > > process, and there is no time or money to change it at this point.
> > >
> > > So, here's the query I need to run:
> > >
> > > CREATE TABLE TEMP_TABLE PARALLEL (DEGREE 8 INSTANCES 1) NOLOGGING
 AS
> > > SELECT /*+ ORDERED FULL(A) FULL(B) USE_HASH(B) */
> > > A.GROUP_CODE, B.NAME, SUM(B.AMOUNT), SUM(B.COUNT)
> > > FROM TABLE2 A, TABLE1 B
> > > WHERE A.ID = B.ID
> > > GROUP BY A.GROUP_CODE, B.NAME;
> > >
> > > This query finishes in about 30 hours when the machine is pretty
 much
> > > dedicated to my process (needless to say, it's too long for just
 the
> > > first step). I am looking at PL/SQL and bulk DML, but I am not
 sure
 yet
> > > how I could apply it in my case. Maybe, there are ways besides
 bulk
 DML
> > > that I could try. If you have any suggestions/thoughts etc.,
 please
 let
> > > me know.
> > >
> > > I have Oracle 8.0.6.0.0 With Partitioning and Objects Options;
> > > SunOS 5.7 on 8 processors (400 Mz each), 80 Gig Memory, RAID 5 (66
> > > disks in total I think, but not sure).
> > >
> > > Here's some db settings:
> > >
> > > parallel_transaction_resource_timeout 300
> > > shared_pool_size 209715200
> > > shared_pool_reserved_size 37847040
> > > shared_pool_reserved_min_alloc 5K
> > > large_pool_size 0
> > > large_pool_min_alloc 16K
> > > disk_asynch_io TRUE
> > > dbwr_io_slaves 0
> > > lgwr_io_slaves 4
> > > arch_io_slaves 0
> > > db_file_direct_io_count 64
> > > db_block_buffers 48000
> > > db_block_checksum FALSE
> > > db_block_size 16384
> > > db_block_checkpoint_batch 8
> > > db_block_lru_statistics FALSE
> > > db_block_lru_extended_statistics 0
> > > db_writer_processes 4
> > > db_block_lru_latches 8
> > > db_block_max_dirty_target 4294967294
> > > db_file_simultaneous_writes 4
> > > db_file_multiblock_read_count 64
> > > mts_servers 1
> > > mts_max_servers 10
> > > mts_max_dispatchers 10
> > > local_listener
> > > mts_listener_address (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
> > > mts_multiple_listeners FALSE
> > > sort_area_size 10485760
> > > sort_area_retained_size 65536
> > > sort_direct_writes TRUE
> > > sort_write_buffers 4
> > > sort_write_buffer_size 32768
> > > sort_spacemap_size 512
> > > sort_read_fac 5
> > > parallel_min_percent 0
> > > parallel_default_max_instances 0
> > > parallel_min_servers 7
> > > parallel_max_servers 32
> > > parallel_server_idle_time 3
> > > parallel_execution_message_size 2148
> > > parallel_min_message_pool 225540
> > > hash_join_enabled TRUE
> > > hash_area_size 13107200
> > > hash_multiblock_io_count 4
> > >
> > > Thank you very much!
> > > Sergey
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 03 2000 - 18:54:47 CST