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: Transaction Gradually Slows to a crawl! (please help)

Re: Transaction Gradually Slows to a crawl! (please help)

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Wed, 28 May 2003 00:08:03 GMT
Message-ID: <3ED3FDE3.704@nospam_netscape.net>


You can also look into speeding up disk I/O on your machine, e.g. use 15k rpm scsi disks instead of IDE or slower SCSI disks, stripe your disks, and put the data files and the redo log files on separate physical disks to increase concurrency.

Cheers,
Dave

Dave Hau wrote:
> I think what's happening is that your huge transaction is generating a
> lot of rollback data and the database is doing a lot of disk I/O to
> write that rollback data to disk. You can verify this by setting
> timed_statistics and sql_trace to true, run your transaction, then using
> tkprof to generate a report. On the report you'll be able to see how
> much disk I/O you're doing.
>
> One way to fix this is to put more memory into your machine, then
> increase the db_block_buffers parameter in init.ora proportionately to
> enlarge your buffer cache. This way you'll be able to cache a large
> part of the rollback segment in memory. The undo data is discarded on
> committing the transaction so if you can keep it in memory, it'll never
> have to be written onto disk.
>
> The other way to fix it is to break up your transaction into batches.
> Eventually, as your transaction gets larger, you'll have to do this
> anyways because there's no way you can have enough physical memory to
> scale with the size of the transaction.
>
> By the way, when you said "redo logs are DISABLED" I think you meant the
> archiving of redo logs is disabled. Redo log cannot be disabled except
> in very limited circumstances eg. direct loading, because that's the
> basis of the durability aspect of the ACID properties of a transaction.
> Without the redo log, Oracle will not be able to recover in case of a
> crash.
>
> Cheers,
> Dave
>
>
>
>
>
> Mike Harris wrote:
>

>> Hi, I am running Oracle 8.1.7 on W2K Professional (SP/3). Athlon
>> 1400MHz, 768MB RAM.
>>
>> note: redo logs are DISABLED for faster updates (it's a staging
>> machine)
>> I start a transaction
>> I do an insert in a table (it's not an overly wide table, about 30
>> fields wide) which results in about 12000 new rows.
>> I then start a process (within the same transaction) which updates
>> each new row doing some more DB processing.
>> As the number of rows being updated hits about 7000 mark, database
>> starts to slow down. I am sure that it's gradually slowing down, but
>> that's when I start noticing it. When it has updated about 8000, it's
>> realy slow. And when it's close to 8500 mark, it's realy slooooww.
>> Then it goes so slow that I haven't seen it finish all 12000 rows (my
>> patience neven seems to side with me).
>>
>> What gives? In every single case, I ended up killing the session. And
>> that started oracle in a looooong clean up cycle. It is pure hell. Can
>> you please recommend a faster way to deal with this transaction hell?
>> Yes, all of it has to be inside a transaction. Any parameters I can
>> play with? More RAM? ???
>>
>> thanks a lot!
>> Mark.

>
>
Received on Tue May 27 2003 - 19:08:03 CDT

Original text of this message

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