cancel
Showing results for 
Search instead for 
Did you mean: 

Updated: Intra-query ApproximateCPUTime problems

Breck_Carter
Participant
3,852

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

Accepted Solutions (0)

Answers (0)