组合分区与范围分区效率对比测试（1）
来源：互联网 发布：wap多用户建站cms系统 编辑：程序博客网 时间：2024/05/12 23:39
1 测试数据准备
首先创建一个大表big_table：
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
向大表加入2千万条记录：
declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
创建组合分区版的大表脚本，一级分区采用范围分区，分区键采用LAST_DDL_TIME，二级分区采用散列分区，分区键是OWNER。
CREATE TABLE COMP_BIG_TABLE
(
"ID" NUMBER,
"OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
COMPRESS
NOLOGGING
PARTITION BY RANGE (LAST_DDL_TIME)
subpartition by hash(OWNER) subpartitions 4
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_1_sub_1,
subpartition part_1_sub_2,
subpartition part_1_sub_3,
subpartition part_1_sub_4
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2007','dd/mm/yyyy'))
(subpartition part_2_sub_1,
subpartition part_2_sub_2,
subpartition part_2_sub_3,
subpartition part_2_sub_4
),
PARTITION part_3
VALUES LESS THAN(to_date('01/01/2008','dd/mm/yyyy'))
(subpartition part_3_sub_1,
subpartition part_3_sub_2,
subpartition part_3_sub_3,
subpartition part_3_sub_4
),
PARTITION part_4
VALUES LESS THAN(to_date('01/01/2009','dd/mm/yyyy'))
(subpartition part_4_sub_1,
subpartition part_4_sub_2,
subpartition part_4_sub_3,
subpartition part_4_sub_4
),
PARTITION part_5
VALUES LESS THAN(to_date('01/01/2010','dd/mm/yyyy'))
(subpartition part_5_sub_1,
subpartition part_5_sub_2,
subpartition part_5_sub_3,
subpartition part_5_sub_4
),
PARTITION part_6
VALUES LESS THAN(to_date('01/01/2011','dd/mm/yyyy'))
(subpartition part_6_sub_1,
subpartition part_6_sub_2,
subpartition part_6_sub_3,
subpartition part_6_sub_4
),
PARTITION part_7
VALUES LESS THAN(to_date('01/01/2012','dd/mm/yyyy'))
(subpartition part_7_sub_1,
subpartition part_7_sub_2,
subpartition part_7_sub_3,
subpartition part_7_sub_4
)
)
/
创建范围分区版的大表脚本：
CREATE TABLE RANGE_BIG_TABLE
(
"ID" NUMBER,
"OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
COMPRESS
NOLOGGING
PARTITION BY RANGE (LAST_DDL_TIME)
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy')),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2007','dd/mm/yyyy')),
PARTITION part_3
VALUES LESS THAN(to_date('01/01/2008','dd/mm/yyyy')),
PARTITION part_4
VALUES LESS THAN(to_date('01/01/2009','dd/mm/yyyy')),
PARTITION part_5
VALUES LESS THAN(to_date('01/01/2010','dd/mm/yyyy')),
PARTITION part_6
VALUES LESS THAN(to_date('01/01/2011','dd/mm/yyyy')),
PARTITION part_7
VALUES LESS THAN(to_date('01/01/2012','dd/mm/yyyy'))
)
/
2 组合分区与范围分区插入效率对比
组合分区和范围分区对于堆组织表都支持直接路径加载，下面使用runstats工具进行直接路径加载的效率对比测试：
exec runstats_pkg.rs_start
Insert /*+ APPEND */ into COMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2005-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into COMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2006-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into COMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2007-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into COMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2008-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
exec runstats_pkg.rs_middle
Insert /*+ APPEND */ into RANGE_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2005-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into RANGE_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2006-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into RANGE_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2007-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
Insert /*+ APPEND */ into RANGE_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2008-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000
/
Commit;
exec runstats_pkg.rs_stop(500)
以下是测试结果：
Run1 ran in 43478 hsecs
Run2 ran in 50618 hsecs
run 1 ran in 85.89% of the time
Name Run1 Run2 Diff
LATCH.multiblock read objects 108,112 107,582 -530
LATCH.messages 3,238 3,805 567
LATCH.checkpoint queue latch 4,479 5,094 615
LATCH.object queue header oper 1,719,126 1,718,510 -616
STAT...consistent changes 1,397 779 -618
STAT...physical write total mu 11,744 11,054 -690
LATCH.enqueues 11,640 10,920 -720
STAT...physical write total IO 11,780 11,056 -724
STAT...physical write IO reque 11,780 11,056 -724
LATCH.begin backup scn array 11,780 11,056 -724
STAT...IMU undo allocation siz 208 1,036 828
LATCH.file cache latch 2,162 1,276 -886
STAT...cluster key scans 2,155 1,266 -889
STAT...table scan blocks gotte 875,316 874,422 -894
STAT...CR blocks created 2,153 1,258 -895
STAT...deferred (CURRENT) bloc 2,152 1,257 -895
STAT...commit cleanouts succes 2,152 1,256 -896
STAT...commit cleanouts 2,156 1,260 -896
STAT...index fetch by key 2,193 1,272 -921
LATCH.library cache lock 976 1,964 988
STAT...opened cursors cumulati 3,318 1,974 -1,344
STAT...parse count (total) 3,286 1,938 -1,348
STAT...execute count 3,326 1,974 -1,352
STAT...session cursor cache hi 3,300 1,940 -1,360
STAT...calls to kcmgas 3,309 1,937 -1,372
LATCH.simulator lru latch 111,248 109,725 -1,523
LATCH.dml lock allocation 4,385 2,789 -1,596
LATCH.SQL memory manager worka 9,757 11,375 1,618
STAT...db block gets direct 355,008 353,344 -1,664
STAT...physical writes 355,008 353,344 -1,664
STAT...physical writes direct 355,008 353,344 -1,664
STAT...physical writes non che 355,008 353,344 -1,664
STAT...cluster key scan block 4,307 2,526 -1,781
STAT...buffer is not pinned co 4,768 2,794 -1,974
STAT...consistent gets - exami 4,785 2,743 -2,042
STAT...enqueue releases 5,414 3,175 -2,239
STAT...enqueue requests 5,414 3,175 -2,239
STAT...no work - consistent re 878,554 876,278 -2,276
LATCH.undo global data 6,983 4,525 -2,458
LATCH.library cache 15,701 12,765 -2,936
LATCH.enqueue hash chains 18,268 15,278 -2,990
STAT...calls to get snapshot s 7,805 4,707 -3,098
LATCH.simulator hash latch 629,285 625,969 -3,316
LATCH.library cache pin 14,620 10,894 -3,726
STAT...physical reads cache pr 808,622 804,213 -4,409
STAT...physical reads 862,756 858,019 -4,737
STAT...physical reads cache 862,756 858,019 -4,737
STAT...free buffer requested 864,974 859,325 -5,649
STAT...redo entries 23,722 18,036 -5,686
STAT...consistent gets from ca 888,023 881,890 -6,133
STAT...consistent gets 888,023 881,890 -6,133
STAT...db block gets from cach 15,320 8,943 -6,377
STAT...DB time 43,452 50,518 7,066
STAT...user I/O wait time 14,240 21,344 7,104
STAT...Elapsed Time 43,484 50,619 7,135
LATCH.cache buffers lru chain 867,968 860,557 -7,411
STAT...db block changes 18,481 10,813 -7,668
STAT...db block gets 370,328 362,287 -8,041
LATCH.row cache objects 33,281 22,342 -10,939
STAT...IMU Redo allocation siz 0 11,048 11,048
STAT...session logical reads 1,258,351 1,244,177 -14,174
STAT...table scan rows gotten 60,059,488 60,039,820 -19,668
STAT...recursive calls 48,596 28,859 -19,737
LATCH.session allocation 91,753 56,715 -35,038
LATCH.cache buffers chains 2,709,139 2,668,652 -40,487
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 65,536 0 -65,536
STAT...undo change vector size 632,436 369,776 -262,660
STAT...redo size 2,880,980 1,903,056 -977,924
STAT...physical write bytes ######################## -13,631,488
STAT...physical write total by######################## -13,631,488
STAT...physical read bytes ######################## -38,805,504
STAT...physical read total byt######################## -38,805,504
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
6,547,324 6,437,288 -110,036 101.71%
组合分区稍快一点。
- 组合分区与范围分区效率对比测试（1）
- 组合分区与范围分区效率对比测试（2）
- 组合分区与范围分区效率对比测试(3)
- mysql分区效率测试
- 复合分区 （范围-散列分区，范围-列表分区）
- 创建range-hash（范围-哈希）组合分区！
- 创建range-list（范围-列表）组合分区！
- MySQL表分区（1）范围分区-range
- 普通表索引，分区表局部索引和分区表的全局索引分区的效率对比测试
- 创建range（范围）分区！
- 组合分区
- 分区表与分区索引（一）：范围分区表的操作
- 分区表与分区索引（二）：范围分区表的数据管理
- Oracle表分区：范围分区
- mysql分区分表对比测试过程
- Oracle--分区表（范围分区、Hash分区、等）
- Oracle--分区表（范围分区、Hash分区、等）
- 测试分区维护对于字段索引的影响（包括本地/全局索引分区与普通分区）
- 【自我管理】精力，而非时间，是高效能的基础
- S3C2440A bootloader之Stepldr.bib
- 人工神经网络分类
- 设计模式之代理模式
- 使用天乐软件加密狗(JDProtect)保护您的软件，防止程序被跟踪/逆向/反编译/破解
- 组合分区与范围分区效率对比测试（1）
- 如何使链接打开一个没有菜单栏、地址栏、工具条和状态栏的窗口
- Ssldump 编译过程、解密过程以及通过openssl在iis上配置https服务器
- webEX面试
- Visual SourceSafe 入门教学
- 指针和引用有什么分别；如果传引用比传指针安全，为什么？如果我使用常量指针难道不行吗？
- MeeGo平板系统源码已经发布
- 历史系统的现代化改造之路
- 组合分区与范围分区效率对比测试（2）
