Re: Tuning an UPDATE command

Compressed index:

    see CREATE INDEX in sql reference

Buffer_pool:

    See concepts manual, and Oracle Reference

CTAS
    Create table as select
and yes, by ordering the small table in index order the accesses to the large table will be in index order and increase the chances of re-using index blocks in the large index.

Two disks => pretty slow update.

    5,000 updates is a large amount of redo     log generated and written. How well is the     database tuned - you may improve things     for this specific job by:

        increasing redo log file size to > 10M
        increasing log_buffer size to 192K

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kevin O'Gorman wrote in message
<38404C8C.62F612D5_at_trixie.kosman.via.ayuda.com>...
>Jonathan Lewis wrote:
>>
>> Indexing 5,000 times into a 6,000,000 row
>> table - probably the case that every single
>> update is in a different block, so each row
>> results in a physical read for the table.
>>
>> Options for improvement: make sure that
>> VERY few physical reads take place on
>> the big index - your time is indicative of
>> one index physical read and one table
>> physical read per update.
>>
>> what are the options for using a compressed
>> index to make the index very small
>>
>
>I have no idea; I'm not familiar with compressed indexes
>other than BITMAP indexes, and that wouldn't be appropriate
>here.
>
>
>> can you split the buffer into large keep, small
>> recycle and default; set the table to recycle
>> and the index to keep.
>
>I'm new at this; this sounds like gobbledeygook to me. Can
>you point me into the docs where this sort of thing happens?
>I have all the PDF files of Oracle manuals, plus the Oracle
>press DBA and Tuning books.
>
>> Can you recreate the small table in sorted
>> order before doing the update ? CTAS
>> with ORDER BY works in 8.1.
>
>CTAS? What's that? I build the small table every time, so
>adding an ORDER BY is no problem. Is the idea to localize
>references in the big index?
>
>>
>> Can up run parallel updates from the
>> small table - this may interleave I/O time
>> on the large table ?
>
>I've not played with the parallel option at all. I presume
>this would work, but that this would require partitioning
>of the tables. Could be done, I guess.
>
>>
>> Your best serial time is likely to be not much
>> better than 250 seconds (working at 5,000
>> table I/Os at a fairly pessimistic 20 I/Os
>> per second).
>
>Probably a bit faster then, this is pretty fast iron; just a
>slow DBA. Pentium III 550, two IDE drives on two controllers;
>both drives very new. No partitioning of tables at the moment.
>
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>>
>> Kevin O'Gorman wrote in message
>> <384039C0.40012C0_at_trixie.kosman.via.ayuda.com>...
>> >This was a modest win. Time for the installation phase dropped 25%
>> >from 661 to 498 seconds. Now I have to attack the delete that's using
>> >180 of the remaining seconds -- an index will probably help.
>> >
>
>--
>Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net
>At school: kogorman_at_cs.ucsb.edu
>Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org
Received on Sat Nov 27 1999 - 15:55:32 CST