磁盘不太给力 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
海量数据插入性能测试
原创
©著作权归作者所有:来自51CTO博客作者maclean_007的原创作品,请联系作者获取转载授权,否则将追究法律责任
11.2.0.2的RAC系统中原本有一张大的分区表,之前为了测试exchange分区的性能需要将这张分区表上的部分分区数据复制到测试用表上,因为数据量比较大所以记以录之:
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
海量插入数据时
SqlBulkCopy 类,海量数据时效率一流
海量数据 sql JAVA