Re: Simplest way to create/use PLSQL collections

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 18 May 2005 21:55:50 -0600
Message-ID: <BEB16A66.27898%tim@evdbt.com>

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=ALL_ROWS (Cost=1 Card=1 Bytes=100)

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 = 2;

Table created.

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

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=100)

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=CHOOSE

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 = 2;

Table created.

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

1 row created.

Execution Plan

---

   0 INSERT STATEMENT Optimizer=CHOOSE

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
> 
> Connor
> 
> 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?
>> =20
>> -----Original Message-----
> 
> 
> --=20
> Connor McDonald
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D
> email: connor_mcdonald_at_yahoo.com
> web:   http://www.oracledba.co.uk
> 
> "Semper in excremento, sole profundum qui variat"
> --
> http://www.freelists.org/webpage/oracle-l
> 

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 19 2005 - 00:01:54 CDT