组合分区与范围分区效率对比测试(3)

1      组合分区与范围分区删除效率对比

删除一个owner的数据:

exec runstats_pkg.rs_start

delete from comp_big_table where owner = ‘SCOTT’

/

commit

/

exec runstats_pkg.rs_middle

 

delete from range_big_table where owner = ‘SCOTT’

/

commit

/

exec runstats_pkg.rs_stop(500)

 

Run1 ran in 1464 hsecs

Run2 ran in 7895 hsecs

run 1 ran in 18.54% of the time

       

Name                                  Run1        Run2        Diff

STAT...commit cleanouts succes       2,215       1,630        -585

LATCH.row cache objects                373         963         590

STAT...change write time               651       1,252         601

STAT...table scan rows gotten            0         660         660

STAT...CPU used when call star         725       1,391         666

STAT...CPU used by this sessio         725       1,391         666

STAT...IMU undo allocation siz          52         880         828

LATCH.enqueues                         256       1,415       1,159

LATCH.enqueue hash chains              271       1,444       1,173

LATCH.SQL memory manager worka         347       1,754       1,407

LATCH.active checkpoint queue            5       1,782       1,777

LATCH.redo writing                      39       1,918       1,879

LATCH.session allocation                79       2,532       2,453

STAT...hot buffers moved to he       2,296       5,520       3,224

LATCH.messages                         117       4,078       3,961

STAT...user I/O wait time              764       6,552       5,788

STAT...DB time                       1,463       7,878       6,415

STAT...Elapsed Time                  1,469       7,900       6,431

STAT...physical reads                5,564      12,809       7,245

STAT...physical reads cache          5,564      12,809       7,245

STAT...physical read IO reques       5,528      12,809       7,281

STAT...physical read total IO        5,528      12,809       7,281

STAT...free buffer requested         6,074      13,436       7,362

STAT...dirty buffers inspected           0       9,239       9,239

STAT...redo entries                 25,098      35,819      10,721

LATCH.simulator lru latch            1,225      12,175      10,950

STAT...IMU Redo allocation siz           0      11,036      11,036

STAT...free buffer inspected         1,893      13,468      11,575

LATCH.simulator hash latch           2,375      14,684      12,309

STAT...db block gets from cach      27,068      40,901      13,833

STAT...db block gets                27,068      40,901      13,833

STAT...session logical reads        27,139      41,058      13,919

LATCH.checkpoint queue latch           100      17,383      17,283

STAT...db block changes             50,745      72,268      21,523

LATCH.cache buffers lru chain        6,096      43,763      37,667

LATCH.object queue header oper      12,138      60,450      48,312

STAT...session uga memory           65,464           0     -65,464

STAT...session pga memory           65,536           0     -65,536

LATCH.cache buffers chains         167,345     284,197     116,852

STAT...session uga memory max      254,380      73,048    -181,332

STAT...session pga memory max      851,968           0    -851,968

STAT...undo change vector size   3,887,600   4,844,396     956,796

STAT...redo size                 7,392,532   9,824,440   2,431,908

STAT...physical read bytes      45,580,288 104,931,328  59,351,040

STAT...physical read total byt  45,580,288 104,931,328  59,351,040

       

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

     192,549     452,638     260,089     42.54%

 

按照owner删除，组合分区的效率要高得多。

 

删除一年数据(分区删除)：

exec runstats_pkg.rs_start

alter table comp_big_table drop partition part_1;

exec runstats_pkg.rs_middle

alter table range_big_table drop partition part_1;

exec runstats_pkg.rs_stop(500)

 

测试结果：

Run1 ran in 265 hsecs

Run2 ran in 50 hsecs

run 1 ran in 530% of the time

       

Name                                  Run1        Run2        Diff

STAT...table fetch by rowid            772         210        -562

LATCH.enqueue hash chains              830         204        -626

LATCH.file cache latch               1,564         930        -634

STAT...session cursor cache hi         872         205        -667

STAT...opened cursors cumulati         975         282        -693

STAT...execute count                 1,030         291        -739

STAT...sorts (rows)                  3,324       2,582        -742

STAT...calls to get snapshot s       1,181         425        -756

STAT...IMU undo allocation siz          52         880         828

STAT...no work - consistent re       1,193         324        -869

LATCH.simulator lru latch            1,120         197        -923

STAT...consistent gets - exami       1,548         413      -1,135

LATCH.simulator hash latch           1,518         206      -1,312

STAT...buffer is not pinned co       1,873         509      -1,364

STAT...redo entries                  3,899       1,793      -2,106

LATCH.library cache pin              3,180       1,044      -2,136

LATCH.cache buffers lru chain        2,435         137      -2,298

LATCH.object queue header oper       2,569         179      -2,390

STAT...consistent gets               3,342         902      -2,440

STAT...consistent gets from ca       3,342         902      -2,440

LATCH.library cache                  4,637       1,694      -2,943

LATCH.shared pool                    3,922         788      -3,134

STAT...db block gets from cach       6,237       2,918      -3,319

STAT...db block gets                 6,237       2,918      -3,319

STAT...db block changes              6,211       2,660      -3,551

LATCH.row cache objects              4,936       1,257      -3,679

STAT...session logical reads         9,579       3,820      -5,759

STAT...recursive calls              14,761       4,086     -10,675

STAT...IMU Redo allocation siz           0      11,028      11,028

LATCH.session allocation            17,927       4,589     -13,338

LATCH.cache buffers chains          38,299      12,045     -26,254

STAT...session uga memory                0      65,464      65,464

STAT...session pga memory                0      65,536      65,536

STAT...undo change vector size     166,316      43,076    -123,240

STAT...session uga memory max      254,380       7,584    -246,796

STAT...session pga memory max      262,144           0    -262,144

STAT...redo size                   610,128     236,536    -373,592

STAT...physical read bytes       1,171,456     311,296    -860,160

STAT...physical read total byt   1,171,456     311,296    -860,160

       

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

      87,277      24,733     -62,544    352.88%

 

删除单个分区，范围分区优势明显。对于单个分区，组合分区因为有子分区的缘故，比范围分区要复杂，所以删除慢。

2      组合分区与范围分区分区交换效率对比

CREATE TABLE comp_temp_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 hash(OWNER)

(partition part_1_sub_1,

 partition part_1_sub_2,

 partition part_1_sub_3,

 partition part_1_sub_4

)

/

 

CREATE TABLE range_temp_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

/

 

exec runstats_pkg.rs_start

Insert /*+ APPEND  NOLOGGING*/  into COMP_TEMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2009-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000

/

Commit;

Alter table comp_big_table

Exchange partition part_5

With table comp_temp_big_table

Including indexes

Without validation

/

exec runstats_pkg.rs_middle

Insert /*+ APPEND  NOLOGGING*/  into RANGE_TEMP_BIG_TABLE select ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,to_date(‘2009-01-01’,’yyyy-mm-dd’)+mod(rownum,360),TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from big_table where rownum<= 15000000

/

Commit;

Alter table range_big_table

Exchange partition part_5

With table range_temp_big_table

Including indexes

Without validation

/

exec runstats_pkg.rs_stop(500)

 

测试结果如下：

Run1 ran in 10868 hsecs

Run2 ran in 10969 hsecs

run 1 ran in 99.08% of the time

       

Name                                  Run1        Run2        Diff

STAT...session cursor cache hi       1,009         507        -502

LATCH.messages                       1,263         759        -504

LATCH.redo allocation                  671         157        -514

LATCH.redo writing                     758         239        -519

STAT...opened cursors cumulati       1,058         525        -533

STAT...cluster key scan block        1,198         652        -546

STAT...execute count                 1,080         527        -553

STAT...enqueue requests              1,362         794        -568

STAT...enqueue releases              1,363         794        -569

STAT...no work - consistent re     219,894     219,119        -775

STAT...buffer is not pinned co       1,584         769        -815

STAT...consistent gets - exami       1,549         719        -830

LATCH.dml lock allocation            1,600         636        -964

STAT...calls to get snapshot s       2,271       1,246      -1,025

LATCH.simulator lru latch           28,483      27,392      -1,091

STAT...IMU undo allocation siz       2,012         880      -1,132

LATCH.shared pool                   28,958      27,592      -1,366

STAT...redo entries                  5,946       4,518      -1,428

LATCH.simulator hash latch         158,188     156,663      -1,525

LATCH.undo global data               2,543         997      -1,546

STAT...db block gets from cach       3,824       2,249      -1,575

STAT...db block changes              4,636       2,730      -1,906

STAT...db block gets                92,576      90,585      -1,991

LATCH.enqueue hash chains            5,519       3,354      -2,165

STAT...consistent gets from ca     222,773     220,572      -2,201

STAT...consistent gets             222,773     220,572      -2,201

LATCH.library cache lock             2,817         175      -2,642

STAT...session logical reads       315,349     311,157      -4,192

STAT...table scan rows gotten   15,014,880  15,009,944      -4,936

LATCH.row cache objects              9,894       4,745      -5,149

LATCH.library cache pin              7,517       2,282      -5,235

LATCH.library cache                 10,173       2,452      -7,721

STAT...recursive calls              16,423       7,752      -8,671

STAT...IMU Redo allocation siz         312      11,016      10,704

LATCH.session allocation            29,280      13,196     -16,084

LATCH.cache buffers chains         687,296     669,013     -18,283

STAT...undo change vector size     159,660      94,100     -65,560

STAT...session uga memory           65,560           0     -65,560

STAT...session pga memory          196,608           0    -196,608

STAT...redo size                   722,844     477,884    -244,960

STAT...physical read bytes    ########################    -901,120

STAT...physical read total byt########################    -901,120

STAT...session pga memory max    2,686,976           0  -2,686,976

STAT...physical write total by 727,056,384 723,648,512  -3,407,872

STAT...physical write bytes    727,056,384 723,648,512  -3,407,872

       

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

   1,684,577   1,616,705     -67,872    104.20%

 

时间主要消耗在直接路径加载上，分区交换的时间可以忽略，性能差不多。