11.2.0.2的RAC系统中原本有一张大的分区表，之前为了测试exchange分区的性能需要将这张分区表上的部分分区数据复制到测试用表上，因为数据量比较大所以记以录之:

磁盘不太给力 hdparm -tT /dev/sdd/dev/sdd: Timing cached reads:   13672 MB in  2.00 seconds = 6840.55 MB/sec Timing buffered disk reads:  605 MB in  3.02 seconds = 200.33 MB/seccat /proc/cpuinfo |grep processor|wc -l8直接将源分区插入到目标分区表中SQL> select count(*) from sales_history partition (SALES_1996) ;  COUNT(*)----------2568089600SQL> select (bytes) / 1024 / 1024, segment_name, partition_name  2    from dba_segments  3   where segment_name = 'SALES_HISTORY'  4   order by bytes desc  5  /(BYTES)/1024/1024 SEGMENT_NAME         PARTITION_NAME----------------- -------------------- ------------------------------           288710 SALES_HISTORY        SALES_1996              232 SALES_HISTORY        SALES_H2_1997              232 SALES_HISTORY        SALES_H1_1997SQL> set timing on;SQL> alter session enable parallel dml;Session altered.SQL> insert /*+ append parallel(ss,4) */  2  into sales ss  3    select /*+ parallel(sh,4) */ * from sales_history  partition(SALES_1996) sh ;Elapsed: 01:01:08.03                             -- 耗时61分钟SQL> commit;Commit complete.Elapsed: 00:00:00.19Workarea SizeSQL> SELECT  2         sql_id,  3         operation_type,  4         policy,  5         active_time,  6         work_area_size,  7         expected_size,  8         actual_mem_used,  9         max_mem_used, 10         number_passes, 11         tempseg_size 12    FROM (SELECT swa.workarea_address, 13                 swa.sql_id, 14                 sa.sql_text, 15                 swa.operation_type, 16                 swa.policy, 17                 swa.sid, 18                 swa.active_time / 1000 active_time, 19                 swa.work_area_size, 20                 swa.expected_size,               swa.actual_mem_used,               swa.max_mem_used,               swa.number_passes,               swa.tempseg_size,               swa.tablespace,               (CASE                 WHEN sl.totalwork <> 0 THEN                  sl.sofar / sl.totalwork 21   22   23   24   25   26   27   28   29                   ELSE 30                    NULL 31                 END) complete_ratio, 32                 sl.elapsed_seconds * 1000 elapsed, 33                 sl.time_remaining * 1000 time_remaining, 34                 sl.opname, 35                 s.machine,               s.program, 36   37                 s.module, 38                 s.osuser, 39                 NVL(DECODE(TYPE, 40                            'BACKGROUND', 41                            'SYS (' || b.ksbdpnam || ')', 42                            s.username), 43                     SUBSTR(p.program, INSTR(p.program, '('))) username, 44                 ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum 45            FROM v$sql_workarea_active swa, 46                 v$sqlarea sa, 47                 (SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl, 48                 v$session s, 49                 v$process p, 50                 x$ksbdp b 51           WHERE sl.sid(+) = swa.sid 52             AND sl.sql_id(+) = swa.sql_id 53             AND swa.sid <> USERENV('sid') 54             AND sa.sql_id = swa.sql_id 55             AND s.sid = swa.sid 56             AND s.paddr = p.addr 57             AND b.inst_id(+) = USERENV('INSTANCE') 58             AND p.addr = b.ksbdppro(+) 59           ORDER BY swa.number_passes DESC, swa.work_area_size DESC) 60   WHERE rnum = 1 61  /SQL_ID        OPERATION_TYPE                 POLICY ACTIVE_TIME WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE------------- ------------------------------ ------ ----------- -------------- ------------- --------------- ------------ ------------- ------------ak9ht406k4zn4 LOAD WRITE BUFFERS             AUTO    889394.542         541696       1048576          541696       541696             0SQL> alter session set workarea_size_policy=MANUAL;Session altered.Elapsed: 00:00:00.04SQL> alter session set sort_area_size=314572800;Session altered.Elapsed: 00:00:00.00SQL> alter session set sort_area_size=314572800;Session altered.创建索引create index ind_sales on sales(prod_id,cust_id,time_id,channel_id)  nologging parallel 8/Index created.Elapsed: 01:04:12.68SQL>@sort_activitySQL_ID        OPERATION_TYPE                 POLICY ACTIVE_TIME/1000 WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE_IN_GB------------- ------------------------------ ------ ---------------- -------------- ------------- --------------- ------------ ------------- ------------------490ntjgc2dass SORT (v2)                      MANUAL       1275.18291              0                     287324160    310392832             1         6.94238281SQL> set linesize 200 pagesize 1400SQL> col opname for a20SQL> select opname,totalwork,units,elapsed_seconds,sql_plan_options from v$session_longops  where opname='Sort Output';OPNAME                TOTALWORK UNITS                            ELAPSED_SECONDS SQL_PLAN_OPTIONS-------------------- ---------- -------------------------------- --------------- ------------------------------Sort Output             1528129 Blocks                                      1809 CREATE INDEXSort Output             1529098 Blocks                                      1701 CREATE INDEX