Re: Bulk DML question ...

Check to see how much temp activity running this generates. Your central tuning goal should be to reduce this to a minimum. Increasing hash_area_size and sort_area_size may be needed.

You should be able to get the hash to fit in memory. Hashes take about 1.5 times the data size to fit. The sort being done for the group may be harder, but the more of it you do in memory, the faster it will go.

If you can't eliminate temp, make it write faster by using disk system tricks. Direct IO, isolated temp disks, adding more disks to the stripeset,  etc...

You should run a bstat/estat during the load to see if anything else is going on.

Hope this helps.

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. Received on Tue Nov 07 2000 - 11:54:20 CST