Re: Archive Log affects performance?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 21 Jan 2002 10:18:12 -0000
Message-ID: <3c4beae6$0$8507$ed9e5944@reading.news.pipex.net>

In fact I was correct.

I have a table t defined as (x number, y number, z number);

The following shows what happens with million row updates and different sized rollback segments

SQL> begin
  2 for i in 1 .. 1000000 loop
  3 insert into t values (i,power(i,2),power(i,3));   4 end loop;
  5 commit;
  6 end;
  7 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:107.99
SQL> create rollback segment small_roll
  2 tablespace rbs
  3 storage(initial 10k next 10k maxextents unlimited);

Rollback segment created.

Elapsed: 00:00:00.00
SQL> alter rollback segment small_roll online;

Rollback segment altered.

Elapsed: 00:00:00.70
SQL> set transaction use rollback segment small_roll;

Transaction set.

Elapsed: 00:00:00.60
SQL> update t set x = x + 10;

1000000 rows updated.

Elapsed: 00:00:53.09

Statistics

---

       5520 recursive calls
    1018670 db block gets

       5947  consistent gets
       1940  physical reads
  236690304  redo size
        863  bytes sent via SQL*Net to client
        549  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

    1000000 rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:01:88.11
SQL> create rollback segment big_roll
  2 tablespace rbs
  3 storage (initial 50m next 50m maxextents unlimited);

Rollback segment created.

Elapsed: 00:00:00.62
SQL> alter rollback segment big_roll online;

Rollback segment altered.

Elapsed: 00:00:00.60
SQL> set transaction use rollback segment big_roll;

Transaction set.

Elapsed: 00:00:00.50
SQL> update t set x = x + 10;

1000000 rows updated.

Elapsed: 00:00:52.57

Statistics

---

          0 recursive calls
    1011080 db block gets

       1940  consistent gets
       1761  physical reads
  236411244  redo size
        864  bytes sent via SQL*Net to client
        550  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

    1000000 rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.60
SQL> The elapsed time for the two updates is identical.

*thinks* maybe I should do all my work on 3 beers late at night.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:a2ffg0$c6g$1_at_helle.btinternet.com...


> "Stan" <stan0074_at_yahoo.com> wrote in message

> news:3C4A4E55.5060607_at_yahoo.com...

> > Noodles,

> >

> > along with no-archive mode, create a huge private rollback segment and

> > assign them to your update transaction (set transaction method).  This

> > would expedite your updates to run more faster.

> >

> > Stan

>


> I'm not convinced (but I am on my third beer at 10:15pm). Surely the

> transaction will still require the same amount of redo. How does making a

> single huge rbs help (excepting of course the case where a rollback

segment


> has maxextents set too small and the whole thing falls over in a heap)?

>
>


> --

> Niall Litchfield

> Oracle DBA

> Audit Commission UK

>
>

Received on Mon Jan 21 2002 - 04:18:12 CST