Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bulk DML question ...

Re: Bulk DML question ...

From: <sergey_s_at_my-deja.com>
Date: Fri, 03 Nov 2000 20:18:39 GMT
Message-ID: <8tv6in$ta4$1@nnrp1.deja.com>

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. Received on Fri Nov 03 2000 - 14:18:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US