It appears that when max_client_statements_cached = '10' (the default), SQL Anywhere 16.0.0.1823 is able to show the most recent LastPlanText but not the most recent LastStatement.

It is not surprising the behavior is different for the two properties since the LastPlanText value may or may not apply to the same query as LastStatement. LastStatement shows what came from the client application, whereas LastPlanText applies to the last query run by the server whether it came from the client or came from within a stored procedure.

HOWEVER, both are very useful when analyzing performance, and arguably, LastStatement is MORE USEFUL, at least initially when dealing with a runaway client connection.

If the server is smart enough to cache the plan, and show it in LastPlanText, why can't it show LastStatement?

Step 6 shows the undesirable behavior, while Step 12 shows how max_client_statements_cached = '0' is the workaround.

----------------------------------------------------------------
-- 1. start a new 16.0.0.1823 database

"%SQLANY16%\bin64\dbinit.exe" ddd16.db

"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -zl^
  -zp^
  -zt^
  ddd16.db

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=ddd16;DBN=ddd16;UID=dba;PWD=sql;CON=DBA"

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=ddd16;DBN=ddd16;UID=dba;PWD=sql;CON=test"

----------------------------------------------------------------
-- 2. run on the "DBA" connection...
SET OPTION PUBLIC.max_client_statements_cached = '10'; -- default
CREATE TABLE t1 ( pkey INTEGER PRIMARY KEY, data INTEGER );
CREATE TABLE t2 ( pkey INTEGER PRIMARY KEY, data INTEGER );
INSERT t1 VALUES ( 1, 0 );
INSERT t2 VALUES ( 1, 0 );
COMMIT;

----------------------------------------------------------------
-- 3. run on "test" connection...
UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

----------------------------------------------------------------
-- 4. run on DBA connection...
SELECT PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'LastStatement', 'LastPlanText' ) 
   AND CONNECTION_PROPERTY ( 'Name', sa_conn_properties.Number ) = 'test'
 ORDER BY PropName;

-- OK...

PropName,Value
'LastPlanText',( Update [B]\x0d\x0a  ( IndexScan t1 t1 )\x0d\x0a)\x0d\x0a
'LastStatement',update "t1" set "data" = "data"+3 where "pkey" = 1

----------------------------------------------------------------
-- 5. run on "test" connection...
UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
SELECT @@VERSION, 1;
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

----------------------------------------------------------------
-- 6. run on DBA connection...
SELECT PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'LastStatement', 'LastPlanText' ) 
   AND CONNECTION_PROPERTY ( 'Name', sa_conn_properties.Number ) = 'test'
 ORDER BY PropName;

-- Not OK...

PropName,Value
'LastPlanText',( Update [B]\x0d\x0a  ( IndexScan t1 t1 )\x0d\x0a)\x0d\x0a
'LastStatement',

----------------------------------------------------------------
-- 7. shut down the "test" connection

----------------------------------------------------------------
-- 8. run on DBA connection...
SET OPTION PUBLIC.max_client_statements_cached = '0';

----------------------------------------------------------------
-- . start a new connection with name "test"

----------------------------------------------------------------
-- 9. run on "test" connection...
UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

----------------------------------------------------------------
-- 10. run on DBA connection...
SELECT PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'LastStatement', 'LastPlanText' ) 
   AND CONNECTION_PROPERTY ( 'Name', sa_conn_properties.Number ) = 'test'
 ORDER BY PropName;

-- OK...

PropName,Value
'LastPlanText',( Update [B]\x0d\x0a  ( IndexScan t1 t1 )\x0d\x0a)\x0d\x0a
'LastStatement',update "t1" set "data" = "data"+3 where "pkey" = 1

----------------------------------------------------------------
-- 11. run on "test" connection...
UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

UPDATE t1 SET data = data + 1 WHERE pkey = 1; 
UPDATE t2 SET data = data + 2 WHERE pkey = 1; 
SELECT @@VERSION, 2;
UPDATE t1 SET data = data + 3 WHERE pkey = 1; 

----------------------------------------------------------------
-- 12. run on DBA connection...
SELECT PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'LastStatement', 'LastPlanText' ) 
   AND CONNECTION_PROPERTY ( 'Name', sa_conn_properties.Number ) = 'test'
 ORDER BY PropName;

-- OK...

PropName,Value
'LastPlanText',( Update [B]\x0d\x0a  ( IndexScan t1 t1 )\x0d\x0a)\x0d\x0a
'LastStatement',update "t1" set "data" = "data"+3 where "pkey" = 1