query stats are not published to METRIC_QUERY_TABLE if the query time is greater than STATISTICSLIMIT and smaller than STATISTICSINTERVAL

[Feng, Qiang]

Here are the parameters.
mxosrvr -ZKHOST n007.cm.cluster:2181 -RZ n007.cm.cluster:1:6 -ZKPNODE /trafodion -CNGTO 60 -ZKSTO 180 -EADSCO 0 -TCPADD 16.235.162.84 -MAXHEAPPCT 0 -STATISTICSINTERVAL 60 -STATISTICSLIMIT 1 -STATISTICSTYPE aggregated -STATISTICSENABLE true -SQLPLAN false -PORTMAPTOSECS -1 -PORTBINDTOSECS -1

This one was published
SQL>insert into VITO_TEST.tab1 select colint, 'a' from VITO_TEST.fun f where f.colint < 100000000;

--- 15625 row(s) inserted.

Start Time 2015/03/19 05:51:08.266558
End Time 2015/03/19 05:52:38.551538
Elapsed Time 00:01:30.284980
Compile Time 00:00:00.047735
Execution Time 00:01:30.236976

But this one was not in the table
SQL>insert into VITO_TEST.tab1 select colint, 'a' from VITO_TEST.fun f where f.colint < 100000;

--- 3125 row(s) inserted.

Start Time 2015/03/19 05:46:21.893948
End Time 2015/03/19 05:46:27.043882
Elapsed Time 00:00:05.149934
Compile Time 00:00:00.011382
Execution Time 00:00:05.138318

[Aruna Sadashiva (aruna-sadashiva)]

After discussions with dev, this is the understanding: The STATISTICSINTERVAL time does not apply to query statistics, it is the interval used for query aggregation to be published in METRIC_QUERY_AGGR_TABLE. For query stats, only STATISTICSLIMIT time should be used, if the query runs longer than the limit time, stats should get published in metric_query_table.

So, from the bug it looks like STATISTICSINTERVAL time is being used for query stats instead of STATISTICSLIMIT time.

[Feng, Qiang (qiang-feng)]

the test results with the latest build seems different.
Release 1.1.0 (Build release [1.0.0-224-gf78ef09_Bld7], branch f78ef09-master, date 20150322_083001

STATISTICSINTERVAL time is NOT used for query stats instead of STATISTICSLIMIT time.
STATISTICSLIMIT time works if it's set greater than 60.
But when it's set between 0 and 60, it works as 60.

[Feng, Qiang (qiang-feng)]

add specific scenarios for this issue with type 'default/aggregated'.

1. interval.time = 1, limit.time = 120
    a. aggr stats works as expected.
    b. query stats works as expected.
        query ran for 01:32 did not publish.
        query ran for 02:24 published.

2. interval.time = 1, limit.time = 5
    a. aggr stats works as expected.
    b. query stats works as expected.
        query ran for 01:30 published.
        query ran for 00:07 published.

3. interval.time = 60, limit.time = 5
    a. aggr stats works as expected.
    b. problem with query stats. Looks it took 60 instead.
        query ran for 01:29 published.
        query ran for 00:28 did not publish.

4. interval.time = 180, limit.time = 120
    a. aggr stats works as expected.
    b. problem with query stats. Looks it took 180 instead.
        query ran for 03:41 published.
        query ran for 02:20 did not publish.

5. interval.time = 60, limit.time = 120
    a. aggr stats works as expected.
    b. query stats works as expected.
        query ran for 01:34 did not publish.
        query ran for 03:43 published.

6. interval.time = 1, limit.time = 0
    a. aggr stats works as expected.
    b. problem with query stats. Looks it took 60.
        query ran for 00:25 did not publish.
        query ran for 01:28 published.

7. interval.time = 1, limit.time = -1
    a. no aggr stats.
    b. no query stats. Query ran for 01:35 did not publish.
    c. no session stats.

8. interval.time = -1, limit.time = 1
    a. no aggr stats.
    b. no query stats. Query ran for 01:35 did not publish.
    c. no session stats.

9. interval.time = 0, limit.time = 0
    a. aggr stats works as default 60.
    b. query stats works as default 60.
        query ran for 00:28 did not publish.
        query ran for 01:35 published.

[Judy Zhao (hongxia-zhao)]

Please check here for the fixing <https://review.trafodion.org/#/c/1707/>