Oracle-L: RE: Redos gone crazy--a job for audit?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 10 Oct 2003 11:24:25 -0800
Message-ID: <F001.005D2B80.20031010112425@fatcity.com>

Boris,

I used your script (well, almost: in your script you create temporary global table, but never use it, so, I modified it). And it shows "redo size" increase substantially lower (~7 times) in case of using temp table.
But, I was running script on 8.1.5.
When, running on 9.2 it appears, that you are correct: temp table generates much more "redo" than permanent table. Both results are shown below:

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> create table t6 (i int) ;

Table created.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

8780

SQL>
SQL> insert into t6 select obj# from sys.obj$ where rownum <= 10000;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

157964

SQL> => The "redo size" increase with permanent table is: 157964 - 8780 = 149184

SQL> create global temporary table t7 (i int) on commit 2 delete rows;

Table created.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

162060

SQL>
SQL> insert into t7 select obj# from sys.obj$ where rownum <= 10000;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

189264

SQL> => The "redo size" increase with global temporary table is: 189264 - 162060 = 27204

Now, on 9.2:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table t6 (i int) ;

Table created.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

7204

SQL>
SQL> insert into t6 select obj# from sys.obj$ where rownum <= 10000;

9038 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

150252

SQL> => The "redo size" increase with permanent table is: 150252 - 7204 = 143048

SQL> create global temporary table t7 (i int) on commit 2 delete rows;

Table created.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

154032

SQL>
SQL> insert into t7 select obj# from sys.obj$ where rownum <= 10000;

9039 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size';

VALUE

1287624

SQL>
=> The "redo size" increase with global temporary table is: 1287624 - 154032 = 1133592

which is quite different from the testing results under 8.1.5.

I don't have access to Metalink right now to check Bug# 2874489.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Boris Dali
Sent: Friday, October 10, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L

Igor,

Try running the following test:

create table t6 (i int) ;

create global temporary table t7 (i int) on commit delete rows;

select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size';

--> Note the value here

insert into t6 select obj# from sys.obj$ where rownum <= 10000;

commit;

select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size';

--> Compare the two. In my case it's almost 10-fold increase in the amount of redo generated.

We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms

Note: Bug above doesn't affect direct path load

Thanks,
Boris.

Igor Neyman <ineyman_at_perceptron.com> wrote: > Why?
> Did you have bad experiences with temp tables?
> I thought, using temp tables should reduce amount of
> redo.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> -----Original Message-----
> Boris Dali
> Sent: Friday, October 10, 2003 12:54 PM
> To: Multiple recipients of list ORACLE-L
>
> Barbara,
>
> Shoot in the dark. Any chance last vendor upgrade
> introduced global temporary tables?
>

Post your free ad now! http://personals.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: boris_dali_at_yahoo.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 10 2003 - 14:24:25 CDT