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

asked 29 Mar '14, 12:22

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%


The LastStatement is saved when a statement is prepared, which is the only time the server has the full SQL text of the statement. The LastStatement is cleared when a statement is dropped by the client (which may just be putting the prepared statement in a cached state when client statement caching is used). When client statement caching is enabled, preparing the statement on the client often means just re-using a cached prepared statement, in which case LastStatement is not updated since the server doesn't have the full SQL text of the statement at that point.

Note that this behaviour is documented in the LastStatement property description (and I assume you already know that given the question you are asking).

permanent link

answered 31 Mar '14, 09:46

Ian%20McHardy's gravatar image

Ian McHardy
2.9k23049
accept rate: 38%

Comment Text Removed

Assumptions are soooo dangerous! :)

Even the 10.0.1 Behavior Changes has this: "When client statement caching is enabled and RememberLastStatement is enabled (-zl server option), the LastStatement property is the empty string when reusing a cached statement."

Ommmm! RTFM! Ommmm! RTFM! Ommmm! ... :)

alt text

(31 Mar '14, 11:44) Breck Carter
Your answer
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:

×242

question asked: 29 Mar '14, 12:22

question was seen: 403 times

last updated: 31 Mar '14, 11:49