Prev: Quick move tablespace
Next: LONG to CLOB within a trigger - alternative to synching data values?
From: Mladen Gogala on 7 Jan 2010 15:05
On Thu, 07 Jan 2010 18:38:12 +0000, Jonathan Lewis wrote:

> "Mladen Gogala" <no(a)email.here.invalid> wrote in message
> news:pan.2010.01.07.16.58.25(a)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(a)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(a)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
From: vsevolod afanassiev on 8 Jan 2010 03:41
I suggest creating indexes (online) in the production database and
doing analysis right there (assuming there is space for indexes).
Another option is to create summary table/materialized view and use it
for analysis. If your production box is big enough
then running one query at a time shouldn't affect performane for other
queries. The only disadvantage is that you may not be able to
create bitmap indexes on the audit table as they don't allow multiple
simultaneous updates.

If this is not possible then transportable tablespaces are the best
option for moving entire table.

Another alternative is Data Pump (assuming this is 10g or 11g). The
advantage is that you don't need to export entire table,
export a small subset first and play with it, once you are happy with
it then run it for entire table.
Data Pump can be used to select data from one database and
simultaneously insert into another,
without using any dump file.

The same approach can be used with Export - it has parameter QUERY
that alows to select part of the table.

Finally SQL*Plus can also be used to select subset of data, either by
specifying WHERE clause or by
using SAMPLE clause of SELECT.

Many systems run on weekly cycle, large historic table may get 10 mil
records on Monday, 11 on Tuesday,
13 on Wednesday, 9 on Thursday and 10 on Friday, then 5 on Saturday
and 3 on Sunday. So start from analyzing one day, then on week.



From: Cliff on 9 Jan 2010 12:02
On 2010-01-06 19:46:45 -0500, Cliff said:

> Hey all,
>
> I was looking for some guidance. I need to do some stat analysis on a
> production audit table. One problem is that there are no indexes on
> the audit table and spooling via sqlplus would take years.
>
> Using Export on the server, the disk space required for the dmp file is
> about 40 G ( per the DBA ). This space is not available on the UNIX
> box.
>
> Would running EXP locally be a valid option?
>
> I realize that any solution is going to take a while of processing
> time; it's just that I don't want to get 100-hours in and then find a
> fatal flaw.
>
> Thoughts?
>
> TIA,
> Cliff



Thanks for everyone's responses. I did find out that 2.5 million is
for one of the test environments; production has 390 million. Luckily,
I plan only to extract from 2005 through 2009 receiving a new total of
about 15 million.

I have been working with one of our DBAs and I'm definitely go through
a Proof of Concept with just a month's worth of data to get all of the
network stats and ora segment effects. I've also engaged one of our
SAS programmers to help with the stats.

My original post was to just get some ideas as my active Oracle days
are back with version 7.. <No jokes please>

Thanks again,
Cliff

From: Hemant K Chitale on 11 Jan 2010 21:54

>
> 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- Hide quoted text -
>
> - Show quoted text -


You should control the urge to present humour or cynicism. Like the
joke about "EXP" being illegal in Texas, stating that read only
tablespaces are very useful in RAC becaue GC_% waits 'vanish' may
well be misunderstood.

Hemant K Chitale
From: Mladen Gogala on 12 Jan 2010 12:44
On Mon, 11 Jan 2010 18:54:08 -0800, Hemant K Chitale wrote:

> stating that read only
> tablespaces are very useful in RAC becaue GC_% waits 'vanish' may well
> be misunderstood.

Actually, that was not a joke. Making tablespaces read only is rather
obscure but efficient technique to eliminate the transaction control. In
a DW environment, I regularly make tablespaces read only. There was a
debate whether to startup all the instances in the read only mode but
there are certain reports that use intermediate tables, frequently
erroneously called "temporary tables", which have prevented me from doing
so. I practice what I preach. Here is what I am talking about:

SQL> select inst_id,database_status,instance_name,status
2 from gv$instance;

INST_ID DATABASE_STATUS INSTANCE_NAME STATUS
---------- ----------------- ---------------- ------------
2 ACTIVE NEWS2 OPEN
1 ACTIVE NEWS1 OPEN

Elapsed: 00:00:00.10

SQL> select tablespace_name,status from dba_tablespaces
2 order by status,tablespace_name;

TABLESPACE_NAME STATUS
------------------------------ ---------
ARSYSTEM ONLINE
MEDIACATALOG_DATA ONLINE
NEWS_SEARCH_INBOUND_DAT ONLINE
NS_DATA ONLINE
NS_INBOUND_DAT ONLINE
NS_OUTBOUND_DATA ONLINE
SYSAUX ONLINE
SYSTEM ONLINE
TEMP ONLINE
UNDOTBS1 ONLINE

TABLESPACE_NAME STATUS
------------------------------ ---------
UNDOTBS2 ONLINE
USERS ONLINE
NEWS_ARCHIVE_CLOB READ ONLY
NEWS_ARCHIVE_DATA READ ONLY
NEWS_ARCHIVE_IDX READ ONLY
NEWS_DATA READ ONLY

17 rows selected.

Elapsed: 00:00:00.10
SQL>

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
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.18
SQL>


The read only tablespaces belong to a DW application and, believe it or
not, there aren't any enqueue waits or GC_* waits - ever. The read only
tablespaces are made writable during the ETL process, than locked again.
This technique might be obscure but it really helps with the large
exports and data extractions. And no, I am not always joking. Most of the
time, but not always. I am carefully looking into read only tables in 11G
because that feature may also be helpful when it comes to making DLM work
less. Big data extractions, exports and reports, the very purpose of data
warehouses, are the ideal application for such a tuning trick.

--
http://mgogala.byethost5.com
First  |  Prev  | 
Pages: 1 2 3
Prev: Quick move tablespace
Next: LONG to CLOB within a trigger - alternative to synching data values?