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:

alt text

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

asked 10 Nov '12, 17:15

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 24 Nov '13, 17:01

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 10 Nov '12, 17:15

question was seen: 3,674 times

last updated: 24 Nov '13, 17:01