Trafodion

Following update statistics, stats do not take effect immediately.

Bug #1409937 reported by Julie Thai on 2015-01-12

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	Trafodion	In Progress	High	Barry Fritchman	Trafodion r2.0

Bug Description

On 20150106 & 20150110 builds, immediately following an updatestats, query plan generated does not seem to reflect the existence of stats. In a session, create, & populate table, run updatestats, prepare query and exit. A serial plan is generated and est cardinality is 100 for both tables. In a new session, prepare the same query and a parallel plan is generated, where est cardinality reflects stats.

FIRST SESSION:
>>create table tbl1(
+> t1_c1 integer unsigned
+> ,t1_c2 char(10) character set iso88591
+> ,t1_c3 largeint
+> ,t1_c4 int
+> ,t1_c5 int
+> ,t1_id1 int not null
+> ,t1_id2 int not null)
+>hash partition
+>store by (t1_id1)
+>salt using 8 partitions
+>;

--- SQL operation complete.
>>load into tbl1
+>select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
+> ,cast(c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 as char(10) character set iso88591)
+> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
+> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
+> ,c1
+> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
+> ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
+>from (values(1)) t
+>transpose 0,1,2,3,4,5,6,7,8,9 as c1
+>transpose 0,1,2,3,4,5,6,7,8,9 as c2
+>transpose 0,1,2,3,4,5,6,7,8,9 as c3
+>transpose 0,1,2,3,4,5,6,7,8,9 as c4
+>transpose 0,1,2,3,4,5,6,7,8,9 as c5
+>transpose 0,1,2,3,4,5,6,7,8,9 as c6
+>;
Task: LOAD Status: Started Object: TRAFODION.ASCH.TBL1
Task: CLEANUP Status: Started Object: TRAFODION.ASCH.TBL1
Task: CLEANUP Status: Ended Object: TRAFODION.ASCH.TBL1
Task: DISABLE INDEXE Status: Started Object: TRAFODION.ASCH.TBL1
Task: DISABLE INDEXE Status: Ended Object: TRAFODION.ASCH.TBL1
Task: PREPARATION Status: Started Object: TRAFODION.ASCH.TBL1
Rows Processed: 1000000
Task: PREPARATION Status: Ended ET: 00:00:23.512
Task: COMPLETION Status: Started Object: TRAFODION.ASCH.TBL1
Task: COMPLETION Status: Ended ET: 00:00:01.564
Task: POPULATE INDEX Status: Started Object: TRAFODION.ASCH.TBL1
Task: POPULATE INDEX Status: Ended ET: 00:00:03.479

--- 1000000 row(s) loaded.
>>
>>update tbl1 set t1_c1=7777 where t1_id1 between 3000 and 6999;

--- 4000 row(s) updated.
>>update tbl1 set t1_c1=7777 where t1_id1 between 205000 and 206999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c1=7777 where t1_id1 between 410000 and 411999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c1=7777 where t1_id1 between 615000 and 615999;

--- 1000 row(s) updated.
>>update tbl1 set t1_c1=7777 where t1_id1 between 820000 and 820999;

--- 1000 row(s) updated.
>>update tbl1 set t1_c2='7777' where t1_id1 between 2500 and 6499;

--- 4000 row(s) updated.
>>update tbl1 set t1_c2='7777' where t1_id1 between 204000 and 205999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c2='7777' where t1_id1 between 411000 and 412999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c2='7777' where t1_id1 between 614500 and 615499;

--- 1000 row(s) updated.
>>update tbl1 set t1_c2='7777' where t1_id1 between 820500 and 821499;

--- 1000 row(s) updated.
>>update tbl1 set t1_c3=888 where t1_id1 between 2000 and 7999;

--- 6000 row(s) updated.
>>update tbl1 set t1_c3=888 where t1_id1 between 205000 and 205999;

--- 1000 row(s) updated.
>>update tbl1 set t1_c3=888 where t1_id1 between 410500 and 411499;

--- 1000 row(s) updated.
>>update tbl1 set t1_c3=888 where t1_id1 between 615500 and 616499;

--- 1000 row(s) updated.
>>update tbl1 set t1_c3=888 where t1_id1 between 820500 and 821499;

--- 1000 row(s) updated.
>>update tbl1 set t1_c4=999 where t1_id1 between 3000 and 6999;

--- 4000 row(s) updated.
>>update tbl1 set t1_c4=999 where t1_id1 between 205000 and 206999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c4=999 where t1_id1 between 410000 and 411999;

--- 2000 row(s) updated.
>>update tbl1 set t1_c4=999 where t1_id1 between 615000 and 615999;

--- 1000 row(s) updated.
>>update tbl1 set t1_c4=999 where t1_id1 between 820000 and 820999;

--- 1000 row(s) updated.
>>
>>create table tbl2(
+> t2_c1 integer unsigned
+> ,t2_c2 char(10) character set iso88591
+> ,t2_c3 largeint
+> ,t2_c4 int
+> ,t2_c5 int
+> ,t2_id1 int not null
+> ,t2_id2 int not null)
+>store by (t2_id1)
+>salt using 8 partitions
+>;

--- SQL operation complete.
>>load into tbl2
+>select c1+c2*10+c3*100+c4*1000+c5*10000
+> ,cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(10) character set iso88591)
+> ,c1+c2*10+c3*100+c4*1000+c5*10000
+> ,c1+c2*10+c3*100+c4*1000+c5*10000
+> ,c1
+> ,c1+c2*10+c3*100+c4*1000+c5*10000
+> ,c1+c2*10+c3*100+c4*1000+c5*10000
+>from (values(1)) t
+>transpose 0,1,2,3,4,5,6,7,8,9 as c1
+>transpose 0,1,2,3,4,5,6,7,8,9 as c2
+>transpose 0,1,2,3,4,5,6,7,8,9 as c3
+>transpose 0,1,2,3,4,5,6,7,8,9 as c4
+>transpose 0,1,2,3,4,5,6,7,8,9 as c5
+>;
Task: LOAD Status: Started Object: TRAFODION.ASCH.TBL2
Task: CLEANUP Status: Started Object: TRAFODION.ASCH.TBL2
Task: CLEANUP Status: Ended Object: TRAFODION.ASCH.TBL2
Task: DISABLE INDEXE Status: Started Object: TRAFODION.ASCH.TBL2
Task: DISABLE INDEXE Status: Ended Object: TRAFODION.ASCH.TBL2
Task: PREPARATION Status: Started Object: TRAFODION.ASCH.TBL2
Rows Processed: 100000
Task: PREPARATION Status: Ended ET: 00:00:11.232
Task: COMPLETION Status: Started Object: TRAFODION.ASCH.TBL2
Task: COMPLETION Status: Ended ET: 00:00:01.051
Task: POPULATE INDEX Status: Started Object: TRAFODION.ASCH.TBL2
Task: POPULATE INDEX Status: Ended ET: 00:00:02.736

--- 100000 row(s) loaded.
>>update statistics for table tbl2 on every column;

--- SQL operation complete.
>>
>>update statistics for table tbl1 on every column,
+>(t1_c1, t1_c2), (t1_c1, t1_c4), (t1_c2, t1_c3),
+>(t1_c3, t1_c4), (t1_c2, t1_c4)
+>;

--- SQL operation complete.
>>
>>prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 = t2_c2;

--- SQL command prepared.
>>
>>explain options 'f' XX;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

4 . 5 root 1.00E+000
3 . 4 sort_scalar_aggr 1.00E+000
2 1 3 hybrid_hash_join 2.50E+003
. . 2 trafodion_scan TBL1 1.00E+002
. . 1 trafodion_scan TBL2 1.00E+002

--- SQL operation complete.
>>exit;

SECOND SESSION:
>>set schema asch;

--- SQL operation complete.
>>prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 = t2_c2;

--- SQL command prepared.
>>
>>explain options 'f' XX;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

8 . 9 root 1.00E+000
7 . 8 sort_partial_aggr_ro 1.00E+000
6 . 7 esp_exchange 1:12(hash2) 1.00E+000
5 . 6 sort_partial_aggr_le 1.00E+000
4 2 5 hybrid_hash_join 1.00E+005
3 . 4 esp_exchange 12(hash2):8(hash2) 1.00E+006
. . 3 trafodion_scan TBL1 1.00E+006
1 . 2 esp_exchange 12(hash2):8(hash2) 1.00E+005
. . 1 trafodion_scan TBL2 1.00E+005

--- SQL operation complete.
>>
>>exit;

To reproduce:

step 1: initiate a sqlci session
drop schema asch cascade;
create schema asch;
set schema asch;

drop table tbl1;
drop table tbl2;

-- [a1] fact table; c1,c2,c3,c4 skewed
create table tbl1(
   t1_c1 integer unsigned
   ,t1_c2 char(10) character set iso88591
   ,t1_c3 largeint
   ,t1_c4 int
   ,t1_c5 int
   ,t1_id1 int not null
   ,t1_id2 int not null)
hash partition
store by (t1_id1)
salt using 8 partitions
;
load into tbl1
select c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
    ,cast(c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000 as char(10) character set iso88591)
    ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
    ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
    ,c1
    ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
    ,c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5
transpose 0,1,2,3,4,5,6,7,8,9 as c6
;

update tbl1 set t1_c1=7777 where t1_id1 between 3000 and 6999;
update tbl1 set t1_c1=7777 where t1_id1 between 205000 and 206999;
update tbl1 set t1_c1=7777 where t1_id1 between 410000 and 411999;
update tbl1 set t1_c1=7777 where t1_id1 between 615000 and 615999;
update tbl1 set t1_c1=7777 where t1_id1 between 820000 and 820999;
update tbl1 set t1_c2='7777' where t1_id1 between 2500 and 6499;
update tbl1 set t1_c2='7777' where t1_id1 between 204000 and 205999;
update tbl1 set t1_c2='7777' where t1_id1 between 411000 and 412999;
update tbl1 set t1_c2='7777' where t1_id1 between 614500 and 615499;
update tbl1 set t1_c2='7777' where t1_id1 between 820500 and 821499;
update tbl1 set t1_c3=888 where t1_id1 between 2000 and 7999;
update tbl1 set t1_c3=888 where t1_id1 between 205000 and 205999;
update tbl1 set t1_c3=888 where t1_id1 between 410500 and 411499;
update tbl1 set t1_c3=888 where t1_id1 between 615500 and 616499;
update tbl1 set t1_c3=888 where t1_id1 between 820500 and 821499;
update tbl1 set t1_c4=999 where t1_id1 between 3000 and 6999;
update tbl1 set t1_c4=999 where t1_id1 between 205000 and 206999;
update tbl1 set t1_c4=999 where t1_id1 between 410000 and 411999;
update tbl1 set t1_c4=999 where t1_id1 between 615000 and 615999;
update tbl1 set t1_c4=999 where t1_id1 between 820000 and 820999;

create table tbl2(
   t2_c1 integer unsigned
   ,t2_c2 char(10) character set iso88591
   ,t2_c3 largeint
   ,t2_c4 int
   ,t2_c5 int
   ,t2_id1 int not null
   ,t2_id2 int not null)
store by (t2_id1)
salt using 8 partitions
;
load into tbl2
select c1+c2*10+c3*100+c4*1000+c5*10000
    ,cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(10) character set iso88591)
    ,c1+c2*10+c3*100+c4*1000+c5*10000
    ,c1+c2*10+c3*100+c4*1000+c5*10000
    ,c1
    ,c1+c2*10+c3*100+c4*1000+c5*10000
    ,c1+c2*10+c3*100+c4*1000+c5*10000
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5
;
update statistics for table tbl2 on every column;

update statistics for table tbl1 on every column,
(t1_c1, t1_c2), (t1_c1, t1_c4), (t1_c2, t1_c3),
(t1_c3, t1_c4), (t1_c2, t1_c4)
;

prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 = t2_c2;

explain options 'f' XX;
exit;

step 2: Initiate a new sqlci session and issue:
set schema asch;
prepare XX from select count(*) from tbl1, tbl2 where t1_c1 = t2_c1 and t1_c2 = t2_c2;

explain options 'f' XX;

step 3: compare query plans generated in step 1 and 2.

Tags:

Barry Fritchman (barry-fritchman) on 2015-01-13

Changed in trafodion:
status:	New → In Progress

Julie Thai (julie-y-thai) on 2015-01-14

Changed in trafodion:
milestone:	r1.0 → r1.1
importance:	Critical → High

Revision history for this message

Barry Fritchman (barry-fritchman) wrote on 2015-02-02:

The problem is that the compiler has cached fake histograms for the table because they were requested before real histograms were created. The new histograms when created have to effect because the compiler has the fake ones already in its cache. The solution requires invalidation of cached histograms when new ones are created.

Atanu Mishra (atanu-mishra) on 2015-05-08

Changed in trafodion:
milestone:	r1.1 → r2.0

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.