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/sec
cat /proc/cpuinfo |grep processor|wc -l
8
直接将源分区插入到目标分区表中
SQL> select count(*) from sales_history partition (SALES_1996) ;
COUNT(*)
----------
2568089600
SQL> 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_1997
SQL> 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.19
Workarea Size
SQL> 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 0
SQL> alter session set workarea_size_policy=MANUAL;
Session altered.
Elapsed: 00:00:00.04
SQL> alter session set sort_area_size=314572800;
Session altered.
Elapsed: 00:00:00.00
SQL> 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.68
SQL>@sort_activity
SQL_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.94238281
SQL> set linesize 200 pagesize 1400
SQL> col opname for a20
SQL> 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 INDEX
Sort Output 1529098 Blocks 1701 CREATE INDEX
