RE: Simplest way to create/use PLSQL collections

From: Parker, Matthew <matthewp_at_amazon.com>
Date: Thu, 19 May 2005 00:38:42 -0700
Message-ID: <F385925F530F6C4081F1659F71640EB3042D94D8@ex-mail-sea-04.ant.amazon.com>

There were a variety of bugs in 9.2 time frame that GTT's would actually = generate more redo than the same operation on a physical table, (such as = insert select).
Operations on the GTT's does not generate redo directly, but the = operation does generate undo for the transaction and redo is generated = for that undo.=20

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Wednesday, May 18, 2005 8:56 PM
To: oracle-l_at_freelists.org
Subject: Re: Simplest way to create/use PLSQL collections

Connor,

I'm a little hard-pressed to imagine why any redo at all should be = generated
for GTT, but it can't be too hard to test. If I wasn't so lazy I'd also fire up Log Miner and look at what is being generated...

• First in 10.1.0.3 ***********************

SQL> create global temporary table xtemp   2 (

  3     col1 number,
  4     col2 number

  5 );

Table created.

SQL> set autotrace on
SQL> insert into xtemp values (1,1);

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=3DALL_ROWS (Cost=3D1 Card=3D1 = Bytes=3D100)

Statistics

---

          4  recursive calls
          8  db block gets
          2  consistent gets
          1  physical reads
        284  redo size
        632  bytes sent via SQL*Net to client
        539  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / 1 row created.

Statistics

---

        184 redo size

SQL> / 1 row created.

Statistics

---

        140 redo size

SQL> / 1 row created.

Statistics

---

        184 redo size

SQL> / 1 row created.

Statistics

---

        140 redo size

SQL> / 1 row created.

Statistics

---

        184 redo size

Seems to average about 180 bytes per insert of two NUMBER columns. OK, = what
if the GTT was a regular table?

SQL> create table ytemp as select * from xtemp where 1 =3D 2;

Table created.

SQL> insert into ytemp values (1, 1);

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=3DALL_ROWS (Cost=3D1 Card=3D1 = Bytes=3D100)

Statistics

---

         17  recursive calls
          8  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        637  bytes sent via SQL*Net to client
        540  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / 1 row created.

Statistics

---

        356 redo size

SQL> / 1 row created.

Statistics

---

        296 redo size

SQL> / 1 row created.

Statistics

---

        296 redo size

SQL> / 1 row created.

Statistics

---

        296 redo size

So, in 10g at least, each insert generates about 50-60% of the redo on permanent tables.

• Next in 9.2.0.1 ***********************

SQL> create global temporary table xtemp   2 (

  3     col1 number,
  4     col2 number

  5 );

Table created.

SQL> set autotrace on
SQL> insert into xtemp values (1,1);

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=3DCHOOSE

Statistics

---

          2  recursive calls
         10  db block gets
          2  consistent gets
          2  physical reads
        244  redo size
        618  bytes sent via SQL*Net to client
        530  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / 1 row created.

Statistics

---

        128 redo size

SQL> / 1 row created.

Statistics

---

        128 redo size

SQL> / 1 row created.

Statistics

---

        128 redo size

SQL> / 1 row created.

Statistics

---

        128 redo size

SQL> create table ytemp as select * from xtemp where 1 =3D 2;

Table created.

SQL> insert into ytemp values (1,1);

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=3DCHOOSE

Statistics

---

          2  recursive calls
          7  db block gets
          2  consistent gets
          0  physical reads
        588  redo size
        622  bytes sent via SQL*Net to client
        530  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / 1 row created.

Statistics

---

        240 redo size

SQL> / 1 row created.

Statistics

---

        240 redo size

SQL> / 1 row created.

Statistics

---

        240 redo size

So, on 9i, as with 10g, the amount of redo generated for GTT is about = 50-60%
that generated for "permanent" tables. The same general ratio holds = true
for UPDATEs and DELETEs, as well.

Now, that doesn't seem too excessive, unless you're surprised by the = fact
that DML against GTT creates *any* redo at all (as I was!)...

Someday, when I'm not feeling too lazy (and tired -- it's late!), I'll = fire
up Log Miner and find out what's being logged from GTTs...

Thanks!

-Tim

on 5/18/05 6:46 PM, Connor McDonald at mcdonald.connor_at_gmail.com wrote:

> (If my red-wine diluted memory serves....) GTT dml resulted the block
> being "redo-ed" instead of the rows
>=20
> Connor
>=20
> On 5/18/05, Post, Ethan <Ethan.Post_at_ps.net> wrote:
>> Yes that was one of the issues, wasn't there something else with
>> temporary tables generating a bunch of REDO?
>> =3D20
>> -----Original Message-----
>=20
>=20
> --=3D20
> Connor McDonald
> =

=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D= 3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D

> =3D3D=3D3D
> email: connor_mcdonald_at_yahoo.com
> web:   http://www.oracledba.co.uk
>=20
> "Semper in excremento, sole profundum qui variat"
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu May 19 2005 - 03:43:47 CDT