Re: Extracting 2.5 million rows
Date: Tue, 12 Jan 2010 17:44:24 +0000 (UTC)
Message-ID: <pan.2010.01.12.17.44.24_at_email.here.invalid>
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.comReceived on Tue Jan 12 2010 - 11:44:24 CST