Re: Extracting 2.5 million rows

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 7 Jan 2010 20:05:21 +0000 (UTC)
Message-ID: <pan.2010.01.07.20.05.21_at_email.here.invalid>

On Thu, 07 Jan 2010 18:38:12 +0000, Jonathan Lewis wrote:

> "Mladen Gogala" <no_at_email.here.invalid> wrote in message
> news:pan.2010.01.07.16.58.25_at_email.here.invalid...

>> On Thu, 07 Jan 2010 10:37:08 +0000, Jonathan Lewis wrote:
>>
>>> how about:
>>>      create audit_clone nologging
>>>     as
>>>     select (required columns) from audit_table_at_remote ?
>>
>> That's going to do wonders on the remote rollback segments.
>>
>>
>>
>> --
>> http://mgogala.byethost5.com

>
>
> It shouldn't generate any more undo and redo on "remote" than the
> export; and if you're worried about that you should also be worried
> about the changes of the export failing with ORA-01555 anyway.
>
> True, the remote select will acquire a transaction table slot, and stop
> one undo segment from wrapping until the CTAS is complete - and that can
> have unpleasant side effects (like the example I blogged about
> http://jonathanlewis.wordpress.com/2009/10/07/undone/ ), so it will be
> worth testing how fast the CTAS can run with a "rownum" limit to get an
> estimate of how long the entire operation might take.
>
> Bear in mind, the CTAS may operate faster than the select used by exp
> (and that's something worth testing), and it's always possible that the
> analysis of the audit_table doesn't need all the columns - and the CTAS
> can be selective on columns, unlike the export which has to select all
> columns.

There is a very primitive and efficient trick to prevent using the remote undo segments and it is not always applicable. Making the tablespace read only will, in effect, cause CTAS to use "direct reads" (quotes are deliberate because it still goes through the SGA), thus bypassing the UNDO segments and not generating redo on the remote side. Of course, if the configuration is unlucky enough to have tables that must be updated frequently in the same tablespace as the table that the OP needs to export, the trick isn't applicable. Interestingly enough, oracle 11.2 can make a table read only and that prevents the transaction from using any undo. That is logical because UNDO blocks are consumed by the transactions and only used by queries, if necessary, to maintain the consistency. If the table is read only, there can be no transactions. Any DML fails immediately:

SQL> alter table emp read only;

Table altered.

Elapsed: 00:00:00.12
SQL> delete from emp;
delete from emp

            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP"

Elapsed: 00:00:00.20

The problem with that is that it can only be done with Oracle11. Oracle10 doesn't understand the command:

SQL> connect scott/tiger_at_stag3;
Connected.
SQL> alter table emp read only;
alter table emp read only

                *

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Elapsed: 00:00:00.09
SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.10
SQL> I find read only tablespaces extremely useful in the RAC environment. They do wonders for DLM. The GC_* waits vanish into thin air, just like the evil spirits. Just marvelous, especially for the DW type databases. It's much harder to have read only tablespaces in an OLTP database.

-- 
http://mgogala.byethost5.com

Received on Thu Jan 07 2010 - 14:05:21 CST