DML execution cost preliminary analysis

[Piao songmei]

case:

CREATE GLOBAL TABLE wish_5(     
    host_year INT, 
    nation CHAR(3000), 
    gold INT, 
    silver INT, 
    bronze INT)
PARTITION BY HASH ( host_year) PARTITIONS 4 ON NODE 'node1','node2';
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (2008, 'US', 32, 28, 28);
INSERT INTO wish_5 VALUES (2012, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (2008, 'US', 32, 28, 28);
INSERT INTO wish_5 VALUES (2012, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (2008, 'US', 32, 28, 28);
INSERT INTO wish_5 VALUES (2012, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (2008, 'US', 32, 28, 28);
INSERT INTO wish_5 VALUES (2012, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (2008, 'US', 32, 28, 28);
INSERT INTO wish_5 VALUES (2012, 'JPN', 16, 9, 12);
INSERT INTO wish_5 VALUES (1988, 'NZL', 3, 2, 8);
INSERT INTO wish_5 VALUES (1992, 'CAN', 3, 2, 5);
INSERT INTO wish_5 VALUES (1996, 'KOR', 7, 15, 5);
INSERT INTO wish_5 VALUES (2000, 'RUS', 32, 28, 28);
INSERT INTO wish_5 VALUES (2004, 'JPN', 16, 9, 12);
select * from wish_5;

analyze:

first time:
totle cost:
4.390 second, count of total rows : 40

detail:
1. server execution start @ 58.203s
2. scan start         @ 58.250s
1. remote scan @ 58.250s ~ 58.250s -> 0s
2. local scan         @ 58.250s ~ 58.265s -> 15ms
3. remote scan @ 58.265s ~ 00.531s -> 2.266s
4. get remote page @ 00.531s ~ 00.546s -> 15ms
                @ 00.546s ~ 00.562s -> 16ms
5. local scan         @ 00.562s ~ 00.562s -> 0s
3. scan end         @ 00.562s
4. server execution end @ 00.562s

totle server cost: 2.359s
so the cas cost: 2.031s

second time:
totle cost:
0.078 second , count of total rows : 40

detail:
1. server execution start @ 18.406s
2. scan start         @ 18.406s
1. remote scan @ 18.406s ~ 18.406s -> 0s
2. local scan         @ 18.406s ~ 18.406s -> 0s
3. remote scan @ 18.406s ~ 18.406s -> 0s
4. get remote page @ 18.406s ~ 18.421s -> 15ms
                @ 18.421s ~ 18.437s -> 16ms
5. local scan         @ 18.437s ~ 18.437s -> 0s
3. scan end         @ 18.437s
4. server execution end @ 18.437s

totle server cost: 31ms
so the cas cost: 47ms