Re: Simplest way to create/use PLSQL collections
- From: Tim Gorman <tim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 May 2005 21:55:50 -0600
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@xxxxxxxxx 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@xxxxxx> 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@xxxxxxxxx
> web: http://www.oracledba.co.uk
>
> "Semper in excremento, sole profundum qui variat"
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
