Home » RDBMS Server » Performance Tuning » Does DML on Global temp table generates REDO
Does DML on Global temp table generates REDO [message #65981] Tue, 15 February 2005 06:35 Go to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Does DMLs on Global Temporary table generates REDO
If yes then how do i suppress it

thanks in advance
Vivek
Re: Does DML on Global temp table generates REDO [message #65985 is a reply to message #65981] Tue, 15 February 2005 12:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Does DMLs on Global Temporary table generates REDO
Yes. It does. In 9i it is supposed to be way less than the regular table.
But it will be generating enormous amount of redo.
It is well reported bug (bug# 2874489) is supposed to be fixed in 10g .
Re: Does DML on Global temp table generates REDO [message #65990 is a reply to message #65985] Wed, 16 February 2005 21:48 Go to previous messageGo to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Hi Mahesh,
One more clarification, ok it does generate redo. does it generates UNDO also.
Is there any example to check that for a particular
operation say Insert generates how much redo for Temp table and most importantly is there any way to switch off its redo generation
Re: Does DML on Global temp table generates REDO [message #65992 is a reply to message #65990] Thu, 17 February 2005 07:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
mag@mutation_mutation > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

  1* create global temporary table mygtt (c1 varchar2(30)) on commit preserve rows
mag@mutation_mutation > /

Table created.

mag@mutation_mutation > set autotrace on stat ;

mag@mutation_mutation > insert into mygtt (select segment_name from dba_segments);

1159 rows created.

Statistics
----------------------------------------------------------
        403  recursive calls
       1194  db block gets
      12681  consistent gets
         17  physical reads
     149052  redo size
       1013  bytes sent via SQL*Net to client
       1070  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
       1159  rows processed

--
-- the same operation in regular table will generate much lesser redo
-- 
 
mag@mutation_mutation > create table regular_table ( c1 varchar2(30));

Table created.

mag@mutation_mutation > insert into regular_table (select segment_name from dba_segments);

1160 rows created.

Statistics
----------------------------------------------------------
         23  recursive calls
         61  db block gets
      12587  consistent gets
          0  physical reads
      32696  redo size
       1019  bytes sent via SQL*Net to client
       1081  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1160  rows processed
   


>>how much redo for Temp table
I beleive it depends on numbe of rows.
>> it does generate redo. does it generates UNDO also
yes it does.
actually, let me re-phrase all i have said before, in a different view.

let us asume there is a DML in a GTT
DML to GTT will generate UNDO records.
By default, no REDO records are produced for this DML in GTT
But the changes that happen to the UNDO segments during this DML will generate REDO. And this REDO is what considered as the REDO GENERATED AS A RESULT OF DML OPERATION in GTT.
So actually we are supposed to control the undo generated.
But i dont think we can stop oracle producing undo records!.
Re: Does DML on Global temp table generates REDO [message #109019 is a reply to message #65992] Mon, 21 February 2005 03:39 Go to previous message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Thanks a ton Mahesh
Previous Topic: CBO estimates Bitmap Index retrieval at 100 times Btree cost.
Next Topic: Re: materialized view
Goto Forum:
  


Current Time: Tue Apr 16 05:41:31 CDT 2024