1.测试准备
----从1分15秒优化到31秒
----create public database link dblinkname connect to username identified by passwd using 'tnsname';
----select count(1) from user_tables@dl_11g2;
----SQL> set timing on
----SQL> create table t_zhong as select * from t_news_info@qianlong_link where 1=2;
Table created.
Elapsed: 00:00:00.13
---- truncate table t_zhong;
----SQL> set autot off
----SQL> drop table t_zhong purge;
----SQL> set autot trace stat
SQL> insert into t_zhong select * from qdata.T_NEWS_INFO@qianlong_link where seq>10400000;
114747 rows created.
Elapsed: 00:26:31.62
Statistics
----------------------------------------------------------
973 recursive calls
453767 db block gets
67045 consistent gets
29 physical reads
428009340 redo size
837 bytes sent via SQL*Net to client
837 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
58 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> create table t_test1 as select * from t_zhong where 1=2;
SQL> insert into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:01:15.87
Statistics
----------------------------------------------------------
214 recursive calls
303118 db block gets
95308 consistent gets
25749 physical reads
427150268 redo size
843 bytes sent via SQL*Net to client
794 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> truncate table t_test1;
SQL> analyze table t_test1 compute statistics;
SQL> analyze table t_zhong compute statistics;
SQL> select TABLE_NAME, BLOCKS , EMPTY_BLOCKS from dba_tables where table_name='T_TEST1';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T_TEST1 0 7
SQL> select TABLE_NAME, BLOCKS , EMPTY_BLOCKS from dba_tables where table_name='T_ZHONG';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T_ZHONG 21968 559
2.优化之前插入测试
SQL> set timing on
SQL> set autot trace stat
SQL> insert into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:01:15.21
Statistics
----------------------------------------------------------
217 recursive calls
303099 db block gets
95226 consistent gets
47717 physical reads
427226232 redo size
844 bytes sent via SQL*Net to client
794 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> truncate table t_test1;
3.优化1：将表修改为nologging
----设置表或索引为nologging，执行dml操作时就不会产生大量的redo咯
------注意：nologging在归档模式下有效，非归档下不起作用。库处在FORCE LOGGING模式下，此时的nologging方式是无效的
SQL> set autot off
SQL> select owner,table_name,TABLESPACE_NAME,LOGGING,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name='T_TEST1;
SQL> select NAME,LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;
NAME LOG_MODE OPEN_MODE FOR
--------- ------------ -------------------- ---
ORCL ARCHIVELOG READ WRITE NO
SQL> select table_name,logging from dba_tables where table_name='T_TEST1';
TABLE_NAME LOG
------------------------------ ---
T_TEST1 YES
SQL> alter table t_test1 nologging;
SQL> select table_name,logging from dba_tables where table_name='T_TEST1';
TABLE_NAME LOG
------------------------------ ---
T_TEST1 NO
SQL> set autot trace stat
SQL> insert into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:01:01.21
Statistics
----------------------------------------------------------
209 recursive calls
303076 db block gets
95257 consistent gets
47719 physical reads
427269008 redo size
845 bytes sent via SQL*Net to client
794 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> truncate table t_test1;
SQL> alter table t_test1 logging;
SQL> insert /*+nologging*/ into t_test1 select * from t_zhong;
4.优化2：将索引修改为nologging
select owner,index_name,index_type,table_name,logging,status from dba_indexes where table_name='T_ZHONG';
select * from dba_ind_columns where table_name='T_ZHONG';
select * from dba_objects where object_name='SYS_IL0000088940C00001$$';
SQL> select index_name, index_type，table_name,logging from dba_indexes where table_name='T_ZHONG';
INDEX_NAME INDEX_TYPE TABLE_NAME LOG
---------- ------------ -------- ---
SYS_IL0000088940C00001$$ LOB T_ZHONG YES
----注意，lob字段索引不能alter和drop，所以也就不能做nologging操作咯
----如果有索引可以设置nologging
alter index ind_name nologging;
----如果可以，可以直接删除索引，insert之后在重建
5.优化3：插入的采用append方式
SQL> truncate table t_test1;
----增加hint /*+append*/ 表示不用在高水位下查找可insert的空间，直接在高水位之上插入
SQL> insert /*+append*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:49.75
Statistics
----------------------------------------------------------
297 recursive calls
128430 db block gets
71998 consistent gets
47650 physical reads
255286784 redo size
833 bytes sent via SQL*Net to client
807 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> truncate table t_test1;
SQL> insert /*+append nologging*/ into t_test1 select * from t_zhong;
Elapsed: 00:00:46.72
a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有/*+append nologging*/ 才能大量减少redo。
b、insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。
6.优化4：采用parallel并行
SQL> truncate table t_test1;
----增加hint /*+parallel(tab,4)*/ 增加之前最好先开启会话并行 alter session enable/disable parallel dml; 表示充分利用系统硬件资源
----如果disable关闭并行会话时报ORA-12841错误，那就先做commit或者rollback在关闭并行
SQL> insert /*+parallel(t_test1,2)*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:01:07.09
Statistics
----------------------------------------------------------
216 recursive calls
303106 db block gets
95170 consistent gets
47718 physical reads
427238808 redo size
848 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
114747 rows processed
SQL> truncate table t_test1;
SQL> alter session enable parallel dml;
SQL> insert /*+parallel(t_test1,2)*/ into t_test1 select * from t_zhong;
Elapsed: 00:01:03.46
SQL> truncate table t_test1;
SQL> insert /*+append parallel(t_test1,2)*/ into t_test1 select * from t_zhong;
Elapsed: 00:00:52.04
------另外在插入后面跟的select查询语句，也可以进行优化，比如加一下hint /*+paralle*/等等。 加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过v$px_session查看，或者ps -ef |grep ora_p查看。
7.优化5：增大sort_area或PGA
show parameter sort_area_size
----检查sort_area_size初始大小为:65536 默认64k。以字节为单位
查看workarea_size_policy的值
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
设置workarea_size_policy的值
----SQL> alter system set workarea_size_policy=MANUAL scope=spfile;
System altered.
设置sort_area_size的值
----SQL> alter system set sort_area_size=65535000 scope=spfile;
System altered.
重启db service生效。
-------这里不修改系统参数，只是在会话中调整
----注意，这里的table还是处于nologging模式
SQL> truncate table t_test1;
SQL> alter session set workarea_size_policy=manual;
SQL> alter session set sort_area_size=50240000; ----改为50m
SQL> insert into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:44.15
SQL> truncate table t_test1;
SQL> insert /*+parallel*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:43.06
SQL> truncate table t_test1;
SQL> insert /*+append*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:39.85
SQL> truncate table t_test1;
SQL> insert /*+append parallel(t_test1,2)*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:35.44
SQL> truncate table t_test1;
SQL> insert /*+append parallel*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:33.39
SQL> truncate table t_test1;
SQL> insert /*+append nologging*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:31.79
SQL> truncate table t_test1;
SQL> insert /*+append nologging parallel*/ into t_test1 select * from t_zhong;
114747 rows created.
Elapsed: 00:00:29.72
----未开归档的情况下，最快貌似能达到21秒
Elapsed: 00:00:21.05
reference http://blog.itpub.net/26736162/
http://blog.csdn.net/wyzxg/article/