Updated: Intra-query ApproximateCPUTime problems

[Breck_Carter]

Update: The situation remains the same for 16.0.0.1691. For example, when a parent connection has 8 INT:EXCHANGE child connections associated with it, and they are consuming 90% of the CPU on a 8-processor chip, the parent connection shows zero CPU time and each of the INT:EXCHANGE child connections shows a huge value (e.g., 6m 50.0s) for the CPU time when the clock-on-the-wall time connected is only 1m 0.2s.

Here's what it looks like in Foxhound:

Such a display is meaningless to the end user... a workaround for Foxhound may be to randomly pick one of the child connections to provide the CPU time for the parent, and otherwise dispense with displaying the child connections altogether.

---

The following test demonstrates two problems with the ApproximateCPUTime property and intra-query parallelism in SQL Anywhere 12.0.1:

(a) The connection-level ApproximateCPUTime property for all the INT: Exchange connections is the same, and far too large; i.e., each connection value is roughly the same as the server-level ProcessCPU property in a test where the parallel query is the only work the server has done since it was started.

(b) The server-level ApproximateCPUTime property deviates widely from the ProcessCPU property; i.e., it is far too small.

The test involves two ISQL connections, the first to run the SELECT COUNT_BIG(*), and the other one to query the properties.

SELECT COUNT_BIG(*) 
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSCOLUMN AS C;

-- Test 1 on 12.0.1.3298

SELECT @@VERSION, * FROM sa_conn_properties() WHERE PropName IN ( 'ApproximateCPUTime' ) AND Number > 1000000000 ORDER BY Number;
SELECT @@VERSION, * FROM sa_eng_properties() WHERE PropName IN ( 'ApproximateCPUTime', 'ProcessCPU' );

@@VERSION,Number,PropNum,PropName,PropDescription,Value
'12.0.1.3298',1000000020,377,'ApproximateCPUTime','Approximate CPU time used',1393.4789632
'12.0.1.3298',1000000021,377,'ApproximateCPUTime','Approximate CPU time used',1393.4789632

@@VERSION,PropNum,PropName,PropDescription,Value
'12.0.1.3298',288,'ProcessCPU','Process CPU usage',1394.071736
'12.0.1.3298',377,'ApproximateCPUTime','Approximate CPU time used',5.616128

-- Test 2 on 12.0.1.3797 (most recent EBF)

SELECT @@VERSION, * FROM sa_conn_properties() WHERE PropName IN ( 'ApproximateCPUTime' ) AND Number > 1000000000 ORDER BY Number;
SELECT @@VERSION, * FROM sa_eng_properties() WHERE PropName IN ( 'ApproximateCPUTime', 'ProcessCPU' );

@@VERSION,Number,PropNum,PropName,PropDescription,Value
'12.0.1.3797',1000000030,382,'ApproximateCPUTime','Approximate CPU time used',213.1718144
'12.0.1.3797',1000000031,382,'ApproximateCPUTime','Approximate CPU time used',213.1874816

@@VERSION,PropNum,PropName,PropDescription,Value
'12.0.1.3797',293,'ProcessCPU','Process CPU usage',213.859375
'12.0.1.3797',382,'ApproximateCPUTime','Approximate CPU time used',.8283136