海量数据插入性能测试

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

沪ICP备14014813号

沪公网安备 31010802001379号